[MDEV-21844] Multi-Value Insert with duplicate keys results in out-of-sync replication Created: 2020-02-28  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.0, 10.1, 10.3.14, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Daniel Ragle Assignee: Andrei Elkin
Resolution: Unresolved Votes: 0
Labels: duplicate_key, insert, replication
Environment:

CentOS 7


 Description   

With replication setup as A => B => C, and a MyISAM table with only an id field as a primary key, executing these statements on A

INSERT INTO MyTable ( id ) VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 );
 
INSERT INTO MyTable ( id ) VALUES ( 6 ), ( 5 ), ( 4 ), ( 3 );

of course results in a duplicate key error on the second statement, but not until after keys 6 and 5 are already written. The two statements are then sent to Slave B, with the same result; I.E., the tables on A and B now each have the same 6 rows.

However, Slave B does not pass the second statement on to Slave C at all (never hits the Slave B binlog). So Slave C only has 4 rows. Additionally, I see no errors or messages in the Slave B log to indicate that the statement resulted in duplicate key errors, nor that the statement was not replicated.

sql_mode is blank on all three servers, and slave_skip_errors is OFF. Also, binlog_format is MIXED.

Is this expected/intended behavior, and is it documented somewhere that I could take a look?

 Comments   
Comment by Alice Sherepa [ 2020-03-02 ]

Thanks! The behaviour reproducible on 10.0-10.5, with mixed and statement binlog formats.
Please use binlog_format='row' as a workaround.

--let $rpl_topology=1->2->3
 
--source include/rpl_init.inc
 
 
 
connection server_1;
 
 
 
CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=myisam;
 
INSERT INTO t1 (id) VALUES (1),(2),(3),(4);
 
--error 1062
 
INSERT INTO t1 (id) VALUES (6),(5),(4),(3);
 
--save_master_pos
 
 
 
connection server_2;
 
--sync_with_master
 
SELECT * FROM t1;
 
--save_master_pos
 
 
 
connection server_3;
 
--sync_with_master
 
SELECT * FROM t1;
 
 
 
connection server_2;
 
#binlog
 
let $MYSQLD_DATADIR= `select @@datadir`;
 
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
 
--replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/
 
--exec $MYSQL_BINLOG --base64-output=decode-rows -v $MYSQLD_DATADIR/slave-bin.000001
 
 
 
#relaylog
 
let $MYSQLD_DATADIR= `select @@datadir`;
 
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
 
--replace_regex /SQL_LOAD_MB-[0-9]-[0-9]/SQL_LOAD_MB-#-#/ /exec_time=[0-9]*/exec_time=#/ /end_log_pos [0-9]*/end_log_pos #/ /# at [0-9]*/# at #/ /Xid = [0-9]*/Xid = #/ /thread_id=[0-9]*/thread_id=#/ /table id [0-9]*/table id #/ /mapped to number [0-9]*/mapped to number #/ /server v [^ ]*/server v #.##.##/ /CRC32 0x[0-9a-f]*/CRC32 XXX/ /collation_server=[0-9]+/collation_server=X/ /character_set_client=[0-9]+/character_set_client=X/ /collation_connection=[0-9]+/collation_connection=X/
 
--exec $MYSQL_BINLOG --base64-output=decode-rows -v $MYSQLD_DATADIR/slave-relay-bin.000002
 
# Cleanup
 
 
 
--connection server_1
 
DROP TABLE t1;
 
--save_master_pos
 
 
 
--connection server_2
 
--sync_with_master
 
--save_master_pos
 
 
 
--connection server_3
 
--sync_with_master
 
 
 
--source include/rpl_end.inc

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