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.
