问题背景

最近线上遇到一个问题，用户重启实例后发现有张表打不开了，经调研后发现是用户之前的霸蛮操作导致的，下面给出复现步骤：

  1. create table t1 (id int not null primary key, name varchar(100) not null) engine=innodb;
  2. create table t2 (id int not null primary key, fid int not null, name varchar(100) not null, CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)) engine=innodb;
  3. insert into t1 values(1, 'test 1'),(2, 'test 2'),(3,'test 3');
  4. insert into t2 values(1, 1, 'test 1'),(2, 1, 'test 2'),(3, 2, 'test 3');
  5. show create table t2;
  6. show create table t2\G
  7. *************************** 1. row ***************************
  8. Table: t2
  9. Create Table: CREATE TABLE `t2` (
  10. `id` int(11) NOT NULL,
  11. `fid` int(11) NOT NULL,
  12. `name` varchar(100) NOT NULL,
  13. PRIMARY KEY (`id`),
  14. KEY `fk_fid` (`fid`),
  15. CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
  16. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  18. -- 霸蛮的删掉外键约束用的索引
  19. set foreign_key_checks=off;
  20. alter table t2 drop index `fk_fid`;
  23. -- 重启mysqld前表t2正常
  24. set foreign_key_checks=on;
  25. show create table t2;
  26. *************************** 1. row ***************************
  27. Table: t2
  28. Create Table: CREATE TABLE `t2` (
  29. `id` int(11) NOT NULL,
  30. `fid` int(11) NOT NULL,
  31. `name` varchar(100) NOT NULL,
  32. PRIMARY KEY (`id`),
  33. CONSTRAINT `fk_fid` FOREIGN KEY (`fid`) REFERENCES `t1` (`id`)
  34. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  36. -- 重启mysqld
  37. show tables;
  38. +----------------+
  39. | Tables_in_test |
  40. +----------------+
  41. | t1             |
  42. | t2             |
  43. +----------------+
  44. show create table t2;
  45. ERROR 1146 (42S02): Table 'test.t2' doesn't exist

重启后t2表打不开了。

问题分析

在MySQL中，为了能够快速的检查外键约束，需要子表和父表对应的列上都要有索引，如果对应字段上没有索引, 子表在建立外键约束的时候，会自动加上。

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

子表上的外键索引正常情况是删不掉，如果试图去删除的话会报错：

  1. ERROR 1553 (HY000): Cannot drop index 'fk_fid': needed in a foreign key constrain

删索引时的外键检查逻辑在 innobase_check_foreign_key_index()函数中，感兴趣的同学可以自己看下，调用栈如下：

  1. #0  innobase_check_foreign_key_index
  2. #1  ha_innobase::prepare_inplace_alter_table
  3. #2  handler::ha_prepare_inplace_alter_table
  4. #3  mysql_inplace_alter_table
  5. #4  mysql_alter_table
  6. #5  Sql_cmd_alter_table::execute
  7. #6  mysql_execute_command
  8. #7  mysql_parse
  9. #8  dispatch_command
  10. #9  do_command
  11. #10 do_handle_one_connection
  12. #11 handle_one_connection
  13. #12 pfs_spawn_thread
  14. #13 start_thread
  15. #14 clone

但是如果用户设置了 set foreign_key_checks=off 后，删除索引时就不会进入 innobase_check_foreign_key_index() ，所以索引能被删掉。

在上面的复现步骤中还可以看到，如果不重启的话，表是能正常打开的，即使是做了flush tables，表也能正常打开，这又是为什么呢？

我们知道MySQL有个table_open_cache，这是server层对打开表的缓存，flush tables会把这个cache清掉；InnoDB层的字典系统对打开的表也有一个缓存，基于这个缓存又构建了2个链表dict_sys->table_LRU 和 dict_sys->table_non_LRU，位于前者中的表缓存会被系统用LRU算法清理掉，后者不会，清理由InnoDB后台线程做，flush tables 不会触发清理。并且如果InnoDB表有外键的话，缓存是被放在dict_sys->table_non_LRU上的，所以不会被后台线程清理掉，因此成功打开一次后就不会出问题了。关于含外键表缓存被放在 dict_sys->table_non_LRU 上，具体的调用栈如下：

  1. #0  dict_table_move_from_lru_to_non_lru
  2. #1  dict_foreign_add_to_cache
  3. #2  dict_load_foreign
  4. #3  dict_load_foreigns
  5. #4  dict_load_table
  6. #5  dict_table_open_on_name
  7. #6  ha_innobase::open
  8. #7  handler::ha_open
  9. #8  open_table_from_share
  10. #9  open_table
  11. #10 open_and_process_table
  12. #11 open_tables
  13. #12 open_tables
  14. #13 mysqld_show_create
  15. #14 mysql_execute_command
  16. #15 mysql_parse
  17. #16 dispatch_command
  18. #17 do_command
  19. #18 do_handle_one_connection
  20. #19 handle_one_connection
  21. #20 pfs_spawn_thread
  22. #21 start_thread
  23. #22 clone

关于这点官方文档也有描述：

InnoDB system table instances and parent and child table instances with foreign key relationships are not placed on the LRU list and are not subject to eviction from memory.

问题解决

通过上面的分析可以看出之所以表打不开，是因为用户霸蛮的删除了外键依赖的索引，但是能让用户通过SQL就把表弄的不能访问，也算是数据库系统的bug，也早有人给官方提了bug，详见bug#68148、bug#70260、bug#74659。

MySQL 5.6.12 对此进行了修复，如果遇到这种情况表打不开的话，只需要先 set foreign_key_checks=off，然后表就可以打开了，这时把缺失的索引再加上就可以了，修复详情见patch。

上面的修复是在出问题后，其实最好是能从源头上杜绝，不让用户删除外键约束需要的索引，因为这本身就是错误的操作，因此MySQL 5.7.5又出了一个patch，让用户无法删除外键约束的索引，即使用 set foreign_key_checks=off 关闭掉外键约束检查。