(mysql.info) innodb-troubleshooting-datadict

(mysql.info) innodb-troubleshooting
 
 14.2.17.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, whereas `InnoDB' also stores the information into its own
 data dictionary inside the tablespace files. If you move `.frm' files
 around, or if 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 `innodb_table_monitor' you can see listed a table whose name is
 `#sql-...'. You can perform SQL statements 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. 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 ‘`#'’.
Info Catalog
(mysql.info) innodb-troubleshooting
automatically generated byinfo2html