Schrödingers 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

Solution 1:

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.

Solution 2:

Going on a wild guess here, but it seems like innodb still has an entry for your tables in a tablespace, probably in ibdata. If you really don't need any of the data, or if you have backups, try the following:

1. Delete all schemas (excluding mysql)
2. shut down the database
3. Make sure that all folders in your data directory have been removed properly (again, excluding mysql)
4. delete ibdata and log files
5. restart the database. It should recreate the tablespace and logs from scratch.

Solution 3:

The fix turns out to be easy; at least what I worked out, worked for me. Create a table "zzz" on another MySQL instance, where zzz is the problem table name. (i.e. if the table is called schrodinger, substitute that for zzz whever written.) It does not matter what the definition of the table is. It's a temporary dummy; Copy the zzz.frm file to the database directory on server where the table should be, making sure file ownership and permissions are still correct on the file. On MySQL, you can now do "show tables;", and the table zzz will be there. mysql> drop table zzz; ...should now works. Clear any zzz.MYD or ZZZ.MYI files in the directory if necessary.