9.18 InnoDB Troubleshooting

9.18 InnoDB Troubleshooting

• A general rule is that when an operation fails or you suspect a bug, you should look at the MySQL server error log, which typically has a name something like host_name .err , or mysql.err on Windows.

• When doing troubleshooting, it is usually best to run the MySQL server from the command prompt, not through the mysqld_safe wrapper or as a Windows service. You will then see what mysqld prints to the command prompt window, and you have a better grasp of what is going on. On Windows, you must start the server with the --console option to direct the output to the console window.

• Use the InnoDB Monitors to obtain information about a problem. If the problem is performance- related , or your server appears to be hung, you should use innodb_monitor to print information about the internal state of InnoDB . If the problem is with locks, use innodb_lock_monitor . If the problem is in creation of tables or other data dictionary operations, use innodb_table_monitor to print the contents of the InnoDB internal data dictionary.

• If you suspect a table is corrupt, run CHECK TABLE on that table.

9.18.1 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, while 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 .frm files may end up out of sync with 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.

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.InnoDB'. (errno: 1) 

In the error log you will 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. With innodb_table_monitor you see a table whose name is #sql... , but since MySQL does not allow accessing any table with such a name, you cannot dump or drop it. The solution is to use a special 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; 

The backticks around the table name are needed because a temporary table name contains the character ' - '.

The table definition must be similar to that of the temporary table. If you do not know the definition of the temporary table, you can use an arbitrary definition in the preceding CREATE TABLE statement, and after that replace the file rsql_id.frm by the file #sql_id.frm of the temporary table. Note that to copy or rename a file in the shell, you need to put the filename in double quotes if the filename contains ' # '. Then you can dump and drop the renamed table.