MySQL case sensitive table names on Linux

Question

Moving a MySQL database from Windows to Linux I have the problem that on Linux the names of the tables are case sensitive. This is a problem, because the Java application that I am developing can't find tables.

I have changed my /etc/mysql/my.cnf file adding the row:

lower_case_table_names=1

But that did not change anything.

My server version is: 5.1.61-0ubuntu0.11.10.1 (Ubuntu)

How can I configure MySQL to ignore case in table names?

• Neveroldmilk: I find the most sounding solution to this issue is to write all table names case-sensitive in the code!
• Vitaly Olegovitch: @Neveroldmilk that would be nonstandard: future developers expect that sql in not case sensitive.

Answer 1

Just altering the lower_case_table_names setting isn't enough. It needs to be done before you import your database(s).

The MySQL 5.1 documentation lists a procedure for moving between Windows and Linux/UNIX. This will ensure that your desired rules for enforcing case sensitivity are followed. Take a look and verify that you did these steps in the correct order:

To convert one or more entire databases, dump them before setting lower_case_table_names, then drop the databases, and reload them after setting lower_case_table_names:

1 - Use mysqldump to dump each database:

mysqldump --databases db1> db1.sql

mysqldump --databases db2> db2.sql

... Do this for each database that must be recreated.

2 - Use DROP DATABASE to drop each database.

3 - Stop the server, set lower_case_table_names in the [mysqld] section of your \etc\mysql\my.cnf file, and restart the server.

4 - Reload the dump file for each database. Because lower_case_table_names is set, each database and table name will be converted to lowercase as it is recreated:

mysql < db1.sql

mysql < db2.sql

• Pacerier: Does mysqldump do a perfect dump? Will any of the data change if we export-drop-import?

Answer 2

File in etc/my.cnf Find this # The MySQL server [mysqld] and set lower_case_table_names = 1

There is no need to drop db. It works so first check with this. Reason is default value of lower_case_table_names =1 for Windows.