[MDEV-25347] DML events for auto-partitioned tables are written into binary log twice Created: 2020-04-01  Updated: 2021-04-06  Resolved: 2020-04-06

Status: Closed
Project: MariaDB Server
Component/s: Partitioning, Replication, Versioned Tables
Affects Version/s: N/A
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-17554 Auto-create history partitions for sy... Closed
is blocked by MDEV-22153 ALTER add default history partitions ... Closed
Relates

 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).

 Comments   
Comment by Aleksey Midenkov [ 2020-04-04 ]

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?

Comment by Elena Stepanova [ 2020-04-04 ]

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

Comment by Aleksey Midenkov [ 2020-04-06 ]

Fixed in bb-10.5-midenok-MENT-654

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