Bug #27091 SHOW CREATE TABLE and mysqldump ignores case on foreign key table references
Submitted: 13 Mar 2007 15:13 Modified: 26 Mar 2007 23:21
Reporter: Johan Ekbäck Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.37 OS:Windows (Windows XP SP2)
Assigned to: CPU Architecture:Any
Tags: case, foreign key, mysqldump, reference, SHOW CREATE TABLE

[13 Mar 2007 15:13] Johan Ekbäck
Description:
When running MySQL under Windows with lower_case_table_names=0 MySQL doesn't report the correct case on foreign key references when executing SHOW CREATE TABLE or mysqldump.

For example a foreign key reference to database.TableName.ColumnID shows up as database.tablename.ColumnID

The referential integrity is still working though, it's just the ouput that is wrong which would cause problems in the event of an export/import.

How to repeat:
1. Run a Windows MySQL server with lower_case_table_names=0 specified in my.ini
2. Create a database called "test" if it doesn't exist already.
3. Run the following SQL code:

--- begin SQL ---
CREATE TABLE `ReferredTable` (
	`PrimaryKey` TINYINT NOT NULL ,
	`Data` TEXT NOT NULL ,
	PRIMARY KEY ( `PrimaryKey` )
) ENGINE = innodb;

CREATE TABLE `ReferringTable` (
	`PrimaryKey` TINYINT NOT NULL ,
	`ForeignKey` TINYINT NOT NULL ,
	PRIMARY KEY ( `PrimaryKey` ) ,
	INDEX ( `ForeignKey` )
) ENGINE = innodb;

ALTER TABLE `ReferringTable` ADD FOREIGN KEY ( `ForeignKey` ) REFERENCES `test`.`ReferredTable` (
`PrimaryKey`
) ON DELETE RESTRICT ON UPDATE RESTRICT ;

INSERT INTO `test`.`ReferredTable` (
	`PrimaryKey` ,
	`Data`
)
VALUES (
	'1', 'One'
), (
	'2', 'Two'
);

INSERT INTO `test`.`ReferringTable` (
	`PrimaryKey` ,
	`ForeignKey`
)
VALUES (
	'10', '1'
), (
	'20', '2'
);

DELETE FROM ReferredTable WHERE Data='One';
SHOW CREATE TABLE ReferringTable;
--- end SQL ---

4. Note that the DELETE query failes, as it should, due to a working constraint.
5. Note that SHOW CRATE TABLE writes the referred table in all lowercase, which is wrong.
6. Run mysqldump on the test database and note that it also wrongly reports the foreign key references in all lowercase.

Suggested fix:
Rewrite mysqldump and SHOW CREATE TABLE so that they respect the ower_case_table_names setting.
[13 Mar 2007 17:51] MySQL Verification Team
Thank you for the bug report.
[26 Mar 2007 23:21] Timothy Smith
Hi!  Thank you for your bug report.  This report duplicates bug #6555 (dumping a database ignores the --lower_case_table_names=0 parameter).