php - 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.