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).
One thing to 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).
Hi Rob,
Thank you very much.
Brad, Yaar@Kyayaar
http://www.kyayaar.com