New test rpl.rpl_parallel_sbm in bb-10.4-MDEV-30619 sporadically fails in various locations (prepatch: lines 100, 177, 184) (postpatch_1: lines 180, 187)

mysql-test$ ./mtr --repeat 1000 rpl.rpl_parallel_sbm

...

rpl.rpl_parallel_sbm 'mix'               [ 18 pass ]  18674

rpl.rpl_parallel_sbm 'mix'               [ 19 pass ]  18698

rpl.rpl_parallel_sbm 'mix'               [ 20 fail ]

        Test ended at 2023-07-20 10:13:11

CURRENT_TEST: rpl.rpl_parallel_sbm

mysqltest: At line 100: Seconds_Behind_Master should not have used second transaction timestamp

The result from queries just before the failure was:

< snip >

# Waiting for transaction to arrive on slave and begin SQL Delay..

# Validating SBM is updated on event arrival..

# ..done

connection slave;

UNLOCK TABLES;

include/sync_with_master_gtid.inc

#

# Pt 2) If the SQL thread has not entered an idle state, ensure

# following events do not update SBM

connection master;

# Sleep 2 to allow a buffer between events for SBM check

insert into t1 values (1);

# Sleep 3 to create gap between events

insert into t1 values (2);

include/save_master_pos.inc

connection slave;

LOCK TABLES t1 WRITE;

# Wait for first transaction to complete SQL delay and begin execution..

# Validate SBM calculation doesn't use the second transaction because SQL thread shouldn't have gone idle..

# SBM 3 was more recent than time since last transaction (4 seconds)

...

rpl.rpl_parallel_sbm 'mix'               [ 19 pass ]  18622

rpl.rpl_parallel_sbm 'mix'               [ 20 pass ]  19592

rpl.rpl_parallel_sbm 'mix'               [ 21 fail ]

        Test ended at 2023-07-20 12:47:36

CURRENT_TEST: rpl.rpl_parallel_sbm

mysqltest: At line 177: Seconds_Behind_Master is too recent

The result from queries just before the failure was:

< snip >

connection master;

include/sync_slave_sql_with_master.inc

# Lock t1 on slave so the first received transaction does not complete/commit

connection slave;

LOCK TABLES t1 WRITE;

connection master;

INSERT INTO t1 VALUES (1);

include/save_master_gtid.inc

connection slave;

# Waiting for first transaction to begin..

# Wait for SQL thread to catch up and idle

# Sleep 2s to create a gap between events

LOCK TABLES mysql.gtid_slave_pos WRITE;

connection master;

INSERT INTO t2 VALUES (1);

include/save_master_gtid.inc

connection slave;

# Waiting for second transaction to begin..

# Validate that the second transaction doesn't update Seconds_Behind_Master on reception after the SQL thread has gone idle..

# Seconds_Behind_Master was 2 yet should not have been less than 3 (1689821254 - 1689821251)

...

rpl.rpl_parallel_sbm 'mix'               [ 85 pass ]  18592

rpl.rpl_parallel_sbm 'mix'               [ 86 pass ]  19673

rpl.rpl_parallel_sbm 'mix'               [ 87 fail ]

        Test ended at 2023-07-20 13:33:27

CURRENT_TEST: rpl.rpl_parallel_sbm

mysqltest: At line 184: Seconds_Behind_Master is too old

The result from queries just before the failure was:

< snip >

include/sync_slave_sql_with_master.inc

# Lock t1 on slave so the first received transaction does not complete/commit

connection slave;

LOCK TABLES t1 WRITE;

connection master;

INSERT INTO t1 VALUES (1);

include/save_master_gtid.inc

connection slave;

# Waiting for first transaction to begin..

# Wait for SQL thread to catch up and idle

# Sleep 2s to create a gap between events

LOCK TABLES mysql.gtid_slave_pos WRITE;

connection master;

INSERT INTO t2 VALUES (1);

include/save_master_gtid.inc

connection slave;

# Waiting for second transaction to begin..

# Validate that the second transaction doesn't update Seconds_Behind_Master on reception after the SQL thread has gone idle..

# ..and that its value correctly corresponds to the first transaction's timestamp on the primary

# Seconds_Behind_Master was 3 yet should not have been more than 2 (1689824005 - 1689824003)

./mtr --repeat 100 rpl.rpl_delayed_parallel_slave_sbm