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 16:24	Modified:	23 Jan 2008 15:08
Reporter:	Trey Howard
Status:	Not a Bug
Category:	Server: Backup	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Microsoft Windows
Assigned to:		Target Version:

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