Why can't I create a table in MySQL?

We previously had a database in MySQL. In it, we had a table called trace. We've long since dropped the entire database and created another one with the same name. Now, we try to re-create the table trace from a backup script and we get table already exists. Even though there are clearly no tables in the new database. If I try to create a table that existed before, I get that error. If I create a random table that never existed then it's fine.

Here is the code I am using to create the table:

DROP TABLE IF EXISTS `Trace`;
CREATE TABLE `Trace` (
  `TraceKey` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Trace Key',
  `TableName` varchar(100) NOT NULL DEFAULT '' COMMENT 'Table Name',
  `RecordKey` int(10) NOT NULL,
  `Action` varchar(100) NOT NULL DEFAULT '',
  `ActionTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Time Stamp',
  `UserName` varchar(100) NOT NULL DEFAULT '' COMMENT 'UserName',
  PRIMARY KEY (`TraceKey`)
) ENGINE=InnoDB AUTO_INCREMENT=6735 DEFAULT CHARSET=latin1;

Error:

Table 'trace' already exists

We did have about 20 tables (trace was one of many) and there were lots of foreign keys if that helps. But we clearly dropped the DB and recreated it.

UPDATE

For testing, I tried this and it WORKS

CREATE TABLE trace (id INT,data VARCHAR(100));

However, after dropping that table and trying it again like this:

CREATE TABLE Trace (id INT,data VARCHAR(100));

It doesn't work and I get the trace already exists error.

The difference is upper vs lowercase??

UPDATE 2

It is definitely an uppercase vs lowercase issue. Changing the table name from Trace to trace works even using the old script.

Any ideas on why this is so?

Solution

You might have wrong lower_case_table_names setting set on your MySQL Server. Correct values can be found In MySQL documentation. It states:

If you are using MySQL on only one platform, you do not normally have to change the lower_case_table_names variable from its default value. However, you may encounter difficulties if you want to transfer tables between platforms that differ in file system case sensitivity. For example, on Unix, you can have two different tables named my_table and MY_TABLE, but on Windows these two names are considered identical. To avoid data transfer problems arising from lettercase of database or table names, you have two options:

Use lower_case_table_names=1 on all systems. The main disadvantage with this is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase.

Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. This preserves the lettercase of database and table names. The disadvantage of this is that you must ensure that your statements always refer to your database and table names with the correct lettercase on Windows. If you transfer your statements to Unix, where lettercase is significant, they do not work if the lettercase is incorrect.

Exception: If you are using InnoDB tables and you are trying to avoid these data transfer problems, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lowercase.

If you're using MySQL on Windows and have lower_case_table_names=0 then you might get this error because table Trace (case-sensitive) does not exist in MySQL but file Trace.frm already exists on file system.