MariaDB缺失customers表重建报121/150错误如何排查解决

你已经清理了INFORMATION_SCHEMA.KEY_COLUMN_USAGE中可见的外键记录后仍报错，基本都是SQL层元数据和InnoDB引擎层元数据不一致、或者漏查了跨范围的约束导致的，按以下优先级排查：

• 优先排查InnoDB引擎内部的系统表残留
  KEY_COLUMN_USAGE是SQL层的元数据视图，异常删表时不会同步清理InnoDB存放在ibdata1系统表空间里的内部外键记录，这些孤立记录是触发150错误的最常见原因。直接执行以下SQL查询引擎层真实留存的外键条目：

-- MySQL 8.0以上版本请将表名替换为information_schema.INNODB_FOREIGN
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN 
WHERE FOR_NAME LIKE '%/customers' OR REF_NAME LIKE '%/customers';

查出来的结果里，FOR_NAME是外键所在的子表名，REF_NAME是被引用的父表名，把所有匹配到的外键，在对应子表上执行ALTER TABLE 子表名 DROP FOREIGN KEY 外键约束名;清理即可。如果常规删除报错，先临时执行SET FOREIGN_KEY_CHECKS=0;再操作，清理完记得把参数改回1。

• 排查跨库外键引用
  如果你之前查KEY_COLUMN_USAGE的时候只扫描了当前业务库，要注意MySQL支持跨库外键：实例下其他库的表如果创建过指向当前库customers表的外键，异常删表后这些约束不会被自动清理，也会阻断建表。不带TABLE_SCHEMA过滤条件全量查询KEY_COLUMN_USAGE中REFERENCED_TABLE_NAME = 'customers'的记录，即可找到漏网的约束。
• 检查磁盘层面的孤立表文件残留
  如果实例开启了innodb_file_per_table，异常删表时可能残留customers.frm、customers.ibd文件在数据目录对应库的文件夹下。先关闭实例，把残留文件移走备份，再启动实例执行FLUSH TABLES;后重试建表。
• 校验建表语句本身的定义匹配度
  如果上述残留都清理完还是报错，检查备份导出的建表语句：
• 被其他表外键引用的字段（通常是customers表的主键customer_id）必须显式创建主键或者唯一索引，不能是无索引的普通字段
• 被引用字段的字段类型、无符号属性、字符集、排序规则必须和所有关联表的外键字段完全一致，比如旧表主键是INT UNSIGNED，建表时写成INT SIGNED就会触发150错误
• 检查建表语句里定义的约束、索引名有没有和实例内已存在的对象重名——errno 121本身就是对象重名错误，InnoDB要求外键约束名在整个实例范围内全局唯一，哪怕是其他库的约束和你建表时写的约束重名，也会报错。
• 紧急恢复临时绕过方案
  如果业务停机时间要求紧，可以先执行SET FOREIGN_KEY_CHECKS=0;，再跑customers的建表语句，建完立刻把参数改回1。后续再逐个对关联表执行ALTER TABLE 关联表名 ENGINE=InnoDB;，触发引擎重建表元数据，自动清理掉孤立的残留约束，再补建需要的外键即可。

注意：所有操作前一定要先对当前生产实例做冷备份，避免清理操作导致更多数据损坏。

内容的提问来源于stack exchange，提问作者Neal Crankshaw