Schrodingers MySQL table: exists, yet it does not

I am having the weirdest error of all.

Sometimes, when creating or altering tables, I get the 'table already exists' error. However, DROP TABLE returns '#1051 - unknown table'. So I got a table I cannot create, cannot drop.

When I try to drop the database, mysqld crashes. Sometimes it helps to create another db with different name, sometimes it does not.

I use a DB with ~50 tables, all InnoDB. This problem occurs with different tables.

I experienced this on Windows, Fedora and Ubuntu, MySQL 5.1 and 5.5. Same behaviour, when using PDO, PHPMyAdmin or commandline. I use MySQL Workbench to manage my schema - I saw some related errors (endlines and stuff), however none of them were relevant for me.

No, it is not a view, it is a table. All names are lowercase.

I tried everything I could google - flushing tables, moving .frm files from db to db, reading mysql log, nothing helped but reinstalling the whole damn thing.

'Show tables' reveals nothing, 'describe' table says 'table doesn't exist,' there is no .frm file, yet 'create table' still ends with an error (and so does 'create table if not exists') and dropping database crashes mysql

Related, yet unhelpful questions:

• Mysql 1050 Error "Table already exists" when in fact, it does not
• MySQL Table does not exist error, but it does exist

Edit:

mysql> use askyou;
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> create table users_has_friends (id int primary key);
ERROR 1050 (42S01): Table '`askyou`.`users_has_friends`' already exists

mysql> drop table users_has_friends;
ERROR 1051 (42S02): Unknown table 'users_has_friends'

And such, all the same: table doesn't exist, yet cannot be created;

mysql> drop database askyou;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Names change, this is not the only table / database I've run into problems with

 Answers

I've seen this issue when the data file is missing in the data directory but the table definition file exists or vise-versa. If you're using innodb_file_per_table, check the data directory to make sure you have both an .frm file and .ibd file for the table in question. If it's MYISAM, there should be a .frm, .MYI and a .MYD file.

The problem can usually be resolved by deleting the orphaned file manually.

---

I doubt this is a direct answer to the question case here, but here is how I solved this exact perceived problem on my OS X Lion system.

I frequently create/drop tables for some analytics jobs I have scheduled. At some point, I started getting table already exists errors half-way through my script. A server restart typically solved the issue, but that was too annoying of a solution.

Then I noticed in the local error log file this particular line:

[Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive

This gave me the idea that maybe if my tables contained capital letters, MySQL would be fooled into thinking they are still there even after I had dropped them. That turned out to be the case and switching to using only lowercase letters for table names made the problem go away.

It is likely the result of some misconfiguration in my case, but hopefully this error case will help someone waste less time trying to find a solution.

---

In my case the problem was solved by changing the ownership of the mysql data directory to the user that ran the application. (In my case it was a Java application running Jetty webserver.)

Even though mysql was running and other apps could use it properly, this app had a problem with that. After changing the data directory ownership and resetting the user's password, everything worked properly.

---

If will are stock with this error 1051 and you only want to delete the database and import this again do this steps and all gonna be just fine....

in Unix envoriment AS root:

• rm -rf /var/lib/mysql/YOUR_DATABASE;
• OPTIONAL -> mysql_upgrade --force
• mysqlcheck -uUSER -pPASS YOUR_DATABASE
• mysqladmin -uUSER -pPASS drop YOUR_DATABASE
• mysqladmin -uUSER -pPASS create YOUR_DATABASE
• mysql -uUSER -pPASS YOUR_DATABASE < IMPORT_FILE

Regards, Christus

---

I ran into this error after I created a table and deleted it, then wanted to create it again. In my case, I had a self-contained dump file so I dropped my schema, recreated it and imported tables and data using the dump file.

---

I was having this problem with one particular table. Reading the possible solutions i've did some steps like:

• Search for orphan files: didn't exist anyone;
• execute: show full tables in database;: didn't see the problematic one;
• execute: describe table;: returned table doesn't exist;
• execute: SELECT * FROM information_schema.TABLES WHERE TABLE_NAME='table';: returned Empty set;
• Search by the phpMyAdmin manually the query above: didn't exist;

And, after those steps, i check again with the show tables; and... vualá! the problematic table was gone. I could create it and drop it with the same problematic name with no problem, and i didn't have even to restart the server! Weird...