Scenario Description
A business instance was restarted after a routine change. After the restart, the database instance started normally, but the following exceptions occurred:
When 'table' is accessed, an error is received from the client or application side: table 'parent' doesn't exist;.
The primary database appears normal, but the secondary database fails to load the table, causing the replication thread to disconnect abnormally (the Replica SQL thread reports an error).
A scheduled backup task fails, and the backup tool reports that the table does not exist when the backup tool is executing SHOW TABLES, FLUSH TABLES, or exporting the table.
The error log contains information indicating that InnoDB failed to load foreign key relationships during the open table phase.
Cause Analysis
Normal Behavior (for Comparison)
When a field is referenced by a foreign key, MySQL protects it from arbitrary modification. For example, directly execute:
ALTER TABLE parent MODIFY id VARCHAR(10) CHARACTER SET utf8mb4;
The expected protective error is returned:
ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint
Bug Trigger Conditions
When foreign_key_checks=0 is set for a session, MySQL intends to skip row-by-row foreign key checks during the DML phase. However, this setting also bypasses the protective check during the ALTER phase that prevents fields referenced by foreign keys from being modified, causing the preceding ALTER operation to succeed.
However, the ALTER operation only updated the column definition in the .frm file (for example, utf8 to utf8mb4). It did not synchronously update the collation records of the foreign key index in the InnoDB data dictionary, nor did it rebuild the foreign key secondary index on the child table. At this point, the character set of the parent table field has changed to utf8mb4_general_ci, while the foreign key index record on the child table remains at utf8_general_ci.
Verification Failure After Restart (Fault Outbreak Point)
After the instance restarts, InnoDB performs the dict_load_foreigns foreign key index consistency check during the open table phase and discovers the following:
Referenced field in the parent table: utf8mb4_general_ci.
Foreign key index record in the child table: utf8_general_ci.
The two do not match, so InnoDB refuses to load the table, which is externally reported as "Table 'parent' doesn't exist". Because this table is part of the replication link and backup set, a chain of failures occurs, including abnormal disconnection of the secondary database and backup failures.
Root Cause Summary
foreign_key_checks=0
ALTER bypasses the protection for fields referenced by foreign keys.
Only the .frm file is modified, while the InnoDB dictionary collation is not synchronized / the foreign key index on the child table is not rebuilt.
After the instance restarts, the dict_load_foreigns check reports an inconsistency.
The table is refused to load, resulting in "Table doesn't exist", which then causes the secondary database to disconnect / the backup to fail.
Problem-solving Ideas
1. Do not disable foreign key checks during change processes.
2. Upgrade to MySQL 8.0: 8.0 uses a transactional data dictionary, so ALTER checks on fields referenced by foreign keys are not affected by foreign_key_checks and are always enforced, which completely avoids this issue.
Operation Steps
1. When performing change operations, ensure that the target field is not referenced by any foreign key. Otherwise, do not execute SET foreign_key_checks=0 and then perform ALTER on such fields.
Impact Scope
|
MySQL 5.6 (all versions) | Affected. | foreign_key_checks=0 can bypass ALTER foreign key protection. |
MySQL 5.7 (all versions) | Affected. | Same as above. |
MySQL 8.0,MySQL 8.4 | Unaffected. | Transactional data dictionary: ALTER checks on referenced fields are always forcibly intercepted. |
Bug Reference Links