14.22 InnoDB Troubleshooting

14.22.1 Troubleshooting InnoDB I/O Problems

The troubleshooting steps for 'InnoDB' I/O problems depend on when the problem occurs: during startup of the MySQL server, or during normal operations when a DML or DDL statement fails due to problems at the file system level.

Initialization Problems

If something goes wrong when 'InnoDB' attempts to initialize its tablespace or its log files, delete all files created by 'InnoDB': all 'ibdata' files and all 'ib_logfile' files. If you already created some 'InnoDB' tables, also delete the corresponding '.frm' files for these tables, and any '.ibd' files if you are using multiple tablespaces, from the MySQL database directories. Then try the 'InnoDB' database creation again. For easiest troubleshooting, start the MySQL server from a command prompt so that you see what is happening.

Runtime Problems

If 'InnoDB' prints an operating system error during a file operation, usually the problem has one of the following solutions:

• Make sure the 'InnoDB' data file directory and the 'InnoDB' log directory exist.

• Make sure *note 'mysqld': mysqld. has access rights to create files in those directories.

• Make sure *note 'mysqld': mysqld. can read the proper 'my.cnf' or 'my.ini' option file, so that it starts with the options that you specified.

• Make sure the disk is not full and you are not exceeding any disk quota.

• Make sure that the names you specify for subdirectories and data files do not clash.

• Doublecheck the syntax of the 'innodb_data_home_dir' and 'innodb_data_file_path' values. In particular, any 'MAX' value in the 'innodb_data_file_path' option is a hard limit, and exceeding that limit causes a fatal error.

 File: manual.info.tmp, Node: forcing-innodb-recovery, Next: innodb-troubleshooting-datadict, Prev: error-creating-innodb, Up: innodb-troubleshooting

14.22.2 Forcing InnoDB Recovery

To investigate database page corruption, you might dump your tables from the database with note 'SELECT ... INTO OUTFILE': select-into. Usually, most of the data obtained in this way is intact. Serious corruption might cause 'SELECT FROM TBL_NAME' statements or 'InnoDB' background operations to unexpectedly exit or assert, or even cause 'InnoDB' roll-forward recovery to crash. In such cases, you can use the 'innodb_force_recovery' option to force the 'InnoDB' storage engine to start up while preventing background operations from running, so that you can dump your tables. For example, you can add the following line to the '[mysqld]' section of your option file before restarting the server:

 [mysqld]
 innodb_force_recovery = 1

For information about using option files, see *note option-files::.

Warning:

Only set 'innodb_force_recovery' to a value greater than 0 in an emergency situation, so that you can start 'InnoDB' and dump your tables. Before doing so, ensure that you have a backup copy of your database in case you need to recreate it. Values of 4 or greater can permanently corrupt data files. Only use an 'innodb_force_recovery' setting of 4 or greater on a production server instance after you have successfully tested the setting on a separate physical copy of your database. When forcing 'InnoDB' recovery, you should always start with 'innodb_force_recovery=1' and only increase the value incrementally, as necessary.

'innodb_force_recovery' is 0 by default (normal startup without forced recovery). The permissible nonzero values for 'innodb_force_recovery' are 1 to 6. A larger value includes the functionality of lesser values. For example, a value of 3 includes all of the functionality of values 1 and 2.

If you are able to dump your tables with an 'innodb_force_recovery' value of 3 or less, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 4 or greater is considered dangerous because data files can be permanently corrupted. A value of 6 is considered drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

As a safety measure, 'InnoDB' prevents note 'INSERT': insert, note 'UPDATE': update, or *note 'DELETE': delete. operations when 'innodb_force_recovery' is greater than 0. An 'innodb_force_recovery' setting of 4 or greater places 'InnoDB' in read-only mode.

• '1' ('SRV_FORCE_IGNORE_CORRUPT')

  Lets the server run even if it detects a corrupt page. Tries to make 'SELECT * FROM TBL_NAME' jump over corrupt index records and pages, which helps in dumping tables.

• '2' ('SRV_FORCE_NO_BACKGROUND')

  Prevents the master thread and any purge threads from running. If an unexpected exit would occur during the purge operation, this recovery value prevents it.

• '3' ('SRV_FORCE_NO_TRX_UNDO')

  Does not run transaction rollbacks after crash recovery.

• '4' ('SRV_FORCE_NO_IBUF_MERGE')

  Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes. Sets 'InnoDB' to read-only.

• '5' ('SRV_FORCE_NO_UNDO_LOG_SCAN')

  Does not look at undo logs when starting the database: 'InnoDB' treats even incomplete transactions as committed. This value can permanently corrupt data files. Sets 'InnoDB' to read-only.

• '6' ('SRV_FORCE_NO_LOG_REDO')

  Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures. Sets 'InnoDB' to read-only.

You can note 'SELECT': select. from tables to dump them. With an 'innodb_force_recovery' value of 3 or less you can 'DROP' or 'CREATE' tables. note 'DROP TABLE': drop-table. is also supported with an 'innodb_force_recovery' value greater than 3, up to MySQL 5.7.17. As of MySQL 5.7.18, *note 'DROP TABLE': drop-table. is not permitted with an 'innodb_force_recovery' value greater than 4.

If you know that a given table is causing an unexpected exit on rollback, you can drop it. If you encounter a runaway rollback caused by a failing mass import or note 'ALTER TABLE': alter-table, you can kill the note 'mysqld': mysqld. process and set 'innodb_force_recovery' to '3' to bring the database up without the rollback, and then 'DROP' the table that is causing the runaway rollback.

If corruption within the table data prevents you from dumping the entire table contents, a query with an 'ORDER BY PRIMARY_KEY DESC' clause might be able to dump the portion of the table after the corrupted part.

If a high 'innodb_force_recovery' value is required to start 'InnoDB', there may be corrupted data structures that could cause complex queries (queries containing 'WHERE', 'ORDER BY', or other clauses) to fail. In this case, you may only be able to run basic 'SELECT * FROM t' queries.

 File: manual.info.tmp, Node: innodb-troubleshooting-datadict, Next: innodb-error-handling, Prev: forcing-innodb-recovery, Up: innodb-troubleshooting

14.22.3 Troubleshooting InnoDB Data Dictionary Operations

Information about table definitions is stored both in the '.frm' files, and in the InnoDB data dictionary. If you move '.frm' files around, or if the server crashes in the middle of a data dictionary operation, these sources of information can become inconsistent.

If a data dictionary corruption or consistency issue prevents you from starting 'InnoDB', see *note forcing-innodb-recovery:: for information about manual recovery.

CREATE TABLE Failure Due to Orphan Table

A symptom of an out-of-sync data dictionary is that a *note 'CREATE TABLE': create-table. statement fails. If this occurs, 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 orphan 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 orphan table by following the instructions given in the error message. If you are still unable to use note 'DROP TABLE': drop-table. successfully, the problem may be due to name completion in the note 'mysql': mysql. client. To work around this problem, start the note 'mysql': mysql. client with the '--skip-auto-rehash' option and try note 'DROP TABLE': drop-table. again. (With name completion on, *note 'mysql': mysql. tries to construct a list of table names, which fails when a problem such as just described exists.)

Cannot Open Datafile

With 'innodb_file_per_table' enabled (the default), the following messages may appear at startup if a file-per-table tablespace file ('.ibd' file) is missing:

 [ERROR] InnoDB: Operating system error number 2 in a file operation.
 [ERROR] InnoDB: The error means the system cannot find the path specified.
 [ERROR] InnoDB: Cannot open datafile for read-only: './test/t1.ibd' OS error: 71
 [Warning] InnoDB: Ignoring tablespace `test/t1` because it could not be opened.

To address these messages, issue *note 'DROP TABLE': drop-table. statement to remove data about the missing table from the data dictionary.

Cannot Open File Error

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.ibd'. (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 orphan '.frm' file without a corresponding table inside 'InnoDB'. You can drop the orphan '.frm' file by deleting it manually.

Orphan Intermediate Tables

If MySQL exits in the middle of an in-place *note 'ALTER TABLE': alter-table. operation ('ALGORITHM=INPLACE'), you may be left with an orphan intermediate table that takes up space on your system. Also, an orphan intermediate table in an otherwise empty general tablespace prevents you from dropping the general tablespace. This section describes how to identify and remove orphan intermediate tables.

Intermediate table names begin with an '#sql-ib' prefix (e.g., '#sql-ib87-856498050'). The accompanying '.frm' file has an '#sql-*' prefix and is named differently (e.g., '#sql-36ab_2.frm').

To identify orphan intermediate tables on your system, you can query the Information Schema note 'INNODB_SYS_TABLES': information-schema-innodb-sys-tables-table. table. Look for table names that begin with '#sql'. If the original table resides in a file-per-table tablespace, the tablespace file (the '#sql-.ibd' file) for the orphan intermediate table should be visible in the database directory.

 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

To remove an orphan intermediate table, perform the following steps:

1. In the database directory, rename the '#sql-*.frm' file to match the base name of the orphan intermediate table:

     $> mv #sql-36ab_2.frm #sql-ib87-856498050.frm

   Note:

   If there is no '.frm' file, you can recreate it. The '.frm' file must have the same table schema as the orphan intermediate table (it must have the same columns and indexes) and must be placed in the database directory of the orphan intermediate table.

2. Drop the orphan intermediate table by issuing a *note 'DROP TABLE': drop-table. statement, prefixing the name of the table with '#mysql50#' and enclosing the table name in backticks. For example:

     mysql> DROP TABLE `#mysql50##sql-ib87-856498050`;

   The '#mysql50#' prefix tells MySQL to ignore 'file name safe encoding' introduced in MySQL 5.1. Enclosing the table name in backticks is required to perform SQL statements on table names with special characters such as '#'.

Note:

If an unexpected exit occurs during an in-place *note 'ALTER TABLE': alter-table. operation that was moving a table to a different tablespace, the recovery process restores the table to its original location but leaves an orphan intermediate table in the destination tablespace.

Note:

If MySQL exits in the middle of an in-place *note 'ALTER TABLE': alter-table. operation on a partitioned table, you may be left with multiple orphan intermediate tables, one per partition. In this case, use the following procedure to remove the orphan intermediate tables:

1. In a separate instance of the same MySQL version, create a non-partitioned table with the same schema name and columns as the partitioned table.

2. Copy the '.frm' file of the non-partitioned table to the database directory with the orphan intermediate tables.

3. Make a copy of the '.frm' file for each table, and rename the '.frm' files to match names of the orphan intermediate tables (as described above).

4. Perform a *note 'DROP TABLE': drop-table. operation (as described above) for each table.

Orphan Temporary Tables

If MySQL exits in the middle of a table-copying *note 'ALTER TABLE': alter-table. operation ('ALGORITHM=COPY'), you may be left with an orphan temporary table that takes up space on your system. Also, an orphan temporary table in an otherwise empty general tablespace prevents you from dropping the general tablespace. This section describes how to identify and remove orphan temporary tables.

Orphan temporary table names begin with an '#sql-' prefix (e.g., '#sql-540_3'). The accompanying '.frm' file has the same base name as the orphan temporary table.

Note:

If there is no '.frm' file, you can recreate it. The '.frm' file must have the same table schema as the orphan temporary table (it must have the same columns and indexes) and must be placed in the database directory of the orphan temporary table.

To identify orphan temporary tables on your system, you can query the Information Schema note 'INNODB_SYS_TABLES': information-schema-innodb-sys-tables-table. table. Look for table names that begin with '#sql'. If the original table resides in a file-per-table tablespace, the tablespace file (the '#sql-.ibd' file) for the orphan temporary table should be visible in the database directory.

 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

To remove an orphan temporary table, drop the table by issuing a *note 'DROP TABLE': drop-table. statement, prefixing the name of the table with '#mysql50#' and enclosing the table name in backticks. For example:

 mysql> DROP TABLE `#mysql50##sql-540_3`;

The '#mysql50#' prefix tells MySQL to ignore 'file name safe encoding' introduced in MySQL 5.1. Enclosing the table name in backticks is required to perform SQL statements on table names with special characters such as '#'.

Note:

If MySQL exits in the middle of an table-copying *note 'ALTER TABLE': alter-table. operation on a partitioned table, you may be left with multiple orphan temporary tables, one per partition. In this case, use the following procedure to remove the orphan temporary tables:

1. In a separate instance of the same MySQL version, create a non-partitioned table with the same schema name and columns as the partitioned table.

2. Copy the '.frm' file of the non-partitioned table to the database directory with the orphan temporary tables.

3. Make a copy of the '.frm' file for each table, and rename the '.frm' files to match the names of the orphan temporary tables (as described above).

4. Perform a *note 'DROP TABLE': drop-table. operation (as described above) for each table.

Tablespace Does Not Exist

With 'innodb_file_per_table' enabled, the following message might occur if the '.frm' or '.ibd' files (or both) are missing:

 InnoDB: in InnoDB data dictionary has tablespace id N,
 InnoDB: but tablespace with that id or name does not exist. Have
 InnoDB: you deleted or moved .ibd files?
 InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
 InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
 InnoDB: table still exists in the InnoDB internal data dictionary.

If this occurs, try the following procedure to resolve the problem:

1. Create a matching '.frm' file in some other database directory and copy it to the database directory where the orphan table is located.

2. Issue *note 'DROP TABLE': drop-table. for the original table. That should successfully drop the table and 'InnoDB' should print a warning to the error log that the '.ibd' file was missing.

Restoring Orphan File-Per-Table ibd Files

This procedure describes how to restore orphan file-per-table '.ibd' files to another MySQL instance. You might use this procedure if the system tablespace is lost or unrecoverable and you want to restore '.ibd' file backups on a new MySQL instance.

The procedure is not supported for general tablespace '.ibd' files.

The procedure assumes that you only have '.ibd' file backups, you are recovering to the same version of MySQL that initially created the orphan '.ibd' files, and that '.ibd' file backups are clean. See *note innodb-migration:: for information about creating clean backups.

Table import limitations outlined in *note innodb-table-import:: are applicable to this procedure.

1. On the new MySQL instance, recreate the table in a database of the same name.

     mysql> CREATE DATABASE sakila;
   
     mysql> USE sakila;
   
     mysql> CREATE TABLE actor (
              actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
              first_name VARCHAR(45) NOT NULL,
              last_name VARCHAR(45) NOT NULL,
              last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              PRIMARY KEY  (actor_id),
              KEY idx_actor_last_name (last_name)
            )ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Discard the tablespace of the newly created table.

     mysql> ALTER TABLE sakila.actor DISCARD TABLESPACE;

3. Copy the orphan '.ibd' file from your backup directory to the new database directory.

     $> cp /backup_directory/actor.ibd PATH/TO/MYSQL-5.7/DATA/sakila/

4. Ensure that the '.ibd' file has the necessary file permissions.

5. Import the orphan '.ibd' file. A warning is issued indicating that 'InnoDB' tries to import the file without schema verification.

     mysql> ALTER TABLE sakila.actor IMPORT TABLESPACE; SHOW WARNINGS;
     Query OK, 0 rows affected, 1 warning (0.15 sec)
   
     Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory)
     Error opening './sakila/actor.cfg', will attempt to import
     without schema verification

6. Query the table to verify that the '.ibd' file was successfully restored.

     mysql> SELECT COUNT(*) FROM sakila.actor;
     +----------+
     | count(*) |
     +----------+
     |      200 |
     +----------+

 File: manual.info.tmp, Node: innodb-error-handling, Prev: innodb-troubleshooting-datadict, Up: innodb-troubleshooting

14.22.4 InnoDB Error Handling

The following items describe how 'InnoDB' performs error handling. 'InnoDB' sometimes rolls back only the statement that failed, other times it rolls back the entire transaction.

• If you run out of file space in a tablespace, a MySQL 'Table is full' error occurs and 'InnoDB' rolls back the SQL statement.

• A transaction deadlock causes 'InnoDB' to roll back the entire transaction. Retry the entire transaction when this happens.

  A lock wait timeout causes 'InnoDB' to roll back the current statement (the statement that was waiting for the lock and encountered the timeout). To have the entire transaction roll back, start the server with '--innodb-rollback-on-timeout' enabled. Retry the statement if using the default behavior, or the entire transaction if '--innodb-rollback-on-timeout' is enabled.

  Both deadlocks and lock wait timeouts are normal on busy servers and it is necessary for applications to be aware that they may happen and handle them by retrying. You can make them less likely by doing as little work as possible between the first change to data during a transaction and the commit, so the locks are held for the shortest possible time and for the smallest possible number of rows. Sometimes splitting work between different transactions may be practical and helpful.

• A duplicate-key error rolls back the SQL statement, if you have not specified the 'IGNORE' option in your statement.

• A 'row too long error' rolls back the SQL statement.

• Other errors are mostly detected by the MySQL layer of code (above the 'InnoDB' storage engine level), and they roll back the corresponding SQL statement. Locks are not released in a rollback of a single SQL statement.

During implicit rollbacks, as well as during the execution of an explicit note 'ROLLBACK': commit. SQL statement, note 'SHOW PROCESSLIST': show-processlist. displays 'Rolling back' in the 'State' column for the relevant connection.

 File: manual.info.tmp, Node: innodb-limits, Next: innodb-restrictions-limitations, Prev: innodb-troubleshooting, Up: innodb-storage-engine