14.2.17.1. Troubleshooting InnoDB Data Dictionary Operations
A specific issue with tables is that the MySQL server keeps data
dictionary information in .frm files it
stores in the database directories, whereas
InnoDB also stores the information into its
own data dictionary inside the tablespace files. If you move
.frm files around, or if the server crashes
in the middle of a data dictionary operation, the locations of
the .frm files may end up out of synchrony
with the locations recorded in the InnoDB
internal data dictionary.
A symptom of an out-of-sync data dictionary is that a
CREATE TABLE statement fails. If this occurs,
you should look in the server's error log. If the log says that
the table already exists inside the InnoDB
internal data dictionary, you have an orphaned table inside the
InnoDB tablespace files that has no
corresponding .frm file. The error message
looks like this:
InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
You can drop the orphaned table by following the instructions
given in the error message. If you are still unable to use
DROP TABLE successfully, the problem may be
due to name completion in the mysql client.
To work around this problem, start the mysql
client with the --skip-auto-rehash option and
try DROP TABLE again. (With name completion
on, mysql tries to construct a list of table
names, which fails when a problem such as just described
exists.)
Another symptom of an out-of-sync data dictionary is that MySQL
prints an error that it cannot open a
.InnoDB file:
ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)
In the error log you can find a message like this:
InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?
This means that there is an orphaned .frm
file without a corresponding table inside
InnoDB. You can drop the orphaned
.frm file by deleting it manually.
If MySQL crashes in the middle of an ALTER
TABLE operation, you may end up with an orphaned
temporary table inside the InnoDB tablespace.
Using innodb_table_monitor you can see listed
a table whose name is #sql-.... You can
perform SQL statements on tables whose name contains the
character ‘#’ if you enclose the
name within backticks. Thus, you can drop such an orphaned table
like any other orphaned table using the method described
earlier. Note that to copy or rename a file in the Unix shell,
you need to put the file name in double quotes if the file name
contains ‘#’.
