Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28310

Missing binlog data for INSERT .. ON DUPLICATE KEY UPDATE

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17614 INSERT on dup key update is replication unsafe

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31770 Non-full binary log row image for INSERT ... ON DUPLICATE KEY UPDATE

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21810 MBR: Unexpected "Unsafe statement" warning and use of non-full image with binlog_row_image=FULL

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            monty Michael Widenius added a comment -

            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
            monty Michael Widenius added a comment - 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
            Elkin Andrei Elkin added a comment -

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

            Elkin Andrei Elkin added a comment - The latest patch 744dabd4776 HEAD -> bb-10.2-andrei is ready for review.

            People

              monty Michael Widenius
              vnsavage Iliya Polihronov
              Votes:
              4 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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