Schrödingers MySQL table: exists, yet it does not

Question

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.

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.

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.

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.

This is an old question but I just hit the same issue and an answer in one of the related issues linked at the top was just what I needed and far less drastic than deleting files, tables, shutting down the server etc.

mysqladmin -uxxxxxx -pyyyyy flush-tables