Skip navigation links
13.2.15 Restrictions on InnoDB Tables »
Section Navigation      [Toggle]

13.2.14.4. 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 use DROP DATABASE in MySQL versions before 3.23.44, or 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.

A symptom of an out-of-sync data dictionary is that a CREATE TABLE statement fails. If this occurs, you should 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.)

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.

If MySQL crashes in the middle of an ALTER TABLE operation, you may end up with an orphaned temporary table inside the InnoDB tablespace. Using the Table Monitor, you can see listed a table with a name that begins with #sql-.... Starting from MySQL 4.0.6, you can perform SQL statements also 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 quotes if the file name contains “#”.

Older MySQL versions did not allow accessing any table with a name containing “#”. The solution in older MySQL versions is to use a special InnoDB mechanism available starting from MySQL 3.23.48. When you have an orphaned table #sql-id inside the tablespace, you can cause InnoDB to rename it to rsql-id_recover_innodb_tmp_table with the following statement: 

CREATE TABLE `rsql-id_recover_innodb_tmp_table`(...) TYPE=InnoDB;

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.


User Comments

Posted by David Hillman on February 14 2006 10:33pm[Delete] [Edit]

I do not believe that this recovery method works on version 4.1.18. I have three otherwise identical servers here, and this procedure worked on 4.1.16, and only made the issue worse on the two 4.1.18 systems.

Be careful.

Posted by sean fulton on June 4 2006 1:14pm[Delete] [Edit]

I had these errors after creating a new slave from the master.

I used the method of stopping the master, creating a tarfile of the ~/mysql directory, moving it to the slave, and then removing from ~/mysql server-specific files like the log files and myl.cnf. I also removed the following files:

ib_arch_log_0000000000 ibdata1 ib_logfile0 ib_logfile1

I got the errors listed above, but noted that the .frm files on the master worked. I then copied the four files above over to the slave, restarted mysql and it worked fine.

So don't just wantonly start deleting those .frm files.



I

Posted by Don Smith on December 19 2008 1:27pm[Delete] [Edit]

Error 121 can also be caused by attempting to add a constraint (foreign key) that already exists.

Add your own comment.