MySQL Case Sensitive Tables Conversion

Question

I have a PHP codebase that was written to address our MySQL tables in mixed case. For example, xar_intakeformgenerator_ChangeLog.

Our code also runs on windows, and before we knew any better, we imported several databases on to a Windows server. This resulted in Windows MySQL changing all the table names to lower case. (xar_intakeformgenerator_changelog). We now know how to prevent that from happening to new databases. (Set lower_case_table_names) And, the code runs fine on the Windows servers because MySQL just doesn't care about the case of the tables on Windows.

Here's the problem. The Windows server is giving us grief and we need to move all of the databases off to a Linux server. Because all of the table names have converted to lower case, the code will NOT work on Linux. Luckily, Xaraya creates table mappings. So, in theory, I could set up a new code base for these databases and change the mappings of every module to use the lower case tables. Or, we could manually change the table names after we import them onto the Linux machine to get the table case correct.

changing lower_case_table_names does not correct the databases that were mangled before the flag was set. These all have the lower case table names.

I'm not wild about either option. Does anybody know an ingenious way to handle this?

Answer 1

OK. I found my answer.

On the Linux server, I needed to run the following to change all the table names in my Linux generated databases to lower case:

1. How to produce a SQL script that renames all tables in a schema to its lower case form:

   select concat('rename table ', table_name, ' to ' , lower(table_name) , ';') 
   from information_schema.tables where table_schema = 'your_schema_name';
   

2. Renamed the databases in phpmyadmin to lowercase names.

3. Modified the my.cnf on the Linux server to use lower_case_table_names=1

4. Restarted mysql.

After this, my code would work with the lower case table names. So, I was able to import the Windows ones and have the same code base work on both.

Answer 2

If I remember correctly (had the same kind of problem a while back -- but I stopped working on that project before we decided which solution to adopt...), there is a configuration option which says how tablenames should be used (case-sensitive or not case-sensitive).

Here's what I found : Identifier Case Sensitivity

Quoting that page :

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 these two 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.

(There is more I didn't copy-paste ; so going to read that page might be a good idea ;-) )

Hope this will help...

Answer 3

lower_case_table_names

For Windows, it defaults to "make everything lower case" (1). Set it to 2:"Case insensitive but keep the case as it is".

These changes go into your my.cnf