(mysql.info.gz) InnoDB troubleshooting datadict

(mysql.info.gz) InnoDB troubleshooting
 
 15.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-...'.
 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;
Info Catalog
(mysql.info.gz) InnoDB troubleshooting
automatically generated byinfo2html