We formerly were built with a database in MySQL. Inside it, we'd a table known as
trace. We have lengthy since dropped the whole database and produced a different one with similar title. Now, we attempt to re-produce the table
trace from the backup script and that we get
table already exists. Despite the fact that you will find clearly no tables within the new database. Basically try to produce a table that been around before, I recieve that error. Basically produce a random table that never been around then it is fine.
This is actually the code I'm using to produce 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;
Table 'trace' already exists
We did have about 20 tables (trace was one of several) and there have been plenty of foreign secrets in the event that helps. But we clearly dropped the DB and recreated it.
For testing, I attempted this and delay pills work
CREATE TABLE trace (id INT,data VARCHAR(100));
However, after shedding that table and seeking it again such as this:
CREATE TABLE Trace (id INT,data VARCHAR(100));
It does not work and that i obtain the
trace already exists error.
The main difference is upper versus lowercase??
It's certainly an uppercase versus lowercase problem. Altering the table title from
trace works even while using old script.
Any applying for grants why this really is so?
You may have wrong
lower_case_table_names setting set in your MySQL Server. Correct values are available In MySQL documentation. It states:
If you work with MySQL on just one platform, you don't ordinarily have to alter the low_situation_table_names variable from the default value. However, you might encounter difficulties if you wish to transfer tables between platforms that differ in file system situation sensitivity. For instance, on Unix, you could have two different tables named my_table and MY_TABLE, but on Home windows both of these names are thought identical. To prevent bandwith problems developing from lettercase of database or table names, you've two options:
Use lower_situation_table_names=1 on all systems. The primary disadvantage with this particular is the fact that if you use SHOW TABLES or SHOW DATABASES, you don't begin to see the names within their original lettercase.
Use lower_situation_table_names= on Unix minimizingsituation_table_names=2 on Home windows. This preserves the lettercase of database and table names. The drawback to this is you must make sure that your claims always make reference to your database and table names using the correct lettercase on Home windows. Should you transfer your claims to Unix, where lettercase is important, they don't work when the lettercase is incorrect.
Exception: If you work with InnoDB tables and you're simply attempting to avoid these bandwith problems, you need to set lower_situation_table_names to at least one on all platforms to pressure names to become transformed into lowercase.
If you are using MySQL on Home windows and also have
lower_case_table_names=0 then you can find this error because table
Trace (situation-sensitive) doesn't appear in MySQL but file
Trace.frm already is available on file system.
It is best to restart your database server. I acquired similar problem before.