[MDEV-27628] Lag free ALTER statement breaks replication when executing binlog_alter_two_phase = 1,0 dynamically Created: 2022-01-26  Updated: 2022-02-01  Resolved: 2022-02-01

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.8
Fix Version/s: 10.8.1

Type: Bug Priority: Critical
Reporter: Ramesh Sivaraman Assignee: Andrei Elkin
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-11675 Lag Free Alter On Slave Closed

 Description   

Lag free ALTER statement breaks replication when executing binlog_alter_two_phase = 1,0 dynamically

Testcase

1) Configure async master-slave replication.

config info

gtid_strict_mode=1
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_alter_two_phase=YES

2) Execute given SQLs on master server
Replication stops with the following error info.

                Last_SQL_Errno: 1950
                Last_SQL_Error: An attempt was made to binlog GTID 0-112-10 which would create an out-of-order sequence number with existing GTID 0-112-10, and gtid strict mode is enabled
 
2022-01-26 11:01:22 6 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 4, relay log './galapq-relay-bin.000001' position: 4; GTID position ''
2022-01-26 11:01:22 5 [Note] Slave I/O thread: connected to master 'repl@127.0.0.1:4070',replication starts at GTID position ''
2022-01-26 11:01:44 6 [ERROR] Slave SQL: An attempt was made to binlog GTID 0-112-10 which would create an out-of-order sequence number with existing GTID 0-112-10, and gtid strict mode is enabled, Gtid 0-112-10, Internal MariaDB error code: 1950
2022-01-26 11:01:44 6 [Warning] Slave: An attempt was made to binlog GTID 0-112-10 which would create an out-of-order sequence number with existing GTID 0-112-10, and gtid strict mode is enabled Error_code: 1950
2022-01-26 11:01:44 6 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'binlog.000001' position 1914; GTID position '0-112-9'
2022-01-26 11:01:44 6 [Note] Slave SQL thread exiting, replication stopped in log 'binlog.000001' at position 1914; GTID position '0-112-9', master: 127.0.0.1:4070

SQLs

CREATE  TABLE t1 (a1 int, d1 int DEFAULT 0);
INSERT INTO t1 VALUES  (1,1) ;
SET binlog_alter_two_phase=YES;
ALTER  TABLE t1  WAIT 9 RENAME COLUMN a1 TO a2;
SET  binlog_alter_two_phase=NO;
ALTER  TABLE t1  ALTER COLUMN d1 DROP DEFAULT;
DROP TABLE t1;



 Comments   
Comment by Andrei Elkin [ 2022-01-26 ]

The latest commit is in bb-10.8-andrei.

Comment by Andrei Elkin [ 2022-01-27 ]

The patch got updated in bb-10.8-andrei.

Comment by Brandon Nesterenko [ 2022-01-27 ]

Looks good! Thanks for the discussion and refinement.

Comment by Andrei Elkin [ 2022-02-01 ]

Closed along with MDEV-11675.

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