[MDEV-18141] Unexpected ER_KEY_NOT_FOUND upon REPLACE into table with foreign key with statement binary logging Created: 2019-01-05  Updated: 2019-09-02  Resolved: 2019-09-02

Status: Closed
Project: MariaDB Server
Component/s: Replication, Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3, 10.4
Fix Version/s: 10.2.27, 10.3.18, 10.4.8

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: affects-tests

Issue Links:
Duplicate
duplicates MDEV-17614 INSERT on dup key update is replicat... Closed
Relates
relates to MDEV-17073 INSERT…ON DUPLICATE KEY UPDATE became... Closed
relates to MDEV-17603 Allow statement-based replication for... Closed

 Description    

--source include/have_innodb.inc
 
--source include/have_log_bin.inc
 
--source include/have_binlog_format_mixed.inc
 
 
 
CREATE TABLE t1 (f1 INT, f2 INT, PRIMARY KEY (f1), UNIQUE(f2), FOREIGN KEY (f1) REFERENCES t1 (f2)) ENGINE=InnoDB;
 
REPLACE INTO t1 VALUES (1,1),(2,2);
 
 
 
# Cleanup
 
DROP TABLE t1;

The test case above produces error 1032 (ER_KEY_NOT_FOUND):

Actual result

 
mysqltest: At line 6: query 'REPLACE INTO t1 VALUES (1,1),(2,2)' failed: 1032: Can't find record in 't1'

which is also written as an error in the error log.

Normally you would expect a different error, ER_NO_REFERENCED_ROW_2:

Expected result

 
mysqltest: At line 6: query 'REPLACE INTO t1 VALUES (1,1),(2,2)' failed: 1452: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f2`))

which is also not very intuitive, but eventually understandable.

MariaDB 5.5-10.1 and MySQL 5.6/5.7 produce ER_NO_REFERENCED_ROW_2 for the same test case.

MariaDB 10.2+ produces ER_NO_REFERENCED_ROW_2 if the binary log is not enabled, or the logging format is ROW, or INSERT is run instead of REPLACE.

 Comments   
Comment by Marko Mäkelä [ 2019-03-06 ]

In both cases, row_ins_check_foreign_constraints() is reporting DB_NO_REFERENCED_ROW for inserting the record into the PRIMARY KEY. In the problematic case, this error is being ignored, and we will wrongly proceed to insert the record into the secondary index, which will cause row_mysql_handle_errors() to handle the DB_DUPLICATE_KEY.

I was initially suspecting a regression due to MDEV-17073, but I am observing no change even after reverting that change. Rather, it looks like this is broken by the MySQL 5.7 change that MDEV-17073 only reverted for the case binlog_format=ROW.

The following test works for me:

--source include/have_innodb.inc
 
--source include/innodb_binlog.inc
 
 
 
CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE,
 
FOREIGN KEY (a) REFERENCES t1 (b)) ENGINE=InnoDB;
 
--error ER_NO_REFERENCED_ROW_2
 
REPLACE INTO t1 VALUES (1,1),(2,2);
 
DROP TABLE t1;


./mtr --mysqld=--binlog-format=row --parallel=auto innodb.replace_fk

For binlog_format=MIXED, thd_rpl_stmt_based(trx->mysql_thd) would hold in row_ins(), and the error DB_NO_REFERENCED_ROW would be transformed into DB_DUPLICATE_KEY, causing the reported difference.

Comment by Marko Mäkelä [ 2019-03-06 ]

A similar problem should be possible also for INSERT…ON DUPLICATE KEY UNIQUE.

Elkin, could we always switch to binlog_format=ROW for REPLACE or INSERT…ON DUPLICATE KEY UNIQUE statements?

If that is too broad, would it be feasible to switch to binlog_format=ROW if there exist FOREIGN KEY constraints either in the affected table(s) or in other tables that are referencing the affected table(s)?

Comment by Marko Mäkelä [ 2019-09-02 ]

This issue was fixed by my after-merge fix of MDEV-17614.

Generated at Thu Feb 08 08:41:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.