Fix Mysql Workbench Synchronization Issues on Mac

Fix Mysql Workbench Synchronization Issues on Mac

Update (8/7/2012): After setting up a new OS X (Lion), I had installed MySQL via MacPorts and just moved my db files over to the case sensitive partition.  That works fine (been using it this way for a month).  With exception to the note below, I didn’t have to go through the trouble of moving the entire MySQL install.  So in my case,  I moved /opt/local/var/db/mysql5 to my other partition /Volumes/Macintosh HD 2/mysql5.  Then created a symbolic link pointing from the old to the new location.  That’s it.

Original (2010):
MySQL Workbench doesn’t play nice with case insensitive file systems when you try to use the db synchronization feature on schemas with mixed case table names.  …And the lower_case_table_names option in MySQL doesn’t quite fix it.

Ultimately, I decided to copy and move MySQL over to a case sensitive partition that was created.  The result… MySQL Workbench db synchronization now works as expected.

Also, I didn’t have to worry about crapping up anything since I was just making a copy of MySQL and pointing the “mysql” sym link to it’s new location;  plus it only takes 10 minutes to setup.

To get started (this may vary slightly on your OS version):

  1. Create a partition using Applications>>Utilities>>Disk Utility.  Make sure under “Format” you select a “Case-sensitive” format.  I went with “Mac OS Extended (Case-Sensitive, Journaled)”.
  2. Stop MySQL if it’s running:
    $  sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
  3. Copy your current MySQL install to the new partition:
    $ sudo cp -rp /usr/local/mysql  /Volumes/Macintosh/NewPartition
  4. Point the symbolic link “mysql” in /usr/local/mysql to the new location:
    $ sudo ln -sf /Volumes/Macintosh/NewPartition/mysql  /usr/local/mysql
  5. Start MySQL back up:
    $  sudo /Library/StartupItems/MySQLCOM/MySQLCOM start
  6. You’re finished. If it doesn’t work for you, you can always point your “mysql” sym link back to your original install and restart.

If you run $ mysqladmin -u root -p variables      …you’ll see that “lower_case_table_names” automatically sets to zero (case sensitive setting).

Note: You may have to drop and rebuild any schemas/tables that you copied over.  This seems to only apply to schemas with linked tables (the “information_schema.REFERENTIAL_CONSTRAINTS.REFERENCED_TABLE_NAMES.” column has case insensitive values which MySQL may choke on).