MySQL Bugs: #33898: mysqldump from Windows database has lowercase table names

Bug #33898	mysqldump from Windows database has lowercase table names
Submitted:	17 Jan 2008 15:24	Modified:	23 Jan 2008 14:08
Reporter:	Trey Howard	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When performing a MySQL dump from a Windows database (doesn't matter which OS executes mysqldump), the output contains lowercase table names in all FOREIGN KEY declarations.
For example:
CREATE TABLE Campus (...);
CREATE TABLE Other (
   ...
   CONSTRAINT `FKC1228336F55B91A6` FOREIGN KEY (`defaultCampus`) REFERENCES `campus` (`id`)
);

So long as this database is only imported into other Windows databases, everything will work just fine. However, this database will not work in a Linux installation. Worse yet, no errors are thrown when importing the SQL file. Instead, a runtime error occurs when a value is inserted into an affected column.

Among other issues, bug 1528 brought this bug to your attention in version 4. A developer responded that no such fix could be applied before version 5. Note that this bug is filed against version 5.0.45.

How to repeat:
To reproduce:
1. Create a database with camel-cased table names on a Windows database.
2. Perform a mysqldump on that database.
3. Import that database into a Linux database.
4. Perform an INSERT operation that assigns a non-NULL value to a FOREIGN KEY column.

Suggested fix:
As mentioned in the discussion of bug 1528, MySQL needs to move away from coupling the case-sensitivity of table names to the underlying operating system. Bugs such as this will continue to plague the product until such a fix is made.

Couldn't this be overcome using lower_case_table_names=2?

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

I tested this out on 5.0.45 on Windows and it preserved the case of statements used in creating my tables on running mysqldump.

While it's not elegant and requires additional startup parameters, there is a way to avoid conversion to lowercase.

Technically speaking, yes.
Beyond not being elegant, this is not satisfactory for our purposes. We often have to deal with customer databases, and we have no control over the configuration parameters of customer databases.

Can you be more specific on why conversion to lowercase cannot be avoided? Do you mean that this cannot be avoided for MySQL5? Is it a planned improvement for a future release (eg: MySQL6)? Surely there is a way to fix this, even if the fix is non-trivial.

I think I mis-spoke before. You had suggested lctn=2, but I thought you mean lower_case_table_name=1. I don't believe lctn=2 will solve the problem of dumping from the Windows box and importing into a Linux box. Isn't setting 2 only available for the Windows box (as per the documentation)?

Furthermore, I'm confused by the documentation because I am running a Windows server with lctn=0, but this does not seem to be enforced. The FKs in my database display via SHOW CREATE TABLE with the lowercase table name, but work just fine. The docs for lctn=0 indicate that the comparison is made case-sensitively, which should prevent my database from behaving as observed, correct? What differences should I expect to see between lctn=0 and =2?

Using lctn=2 still seems to convert to lowercase while running on Windows, but will preserve the original case on mysqldump -- Windows-based server behaves as lctn=1 while on Windows.

When you use mysqldump when lctn=2, the output will exhibit the original case of your SQL statements --

CREATE TABLE tEsTcAsE (Column_1 int);

Will be the mysqldump output even if the windows machine accepts testcase, TESTCASE, etc.

I can't speak for any planned changes to this behavior, I was only trying to suggest something that would help.

Thank you for the report.

But this is not a bug. Read about lower_case_table_names and Windows at http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html

Same behaviour on OS X as well ;(