Instance Reboot Failure or ERROR 1146: Table 'xxx' doesn't exist Reported During Table Operations

Scenario

• After an RDS for MySQL DB instance is rebooted, the following error message is displayed when you perform any table operation:

  ERROR 1146: Table `xxx` doesn't exist

  In addition, the following information is displayed in the error log:

  [Warning] InnoDB: Load table `xxx` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again. 
  [Warning] InnoDB: Cannot open table 'xxx' from the internal data dictionary of InnoDB though the .frm file for the table exists.

• A DB instance fails to be rebooted due to incorrect foreign key usage and the following error information is displayed in the error log:

  [Warning] InnoDB: Load table `xxx` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again. 
  [Warning] InnoDB: Cannot open table xxx/xxx from the internal data dictionary of InnoDB though the .frm file for the table exists.

Fault Analysis

The possible cause is that the foreign key added does not meet the corresponding conditions and constraints. For details about the foreign key usage rules, see FOREIGN KEY Constraints.

RDS for MySQL uses variable foreign_key_checks (default value: ON) to control foreign key constraint check. When foreign_key_checks is set to OFF, the foreign key constraint check does not take effect. In this case, no error will be reported even if you add an incorrect foreign key. After the DB instance is rebooted, foreign_key_checks is enabled by default. InnoDB checks foreign key constraints when opening a table, thus generating an error.

The common reasons are as follows:

• The character set of the foreign key-related column in the parent or child table has been changed.

  RDS for MySQL 5.6, 5.7, and 8.0 allow you to modify the character set of the foreign key-related column in the parent or child table when foreign_key_checks is set to OFF. After the DB instance is rebooted:

  • RDS for MySQL 5.6 and 5.7: A warning will be displayed in the error log and the parent table cannot be used.
  • RDS for MySQL 8.0: No warning will be displayed in the error log and the parent table can be used.
• The index of the foreign key-related column in the parent or child table has been deleted.
  • RDS for MySQL 5.7 and 8.0: The index of the foreign key-related column in the parent or child table cannot be deleted when foreign_key_checks is set to OFF.
  • RDS for MySQL 5.6: The index of the foreign key-related column in the parent or child table can be deleted when foreign_key_checks is set to OFF. After the index is deleted, a warning will be displayed when the DB instance is rebooted. In addition, the table whose index was deleted cannot be used.

Solution

• If the problem is caused by character set modification, set foreign_key_checks to OFF and change the character sets of the foreign key-related columns in the parent and child tables to be the same.
• If the problem is caused by index deletion, set foreign_key_checks to OFF and recreate an index.