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

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

    XMLWordPrintable

    Details

      Description

      Test case 1 - Unexpected warning

      --source include/have_binlog_format_mixed.inc
       
      CREATE TABLE t1 (
        a INT,
        b INT,
        UNIQUE (a),
        UNIQUE (b)
      );
       
      INSERT INTO t1 VALUES (1,10);
      INSERT INTO t1 VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30;
       
      # Cleanup
      DROP TABLE t1;
      

      Despite the MBR mode which is supposed to switch to row-based format automatically, the second INSERT causes a warning:

      10.4 e6373551

      2020-02-24 18:32:01 10 [Warning] 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 Statement: INSERT INTO t1 VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30
      

      However, the event is still written into the binlog in the row format:

      BEGIN
      /*!*/;
      # at 718
      # at 800
      #200224 18:32:01 server id 1  end_log_pos 800 CRC32 0x699db07f 	Annotate_rows:
      #Q> INSERT INTO t1 VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30
      #200224 18:32:01 server id 1  end_log_pos 846 CRC32 0xb43256af 	Table_map: `test`.`t1` mapped to number 32
      # at 846
      #200224 18:32:01 server id 1  end_log_pos 888 CRC32 0x2da8ac3f 	Write_rows: table id 32 flags: STMT_END_F
      ### INSERT INTO `test`.`t1`
      ### SET
      ###   @1=2
      ###   @2=20
      # Number of rows: 1
      # at 888
      #200224 18:32:01 server id 1  end_log_pos 919 CRC32 0xea50f89a 	Xid = 26
      COMMIT/*!*/;
      

      Now, if we extend the test case a little bit, adding a third field to the table:

      Test case 2 - Unexpected non-full image upon INSERT

      --source include/have_binlog_format_mixed.inc
       
      CREATE TABLE t1 (
        a INT,
        b INT,
        c INT,
        UNIQUE (a),
        UNIQUE (b)
      );
       
      INSERT INTO t1 (a,b) VALUES (1,10);
      INSERT INTO t1 (a,b) VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30;
       
      # Cleanup
      DROP TABLE t1;
      

      The warning is still produced, but on top of that, the row event in the binary log contains a non-full image, even though binlog_row_image=FULL is still used by default (the third column is missing):

      #Q> INSERT INTO t1 (a,b) VALUES (2,20) ON DUPLICATE KEY UPDATE b = 30
      #200224 18:35:37 server id 1  end_log_pos 866 CRC32 0x2f651082 	Table_map: `test`.`t1` mapped to number 32
      # at 866
      #200224 18:35:37 server id 1  end_log_pos 908 CRC32 0x776ec7d7 	Write_rows: table id 32 flags: STMT_END_F
      ### INSERT INTO `test`.`t1`
      ### SET
      ###   @1=2
      ###   @2=20
      # Number of rows: 1
      

      If we make the INSERT.. ON DUPLICATE KEY UPDATE actually hit a duplicate key and be converted into an UPDATE, we'll see a mix of full/minimal image:

      Test case 3 - Unexpected non-full image upon UPDATE

      --source include/have_binlog_format_mixed.inc
       
      CREATE TABLE t1 (
        a INT,
        b INT,
        c INT,
        UNIQUE (a),
        UNIQUE (b)
      );
       
      INSERT INTO t1 (a,b) VALUES (1,10);
      INSERT INTO t1 (a,b) VALUES (1,20) ON DUPLICATE KEY UPDATE b = 30;
       
      # Cleanup
      DROP TABLE t1;
      

      #Q> INSERT INTO t1 (a,b) VALUES (1,20) ON DUPLICATE KEY UPDATE b = 30
      #200224 18:38:04 server id 1  end_log_pos 866 CRC32 0xda8e71b8 	Table_map: `test`.`t1` mapped to number 32
      # at 866
      #200224 18:38:04 server id 1  end_log_pos 918 CRC32 0x672a1460 	Update_rows: table id 32 flags: STMT_END_F
      ### UPDATE `test`.`t1`
      ### WHERE
      ###   @1=1
      ###   @2=10
      ###   @3=NULL
      ### SET
      ###   @1=1
      ###   @2=30
      # Number of rows: 1
      

      Reproducible on 10.1-10.5.
      Not reproducible on MySQL 5.6.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Elkin Andrei Elkin
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.