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 hostname.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.
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-.... Starting from MySQL 4.0.6, you can perform SQL statements also on tables whose name contains the character ‘#’ if you enclose the name in backticks. Thus, you can drop such an orphaned table like any other orphaned table with the method described above. Note that 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;