外键使用不规范导致实例重启失败或执行表操作报错error 1146: table 'xxx' doesn't exist-九游平台

场景描述

• 重启rds for mysql实例后，在进行表相关操作时，提示如下错误：

  error 1146: table `xxx` doesn't exist

  同时，在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.

• 由于外键使用不规范，导致实例重启失败，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.

故障分析

发生该问题的原因可能是用户添加的外键不满足相应条件和限制，具体外键使用规则参考。

rds for mysql通过变量foreign_key_checks（默认值为on）来控制外键限制检查，当foreign_key_checks设置为off时，外键限制检查不生效，此时用户添加或修改的外键可以不满足外键限制而不会报错，在实例重启时，foreign_key_checks默认开启，innodb打开表时会进行外键限制检查，此时会报错。

常见的情况有以下两种：

• 更改了父表和子表外键相关列的字符集

  mysql 5.6、5.7、8.0允许在foreign_key_checks设置为off的情况下，修改父表和子表外键相关列的字符集。实例重启以后：

  • mysql 5.6、5.7会在error log中提示warning，同时无法使用父表。
  • mysql 8.0不会在error log中提示warning，可以使用父表。
• 删除了父表和子表外键相关列的索引
  • 对于mysql 5.7、8.0：不允许在foreign_key_checks设置为off的情况下，删除父表和子表外键相关列的索引。
  • 对于mysql 5.6：允许在foreign_key_checks设置为off的情况下，删除父表和子表外键相关列的索引。删除相关列索引后，重启实例会提示warning，同时被删除索引的表无法使用。

九游平台的解决方案

• 对于修改字符集导致的问题，将foreign_key_checks设置为off，将父表和子表外键相关列的字符集修改一致。
• 对于删除索引导致的问题，将foreign_key_checks设置为off，重建索引。