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.
If a data dictionary corruption or consistency issue prevents you
Section 14.18.2, “Forcing InnoDB Recovery” for information about
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 be left with an orphaned
intermediate table. Intermediate table names begin with
“#sql-”. In your data directory you will see an
#sql-*.ibd file and possibly an accompanying
#sql-*.frm file. The intermediate table is
also listed in Table
Monitor output and referenced in
Removing an orphaned intermediate table requires a table format
.frm file) that matches the table
schema defined in the
#sql-*.ibd file (it
must have the same columns and indexes). Depending on when the
crash occurred during the
operation, the orphaned
#sql-*.ibd file could
have a pre-
ALTER schema definition, and the data in
#sql-*.frm file (if present)
may or may not match.
To remove the orphaned intermediate table, perform the following steps:
Determine if the
#sql-*.ibd file has a
ALTER or post-
schema definition. You can view the columns and indexes of the
intermediate table using the
Table Monitor or by
INNODB_SYS_TABLES provides the
TABLE_ID for the intermediate table, which
you can use to retrieve column and index information from
Once you have determined if the
#sql-*.ibd file has a
ALTER or post-
schema definition, create a matching
#sql-*.frm file in a different database
directory. For example, if an intermediate table has a
ALTER schema definition, create an
.frm file that matches the altered schema
mysql> CREATE TABLE tmp LIKE employees.salaries; ALTER TABLE tmp DROP COLUMN to_date; Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0
.frm file to the database
directory where the orphaned table is located and rename it to
match the name of the
shell> cp tmp.frm employees/#sql-ib87.frm
Drop the intermediate table by issuing a
TABLE statement, prefixing the name of the table
#mysql50# and enclosing the table name
in backticks. For example:
mysql> DROP TABLE `#mysql50##sql-ib87`; Query OK, 0 rows affected (0.01 sec)
#mysql50# prefix tells MySQL to ignore
file name safe encoding introduced in MySQL
5.1. Enclosing the table name in backticks is required to
perform SQL statements on table names with special characters
such as “#”.
If there is a leftover
drop it. MySQL reports an “unknown table” error,
which can be ignored.
mysql> DROP TABLE `#mysql50##sql-36ab_2`; ERROR 1051 (42S02): Unknown table 'employees.#mysql50##sql-36ab_2'
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.
This procedure describes how to restore orphaned
.ibd files to another MySQL instance. You
might use this procedure if the system tablespace is lost or
unrecoverable and you want to restore
file backups on a new MySQL instance.
The procedure assumes that you are recovering to the same version
of MySQL that initially created the orphaned
.idb file, and that
file backups are clean. See Section 14.5.2, “Moving or Copying InnoDB Tables to Another Machine” for
information about creating clean backups.
Tablespace copying limitations outlined in Section 14.4.6, “Copying File-Per-Table Tablespaces to Another Server” are applicable to this procedure.
On the new MySQL instance, recreate the table in a database of the same name.
mysql> CREATE DATABASE sakila; mysql> USE sakila; mysql> CREATE TABLE actor ( -> actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> first_name VARCHAR(45) NOT NULL, -> last_name VARCHAR(45) NOT NULL, -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (actor_id), -> KEY idx_actor_last_name (last_name) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Discard the tablespace of the newly created table.
mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;
Copy the orphaned
.idb file from your
backup directory to the new database directory.
shell> cp /
Ensure that the
.ibd file has the
necessary file permissions.
Import the orphaned
.ibd file. A warning is
issued indicating that
InnoDB will attempt
to import the file without schema verification.
mysql> ALTER TABLE sakila.actor IMPORT TABLESPACE; SHOW WARNINGS; Query OK, 0 rows affected, 1 warning (0.15 sec) Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './sakila/actor.cfg', will attempt to import without schema verification
Query the table to verify that the
file was successfully restored.
mysql> SELECT COUNT(*) FROM sakila.actor; +----------+ | count(*) | +----------+ | 200 | +----------+