How to enforce case sensitive table and column names in MySql?

Question

The thing is we are working on windows machine and once done we deploy the code on unix machine. The code works fine on windows but then in unix we get the error like 'no such table exists' after changing the table name in correct case it works fine in unix too. Actually, in windows there is no case sensitive table names by default but in unix they do have ( read that MySQL tables are actually files and in unix we have case sensitive file name but not in windows ). Workaround could be to create all tables again and lets have the table name in lowercase. Yes, we can do that too, thats fine.

But, still can we impose case sensitivity on table names in MySql ( windows machine ). If yes, then please let me know how to do that.

Answer 1

The setting is called lower_case_table_names. If you set it to 0, comparisons will be case sensitive.

However,

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.

making all table names lowercase across all systems (including Linux), ie. setting it to a value of 1, sounds like the better option:

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.

Answer 2

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 is 1 before MySQL 4.0.18 and 2 as of 4.0.18.

To fix this, you can look for the setting: lower_case_table_names in your my.ini file, found in or around: C:\Program Files\MySQL\MySQL Server 4.1, depending on which version you are running. If you don't find the setting, you can just add it to the end of the my.ini file, as I did, like so:

lower_case_table_names=0

Remember to restart the MySQL service before you test whether or not it works.

If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows those names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.

Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

Exception: If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

If you plan to set the lower_case_table_names system variable to 1 on Unix, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting.

Please check out the MySQL website for additional information about this, and some important warnings http://dev.mysql.com/doc/refman/4.1/en/identifier-case-sensitivity.html

Answer 3

(This Answer does not give you a clear path to solving the problem, but provides more insight.)

Mac OS has different considerations than Windows:

9.2.2 Identifier Case Sensitivity In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Triggers also correspond to files. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. This means such names are not case-sensitive in Windows, but are case-sensitive in most varieties of Unix. One notable exception is macOS, which is Unix-based but uses a default file system type (HFS+) that is not case-sensitive. However, macOS also supports UFS volumes, which are case-sensitive just as on any Unix. See Section 1.8.1, “MySQL Extensions to Standard SQL”. The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.

-- https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

From some bug reports:

Failure to fully convert table names to lowercase caused errors later when attempting to rename tables. (esp partitioned tables)

Table names were not compared in lowercase on macOS with a setting of lower_case_table_names=2, which caused instability after restarting the server. (Bug #28170699, Bug #91204)

An important note for MySQL 8.0:

It is now prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared. (Bug #27309094, Bug #89035)

When I last faced the problem I decided to do the arduous task of lowercasing all the table names and changing all the code. Sorry, I don't have a better way.