[MDEV-28310] Missing binlog data for INSERT .. ON DUPLICATE KEY UPDATE Created: 2022-04-13  Updated: 2023-07-24  Resolved: 2022-05-06

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.3.34, 10.2, 10.3, 10.4
Fix Version/s: 10.2.44, 10.3.35, 10.4.25, 10.5.16, 10.6.8, 10.7.4, 10.8.3, 10.9.1

Type: Bug Priority: Blocker
Reporter: Iliya Polihronov Assignee: Michael Widenius
Resolution: Fixed Votes: 4
Labels: None
Environment:

Linux

Issue Links:
Relates
relates to MDEV-17614 INSERT on dup key update is replicat... Closed
relates to MDEV-31770 Non-full binary log row image for INS... Confirmed
relates to MDEV-21810 MBR: Unexpected "Unsafe statement" wa... Closed

 Description   

Using the following test case, the second INSERT statement is not logged in the binlog when using MIXED binlog format and thus is not replicated to a slave, leading to inconsistent state of replication.

Server version: 10.3.34-MariaDB-log MariaDB Server

CREATE TABLE t1 (
 
    id INT PRIMARY KEY AUTO_INCREMENT,
 
    a INT,
 
    b INT,
 
    c INT,
 
    UNIQUE (a),
 
    UNIQUE (b)
 
);
 
 
INSERT INTO t1 (`a`,`c`) VALUES (1,1), (2,1) ON DUPLICATE KEY UPDATE c = 1;
 
INSERT INTO t1 (`a`,`c`) VALUES (3, 1),(2,1), (1,1) ON DUPLICATE KEY UPDATE c = 1;



 Comments   
Comment by Michael Widenius [ 2022-05-03 ]

I checked 10.6 and there both statements are replicated properly with binlog_format=mixed
In 10.2 it does not. Probably related to the (wrong) warning that we get in 10.2

Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe
Comment by Andrei Elkin [ 2022-05-04 ]

The latest patch 744dabd4776 HEAD -> bb-10.2-andrei is ready for review.

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