MySQL Bugs: #19351: Referenced table in foreign key improperly capitalized

Bug #19351	Referenced table in foreign key improperly capitalized
Submitted:	25 Apr 2006 19:42	Modified:	26 Mar 2007 23:20
Reporter:	[ name withheld ]	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	1.1.9	OS:	Windows (Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
MySQL Admin was used to design the structure of several new tables and the indices, foreign keys, etc. on a Windows machine running MySQL 4.1.9-nt.

A backup of the new tables was made and was to be used to recreate these tables on a Linux server running MySQL 4.x.

It was noticed that the referenced table in every foreign key creation statement was improperly capitalized.  In each case where a table name contained mixed capitalization, the name of the referenced table name was lowercased - capitalization for every other element of the foreign key create statement was correct.

This capitalization error would prevent our Linux servers from properly re-creating the tables.

In my.ini lower_case_table_names=0

How to repeat:
Re-run the backup from MySQLAdmin

Suggested fix:
Referenced foreign key table names should match the capitalization of the referenced table.

Thank you for the bug report. Could you please provide the output
on Windows server of show create table, for two tables with foreign
key between them.

Thank you in advance.

This is a known shortcoming. On Windows, InnoDB internally puts all table names in lower case.

I am changing this to an InnoDB feature request.

To avoid the problem on Linux, you should set in my.cnf on Linux:

lower_case_table_names=1

That will remove also other porting problems that are caused by case-insensitiviness of MySQL table names on Windows.

That would solve the problem, BUT our code depends on having capitalization maintained - lowercasing all field/table names will break our app.

I've included a partial sample of the SQL:

CREATE TABLE `packageProduct` (
  `packageProductID` int(11) unsigned NOT NULL auto_increment,
  `productGroupID` int(11) unsigned NOT NULL default '0',
  `packageBrandID` int(11) unsigned NOT NULL default '0',
  `packageUsageID` int(11) unsigned NOT NULL default '0',
  `productID` int(11) unsigned NOT NULL default '0',
  `packageID` int(11) unsigned NOT NULL default '0',
  `packagePriceGroupID` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`packageProductID`),
  UNIQUE KEY `Index_8` (`productGroupID`,`packageBrandID`,`packageUsageID`,`productID`,`packageID`,`packagePriceGroupID`),
  KEY `FK_packageProduct_2` (`packageBrandID`),
  KEY `FK_packageProduct_3` (`packageUsageID`),
  KEY `FK_packageProduct_4` (`productID`),
  KEY `FK_packageProduct_5` (`packageID`),
  KEY `FK_packageProduct_6` (`packagePriceGroupID`),
  CONSTRAINT `FK_packageProduct_1` FOREIGN KEY (`productGroupID`) REFERENCES `productgroup` (`productGroupID`),
  CONSTRAINT `FK_packageProduct_2` FOREIGN KEY (`packageBrandID`) REFERENCES `packagebrand` (`packageBrandID`),
  CONSTRAINT `FK_packageProduct_3` FOREIGN KEY (`packageUsageID`) REFERENCES `packageusage` (`packageUsageID`),
  CONSTRAINT `FK_packageProduct_4` FOREIGN KEY (`productID`) REFERENCES `product` (`productID`),
  CONSTRAINT `FK_packageProduct_5` FOREIGN KEY (`packageID`) REFERENCES `package` (`packageID`),
  CONSTRAINT `FK_packageProduct_6` FOREIGN KEY (`packagePriceGroupID`) REFERENCES `packagepricegroup` (`packagePriceGroupID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `packageBrand` (
  `packageBrandID` int(11) unsigned NOT NULL auto_increment,
  `manufacturerID` int(11) unsigned NOT NULL default '0',
  `productGroupID` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`packageBrandID`),
  UNIQUE KEY `brand` (`manufacturerID`,`productGroupID`),
  CONSTRAINT `FK_manufacturer` FOREIGN KEY (`manufacturerID`) REFERENCES `manufacturer` (`manufacturerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Hi! Thank you for the bug report. This report duplicates bug #6555 (dumping a database ignores the --lower_case_table_names=0 parameter).