All Products
Search

This Product

    ApsaraDB RDS:What do I do if the "ERROR 1146 (42S02): Table 'xxx.xxx' doesn't exist" error message is displayed for my ApsaraDB RDS for MySQL instance?

    Document Center

    ApsaraDB RDS:What do I do if the "ERROR 1146 (42S02): Table 'xxx.xxx' doesn't exist" error message is displayed for my ApsaraDB RDS for MySQL instance?

    Last Updated:Oct 28, 2024

    Problem description

    After I restart my ApsaraDB RDS for MySQL instance and then perform table-related operations, the following error message is displayed:

    ERROR 1146 (42S02): Table 'xxx.xxx' doesn't exist

    The following information is included in the error log:

    [Warning] InnoDB: Load table `xxx`.`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.

    Causes

    In MySQL, you can add foreign keys only if the foreign keys meet the required constraints. For more information, see FOREIGN KEY Constraints.

    In MySQL, the foreign_key_checks variable is used to check whether foreign keys meet the required constraints. You can modify the variable when the RDS instance is running, and you do not need to restart the RDS instance for the modification to take effect. The default value of the variable is ON. If you set the foreign_key_checks variable to OFF, the check on foreign keys is disabled. In some cases, the value OFF cannot be used to disable the check on foreign keys. For more information, see FOREIGN KEY Constraints.

    If you disable the check on foreign keys and modify the foreign keys for the keys to fail to meet foreign key constraints, the error message is not displayed when the RDS instance is running. By default, the foreign_key_checks variable is set to ON when you restart the RDS instance. When you open an InnoDB table after your RDS instance is restarted, foreign keys are checked, and the error message is displayed.

    This section describes the possible causes of the preceding issue:

    • Cause 1: The character sets of foreign key-related columns in the parent table and child table are changed.

      If your RDS instance runs MySQL 5.6, MySQL 5.7, or MySQL 8.0, you can change the character sets of foreign key-related columns in the parent table and child table when the foreign_key_checks variable is set to OFF. After the RDS instance is restarted, the RDS instance may encounter different issues based on the database engine.

      • For MySQL 5.6 or MySQL 5.7, the error message is displayed in the error log, and the parent table cannot be used.

      • For MySQL 8.0, the error message is not displayed in the error log, and the parent table can be used.

    • Cause 2: The indexes of foreign key-related columns in the parent table and child table are deleted.

      • For MySQL 5.7 and MySQL 8.0, you cannot delete the indexes of the foreign key-related columns in the parent table and child table when the foreign_key_checks variable is set to OFF.

      • For MySQL 5.6, you can delete the indexes of the foreign key-related columns in the parent table and child table when the foreign_key_checks variable is set to OFF. After the indexes are deleted, the error message is displayed when you restart the RDS instance, and the table from which the indexes are deleted cannot be used.

    Solutions

    • Solution for Cause 1: Set the foreign_key_checks variable to OFF and change the character sets of the foreign key-related columns in the parent table and child table to the same character set.

    • Solution for Cause 2: Set the foreign_key_checks variable to OFF and recreate the indexes.