Information about table definitions is stored both in the
.frm files, and in the InnoDB
data dictionary. If
.frm files around, or if the server
crashes in the middle of a data dictionary operation, these
sources of information can become inconsistent.
A symptom of an out-of-sync data dictionary is that a
CREATE TABLE statement fails. If
this occurs, look in the server's error log. If the log says that
the table already exists inside the
internal data dictionary, you have an orphaned table inside the
InnoDB tablespace files that has no
.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
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
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
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
TABLE operation, you may end up with an orphaned
temporary table inside the
Using the Table Monitor, you can see listed a table with a name
that begins with
#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. To copy
or rename a file in the Unix shell, you need to put the file name
in double quotation marks if the file name contains
enabled, the following message might occur if the
.ibd files (or both)
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
.frm file in some other
database directory and copy it to the database directory where
the orphan table is located.
DROP TABLE for the
original table. That should successfully drop the table and
InnoDB should print a warning to the error
log that the
.ibd file was missing.