Bug #1706 MySQL and mysqldump mis-handle mixed case table names

• Submitted: 29 Oct 2003 20:14
• Modified: 30 Oct 2003 9:39
• Reporter: David Gardiner
• Status: Not a Bug
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 4.0.16
• OS: Windows (XP to Solaris)
• CPU Architecture: Any

[29 Oct 2003 20:14] David Gardiner

Description:
Dumped a database using mysqldump on XP.  Tried to import it on Solaris and got messages indicating that the tables did not exist.  The problem (and this did not seem to occur in 4.0.13): MySQL is inconsistent in how it handles mixed case.

How to repeat:
On XP:
create database MyDB;

create table EditLog (
  Timestamp TIMESTAMP,
  UserName varchar(10) not null,
  Salt INT not null default 0,
  ConceptID INT,
  Action varchar(128),
  primary key (Timestamp, UserName, Salt),
  index (ConceptID)
);

Dump it on XP using 

mysqldump --opt -uMyUser -pMyPass MyDB EditLog > EditLog.sql
ftp My.sql to Solaris.

On Solaris:

mysql –u MyUser –pMyPass MyDB < EditLog.sql

You will get errors saying that the table TestTable doesn’t exist.  The error originates from the LOCK TABLES line below (there is no table EditLog, only editlog).

Actual segment from EditLog.sql (note the name in CREATE TABLE):

DROP TABLE IF EXISTS EditLog;
CREATE TABLE editlog (
  Timestamp timestamp(14) NOT NULL,
  UserName varchar(10) NOT NULL default '',
  Salt int(11) NOT NULL default '0',
  ConceptID int(11) default NULL,
  Action varchar(128) default NULL,
  PRIMARY KEY  (Timestamp,UserName,Salt),
  KEY ConceptID (ConceptID)
) TYPE=MyISAM;

--
-- Dumping data for table `EditLog`
--

/*!40000 ALTER TABLE EditLog DISABLE KEYS */;
LOCK TABLES EditLog WRITE;

 

Suggested fix:
Have MySQL maintain mixed case table names in Windows (it appears to do so in Solaris).

[29 Oct 2003 20:38] David Gardiner

Just noticed an error in the "how to repeat" section.  The sentence "You will get errors saying that the table TestTable doesn’t exist." should say "You will get errors saying that the table EditLog doesn’t exist."

[30 Oct 2003 9:39] Dean Ellis

You need to place lower_case_table_names=0 in your my.ini on the Windows machine.

Thank you