«
»

I had an issue recently when I was working with a couple other developers on a project. We keep our MySQL database in sync by exporting the database as an SQL script and sharing it in a subversion repository (I wish MS SQL could be this easy!). Our table names are saved with mix case, like “userFiles”, but everytime I would export my database the tables would come out all lower case, like “userfiles”.

Turns out this is a simple thing to fix, I had to add a line to MySQL’s config file: my.cnf. Mine was located at [MySQL installed root]\bin\my.cnf. The setting that needs to change is lower_case_table_names, I set the value to 2, like this:

lower_case_table_names=2

If you are interested, here are the different possible values:

Value Meaning
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. Note that 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.

On Windows the default is 1.

More information at http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

This entry was posted on 3 January 2010 at 11:24 am and is filed under SQL, System Administration. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

2 Comments

  1. Jake says:

    I'm really glad you found this.  It helps a lot

    4 January 2010, 9:21 am

  2. Mark says:

    I had no clue this existed. It pissed me off many time but I guess it didn't bother me enough to lookup a solution for it 🙂

    13 April 2011, 7:59 am