Removing lines and trailing commas from mysqldump

Question

I'm trying to remove constraints from a mysqldump before piping it into another SQL database. Mysqldump generates tables that look something like this with 1 or more constraints:

CREATE TABLE `SOME_TBL` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'blah',
  /* ... */
  PRIMARY KEY (`ID`) USING BTREE,
  CONSTRAINT `SOME_TBL_FC1` FOREIGN KEY (`SOME_FIELD`) REFERENCES `SOME_OTHER_TBL` (`ID`),
  CONSTRAINT `SOME_TBL_FC2` FOREIGN KEY (`ANOTHER_FIELD`) REFERENCES `ANOTHER_TBL` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3845453 DEFAULT CHARSET=utf8 COMMENT='data lives here';

I can delete the constraint lines like so:

mysqldump --source-database \
  | sed -E '/^ *CONSTRAINT/d' \
  | mysql --result-database

But then I'm left with trailing commas, for example:

CREATE TABLE `SOME_TBL` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'blah',
  /* ... */
  PRIMARY KEY (`ID`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=3845453 DEFAULT CHARSET=utf8 COMMENT='data lives here';

...which SQL doesn't like. (note the comma after "BTREE" above). Note I do no not want to remove all trailing commas, only those trailing commas that appear before a newline and close paren (something like ,\n))

What's the easiest way to delete these trailing commas and the constraint lines mid-stream? Note that the dumps might be several thousand megabytes, therefore I can't simply slurp the whole file into perl or something; I want to be able to do this as part of a pipeline.

I could probably run some SQL afterwards that uses INFORMATION_SCHEMA to delete the constraints, but I'm wondering if there's a more elegant way to do this using only text-processing tools?

Answer 1

No reason why you can't use Perl in a pipeline. If we assume \r (non-DOS) line endings and program is:

#!/usr/bin/perl
unless( defined $previous && length $previous ){
    $previous = `$_`;
    redo LINE;
}

if( m/CONSTRAINT/ ){
    $previous =~ s/\,\s*\n$/ \n/;
    next LINE;
}

print $previous;
$previous = `$_`;

END{
    print $previous;
}

Then cat dump.sql | perl -n program.pm | and so on does the trick.