Error after restarting an RDS for MySQL instance: ERROR 1146 (42S02): Table 'xxx.xxx' doesn't exist - ApsaraDB RDS

After restarting an ApsaraDB RDS for MySQL instance, table operations fail with:

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

The error log contains one or both of these warnings:

[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.

Why this happens

The foreign_key_checks variable controls whether InnoDB validates foreign key constraints. Setting it to OFF at runtime disables constraint checking. The default value is ON. When an RDS instance restarts, the variable resets to ON, and InnoDB validates foreign keys as it opens each table. If the foreign key metadata is inconsistent due to changes made while foreign_key_checks was OFF, InnoDB cannot open the affected tables and reports ERROR 1146.

Cause 1: Character set mismatch in foreign key columns

Foreign key columns in the parent and child tables use different character sets. The character sets were changed while foreign_key_checks was OFF. InnoDB accepted the change at runtime but cannot open the affected child table after a restart.

Version-specific behavior

MySQL version	Error log warning	Child table usable after restart
MySQL 5.7	Displayed	No (ERROR 1146)
MySQL 8.0	Not displayed	Yes

Solution

Disable foreign key checks:
```
   SET foreign_key_checks = OFF;
```
Change the foreign key columns in both the parent and child tables to the same character set: Replace <child_table>, <parent_table>, <fk_column>, and <referenced_column> with actual table and column names. Adjust the column type and character set to match your schema.
```
   ALTER TABLE <child_table> MODIFY <fk_column> VARCHAR(255) CHARACTER SET utf8mb4;
   ALTER TABLE <parent_table> MODIFY <referenced_column> VARCHAR(255) CHARACTER SET utf8mb4;
```
Re-enable foreign key checks:
```
   SET foreign_key_checks = ON;
```

Cause 2: Deleted indexes on foreign key columns

Indexes on foreign key columns were deleted while foreign_key_checks was OFF. After a restart, InnoDB cannot open the table because the required indexes are missing.

Version-specific behavior

MySQL version	Can delete foreign key indexes when `foreign_key_checks = OFF`	Table usable after restart
MySQL 5.6	Yes	No
MySQL 5.7	No (operation blocked)	N/A
MySQL 8.0	No (operation blocked)	N/A

This issue only affects MySQL 5.6 instances. MySQL 5.7 and MySQL 8.0 prevent deletion of foreign key indexes even when foreign_key_checks is OFF.

Solution

Disable foreign key checks:
```
   SET foreign_key_checks = OFF;
```
Recreate the missing indexes on the foreign key columns: Replace <table_name>, <index_name>, and <fk_column> with actual values from your schema.
```
   ALTER TABLE <table_name> ADD INDEX <index_name> (<fk_column>);
```
Re-enable foreign key checks:
```
   SET foreign_key_checks = ON;
```

References

FOREIGN KEY Constraints (MySQL official documentation)