Tuesday, December 20, 2011

MySQL Schema Diffs

MySQL Schema Diffs. This particular issue crops up at the end of every project, so this is more of a note to self. You might find it handy, too.

Initially, I used to do it the hard way. A PHPMyAdmin export of the old schema (from the production release), and a similar export from the development release. Then do a compare, usually in Eclipse using the Compare To Each Other, and manually parse the file to generate a migration script. Yeck.

For a while, I was using DBSync, an online tool that took the two MySQL SQL dumps as input, and would generate SQL to perform a diff. While it worked, it worked fairly well, though for the last year, it has been reporting an error when trying the create the first database. Your mileage may vary.

Failing that, I had to go back to hand written SQL, however, every time I come across the issue, I do a little more research, to see if anything has changed.

Well, this time I got lucky. It turns out that the MySQL Workbench can generate the schema diffs I'm after. It's just a hard option to find. I actually found a guide at Simpcode that covers 5.0.22. I'm working with 5.2.36 CE, so here's how I do it.

  1. Get the scripts of the databases that need to be compared. I still fall back to using the PHPMyAdmin SQL dumps, making sure to not export the AUTO_INCREMENT value.

  2. Run Workbench. Close any Physical Schemata, if any.

  3. Select Create New EER Model to open the MySQL Model view.

  4. Select File > Import > Reverse Engineer MySQL Create Script...
    Select the SQL script for the new database, and select Execute.
    When the populated database view is shown, you may need to select a table in the view. Occasionally, I've found that I don't end up with the diff that I'm after. The 'default_schema' schema, generated by this import ends up getting dropped, and replaced with a 'new_schema' or 'my_schema' schema, created by the next step.

  5. Select File > Export > Forward Engineer SQL ALTER Script...
    Select the SQL script for the old database as the Input File.
    Leave the Output File blank, and select Next.

  6. After a couple of seconds, a script containing the differences will be created.
    Select Copy to Clipboard to copy this. Paste as a migration script.
    Select Cancel to close the window.

    If you don't have a proper diff, it is possible that an existing schema may have been opened. In this case, close Workbench, and start again.

  7. Remove the schema name from the CREATE and ALTER TABLE commands.
    Insert newlines before the ADD COLUMN clauses for readability.
    Double check any DROP INDEX clauses to make sure they are not accidentally being removed.
Even though I'm using CakePHP (only 1.3 in this particular instance), and Capistrano for deliveries, I'm not comfortable in handing over my schema migrations to an automated process that could be used in CakePHP and Capistrano.

No comments:

Post a Comment