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):
- 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)”.
- Stop MySQL if it’s running:
$ sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop - Copy your current MySQL install to the new partition:
$ sudo cp -rp /usr/local/mysql /Volumes/Macintosh/NewPartition - Point the symbolic link “mysql” in /usr/local/mysql to the new location:
$ sudo ln -sf /Volumes/Macintosh/NewPartition/mysql /usr/local/mysql - Start MySQL back up:
$ sudo /Library/StartupItems/MySQLCOM/MySQLCOM start - 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).