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      c21       1select * from t2;c1      c2insert 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 Tablet2      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=‘[email protected]/t2_ibfk_1‘;+-------------------+------------+----------+--------+------+| ID                | FOR_NAME   | REF_NAME | N_COLS | TYPE |+-------------------+------------+----------+--------+------+| [email protected]/t2_ibfk_1 | [email protected]/t2 | a-b/t1   |      1 |    0 |+-------------------+------------+----------+--------+------+select * from information_schema.innodb_sys_tables where name=‘[email protected]/t1‘;+----------+------------+------+--------+-------+-------------+------------+---------------+| TABLE_ID | NAME       | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE |+----------+------------+------+--------+-------+-------------+------------+---------------+|      530 | [email protected]/t1 |    1 |      5 |   525 | Antelope    | Compact    |             0 |+----------+------------+------+--------+-------+-------------+------------+---------------+

表結構正常，表面上看外鍵在系統資料表中中繼資料庫資訊正常。仔細比較發現 innodb_sys_foreign 的REF_NAME欄位"a-b/t1"實際應為"[email protected]/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=‘[email protected]/t2_ibfk_1‘;+-------------------+------------+------------+--------+------+| ID                | FOR_NAME   | REF_NAME   | N_COLS | TYPE |+-------------------+------------+------------+--------+------+| [email protected]/t2_ibfk_1 | [email protected]/t2 | [email protected]/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

MySQL 外鍵異常分析