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
.frm files may end up out of synchrony
with the locations recorded in the
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 orphan table inside the
tablespace files that has no corresponding
.frm file. The error message looks like
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 orphan 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
option and try
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 an
ERROR 1016: Can't open file: 'child2.ibd'. (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 orphan
file without a corresponding table inside
InnoDB. You can drop the orphan
.frm file by deleting it manually.
If MySQL exits in the middle of an
TABLE operation, you may be left with an orphan
temporary table that takes up space on your system. This
section describes how to identify and remove orphan temporary
Orphan temporary table names begin with an
#sql- prefix (e.g.,
#sql-540_3). The accompanying
.frm file has the same base name as the
orphan temporary table.
If there is no
.frm file, you can
recreate it. The
.frm file must have
the same table schema as the orphan temporary table (it must
have the same columns and indexes) and must be placed in the
database directory of the orphan temporary table.
To identify orphan temporary tables on your system, you can
view Table Monitor
output. Look for table names that begin with
#sql. If the original table resides in a
tablespace, the tablespace file (the
#sql-*.ibd file) for the orphan temporary
table should be visible in the database directory.
To remove an orphan temporary table, drop the table by issuing
DROP TABLE statement,
enclosing the table name in backticks. For example:
mysql> DROP TABLE `#sql-540_3`;
enabled, the following message might occur if the
.ibd files (or
both) are missing:
InnoDB: in InnoDB data dictionary has tablespace id
N, InnoDB: but tablespace with that id or name does not exist. Have InnoDB: you deleted or moved .ibd files? InnoDB: This may also be a table created with CREATE TEMPORARY TABLE InnoDB: whose .ibd and .frm files MySQL automatically removed, but the InnoDB: table still exists in the InnoDB internal data dictionary.
If this occurs, try the following procedure to resolve the problem:
Create a matching
.frmfile in some other database directory and copy it to the database directory where the orphan table is located.
DROP TABLEfor the original table. That should successfully drop the table and
InnoDBshould print a warning to the error log that the
.ibdfile was missing.