alter mysqldump file before import

Question

Hi--

I have a mysqldump file created from an earlier version of a product that can't be imported into a new version of the product, since the db structure has changed slightly (mainly altering a column that was NOT NULL DEFAULT 0 to UNIQUE KEY DEFAULT NULL).

If I just import the old dump file, it will error out since the column that has default values of 0 now breaks the UNIQUE constraint.

It would be easy enough to either manually alter the mysqldump file, or import into a temp table and change it, then copy to the new table. However, is there a way to do this programatically, so it will be repeatable and not manual? (this will need to happen for many instances of this product).

I'm thinking something like disabling key constraints for the import, then setting all values that = 0 to NULL, then re-enabling the key constraints?

Is this possible? Any help appreciated.

Answer 1

You could just use sed and modify the dumpfile in an automated, repleatable way.

sed s/NOT NULL DEFAULT 0/UNIQUE KEY DEFAULT NULL/g

or something like that.

Answer 2

Yes.

SET UNIQUE_CHECKS=0; Turns off Unique Key Constraints
SET FOREIGN_KEY_CHECKS=0; Turns off Foreign Key Constraints

Import file Update 0 to Null

SET UNIQUE_CHECKS=1 turns back on
SET FOREIGN_KEY_CHECKS=1 turns back on