Bug #66147 Working with existing Linux dbs in Win, lower-case table names are problematic
Submitted: 1 Aug 2012 22:46 Modified: 1 Aug 2012 22:49
Reporter: Chuck Renner Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:ALL OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: lower-case case-insensitive table database names windows

[1 Aug 2012 22:46] Chuck Renner
Description:
When working on MySQL databases on cross-platform systems, the lack of mixed-case database and table names can created undesired behavior.

Always presenting table names on Windows as lower-case is a real pain, especially when you are working with existing database schema (and not developing your own).

How to repeat:
Use mysqldump on a Linux MySQL server to dump the entire contents of a single database (schema) that contains tables with mixed-case names.  Assume that the source has been in production for a while, and altering the existing schema to convert all table names to lower-case is NOT an option.

Take the resulting .sql file on a Windows system with MySQL installed, and run it, importing the entire database (schema).

All of your tables now have lower-case names.

Use UPDATE on one or more tables to change some of the records in those tables.

Now use mysqldump on the Windows system to dump the entire contents of the altered database to a file.

Take the resulting .sql file on the Linux system, and run it, importing the entire database (schema).  Instead of dropping the existing tables to import the new ones, the new ones will be imported on the Linux system with lower-case table names, ignoring the mixed-case tables entirely.

This is not the only problem with the lack of mixed-case database and table names. There are many more, but this is a simple example for testing purposes.

Suggested fix:
For those of developing on Windows machines, but exporting to Linux machines for production, it would be extraordinarily helpful to have a new option (value 3) that would store the files with mixed-case, and preserve that same mixed-case for the table names (in spite of the fact that Windows has a case-insensitive file system).  That way we can have mixed-case table names that will be presented as mixed-case, and preserved when exported to MySQL on Linux platforms.

If you did this (and please do), you would have to put the extra safety step into the Windows platform that would compare file names present with table names being created (in a case-insensitive manner).
[1 Aug 2012 22:49] Chuck Renner
I forgot to mention in my suggestion, that I am suggesting adding a 4th option with a value of 3 to the option/variable lower_case_table_names that stores table names in mixed-case in Windows, in-spite of the case-insensitive file system.