Running as Root

Here's a quick note on migrating data from MS SQL Server to MySQL. I found out in a white paper entitled A Practical Guide to Migrating From Microsoft SQL Server to MySQL (free registration required) that the MySQL install (on Windows at least) includes a tool called the MySQL Migration Toolkit.

It has a simple wizard that steps you through all of the options and you're ready to migrate. You can even choose to migrate "live" or save the scripts to files. One minor annoyance is that you can't pick the name of the target database/schema. When migrating from a SQL Server database named "blahblah" with default schema of "dbo", the migration tool will create "blahblah_dbo" in MySQL. I thought I would just rename it only to find that support for RENAME DATABASE has been dropped.

Instead, I decided to just do a backup and restore said backup to a new schema. I then ran into a problem with one of our badly designed tables that has large (not huge) amounts of BLOB data. The restore died with an error saying "MySQL Server has gone away." I finally tracked down the issue which can be fixed with a small configuration change. Since I was testing this in MySQL for Windows, I just added max_allowed_packet=12M to the my.ini under the server directory and bounced the service. Everything worked nicely after that. Your size on that configuration option may vary, of course.

This entry was posted on Tuesday, July 22nd, 2008 at 12:25 pm and is filed under Programming, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.