[MDEV-30238] LOAD DATA REPLACE into ucs2-encoded FK field fails - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Fix Version/s: N/A
Component/s: Data Manipulation - Delete, Data Manipulation - Update
Labels:
None

Description

A replace from file of duplicated values into ucs2-encoded referencing field fails:

--source include/have_innodb.inc

create table t1 (

  pk int primary key,

  f char(10) character set ucs2,

  key(f)

) engine=innodb;

create table t2 (

  pk int primary key,

  f13 char(10) character set ucs2

) engine=innodb;

set foreign_key_checks = off;

alter table t2 add foreign key (f13) references t1 (f) on delete set null;

set foreign_key_checks = on;

insert into t1 values (1,'q');

insert into t2(pk, f13) values (1, 'q'), (2, 'q');

select * from t2 into outfile 't2.data';

load data infile 't2.data' replace into table t2;

# cleanup

drop table t2, t1;

10.3 c562ccf796c085211461386510ea5f7a8137cb96
mysqltest: At line 26: query 'load data infile 't2.data' replace
into table t2' failed: 1452: Cannot add or update a child row:
a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1`
FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL)

Expected behavior: no error. Note that if a foreign key is added during CREATE TABLE, LOAD DATA does not fail:

create table t1 (

  pk int primary key,

  f char(10) character set ucs2,

  key(f)

) engine=innodb;

create table t2 (

  pk int primary key,

  f13 char(10) character set ucs2 references t1 (f) on delete set null

) engine=innodb;

insert into t1 values (1,'q');

insert into t2(pk, f13) values (1, 'q'), (2, 'q');

select * from t2 into outfile 't2.data';

load data infile 't2.data' replace into table t2;

# cleanup

drop table t2, t1;

Attachments

Issue Links

blocks

MDEV-15990 REPLACE on a precise-versioned table returns duplicate key error (ER_DUP_ENTRY)

Stalled

relates to

MDEV-18879 WITH SYSTEM VERSIONING and FOREIGN KEY can insert a corrupted record

Closed

MDEV-20812 Unexpected ER_ROW_IS_REFERENCED_2 or server crash in row_ins_foreign_report_err upon DELETE from versioned table with FK

Closed

Activity

Ascending order - Click to sort in descending order

Nikita Malyavin added a comment - 2022-12-15 17:46 - edited

This issue is found during fixing of MDEV-15990. After i left only one execution branch for system versioning in replace (the one that corresponds to having triggers case), it started failing.

Since REPLACE on table with FK added on CREATE works fine, i conclude that the problem is not a file encoding.

With trigger actions logged like this:

create table t1 (

  pk int primary key,

  f char(10) character set ucs2,

  key(f)

) engine=innodb;

create table t2 (

  pk int unsigned primary key,

  f13 char(10) character set ucs2

) engine=innodb;

create table log(id int key auto_increment, s char(30)) engine=myisam;

eval create trigger tr1ins before insert on t2

  for each row insert log(s) values(concat(new.pk, ' ', new.f13));

eval create trigger tr1del before delete on t2

  for each row insert log(s) values(concat(old.pk, ' ', old.f13));

set foreign_key_checks = off;

alter table t2 add foreign key (f13) references t1 (f) on delete set null;

set foreign_key_checks = on;

insert into t1 values (1,'against'),(2,'q');

insert into t2(pk, f13) values (1, 'q'), (2, 'q');

select * from t2 into outfile 't2.data';

--error 1452

load data infile 't2.data' replace into table t2;

select * from log;

# cleanup

drop table t2, t1, log;

We'll have a result

select * from log;

id	s

1	1 q

2	1 q

The first row is BEFORE INSERT log, the second is BEFORE DELETE.

Insert detects duplicate correctly, then Delete, presumably, correctly deletes. After that, Insert is retried and is failed with ER_NO_REFERENCED_ROW_2.

So, innodb matched against duplicate correctly, but did incorrectly match against child row.

Nikita Malyavin added a comment - 2022-12-15 17:46 - edited This issue is found during fixing of MDEV-15990 . After i left only one execution branch for system versioning in replace (the one that corresponds to having triggers case), it started failing. Since REPLACE on table with FK added on CREATE works fine, i conclude that the problem is not a file encoding. With trigger actions logged like this: create table t1 ( pk int primary key , f char (10) character set ucs2, key (f) ) engine=innodb; create table t2 ( pk int unsigned primary key , f13 char (10) character set ucs2 ) engine=innodb; create table log(id int key auto_increment, s char (30)) engine=myisam; eval create trigger tr1ins before insert on t2 for each row insert log(s) values (concat(new.pk, ' ' , new.f13)); eval create trigger tr1del before delete on t2 for each row insert log(s) values (concat(old.pk, ' ' , old.f13)); set foreign_key_checks = off ; alter table t2 add foreign key (f13) references t1 (f) on delete set null ; set foreign_key_checks = on ; insert into t1 values (1, 'against' ),(2, 'q' ); insert into t2(pk, f13) values (1, 'q' ), (2, 'q' ); select * from t2 into outfile 't2.data' ; --error 1452 load data infile 't2.data' replace into table t2; select * from log; # cleanup drop table t2, t1, log; We'll have a result select * from log; id s 1 1 q 2 1 q The first row is BEFORE INSERT log, the second is BEFORE DELETE. Insert detects duplicate correctly, then Delete, presumably, correctly deletes. After that, Insert is retried and is failed with ER_NO_REFERENCED_ROW_2 . So, innodb matched against duplicate correctly, but did incorrectly match against child row.

Marko Mäkelä added a comment - 2022-12-21 16:16

The mention of triggers triggered me to suspect MDEV-12302, but indeed the test case is specific to ucs2 and works fine with utf8mb3.

Marko Mäkelä added a comment - 2022-12-21 16:16 The mention of triggers triggered me to suspect MDEV-12302 , but indeed the test case is specific to ucs2 and works fine with utf8mb3 .

Nikita Malyavin added a comment - 2022-12-21 17:09

A test case triggered this bug to be created is from ~~MDEV-18879~~ patch

Nikita Malyavin added a comment - 2022-12-21 17:09 A test case triggered this bug to be created is from MDEV-18879 patch

Nikita Malyavin added a comment - 2024-03-30 16:39 - edited

I have logged the table lookups with this patch

--- a/storage/innobase/row/row0ins.cc

+++ b/storage/innobase/row/row0ins.cc

@@ -1664,6 +1664,8 @@

 		cmp = cmp_dtuple_rec(entry, rec, offsets);

+		row_ins_foreign_report_add_err(

+						trx, foreign, rec, entry);

 		if (cmp == 0) {

 			if (rec_get_deleted_flag(rec,

 						 rec_offs_comp(offsets))) {

and the following test against the tables as they are created in the initial report, to see the difference with load-repace vs replace vs insert:

insert into t1 values (1,'q');

insert into t2(pk, f13) values (1, 'q'), (2, 'q');

replace into t2(pk, f13) values (1, 'q'), (2, 'q');

select * into outfile 't2.data' from t2 ;

load data infile 't2.data' replace into table t2;

the dict_foreign_err_file contents follow

2024-03-30 17:33:04 0x7d2c2eb016c0 Transaction:

TRANSACTION 33, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

2 lock struct(s), heap size 1152, 0 row lock(s), undo log entries 1

MySQL thread id 4, OS thread handle 137628715325120, query id 25 localhost root Update

insert into t2(pk, f13) values (1, 'q'), (2, 'q')

Foreign key constraint fails for table `test`.`t2`:

  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL in parent table, in index f13 tuple:

DATA TUPLE: 2 fields;

 0: len 20; hex 0071002000200020002000200020002000200020; asc  q                  ;;

 1: len 4; hex 80000001; asc     ;;

But in parent table `test`.`t1`, in index f,

the closest match we can find is record:

PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 20; hex 0071002000200020002000200020002000200020; asc  q                  ;;

 1: len 4; hex 80000001; asc     ;;

2024-03-30 17:33:04 0x7d2c2eb016c0 Transaction:

TRANSACTION 33, ACTIVE 0 sec inserting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1152, 1 row lock(s), undo log entries 2

MySQL thread id 4, OS thread handle 137628715325120, query id 25 localhost root Update

insert into t2(pk, f13) values (1, 'q'), (2, 'q')

Foreign key constraint fails for table `test`.`t2`:

  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL in parent table, in index f13 tuple:

DATA TUPLE: 2 fields;

 0: len 20; hex 0071002000200020002000200020002000200020; asc  q                  ;;

 1: len 4; hex 80000002; asc     ;;

But in parent table `test`.`t1`, in index f,

the closest match we can find is record:

PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 20; hex 0071002000200020002000200020002000200020; asc  q                  ;;

 1: len 4; hex 80000001; asc     ;;

2024-03-30 17:33:04 0x7d2c2eb016c0 Transaction:

TRANSACTION 36, ACTIVE 0 sec updating or deleting

mysql tables in use 1, locked 1

3 lock struct(s), heap size 1152, 1 row lock(s), undo log entries 1

MySQL thread id 4, OS thread handle 137628715325120, query id 28 localhost root Reading file

load data infile 't2.data' replace into table t2

Foreign key constraint fails for table `test`.`t2`:

  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL in parent table, in index f13 tuple:

DATA TUPLE: 2 fields;

 0: len 20; hex 0000007100200020002000200020002000200020; asc    q                ;;

 1: len 4; hex 80000001; asc     ;;

But in parent table `test`.`t1`, in index f,

the closest match we can find is record:

PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 20; hex 0071002000200020002000200020002000200020; asc  q                  ;;

 1: len 4; hex 80000001; asc     ;;

2024-03-30 17:33:04 0x7d2c2eb016c0 Transaction:

TRANSACTION 36, ACTIVE 0 sec updating or deleting

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1152, 2 row lock(s), undo log entries 1

MySQL thread id 4, OS thread handle 137628715325120, query id 28 localhost root Reading file

load data infile 't2.data' replace into table t2

Foreign key constraint fails for table `test`.`t2`:

  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL in parent table, in index f13 tuple:

DATA TUPLE: 2 fields;

 0: len 20; hex 0000007100200020002000200020002000200020; asc    q                ;;

 1: len 4; hex 80000001; asc     ;;

But in parent table `test`.`t1`, in index f,

the closest match we can find is record:

PHYSICAL RECORD: n_fields 2; compact format; info bits 0

 0: len 20; hex 0071002000200020002000200020002000200020; asc  q                  ;;

 1: len 4; hex 80000001; asc     ;;

As we can see, the requested record differs for the load data case.
But we won't see the replace query here. Probably, it diverges somewhere earlier.

Nikita Malyavin added a comment - 2024-03-30 16:39 - edited I have logged the table lookups with this patch --- a/storage/innobase/row/row0ins.cc +++ b/storage/innobase/row/row0ins.cc @@ -1664,6 +1664,8 @@ cmp = cmp_dtuple_rec(entry, rec, offsets); + row_ins_foreign_report_add_err( + trx, foreign, rec, entry); if (cmp == 0) { if (rec_get_deleted_flag(rec, rec_offs_comp(offsets))) { and the following test against the tables as they are created in the initial report, to see the difference with load-repace vs replace vs insert: insert into t1 values (1, 'q' ); insert into t2(pk, f13) values (1, 'q' ), (2, 'q' ); replace into t2(pk, f13) values (1, 'q' ), (2, 'q' ); select * into outfile 't2.data' from t2 ; load data infile 't2.data' replace into table t2; the dict_foreign_err_file contents follow 2024-03-30 17:33:04 0x7d2c2eb016c0 Transaction: TRANSACTION 33, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 2 lock struct(s), heap size 1152, 0 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 137628715325120, query id 25 localhost root Update insert into t2(pk, f13) values (1, 'q'), (2, 'q') Foreign key constraint fails for table `test`.`t2`: , CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL in parent table, in index f13 tuple: DATA TUPLE: 2 fields; 0: len 20; hex 0071002000200020002000200020002000200020; asc q ;; 1: len 4; hex 80000001; asc ;; But in parent table `test`.`t1`, in index f, the closest match we can find is record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 20; hex 0071002000200020002000200020002000200020; asc q ;; 1: len 4; hex 80000001; asc ;; 2024-03-30 17:33:04 0x7d2c2eb016c0 Transaction: TRANSACTION 33, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1152, 1 row lock(s), undo log entries 2 MySQL thread id 4, OS thread handle 137628715325120, query id 25 localhost root Update insert into t2(pk, f13) values (1, 'q'), (2, 'q') Foreign key constraint fails for table `test`.`t2`: , CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL in parent table, in index f13 tuple: DATA TUPLE: 2 fields; 0: len 20; hex 0071002000200020002000200020002000200020; asc q ;; 1: len 4; hex 80000002; asc ;; But in parent table `test`.`t1`, in index f, the closest match we can find is record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 20; hex 0071002000200020002000200020002000200020; asc q ;; 1: len 4; hex 80000001; asc ;; 2024-03-30 17:33:04 0x7d2c2eb016c0 Transaction: TRANSACTION 36, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1152, 1 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 137628715325120, query id 28 localhost root Reading file load data infile 't2.data' replace into table t2 Foreign key constraint fails for table `test`.`t2`: , CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL in parent table, in index f13 tuple: DATA TUPLE: 2 fields; 0: len 20; hex 0000007100200020002000200020002000200020; asc q ;; 1: len 4; hex 80000001; asc ;; But in parent table `test`.`t1`, in index f, the closest match we can find is record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 20; hex 0071002000200020002000200020002000200020; asc q ;; 1: len 4; hex 80000001; asc ;; 2024-03-30 17:33:04 0x7d2c2eb016c0 Transaction: TRANSACTION 36, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1152, 2 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 137628715325120, query id 28 localhost root Reading file load data infile 't2.data' replace into table t2 Foreign key constraint fails for table `test`.`t2`: , CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL in parent table, in index f13 tuple: DATA TUPLE: 2 fields; 0: len 20; hex 0000007100200020002000200020002000200020; asc q ;; 1: len 4; hex 80000001; asc ;; But in parent table `test`.`t1`, in index f, the closest match we can find is record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 20; hex 0071002000200020002000200020002000200020; asc q ;; 1: len 4; hex 80000001; asc ;; As we can see, the requested record differs for the load data case. But we won't see the replace query here. Probably, it diverges somewhere earlier.

Nikita Malyavin added a comment - 2024-03-30 18:30 - edited

Turns out, that the problem here is in LOAD DATA query, which didn't preserve the enoding

Nikita Malyavin added a comment - 2024-03-30 18:30 - edited Turns out, that the problem here is in LOAD DATA query, which didn't preserve the enoding

MariaDB Server

LOAD DATA REPLACE into ucs2-encoded FK field fails

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration