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.

0 – 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.

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

12 thoughts on “Force MySQL to be case sensitive in Windows

  1. Roger Bensont

    Thanks for this post. I was tearing my hair apart trying to fix this issue. I’m surprise MySql doesn’t offer this as a configurable option thru Preferances

     
    Reply
  2. Edgar

    This can be configured with the MySQL Workbench too. Menu Server > Options File > General

     
    Reply
    1. Karthic Raghupathi

      Yeah it should be safe. But keep in mind, linux is case sensitive so make sure you are using the appropriate case when running the CREATE DATABASE or CREATE TABLE statements.

       
      Reply
  3. krip

    I know that we need to put lower_case_table_names=2 into php.ini but in which section? Can I just put at [mysqld] ?

     
    Reply
  4. Dan Dar3

    Just tried it with MySQL 5.7.18 and 8.0.1 and running it from command line I can see the error below:

    2017-05-17T06:08:46.375594Z 0 [ERROR] The server option ‘lower_case_table_names’ is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode.
    2017-05-17T06:08:46.376999Z 0 [ERROR] Aborting

     
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *