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)