[MDEV-28310] Missing binlog data for INSERT .. ON DUPLICATE KEY UPDATE - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.3.34, 10.2(EOL), 10.3(EOL), 10.4(EOL)
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
Component/s: Replication
Labels:
None
Environment:
Linux

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;

Attachments

Issue Links

relates to

MDEV-17614 INSERT on dup key update is replication unsafe

Closed

MDEV-31770 Non-full binary log row image for INSERT ... ON DUPLICATE KEY UPDATE

Confirmed

MDEV-21810 MBR: Unexpected "Unsafe statement" warning and use of non-full image with binlog_row_image=FULL

Closed

Activity

Ascending order - Click to sort in descending order

Michael Widenius added a comment - 2022-05-03 08:05

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

Michael Widenius added a comment - 2022-05-03 08:05 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

Andrei Elkin added a comment - 2022-05-04 18:41

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

Andrei Elkin added a comment - 2022-05-04 18:41 The latest patch 744dabd4776 HEAD -> bb-10.2-andrei is ready for review.

People

Assignee:: Michael Widenius

Reporter:: Iliya Polihronov

Votes:: 4 Vote for this issue

Watchers:: 9 Start watching this issue

Dates

Created:: 2022-04-13 16:37

Updated:: 2023-12-06 20:06

Resolved:: 2022-05-06 19:47

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.