Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.6Mb
PDF (A4) - 35.6Mb
PDF (RPM) - 34.7Mb
EPUB - 8.7Mb
HTML Download (TGZ) - 8.5Mb
HTML Download (Zip) - 8.5Mb
HTML Download (RPM) - 7.3Mb
Eclipse Doc Plugin (TGZ) - 9.3Mb
Eclipse Doc Plugin (Zip) - 11.5Mb
Man Pages (TGZ) - 203.5Kb
Man Pages (Zip) - 310.2Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  ...  /  Troubleshooting InnoDB Data Dictionary Operations

15.21.3 Troubleshooting InnoDB Data Dictionary Operations

Information about table definitions is stored both in the .frm files, and in the InnoDB data dictionary. If you move .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 from starting InnoDB, see Section 15.21.2, “Forcing InnoDB Recovery” for information about manual recovery.

CREATE TABLE Failure Due to Orphan Table

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 InnoDB internal data dictionary, you have an orphan 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 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 --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.)

Cannot Open File Error

Another symptom of an out-of-sync data dictionary is that MySQL prints an error that it cannot open an InnoDB file:

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 .frm file without a corresponding table inside InnoDB. You can drop the orphan .frm file by deleting it manually.

Orphan Intermediate Tables

If MySQL exits in the middle of an in-place ALTER TABLE operation (ALGORITHM=INPLACE), you may be left with an orphan intermediate table that takes up space on your system. Also, an orphan intermediate table in an otherwise empty general tablespace will prevent you from dropping the general tablepace. This section describes how to identify and remove orphan intermediate tables.

Intermediate table names begin with an #sql-ib prefix (e.g., #sql-ib87-856498050). The accompanying .frm file has an #sql-* prefix and is named differently (e.g., #sql-36ab_2.frm).

To identify orphan intermediate tables on your system, you can query INFORMATION_SCHEMA.INNODB_SYS_TABLES. Look for table names that begin with #sql. If the original table resides in a file-per-table tablespace, the tablespace file (the #sql-*.ibd file) for the orphan intermediate table should be visible in the database directory.

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

To remove an orphan intermediate table, perform the following steps:

  1. In the database directory, rename the #sql-*.frm file to match the base name of the orphan intermediate table:

    shell> mv #sql-36ab_2.frm #sql-ib87-856498050.frm
    Note

    If there is no .frm file, you can recreate it. The .frm file must have the same table schema as the orphan intermediate table (it must have the same columns and indexes) and must be placed in the database directory of the orphan intermediate table.

  2. Drop the orphan intermediate table by issuing a DROP TABLE statement, prefixing the name of the table with #mysql50# and enclosing the table name in backticks. For example:

    mysql> DROP TABLE `#mysql50##sql-ib87-856498050`;

    The #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 #.

Note

If a crash occurs during an in-place ALTER TABLE operation that was moving a table to a different tablespace, the recovery process restores the table to its original location but leaves an orphan intermediate table in the destination tablespace.

Orphan Temporary Tables

If MySQL exits in the middle of a table-copying ALTER TABLE operation (ALGORITHM=COPY), you may be left with an orphan temporary table that takes up space on your system. Also, an orphan temporary table in an otherwise empty general tablespace will prevent you from dropping the general tablepace. This section describes how to identify and remove orphan temporary tables.

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.

Note

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 query INFORMATION_SCHEMA.INNODB_SYS_TABLES. Look for table names that begin with #sql. If the original table resides in a file-per-table tablespace, the tablespace file (the #sql-*.ibd file) for the orphan temporary table should be visible in the database directory.

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

To remove an orphan temporary table, drop the table by issuing a DROP TABLE statement, prefixing the name of the table with #mysql50# and enclosing the table name in backticks. For example:

mysql> DROP TABLE `#mysql50##sql-540_3`;

The #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 #.

Tablespace Does Not Exist

With innodb_file_per_table enabled, the following message might occur if the .frm or .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:

  1. Create a matching .frm file in some other database directory and copy it to the database directory where the orphan table is located.

  2. Issue 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.

Restoring Orphan File-Per-Table ibd Files

This procedure describes how to restore orphan file-per-table .ibd files to another MySQL instance. You might use this procedure if the system tablespace is lost or unrecoverable and you want to restore .idb file backups on a new MySQL instance.

The procedure is not supported for general tablespace .ibd files.

The procedure assumes that you only have .ibd file backups, you are recovering to the same version of MySQL that initially created the orphan .idb files, and that .idb file backups are clean. See Section 15.8.4, “Moving or Copying InnoDB Tables to Another Machine” for information about creating clean backups.

Tablespace copying limitations outlined in Section 15.7.6, “Copying File-Per-Table Tablespaces to Another Server” are applicable to this procedure.

  1. 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;
  2. Discard the tablespace of the newly created table.

    mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;
  3. Copy the orphan .idb file from your backup directory to the new database directory.

    shell> cp /backup_directory/actor.ibd path/to/mysql-5.7/data/sakila/
  4. Ensure that the .ibd file has the necessary file permissions.

  5. Import the orphan .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
  6. Query the table to verify that the .ibd file was successfully restored.

    mysql> SELECT COUNT(*) FROM sakila.actor;
    +----------+
    | count(*) |
    +----------+
    |      200 |
    +----------+

User Comments
  Posted by Chris Calender on November 18, 2011
A missing .ibd file can also occur (albeit rare) if you issue a truncate table on an InnoDB table with no foreign keys (FKs) using the InnoDB Plugin and with innodb_file_per_table enabled, and mysqld crashes after the truncate has dropped after the ibd file has been dropped but not yet created.
  Posted by Ralph Bolton on May 16, 2016
When managing intermediate table files, there's no clear way to match .frm files with .ibd files. A 'hexdump -C' of the .frm will tell you which table it relates to, but there's no obvious equivalent for .ibd files. The best way seems to be to match up file Last Modified times.

If you have multiple files all with the same time/date, then matching them up is harder. However, it turns out that .frm files are identical for the same table, so either use 'hexdump' or just md5sum them all and put them into groups. Similarly put the .idb files into groups (by their file prefix) and hopefully you'll be able to match up .frm with .idb files by the numbers of each in each group.

The other good news is that any .frm file for a given table will work with any .idb for the same table. That means you can use one .frm for multiple .idb files (either for convenience or because some .frm files are missing).

It's still hard to know which table a .ibd relates to, but hopefully these tricks will give you some clues. From experience, if you've got more than a couple of files to take care of, you may be better off doing a dump/drop/recreate than to manage the files manually because scripting any of this is pretty much impossible to get right.
Sign Up Login You must be logged in to post a comment.