Force MySQL to be case sensitive in Windows

I develop on Windows / Linux and deploy on Linux. I always run into the issue of my tables having different cases on my development and production environments causing stupid time wasting errors. If only everything in this world was nice. Hold your breath! There is a work around.

I don’t know why this is not default by design but according to the Identifier Case Sensitivity section in the manual:

How table and database names are stored on disk and used in MySQL is affected by the lower_case_table_names system variable, which you can set when starting mysqld. lower_case_table_names can take the values shown in the following table. This variable does not affect case sensitivity of trigger identifiers. On Unix, the default value of lower_case_table_names is 0. On Windows the default value is 1. On Mac OS X, the default value is 2.

**** - Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you force this variable to 0 with -lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.

1 - Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.

2 - Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1.

The best option to simulate this behavior in Windows is to set lower_case_table_names = 2 in your my.ini file. In my Windows 8 for a MySQL 5.6 server install, this file is located at C:\ProgramData\MySQL\MySQL Server 5.6\my.ini. Depending on your installation, this path my be a little different. Open the file and update the value of the lower_case_table_names variable or if it is not present just add the value to the end of the file.

# Force case sensitive storage but case insensitive lookup
lower_case_table_names=2

Remember to restart the MySQL service in Windows by running services.msc. Grab a beer and keep rocking!