MySQL FOREIGN key Anomaly analysis

FOREIGN KEY constraint exception phenomenon

In the following example, there is no insertion failure that violates the referential constraint.

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); //预期应该成功实际失败了。子表插入任何数据都会报违反引用约束。

Anomaly Analysis

First, we'll check that the table structure is normal.

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

View Innodb_sys_foreign Table

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 |+----------+------------+------+--------+-------+-------------+------------+---------------+

Table structure is normal, on the surface of the foreign key in the system table in the meta-database information is normal. A closer look at the innodb_sys_foreign ref_name field "A-b/t1" should actually be "[email protected]/t2".

MySQL internal table name and library name storage format

MySQL internally uses the My_charset_filename character set for table names and library names.

The following array defines the characters that the My_charset_filename character set needs to be converted. The array is labeled as an ASCII value, and 1 means no conversion is required. You can see that alphanumeric and underscore do not need to be converted, while the character '-' is required to be converted, see the conversion function 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{|}~. */};

Anomaly Analysis

As we know from the previous section, the character '-' is required to be converted as a library name or table name. For_name values in Innodb_sys_foreign are converted, only ref_name are not converted, and table names stored in the system table Innodb_sys_tables are converted. dict_get_referenced_tablea-b/t1 to system table Sys_tables lookup will not find records according to the table name that is not converted. This can lead to

 foreign->referenced_table==NULL

Therefore, any insertions to the child table will return error db_no_referenced_row, as shown in the following code

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;

After further debugging, it is found that innobase_get_foreign_key_info the library and table names of the main table in the function are not converted, but rather the system character set is used directly.

Go back and look at the trigger condition of the bug:

1. The table name or library name contains special characters;
2. This table serves as the primary table for referential constraints;
3. Adding the referential constraint is setting the set foreign_key_checks=0;

The 3rd is emphasized here, if the set foreign_key_checks=0 is removed from the above example, then the result ref_name will be converted normally.

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 and foreign key

MySQL 5.6 Online DDL is built to support indexing. And for the built foreign key index is also supported, the condition is 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); }

When SET foreign_key_checks=0, prebuilt->trx->check_foreigns false.

Let's look at the problem function again innobase_get_foreign_key_info , only the code path of the online DDL calls this function:

#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......

Instead of the online DDL path, the function dict_scan_id converts the table name and the library name:

#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......

Repair

Although there is no relevant bug information in the bug system, from MySQL 5.6.26 we see that the official bug#21094069 has been repaired, innobase_get_foreign_key_info and the library name and table name are converted in.

Reference commit:1fae0d42c352908fed03e29db2b391a0d2969269

MySQL FOREIGN key Anomaly analysis