0

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?

  • This might help: stackoverflow.com/questions/2429655/… , but I have not tried it, so I won't put it in an answer – Bruno9779 May 15 '17 at 19:32
  • @Bruno9779 - Yes, this turns them off and on during the insert process. However, I'm going to be altering the table and dropping columns. As long as the constraints were created in the first place, I can't drop any columns they reference. – Robert Fraser May 15 '17 at 22:58
0

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.

  • Finally got the code quotation above to come out right. I hadn't realized that the code quoting mechanism on this site is the one that uses white-space as various different operators - depending on context. – Nadreck May 18 '17 at 5:31
  • Thanks! I ended up having to do it in SQL anyways since it turns out we needed some of the indexes after all, but I'll keep this script around since it looks like a useful thing to have anyways. – Robert Fraser May 18 '17 at 13:16
  • 2
    BTW, to format code on stack exchange, either precede each line nby a tab/four spaces, or highlight it and click the "code" thing on the top of the editor (looks like two curly braces). – Robert Fraser May 18 '17 at 13:21
  • Thanks! I had finally figured that out by digging in the <i>Help Centre</i>. I knew that there had to be an easier way as all of the people editing my code quotes seemed to have no trouble at all and without resorting to HTML formatting, – Nadreck May 18 '17 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.