15.17. Resolver problemas relacionados con InnoDB

MySQL 5.0

previous page next page

15.17. Resolver problemas relacionados con InnoDB

  • 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 .err, or possibly on Windows.

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

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

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

15.17.1. Resolver problemas de las operaciones del diccionario de datos de InnoDB

A specific issue with tables is that the MySQL server keeps data dictionary information in files it stores in the database directories, while also stores the information into its own data dictionary inside the tablespace files. If you move files around, or if the server crashes in the middle of a data dictionary operation, the files may end up out of sync with InnoDB's internal data dictionary.

A symptom of an out-of-sync data dictionary is that a 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 internal data dictionary, you have an orphaned table inside the tablespace files that has no corresponding 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 a 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 file without a corresponding table inside . You can drop the orphaned file by deleting it manually.

If MySQL crashes in the middle of an operation, you may end up with an orphaned temporary table inside the tablespace. Using you can see listed a table whose name is . In MySQL 5.0, you can perform SQL statements 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 using 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 ''.

previous page start next page