MySQL Bugs: #6555: Dumping a table with a FOREIGN KEY ignores --lower_case_table

Bug #6555	Dumping a table with a FOREIGN KEY ignores --lower_case_table_names=0
Submitted:	10 Nov 2004 17:43	Modified:	15 May 2019 10:37
Reporter:	Federico Chiesa	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	6.1,4.021	OS:	Windows (Windows (XP))
Assigned to:		CPU Architecture:	Any

Description:
When dumping a database, the FOREIGN KEY CONSTRAINTS report the table names REFERENCE ignoring the --lower_case_table_names=0 directive.

The REFERENCE clause always reports tables with all lower case letters, even if the table has upper case letters in its name.

How to repeat:
Start mysql with --lower_case_table_names=0 on a Windows machine.

Create an empty database.

Create an INNODB table named motherTable (yes, the T is uppercase)
Create an ID field in it, primary key

CREATE TABLE `motherTable` (
  `ID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) TYPE=InnoDB;

Create an INNODB table named childTable
Create an ID field in it, primary key
Create a field called motherID
Create an index on this field to allow the FOREIGN KEY to be created

CREATE TABLE `childTable` (
  `ID` int(11) NOT NULL default '0',
  `motherID` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `motherID` (`motherID`)
) TYPE=InnoDB;

Create a table constraint from childTable.motherID and motherTable.ID

ALTER TABLE `childTable`
  ADD CONSTRAINT `childtable_ibfk_1` FOREIGN KEY (`motherID`) REFERENCES `motherTable` (`ID`);
        

Now dump the database.

You will end up with this:

[...]

ALTER TABLE `childTable`
  ADD CONSTRAINT `childtable_ibfk_1` FOREIGN KEY (`motherID`) REFERENCES `mothertable` (`ID`);

Notice that the name of the mother table is "mothertable" and NOT motherTable.

When trying to restore a database dumped this way, all these ALTER TABLE ADD CONSTRAINT statements will fail because the table mothertable does not exist (only motherTable does).

Hi!

To fix this, we need a MySQL function:

get_table_name_in_proper_case_on_windows(char* lower_case_table_name)

Internally, InnoDB keeps all table names in lower case on Windows. In SHOW CREATE TABLE, we could look at the name of the .frm file to get the real case of the table name.

--

Workaround for the user: set lower_case_table_names=1 in my.cnf also in the (Unix) server where you are importing. Then the case of table names is ignored also on Unix.

Regards,

Heikki

Hi!

Actually, we need a function

get_database_and_table_name_for_a_lower_case_name()

because we ned to know also the case of the database name. On Windows, InnoDB puts all database names and table names to lower case.

Regards,

Heikki

Changing the catogory to 'MySQL Server' because to fix this bug we need MySQL to provide a way to retrieve the correct case of a table name in an .frm file.

Random notes while investigating:
While parsing the CREATE TABLE command we call the function dict_scan_table_name to extract a table name from our SQL statement. If this function finds a pattern that matches our expectations we always get a lower case result (table name):

#ifndef __WIN__
	if (srv_lower_case_table_names) {
#endif /* !__WIN__ */
		/* The table name is always put to lower case on Windows. */
		innobase_casedn_str(ref);
#ifndef __WIN__
	}
#endif /* !__WIN__ */

Note: Without the innobase_casedn_str(ref) the function would return the correct representation on the windows platform, ie motherTable instead of mothertable.

Why can't we use that value?

Hi!

Inside InnoDB, table names are always case sensitive, whereas in MySQL on Windows treats them like file names, and they are case insensitive.

The problem originates from 2001 when MySQL did not give the table name in a consistent case on Windows. The case that MySQL passed to InnoDB depended on the case that a database client had used to access the table after a mysqld startup.

I solved the problem by always lower-casing InnoDB's table names on Windows.

A question is, if we could remove the lower-casing code of InnoDB on Windows?

Hmm... we could do that if:

1) we knew that the modern MySQL-5.1 always passes the table name case consistently to storage engines;
and
2) we would add to InnoDB code, that would look for a table name from InnoDB's data dictionary both in the original case AND in lower case (so that we would find also old tables where InnoDB lower-cased the table name).

The above would solve this bug report for newly created tables. For older tables we would still need the function get_table_name_in_original_case(). Actually, we could probably write the function get_table_name_in_original_case() within InnoDB. It is not that hard to find the .frm file of a table from the database directory. No need for MySQL to implement that function. But we need to check that SHOW CREATE TABLE does not retrieve table names from the 5.1 information schema tables, where the case of the table name can also be wrong.

I am reclassifying this to an 'InnoDB bug'.

Regards,

Heikki

Reclassifying as a MySQL bug, since storage of foreign key clauses in the .frm file will solve this bug in the long run.

Is there a solution to this or is there an ETA on the solution? It is more than four years old!

We develop on Windows and then use Linux test and production servers. This is causing havoc in our process to synchronize all our various development, test, and production databases.

Is there at least a nice work around?

Hope this will be fixed soon (6.1 sounds far away?). The bug is still present in MySQL 6.0.6. A "side effect" is that it seems to cause NetBeans to fail importing tables into Java entity classes (http://www.netbeans.org/issues/show_bug.cgi?id=146940). The issue isn't critical or very bad, but it's a bit annoying and wastes some time on manual fixing of the entity class import.

I don't see why the database shouldn't respect my choice of letter case, i.e. I would have defaulted the lower_case_table_names option to zero. If I name a table JobType in my create table script, it's because I actually want it to be named JobType. If anyone refers to the table as jobtype, it's a user/programmer mistake and should produce some "Table not found" error.

Hi,
We are using MySql version 5.1.38 on Windows.
I see this problem exists and reported earlier.
Appreciating the efforts to fix the problem, i would like to know the status of the fix.
Is it fixed in the newer versions of MySql.
Please reply as soon as possible.

Thanks,
Yousuf Ahmed

This bug has been open for over 7 years now, come on MySQL it needs to be fixed!
I have no control over how 3rd party databases are created so I am stuck with camel case table names.

This needs to be fixed.

Seems to be fixed in (or before) 8.0.3.

Posted by developer:
 
Hello!

First of all I need to point out that using --lower-case-table-names=0
on case-insensitive file system (e.g. on Windows) is not (and was not)
supported.

Here is what 5.1 manual says about this:

"
You should not set lower_case_table_names to 0 if you are running MySQL
on a system where the data directory resides on a case-insensitive file
system (such as on Windows or OS X). It is an unsupported combination
that could result in a hang condition when running an INSERT INTO ...
SELECT ... FROM tbl_name operation with the wrong tbl_name letter case.
With MyISAM, accessing table names using different letter cases could
cause index corruption.".

Yes, the above text doesn't mention problems with foreign keys specifically,
but its main point that this is an unsupported combination and that problems
can occurr if one tries to use it. It can't cover all possible problems
that will follow.

Recent 8.0 versions will simply refuse to start in such a case.
E.g. in addition to the above 8.0 manual page describing
lower_case_table_names variable says (see section "5.1.8 Server
System Variables"):

"An error message is printed and the server exits if you attempt to start
the server with --lower_case_table_names=0 on a case-insensitive file system.".

Now if you are on Windows (or other system with case-insensitive filesystem)
and want to preserve case of table and database names you can use
--lower-case-table-name=2 option, which is supported.

However, in 5.1 --lower-case-table-names=2 mode on Windows is affected by
the problem similar to the above. The name of referenced table in
dumps doesn't preserve its original case.

This problem was reported as bug #11762610 / #55222 "MYSQLDUMP TABLE
NAMES CASE BUG IN REFERENCES CLAUSE", and was fixed in MySQL 5.5.9.

Taking into account the above I am closing this bug report as duplicate of
bug #11762610 / #55222.