Mixed Case Table Names
Mixed case tables names (e.g. wp_Custom_Table) are potentially problematic in certain environments where the MySQL setting lower_case_table_name is set to 1.
See MySQL’s lower_case_table_name documentation for a full explanation of what the setting does.
In short …
If set to 0, table names are stored as specified and comparisons are case sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. If set to 2, table names are stored as given but compared in lowercase. The default value is 0. On Windows the default value is 1. On Mac OS X, the default value is 2.
Scenario
You have two sites, a live production site (Site A) and a local development site (Site B).
Your production environment is running a Linux distribution and has lower_case_table_name set to 0.
Your local development environment is running Windows and has lower_case_table_name set to 1.
You initiate a pull from Site A into Site B. The migration completes successfully but the Site A table wp_Custom_Table is renamed to wp_custom_table as a result of the lower_case_table_name setting of 1 on Site B.
You continue development on Site B and decide to push those changes back up to Site A. The migration completes successfully but you now have a duplicated table, i.e. both wp_Custom_Table and wp_custom_table exist on Site A. The changes made on Site B will be ignored as Site A is configured to make case sensitive comparisons and will typically perform queries on the outdated wp_Custom_Table table.
Recommendations
The easiest way to circumvent this problem is to always exclusively work with lower case table names. If this isn’t a viable solution you’ll need to ensure that your environments are compatible with each other.
The following environment setups should work in harmony with each other:
- Windows – Windows
- Linux – Linux
- Mac OS – Mac OS
- Linux – Mac OS
- Mac Os – Linux
Please contact us at nom@deliciousbrains.com if you’d like any further explanation on this topic.