14.6.12.3 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.

If a data dictionary corruption or consistency issue prevents you from starting InnoDB, see Section 14.6.12.2, “Forcing InnoDB Recovery” for information about manual recovery.

Problem with CREATE 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 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.)

Problem Opening Table

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.

Orphaned Intermediate Tables

If MySQL crashes in the middle of an ALTER 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 an accompanying #sql-*.frm file with the same name. The intermediate table is also listed in Table Monitor output.

If both the #sql-*.ibd and #sql-*.frm files appear in your data directory, drop the 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-1291_3`;
Query OK, 0 rows affected (0.01 sec)

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

If there is no table format file (#sql-*.frm file) in your data directory or the DROP TABLE operation fails, create a new .frm file that matches the table schema of the #sql-*.ibd file (it must have the same columns and indexes defined). To do this, perform the following steps:

  1. Determine if the #sql-*.ibd file has a pre-ALTER or post-ALTER schema definition. You can view the columns and indexes of the intermediate table using the Table Monitor.

  2. Once you have determined if the #sql-*.ibd file has a pre-ALTER or post-ALTER schema definition, create a matching #sql-*.frm file in a different database directory. For example, if an intermediate table has a post-ALTER schema definition, create an .frm file that matches the altered schema definition:

    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    
  3. Copy the .frm file to the database directory where the orphaned table is located and rename it to match the name of the #sql-*.ibd file

    shell> cp tmp.frm employees/#sql-sql-1291_3.frm
  4. Drop the 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-1291_3`;
    Query OK, 0 rows affected (0.01 sec)