Seconds_Behind_Master is not zero after slave syncs up with master

server_2 syncs with server_1

include/sync_with_master_gtid.inc

server_2 info after syncing with server_1

#Check that Slave_last_event_time ~= Master_last_event_time

set @s2mle3 = '2024-08-30 09:28:38';

set @s2sle3 = '2024-08-30 09:28:38';

#Check on server_2 Seconds_Behind_Master is equal to 0

set @s2sbm3 = 59;

rpl.ment2095 'mix'                       [ fail ]

        Test ended at 2024-08-30 14:59:39

CURRENT_TEST: rpl.ment2095

mysqltest: At line 165: server_2 SBM didn't set at 0 after sync up.

--source include/have_binlog_format_mixed.inc

--let $rpl_topology= 1->2->3

--source include/rpl_init.inc

--connection server_1

--source include/have_innodb.inc

--connection server_2

--source include/have_innodb.inc

--connection server_3

--source include/have_innodb.inc

--connection server_1

--echo #Write some data on server_1

create table t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(25)) engine=innodb;

insert into t1 (name) values ('foxy1');

insert into t1 (name) values ('foxy2');

insert into t1 (name) values ('foxy3');

insert into t1 (name) values ('foxy4');

insert into t1 (name) values ('foxy5');

insert into t1 (name) select name from t1;

insert into t1 (name) select name from t1;

insert into t1 (name) select name from t1;

insert into t1 (name) select name from t1;

insert into t1 (name) select name from t1;

flush logs;

--eval set @s1tblsize = (select count(id) from t1)

--let $s1tblsize = @s1tblsize

let $gtid_binlog_pos= query_get_value(SELECT @@gtid_binlog_pos, @@gtid_binlog_pos, 1);

--source include/save_master_gtid.inc

--echo *** Test normal shutdown/restart of server_1 ***

--connection server_1

--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect

wait

EOF

--shutdown_server

--source include/wait_until_disconnected.inc

--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect

restart

EOF

--connection default

--enable_reconnect

--source include/wait_until_connected_again.inc

--connection server_1

--enable_reconnect

--source include/wait_until_connected_again.inc

--echo *** server_1 restarted ***

--connection server_2

--echo Wait until server_2 replicates the last server_1 event

--source include/sync_with_master_gtid.inc

--echo #Check on server_2 Master_last_event_time is equal to Slave_last_event_time

--eval set @s2mle1 = '$s2mle1'

--eval set @s2sle1 = '$s2sle1'

if (`SELECT @s2sle1 != @s2mle1`)

{

  --die server_2 SLE didn't match its MLE after sync up.

}

--echo #Check on server_2 Seconds_Behind_Master is equal to 0

--let $s2sbm1 = query_get_value(SHOW ALL SLAVES STATUS, Seconds_Behind_Master, 1)

--eval set @s2sbm1 = $s2sbm1

if (`SELECT @s2sbm1 != 0`)

{

  --die server_2 SBM didn't set at 0 after sync up.

}

--echo #Induce SQL_DELAY of 60s on server_2

--let $s2_master_delay = 60

--eval set @s2_master_delay = $s2_master_delay

--source include/stop_slave.inc

--eval change master to master_delay=$s2_master_delay, master_use_gtid=slave_pos;

--source include/start_slave.inc

--connection server_3

--source include/stop_slave.inc

--let $s3_master_delay = 90

--eval set @s3_master_delay = $s3_master_delay

--eval change master to master_delay=$s3_master_delay, master_use_gtid=slave_pos;

--source include/start_slave.inc

--let $s3sbm1 = query_get_value(SHOW ALL SLAVES STATUS, Seconds_Behind_Master, 1)

--eval set @s3sbm1 = $s3sbm1

--connection server_1

--echo Write some data on server_1 after 5s, this is second event

--eval set @s1_event_delay = 5

--eval set @s1_event_delay_minus1 = @s1_event_delay - 1

--eval set @s1_event_delay_plus1 = @s1_event_delay + 1

select sleep(@s1_event_delay);

insert into t1 (name) select name from t1 where name = 'foxy5';

update t1 set name = 'foxynew4' where name = 'foxy4';

delete from t1 where name = 'foxy3';

flush logs;

--source include/save_master_gtid.inc

--eval set @s1tblsize = (select count(id) from t1)

--let $s1tblsize = @s1tblsize

let $gtid_binlog_pos= query_get_value(SELECT @@gtid_binlog_pos, @@gtid_binlog_pos, 1);

--echo *** Test normal shutdown/restart of server_1 ***

--connection server_1

--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect

wait

EOF

--shutdown_server

--source include/wait_until_disconnected.inc

--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect

restart

EOF

--connection default

--enable_reconnect

--source include/wait_until_connected_again.inc

--connection server_1

--enable_reconnect

--source include/wait_until_connected_again.inc

--echo *** server_1 restarted ***

--connection server_2

--source include/stop_slave.inc

--source include/start_slave.inc

--let $wait_condition= SELECT gtid_io_pos = '$gtid_binlog_pos' FROM information_schema.slave_status;

--source include/wait_condition.inc

--echo #server_2 Info before sync

--echo #Check that Slave_last_event_time ~= Master_last_event_time - 5

--echo #Check that the second even on server_1 has not been replicated

--let $s2mle2= query_get_value(SHOW ALL SLAVES STATUS, Master_last_event_time, 1)

--let $s2sle2= query_get_value(SHOW ALL SLAVES STATUS, Slave_last_event_time, 1)

--eval set @s2mle2 = '$s2mle2'

--eval set @s2sle2 = '$s2sle2'

--eval set @timediff = (SELECT ABS(TIME_TO_SEC(TIMEDIFF(@s2mle2, @s2sle2))));

if (`SELECT @timediff < @s1_event_delay_minus1 OR @timediff > @s1_event_delay_plus1`)

{

  --die server_2 (MLE - SLE) didn't match ~5s

}

--echo #Check on server_2 Seconds_Behind_Master is ~5s

--let $s2sbm2= query_get_value(SHOW ALL SLAVES STATUS, Seconds_Behind_Master, 1)

--eval set @s2sbm2 = $s2sbm2

if (`SELECT @s2sbm2 < @s1_event_delay_minus1 OR @s2sbm2 > @s1_event_delay_plus1`)

{

  --die server_2 SBM didn't match ~5s

}

--connection server_2

--echo server_2 syncs with server_1

--source include/sync_with_master_gtid.inc

--echo server_2 info after syncing with server_1

--echo #Check that Slave_last_event_time ~= Master_last_event_time

--let $s2mle3 = query_get_value(SHOW ALL SLAVES STATUS, Master_last_event_time, 1)

--let $s2sle3 = query_get_value(SHOW ALL SLAVES STATUS, Slave_last_event_time, 1)

--eval set @s2mle3 = '$s2mle3'

--eval set @s2sle3 = '$s2sle3'

if (`SELECT @s2sle3 != @s2mle3`)

{

  --die server_2 SLE didn't match its MLE after sync up.

}

--echo #Check on server_2 Seconds_Behind_Master is equal to 0

--let $s2sbm3 = query_get_value(SHOW ALL SLAVES STATUS, Seconds_Behind_Master, 1)

--eval set @s2sbm3 = $s2sbm3

if (`SELECT @s2sbm3 != 0`)

{

  --die server_2 SBM didn't set at 0 after sync up.

}