Bug #219 Bug in "show create table" on Win32 server: Loses case of foreign key constrai

• Submitted: 1 Apr 2003 11:12
• Modified: 7 Apr 2003 6:48
• Reporter: Scott Maxwell
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.0.11-gama-Max
• OS: Windows (Windows XP)
• CPU Architecture: Any

[1 Apr 2003 11:12] Scott Maxwell

Description:
If I do this:

SHOW CREATE TABLE `PocketPurchase`.`Vendor`;

The generated CREATE statement is incorrect.  All of the FOREIGN KEY checks have all lowercase table names.  For instance, instead of:

FOREIGN KEY (`CurrencyCode`) REFERENCES `CurrencyCode` (`CurrencyCode`) ON UPDATE CASCADE 

I get:

FOREIGN KEY (`CurrencyCode`) REFERENCES `currencycode` (`CurrencyCode`) ON UPDATE CASCADE 

This only happens on Windows.  I do have lower-case-table-names=0 and everything else is mixed case as expected.  Looks like that setting is just getting missed for this section.

I marked this as 'Serious' since it makes it impossible to dump the database on Windows and import the result into a Linux box.  Even on Windows, regenerating the database from a dump results may result in bogus constraints if lower-case-table-names=0.  We are still trying to confirm this.

How to repeat:
Add the following to c:\Windows\my.ini:

[mysqld]
set-variable = lower-case-table-names=0

Run this query:

DROP DATABASE IF EXISTS KeyFailTest;
CREATE DATABASE KeyFailTest;

USE KeyFailTest;

CREATE TABLE `CurrencyCode` (
	`CountryName` varchar(50) NOT NULL default '',
	`CurrencyCode` char(3) NOT NULL default '',
	PRIMARY KEY  (`CurrencyCode`)
) TYPE=InnoDB;

CREATE TABLE `Vendor` (
	`VendorGID` int(10) unsigned NOT NULL auto_increment,
	`Name` varchar(64) NOT NULL default '',
	`CurrencyCode` char(3) NOT NULL default 'USD',
	PRIMARY KEY  (`VendorGID`),
	KEY `CurrencyCodeIndex` (`CurrencyCode`),
	FOREIGN KEY (`CurrencyCode`) REFERENCES `CurrencyCode` (`CurrencyCode`) ON UPDATE CASCADE
) TYPE=InnoDB;

SHOW CREATE TABLE Vendor;

You will see that the result is incorrect.

Suggested fix:
My suspicion is that lower-case-table-names=0 is not being observed properly in this section of code.

[7 Apr 2003 6:48] MySQL Verification Team

According with the InnoDB main developer, this behavior
was introduced for to avoid problems on Windows when the
*.frm files are renamed (MySQL thinks the table name has changed, but InnoDB did not find the table from its own data dictionary).

This will be fixed on MySQL 5.0 when MySQL stores the CREATE TABLE statement verbatim to the .frm file.

For now this will declared as a known design deficiency:
in Windows SHOW CREATE TABLE always prints FOREIGN KEY table names in lower case!