MySQL · 捉虫动态 · MySQL 外键异常分析 · 数据库内核月报

## 外键约束异常现象如下测例中，没有违反引用约束的插入失败。 ~~~ create database `a-b`; use `a-b`; SET FOREIGN_KEY_CHECKS=0; create table t1(c1 int primary key, c2 int) engine=innodb; create table t2(c1 int primary key, c2 int) engine=innodb; alter table t2 add foreign key(c2) references `a-b`.t1(c1); SET FOREIGN_KEY_CHECKS=1; insert into t1 values(1,1); select * from t1; c1 c2 1 1 select * from t2; c1 c2 insert into t2 values(1,1); ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`a-b`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `a-b`.`t1` (`c1`)) insert into t2 values(1,1); //预期应该成功实际失败了。子表插入任何数据都会报违反引用约束。 ~~~ ## 异常分析首先我们会检查表结构是否正常 ~~~ show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `c1` int(11) NOT NULL, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`c1`), KEY `c2` (`c2`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `a-b`.`t1` (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ~~~ 查看 innodb_sys_foreign 表 ~~~ select * from information_schema.innodb_sys_foreign where id='a@002db/t2_ibfk_1'; +-------------------+------------+----------+--------+------+ | ID | FOR_NAME | REF_NAME | N_COLS | TYPE | +-------------------+------------+----------+--------+------+ | a@002db/t2_ibfk_1 | a@002db/t2 | a-b/t1 | 1 | 0 | +-------------------+------------+----------+--------+------+ select * from information_schema.innodb_sys_tables where name='a@002db/t1'; +----------+------------+------+--------+-------+-------------+------------+---------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | +----------+------------+------+--------+-------+-------------+------------+---------------+ | 530 | a@002db/t1 | 1 | 5 | 525 | Antelope | Compact | 0 | +----------+------------+------+--------+-------+-------------+------------+---------------+ ~~~ 表结构正常，表面上看外键在系统表中元数据库信息正常。仔细比较发现 innodb_sys_foreign 的REF_NAME字段”a-b/t1”实际应为”a@002db/t2”。 ## MySQL内部表名和库名存储格式 MySQL 内部用 my_charset_filename 字符集来表名和库名。以下数组定义了 my_charset_filename 字符集需要转换的字符。数组下标为 ascii 值，1代表不需要转换。可以看到字母数字和下划线等不需要转换，同时字符’-‘是需要转换的，转换函数参见`my_wc_mb_filename`。 ~~~ static char filename_safe_char[128]= { 1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, /* ................ */ 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, /* ................ */ 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, /* !"#$%&'()*+,-./ */ 1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0, /* 0123456789:;<=>? */ 0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, /* @ABCDEFGHIJKLMNO */ 1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,1, /* PQRSTUVWXYZ[\]^_ */ 0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1, /* `abcdefghijklmno */ 1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0, /* pqrstuvwxyz{|}~. */ }; ~~~ ## 异常分析由上节可知，字符’-‘作为库名或表名是需要转换的。innodb_sys_foreign 中 FOR_NAME 值是转换过的，只有 REF_NAME 未转换，而系统表 innodb_sys_tables 中存储的表名是转换后的。`dict_get_referenced_table` 根据未转换的表名 a-b/t1 去系统表 SYS_TABLES 查找会查找不到记录。于是会导致 ~~~ foreign->referenced_table==NULL ~~~ 因此对子表的任何插入都会返回错误 DB_NO_REFERENCED_ROW，如下代码 ~~~ row_ins_check_foreign_constraint: if (check_ref) { check_table = foreign->referenced_table; check_index = foreign->referenced_index; } else { check_table = foreign->foreign_table; check_index = foreign->foreign_index; } if (check_table == NULL || check_table->ibd_file_missing || check_index == NULL) { if (!srv_read_only_mode && check_ref) { …… err = DB_NO_REFERENCED_ROW; } goto exit_func; ~~~ 经过进一步调试分析发现，函数`innobase_get_foreign_key_info`中主表的库名和表名都没有经过转换，而是直接使用系统字符集。回过头再看看bug的触发条件： 1. 表名或库名包含特殊字符； 2. 此表作为引用约束的主表； 3. 增加引用约束是设置了SET FOREIGN_KEY_CHECKS=0；这里强调下第3条, 如果上面的测例中去掉了SET FOREIGN_KEY_CHECKS=0，那么结果 REF_NAME会正常转换 ~~~ SET FOREIGN_KEY_CHECKS=1; create table t1(c1 int primary key, c2 int) engine=innodb; create table t2(c1 int primary key, c2 int) engine=innodb; alter table t2 add foreign key(c2) references `a-b`.t1(c1); select * from information_schema.innodb_sys_foreign where id='a@002db/t2_ibfk_1'; +-------------------+------------+------------+--------+------+ | ID | FOR_NAME | REF_NAME | N_COLS | TYPE | +-------------------+------------+------------+--------+------+ | a@002db/t2_ibfk_1 | a@002db/t2 | a@002db/t1 | 1 | 0 | +-------------------+------------+------------+--------+------+ ~~~ ## online DDL 与 foreign key MySQL 5.6 online DDL 是支持建索引的。而对于建外键索引同样也是支持的，条件是SET FOREIGN_KEY_CHECKS=0。 ~~~ ha_innobase::check_if_supported_inplace_alter： if ((ha_alter_info->handler_flags & Alter_inplace_info::ADD_FOREIGN_KEY) && prebuilt->trx->check_foreigns) { ha_alter_info->unsupported_reason = innobase_get_err_msg( ER_ALTER_OPERATION_NOT_SUPPORTED_REASON_FK_CHECK); DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED); } ~~~ SET FOREIGN_KEY_CHECKS=0时，`prebuilt->trx->check_foreigns`为false。我们再来看出问题的函数`innobase_get_foreign_key_info`，只有online DDL的代码路径才会调用此函数： ~~~ #0 innobase_get_foreign_key_info #1 ha_innobase::prepare_inplace_alter_table #2 handler::ha_prepare_inplace_alter_table #3 mysql_inplace_alter_table #4 mysql_alter_table ...... ~~~ 而非online DDL的路径如下，函数 `dict_scan_id` 会对表名和库名进行转换： ~~~ #0 dict_scan_id #1 dict_scan_table_name #2 dict_create_foreign_constraints_low #3 dict_create_foreign_constraints #4 row_table_add_foreign_constraints #5 ha_innobase::create #6 handler::ha_create #7 ha_create_table #8 mysql_alter_table ...... ~~~ ## 修复 bug系统中虽然没有相关的bug信息，但从MySQL 5.6.26中我们看到官方Bug#21094069已经进行了修复，在`innobase_get_foreign_key_info`中对库名和表名进行转换。参考commit:[1fae0d42c352908fed03e29db2b391a0d2969269](https://github.com/mysql/mysql-server/commit/1fae0d42c352908fed03e29db2b391a0d2969269)