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

DML events for auto-partitioned tables are written into binary log twice

Details

    Description

      --source include/have_partition.inc
      --source include/have_binlog_format_mixed.inc
      --source include/master-slave.inc
       
      create table t1 (a int) with system versioning partition by system_time limit 1000 auto;
      insert into t1 values (1);
      show binlog events;
       
      --sync_slave_with_master
       
      select * from t1;
      --connection master
       
      # cleanup
      drop table t1;
      --source include/rpl_end.inc
      

      bb-10.5-midenok-MENT-654 256d3c1be

      show binlog events;
      Log_name	Pos	Event_type	Server_id	End_log_pos	Info
      master-bin.000001	4	Format_desc	1	256	Server ver: 10.5.1-MariaDB-debug-log, Binlog ver: 4
      master-bin.000001	256	Gtid_list	1	285	[]
      master-bin.000001	285	Binlog_checkpoint	1	329	master-bin.000001
      master-bin.000001	329	Gtid	1	371	GTID 0-1-1
      master-bin.000001	371	Query	1	525	use `test`; create table t1 (a int) with system versioning partition by system_time limit 1000 auto
      master-bin.000001	525	Gtid	1	567	BEGIN GTID 0-1-2
      master-bin.000001	567	Query	1	663	use `test`; insert into t1 values (1)
      master-bin.000001	663	Query	1	740	COMMIT
      master-bin.000001	740	Gtid	1	782	GTID 0-1-3
      master-bin.000001	782	Query	1	886	use `test`; insert into t1 values (1)
      connection slave;
      select * from t1;
      a
      1
      1
      

      In case of the row binlog format, first time the event is written in a row format and the second one as a statement (as if it's interpreted as DDL).

      Attachments

        Issue Links

          Activity

            I cannot reproduce, any combination stmt, row or mix has good result:

            create table t1 (a int) with system versioning partition by system_time limit 1000 auto;
            show create table t1;
            Table   Create Table
            t1      CREATE TABLE `t1` (
              `a` int(11) DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
             PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO_INCREMENT
            PARTITIONS 2
            insert into t1 values (1);
            show create table t1;
            Table   Create Table
            t1      CREATE TABLE `t1` (
              `a` int(11) DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
             PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO_INCREMENT
            PARTITIONS 3
            show binlog events;
            Log_name        Pos     Event_type      Server_id       End_log_pos     Info
            master-bin.000001       4       Format_desc     1       256     Server ver: 10.5.2-MariaDB-debug-log, Binlog ver: 4
            master-bin.000001       256     Gtid_list       1       285     []
            master-bin.000001       285     Binlog_checkpoint       1       329     master-bin.000001
            master-bin.000001       329     Gtid    1       371     GTID 0-1-1
            master-bin.000001       371     Query   1       525     use `test`; create table t1 (a int) with system versioning partition by system_time limit 1000 auto
            master-bin.000001       525     Gtid    1       567     BEGIN GTID 0-1-2
            master-bin.000001       567     Query   1       663     use `test`; insert into t1 values (1)
            master-bin.000001       663     Query   1       740     COMMIT
            connection slave;
            select * from t1;
            a
            1
            connection master;
            drop table t1;
            include/rpl_end.inc
            

            Maybe some other settings required?

            midenok Aleksey Midenkov added a comment - I cannot reproduce, any combination stmt, row or mix has good result: create table t1 (a int) with system versioning partition by system_time limit 1000 auto; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO_INCREMENT PARTITIONS 2 insert into t1 values (1); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME LIMIT 1000 AUTO_INCREMENT PARTITIONS 3 show binlog events; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 4 Format_desc 1 256 Server ver: 10.5.2-MariaDB-debug-log, Binlog ver: 4 master-bin.000001 256 Gtid_list 1 285 [] master-bin.000001 285 Binlog_checkpoint 1 329 master-bin.000001 master-bin.000001 329 Gtid 1 371 GTID 0-1-1 master-bin.000001 371 Query 1 525 use `test`; create table t1 (a int) with system versioning partition by system_time limit 1000 auto master-bin.000001 525 Gtid 1 567 BEGIN GTID 0-1-2 master-bin.000001 567 Query 1 663 use `test`; insert into t1 values (1) master-bin.000001 663 Query 1 740 COMMIT connection slave; select * from t1; a 1 connection master; drop table t1; include/rpl_end.inc Maybe some other settings required?

            No extra options, and it is still reproducible on the freshly cloned and built tree (see below).
            Could it be that you have uncommitted (or unpushed) changes in the local branch?
            The other reason I can think of is that maybe there is a race condition involved and the second event is written asynchronously with the executed SQL, so in your case SHOW BINLOG EVENTS and sync with master happen before that. I didn't consider this possibility before, as it always fails for me, whenever I tried. If, based on implementation, it if technically possible at all, it should be easy enough to check – just add a sleep before SHOW BINLOG EVENTS (but please don't leave the sleep in the test case when/if you push it, synchronize properly instead).

            $ git status
            Your branch is up to date with 'origin/bb-10.5-midenok-MENT-654'.
             
            Untracked files:
              (use "git add <file>..." to include in what will be committed)
             
            	mysql-test/main/ment685.test
             
            nothing added to commit but untracked files present (use "git add" to track)
            $ git log -1
            commit fa141fc3e69e9a9124d88cdb18096ecd3b9c88a1 (HEAD -> bb-10.5-midenok-MENT-654, origin/bb-10.5-midenok-MENT-654)
            Author: Aleksey Midenkov <midenok@gmail.com>
            Date:   Thu Feb 13 11:46:40 2020 +0300
             
                Compilation fixes (other)
            $ git remote -v
            origin	git@github.com:mariadb-corporation/MariaDBEnterprise.git (fetch)
            origin	git@github.com:mariadb-corporation/MariaDBEnterprise.git (push)
            

            $ perl ./mtr main.ment685
            Logging: ./mtr  main.ment685
            vardir: /data/src/bb-10.5-midenok-MENT-654/mysql-test/var
            Checking leftover processes...
            Removing old var directory...
            Creating var directory '/data/src/bb-10.5-midenok-MENT-654/mysql-test/var'...
            Checking supported features...
            MariaDB Version 10.5.2-MariaDB-debug
             - SSL connections supported
             - binaries are debug compiled
             - binaries built with wsrep patch
            Collecting tests...
            Installing system database...
             
            ==============================================================================
             
            TEST                                      RESULT   TIME (ms) or COMMENT
            --------------------------------------------------------------------------
             
            worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
            include/master-slave.inc
            [connection master]
            create table t1 (a int) with system versioning partition by system_time limit 1000 auto;
            insert into t1 values (1);
            show binlog events;
            Log_name	Pos	Event_type	Server_id	End_log_pos	Info
            master-bin.000001	4	Format_desc	1	256	Server ver: 10.5.2-MariaDB-debug-log, Binlog ver: 4
            master-bin.000001	256	Gtid_list	1	285	[]
            master-bin.000001	285	Binlog_checkpoint	1	329	master-bin.000001
            master-bin.000001	329	Gtid	1	371	GTID 0-1-1
            master-bin.000001	371	Query	1	525	use `test`; create table t1 (a int) with system versioning partition by system_time limit 1000 auto
            master-bin.000001	525	Gtid	1	567	BEGIN GTID 0-1-2
            master-bin.000001	567	Query	1	663	use `test`; insert into t1 values (1)
            master-bin.000001	663	Query	1	740	COMMIT
            master-bin.000001	740	Gtid	1	782	GTID 0-1-3
            master-bin.000001	782	Query	1	886	use `test`; insert into t1 values (1)
            connection slave;
            select * from t1;
            a
            1
            1
            

            elenst Elena Stepanova added a comment - No extra options, and it is still reproducible on the freshly cloned and built tree (see below). Could it be that you have uncommitted (or unpushed) changes in the local branch? The other reason I can think of is that maybe there is a race condition involved and the second event is written asynchronously with the executed SQL, so in your case SHOW BINLOG EVENTS and sync with master happen before that. I didn't consider this possibility before, as it always fails for me, whenever I tried. If, based on implementation, it if technically possible at all, it should be easy enough to check – just add a sleep before SHOW BINLOG EVENTS (but please don't leave the sleep in the test case when/if you push it, synchronize properly instead). $ git status Your branch is up to date with 'origin/bb-10.5-midenok-MENT-654'.   Untracked files: (use "git add <file>..." to include in what will be committed)   mysql-test/main/ment685.test   nothing added to commit but untracked files present (use "git add" to track) $ git log -1 commit fa141fc3e69e9a9124d88cdb18096ecd3b9c88a1 (HEAD -> bb-10.5-midenok-MENT-654, origin/bb-10.5-midenok-MENT-654) Author: Aleksey Midenkov <midenok@gmail.com> Date: Thu Feb 13 11:46:40 2020 +0300   Compilation fixes (other) $ git remote -v origin git@github.com:mariadb-corporation/MariaDBEnterprise.git (fetch) origin git@github.com:mariadb-corporation/MariaDBEnterprise.git (push) $ perl ./mtr main.ment685 Logging: ./mtr main.ment685 vardir: /data/src/bb-10.5-midenok-MENT-654/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/data/src/bb-10.5-midenok-MENT-654/mysql-test/var'... Checking supported features... MariaDB Version 10.5.2-MariaDB-debug - SSL connections supported - binaries are debug compiled - binaries built with wsrep patch Collecting tests... Installing system database...   ==============================================================================   TEST RESULT TIME (ms) or COMMENT --------------------------------------------------------------------------   worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 include/master-slave.inc [connection master] create table t1 (a int) with system versioning partition by system_time limit 1000 auto; insert into t1 values (1); show binlog events; Log_name Pos Event_type Server_id End_log_pos Info master-bin.000001 4 Format_desc 1 256 Server ver: 10.5.2-MariaDB-debug-log, Binlog ver: 4 master-bin.000001 256 Gtid_list 1 285 [] master-bin.000001 285 Binlog_checkpoint 1 329 master-bin.000001 master-bin.000001 329 Gtid 1 371 GTID 0-1-1 master-bin.000001 371 Query 1 525 use `test`; create table t1 (a int) with system versioning partition by system_time limit 1000 auto master-bin.000001 525 Gtid 1 567 BEGIN GTID 0-1-2 master-bin.000001 567 Query 1 663 use `test`; insert into t1 values (1) master-bin.000001 663 Query 1 740 COMMIT master-bin.000001 740 Gtid 1 782 GTID 0-1-3 master-bin.000001 782 Query 1 886 use `test`; insert into t1 values (1) connection slave; select * from t1; a 1 1

            Fixed in bb-10.5-midenok-MENT-654

            midenok Aleksey Midenkov added a comment - Fixed in bb-10.5-midenok-MENT-654

            People

              midenok Aleksey Midenkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.