MySQL Bugs: #66211: CREATE TABLE says "ERROR 1146 (42S02): Table 'mydb.foo' doesn't exist"

Bug #66211	CREATE TABLE says "ERROR 1146 (42S02): Table 'mydb.foo' doesn't exist"
Submitted:	6 Aug 2012 10:42	Modified:	18 Jan 2013 19:29
Reporter:	Greg Hazel	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.25a	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Trying to create a previously created table, DROP TABLE says the table doesn't exist, and CREATE TABLE says.... the table exists?

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

mysql> CREATE TABLE foo (id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB;
ERROR 1146 (42S02): Table 'mydb.foo' doesn't exist

How to repeat:
Try to create a table which innodb thinks exists but which does not exist.

Suggested fix:
CREATE TABLE should never say "Table ... doesn't exist", since that doesn't make any sense. Correct the internal InnoDB structure, or the filesystem.

I think it would be interesting to know if your are using 'innodb_files_per_table' or not? If you do is there a file for the table in the data dictionary?

Peter
(not a MySQL person)

Yes, I'm using innodb_files_per_table. No, there were no files for the table.

Looks like a corrupted InnoDB data dictionary or something like that. Please, upload the entire error log and send the results of:

ls -l

from <datadir>/mydb

The log says many things, since it has been active for a very long time. Here's a snippet from the relevant time period:

120804 4:57:00 InnoDB: Error; possible reasons:
InnoDB: 1) Table rename would cause two FOREIGN KEY constraints
InnoDB: to have the same internal name in case-insensitive comparison.
InnoDB: 2) table `mydb`.`__old_bar` exists in the InnoDB internal data
InnoDB: dictionary though MySQL is trying to rename table `mydb`.`foo` to it.
InnoDB: Have you deleted the .frm file and not used DROP TABLE?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
InnoDB: If table `mydb`.`__foo` is a temporary table #sql..., then it can be that
InnoDB: there are still queries running on the table, and it will be
InnoDB: dropped automatically when the queries end.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and copying the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
120806 9:07:53 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_2bf2_0.MYI'; try to repair it
120806 9:59:23 [ERROR] Cannot find or open table mydb/old_foo from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

This after using the Percona online migration tool, and suffering a server crash, then starting it again. I do not think the directory contained the .ibd and .frm files. It certainly doesn't now.

Thank you for the feedback.

If I understood correctly you solved the problem and can not provide us necessary information for analysis if this is bug in MySQL code or not. Please confirm or reject.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".