MySQL Bugs: #1578: MySQLCC incorrectly "lower-cases" table names when generating scripts

Bug #1578	MySQLCC incorrectly "lower-cases" table names when generating scripts
Submitted:	16 Oct 2003 14:12	Modified:	20 Nov 2003 8:23
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQLCC	Severity:	S3 (Non-critical)
Version:	0.9.3	OS:	Windows (Windows XP)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
When MySQLCC generates "CREATE TABLE" scripts for InnoDB tables, table names in the "FOREIGN KEY ... REFERENCES ... " clause are converted to lower case. This causes the script to fail if the target table name is not all lower case.

How to repeat:
/* create the following tables using MySQLCC */

create table adr_Address (
  adr_ID int(11) not null auto_increment,
  adr_Line1 varchar(64) not null,
  primary key adr_Line1
) type=InnoDB;

CREATE TABLE usr_User (
  usr_ID int(11) not null auto_increment,
  usr_Name varchar(64) not null,
  usr_adr_ID int(11),
  primary key ( usr_ID )
  key usr_adr_ID ( usr_adr_ID ),
  foreign key ( usr_adr_ID ) references adr_Address ( adr_ID )
) type = InnoDB;

/***
Now use MySQLCC to generate a CREATE script for the usr_User table. It will produce "references adr_address" instead of "references adr_Address" (incorrect capitalization). 
***/

Suggested fix:
MySQLCC should not convert table names to lower case when generating "CREATE TABLE" scripts - it should use the table name without changing the capitalization.

Sorry, original script for adr_Address table was incorrect - primary key incorrectly defined.  Here is the correct version:

create table adr_Address (
  adr_ID int(11) not null auto_increment,
  adr_Line1 varchar(64) not null,
  primary key adr_ID
) type=InnoDB;

MySQLCC just print the output of the show create table table_name
from the server. This behavior is defined by the lower_case_table_name
start option and/or InnoDB.

Sorry, but the "lower_case_table_names" does not affect the way that the "CREATE TABLE" scripts are generated.  If MySQLCC is just echoing what MySQL is spitting out, this bug lies in MySQL.

Notice that in your statement for create table you wrote the table name
with some capital letter, then these letters are converted to lower case.
Also from InnoDB manual:

On Windows InnoDB stores the database names and table names internally always
in lower case. To move databases in a binary format from Unix to Windows or
from Windows to Unix you should have all table and database names in lower case. A convenient way to accomplish this is to add on Unix the line.... 

So currently that behavior is by design due too to OS behavior and when it
will be changed for MySQL to handle the case sensitive names on Windows, I
can't to say you.

Thanks Miguel - this clarifies the matter for me.  I will probably be creating a little utility to convert scripts so that the table names are capitalized the way that they are created - when I do, I will post a link to it here.

Thanks again,
Neville