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.

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 end up with an orphaned intermediate table inside the InnoDB tablespace. Orphaned intermediate table names begin with an #sql- prefix. You can view a list of tables that are present in the InnoDB tablespace, including tables named with an #sql- prefix, using the Table Monitor.

ALTER TABLE creates intermediate table files in the same directory as the original table.

The following example uses the salaries table of the employees sample database.

mysql> SHOW CREATE TABLE salaries\G
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `emp_no` (`emp_no`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
1 row in set (0.00 sec)  

The MySQL server connection is lost while performing an ALTER TABLE operation to remove the to_date column from the employees.salaries table:

mysql> ALTER TABLE salaries DROP COLUMN to_date;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> use employees;
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE ***

The ALTER TABLE operation leaves an orphaned intermediate table (#sql-ib87.ibd) and an accompanying table format file (#sql-22d0_1.frm):

mysql> \! ls /path/to/datadir/employees/
db.opt           dept_emp.ibd      employees.ibd    #sql-ib87.ibd
departments.frm  dept_manager.frm  salaries.frm     titles.frm
departments.ibd  dept_manager.ibd  salaries.ibd     titles.ibd
dept_emp.frm     employees.frm     #sql-22d0_1.frm

To remove the orphaned intermediate table, perform the following steps:

  1. In the directory where the intermediate table resides, rename the #sql-*.frm file to match the name of the #sql-*.ibd file.

    shell> mv "#sql-247a_2.frm" "#sql-ib87.frm" 

    To rename or copy a file in the Unix shell, you must enclose the file name in double quotation marks if the file name contains #.

  2. Drop the intermediate table by issuing a DROP TABLE statement, prefixing the name of the table with #mysql50# and enclosing table name in backticks. (The #mysql50# prefix prevents MySQL from escaping the hash mark and hyphen.)

    mysql> DROP TABLE `#mysql50##sql-ib87`;
    Query OK, 0 rows affected (0.01 sec)    

You can perform SQL statements on tables whose name contains the character # if you enclose the name within backticks.

If you have number of orphaned intermediate tables that have accumulated over time, you may need to look at the date modified for each file to match temporary table files (.ibd files) with intermediate table format files (.frm files), as the file names may not match. If the intermediate table format file (.frm file) is not available, create one using the following steps:

  1. In some other database directory, create a table with the structure that the table would have if the ALTER TABLE operation completed successfully:

    mysql> CREATE TABLE tmp LIKE 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    
  2. Shut down MySQL server.

  3. Copy the tmp.frm file that you created and rename it so that it matches the #sql-*.ibd file name.

    shell> cp tmp.frm employees/#sql-ib87.frm
  4. Once the .frm file is in place, issue DROP TABLE for the intermediate table, as described above.

Problem with Missing Tablespace

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.