Export data lower-cases foreign key table names

[nchamberlain]

When exporting data from a database with InnoDB tables (Tools, Export data as batch scripts), SQLyog will convert the names of foreign key tables to all lowercase, for example:

Code:

CREATE TABLE `ust_UserType` (
 `ust_ID` int(11) NOT NULL auto_increment,
 `ust_Name` varchar(24) NOT NULL default '',
 PRIMARY KEY  (`ust_ID`),
 UNIQUE KEY `ust_Name` (`ust_Name`)
) TYPE=InnoDB;

CREATE TABLE `usr_User` (
 `usr_ID` int(11) NOT NULL auto_increment,
 `usr_ust_ID` int(11) NOT NULL default '0',
  …
 KEY `usr_ust_ID` (`usr_ust_ID`),
 FOREIGN KEY (`usr_ust_ID`) REFERENCES `ust_usertype` (`ust_ID`)  // <==== problem on Linux!
) TYPE=InnoDB;

While this is not a problem for me on my WinXP development system, it does cause errors on the Linux system where my applications are deployed – because the Linux file system is case-sensitive, the tables can not be found and the foreign key constraints fail.

Is there a way to work around this?

Thanks!

[Ritesh]

SQLyog exports the “Create Table…” stmts. returned by the MySQL server. It does not modify anything before dumping it into the export file.

[CalEvans]

This is a known issue with MySQL.

=C=

[Shadow]

Set lower_case_table_names variable to 1 in the MySql running on Linux. This forces MySql to create all tables (and since 4.0.4 all databases) lowercase.

[Author]

Posts