Bug #81232 Changing master_delay after stop slave results in loss of events.
Submitted: 29 Apr 2016 10:19 Modified: 1 Apr 2017 15:03
Reporter: Parveez Baig Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[29 Apr 2016 10:19] Parveez Baig
Description:
After Setting up the replication.Stop the slave when there are events at the relay log and sql thread has to apply it,Change the master_delay using CHM command and Start the slave.This will result in loss of events which were waiting for the execution at slave.

How to repeat:
The following test case fails in 5.7 and trunk since the slave do not have the insert transaction  but passes in 5.6.

cat mysql-test/suite/rpl/t/rpl_master_delay.test

--source include/master-slave.inc

--connection master
CREATE TABLE t1(a int);

--source include/sync_slave_sql_with_master.inc
stop slave sql_thread;

--connection master
INSERT INTO t1 values(8);

#Stop the slave,Change the master_delay and ensure that the slave do not loose insert transaction;
--source include/sync_slave_io_with_master.inc
--connection slave 
stop slave io_thread;
change master to master_delay=10;
start slave;

--connection master
--let $ignore_gtids_on_sync=1
--source include/sync_slave_sql_with_master.inc
--let $insert_value=query_get_value( select *from t1,a,1)

--let $assert_text= Assert that the $insert_value is equal to 8 ..
--let $assert_cond= $insert_value=8 
--source include/assert.inc

#clean up
STOP SLAVE;
change master to master_delay=0;
START SLAVE;
--connection master
DROP TABLE IF EXISTS t1;

--source include/rpl_end.inc
[1 Apr 2017 15:03] Paul Dubois
Posted by developer:
 
Noted in 5.7.19, 8.0.2 changelogs.

A slave server could lose events not yet applied when
MASTER_AUTO_POSITION=0, both replication threads were stopped, and
the applier delay was changed using CHANGE MASTER TO MASTER_DELAY=N.
[24 May 2017 12:34] Erlend Dahl
Bug#84249 CHANGE MASTER TO MASTER_DELAY can lead a slave to skip transactions

was marked as a duplicate.