[MDEV-30238] LOAD DATA REPLACE into ucs2-encoded FK field fails Created: 2022-12-15  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Nikita Malyavin Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-15990 REPLACE on a precise-versioned table ... Stalled
Relates
relates to MDEV-18879 WITH SYSTEM VERSIONING and FOREIGN KE... Closed
relates to MDEV-20812 Unexpected ER_ROW_IS_REFERENCED_2 or ... Closed

 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;



 Comments   
Comment by Nikita Malyavin [ 2022-12-15 ]

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.

Comment by Marko Mäkelä [ 2022-12-21 ]

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

Comment by Nikita Malyavin [ 2022-12-21 ]

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

Generated at Thu Feb 08 10:14:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.