1--source include/have_debug.inc 2--source include/have_debug_sync.inc 3--let $rpl_skip_start_slave= 1 4--source include/master-slave.inc 5 6--echo # 7--echo # Bug #84415: slave don't report Seconds_Behind_Master when 8--echo # running slave_parallel_workers > 0 9--echo # 10 11# restart slave in MTS mode 12connection slave; 13SET @@global.debug= "+d,dbug.mts.force_clock_diff_eq_0"; 14SET @save.slave_parallel_workers= @@global.slave_parallel_workers; 15SET @save_slave_transaction_retries= @@global.slave_transaction_retries; 16SET @@global.slave_transaction_retries= 0; 17SET @@global.slave_parallel_workers= 4; 18--source include/start_slave.inc 19 20# 1st scenario. slave executes insert to t1 and is blocked due to lock on t1, after 3s another worker 21# on slave starts to execute insert to t2, this worker is also blocked on insert to t2. After 2 more 22# seconds we check Seconds_Behind_Master. Since worker is still executing insert to t1 this means 23# that slave is at least 5s behind master (might be more due to slow network connection). 24 25connect (slave2,127.0.0.1,root,,test,$SLAVE_MYPORT,); 26connection master; 27 28CREATE TABLE t1 (f1 INT); 29CREATE DATABASE test2; 30USE test2; 31CREATE TABLE t2 (f1 INT); 32--source include/sync_slave_sql_with_master.inc 33 34connection slave1; 35LOCK TABLE test.t1 WRITE; 36 37connection slave2; 38LOCK TABLE test2.t2 WRITE; 39 40connection master; 41 42USE test; 43let $start= `SELECT UNIX_TIMESTAMP()`; 44INSERT INTO t1 VALUES (1); 45 46--real_sleep 3 47 48USE test2; 49let $start2= `SELECT UNIX_TIMESTAMP()`; 50INSERT INTO t2 VALUES (1); 51--real_sleep 2 52 53# 54# all events are in relay-log 55# 56source include/sync_slave_io_with_master.inc; 57 58# Check that two workers are waiting 59let $wait_condition= SELECT count(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock'; 60--source include/wait_condition.inc 61let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event' 62 OR State = 'update'; 63--source include/wait_condition.inc 64 65# $upper_bound = "timestamp on slave after reading Seconds_Behind_Master" - 66# "timestamp on master before the insert statement". 67 68source include/wait_for_mts_checkpoint.inc; 69 70let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1); 71let $stop= `SELECT UNIX_TIMESTAMP()`; 72let $upper_bound= `SELECT $stop - $start`; 73let $assert_text= Seconds_Behind_Master must be between 5 and upper_bound; 74let $assert_cond= 5 <= $sbm AND $sbm <= $upper_bound; 75source include/assert.inc; 76 77connection slave1; 78UNLOCK TABLES; 79 80# Now the first worker which was 5s behind the master has completed its task. However the second 81# worker is still running its task. This means that Seconds_Behind_Master should now be 2s 82# or slightly more. 83 84# Check that one worker is still running (waiting) 85let $wait_condition= SELECT count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock'; 86--source include/wait_condition.inc 87let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event' 88 OR State = 'update'; 89--source include/wait_condition.inc 90 91source include/wait_for_mts_checkpoint.inc; 92 93let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1); 94let $stop= `SELECT UNIX_TIMESTAMP()`; 95let $upper_bound= `SELECT $stop - $start2`; 96let $assert_text= Seconds_Behind_Master must be between 2 and upper_bound; 97let $assert_cond= 2 <= $sbm AND $sbm <= $upper_bound; 98source include/assert.inc; 99 100connection slave2; 101UNLOCK TABLES; 102 103--connection master 104--source include/sync_slave_sql_with_master.inc 105 106# Now both workers have completed their tasks and no new tasks have arrived, thus 107# Seconds_Behind_Master should be equal to 0. 108 109let $wait_condition= SELECT count(*) = 4 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for an event from Coordinator'; 110--source include/wait_condition.inc 111 112source include/wait_for_mts_checkpoint.inc; 113 114let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1); 115let $assert_text= Seconds_Behind_Master must be 0; 116let $assert_cond= $sbm = 0; 117source include/assert.inc; 118 119# 2nd scenario. In this scenario we check if Seconds_Behind_Master is updated correctly when first 120# worker compeletes its tasks first. 121 122connection slave2; 123LOCK TABLE test2.t2 WRITE; 124 125connection master; 126 127USE test; 128let $start= `SELECT UNIX_TIMESTAMP()`; 129INSERT INTO t1 VALUES (1); 130 131--real_sleep 3 132 133USE test2; 134let $start2= `SELECT UNIX_TIMESTAMP()`; 135INSERT INTO t2 VALUES (1); 136--real_sleep 2 137 138# 139# all events are in relay-log 140# 141source include/sync_slave_io_with_master.inc; 142 143# Check that one worker is still running 144let $wait_condition= SELECT count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock'; 145--source include/wait_condition.inc 146let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event' 147 OR State = 'update'; 148--source include/wait_condition.inc 149 150# First worker has completed its task, second one is still running. 151# We should be 2 seconds behind the master. 152 153source include/wait_for_mts_checkpoint.inc; 154 155let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1); 156let $stop= `SELECT UNIX_TIMESTAMP()`; 157let $upper_bound= `SELECT $stop - $start2`; 158let $assert_text= Seconds_Behind_Master must be between 2 and upper_bound; 159let $assert_cond= 2 <= $sbm AND $sbm <= $upper_bound; 160source include/assert.inc; 161 162connection slave2; 163UNLOCK TABLES; 164 165let $wait_condition= SELECT count(*) = 4 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for an event from Coordinator'; 166--source include/wait_condition.inc 167 168source include/wait_for_mts_checkpoint.inc; 169 170let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1); 171let $assert_text= Seconds_Behind_Master must be 0; 172let $assert_cond= $sbm = 0; 173source include/assert.inc; 174 175# 3rd scenario. Three workers are running. In this scenario we check if correct Seconds_Behind_Master 176# is calculated when second worker completes first. 177 178--connection master 179 180CREATE DATABASE test3; 181USE test3; 182CREATE TABLE t3 (f1 INT); 183--source include/sync_slave_sql_with_master.inc 184 185connect (slave3,127.0.0.1,root,,test,$SLAVE_MYPORT,); 186 187connection slave1; 188LOCK TABLE test.t1 WRITE; 189 190connection slave3; 191LOCK TABLE test3.t3 WRITE; 192 193connection master; 194 195let $start= `SELECT UNIX_TIMESTAMP()`; 196 197USE test; 198INSERT INTO t1 VALUES (1); 199--real_sleep 3 200 201use test2; 202INSERT INTO t2 VALUES (1); 203--real_sleep 2 204 205USE test3; 206let $start3= `SELECT UNIX_TIMESTAMP()`; 207INSERT INTO t3 VALUES (1); 208--real_sleep 2 209 210source include/sync_slave_io_with_master.inc; 211 212# Only second worker has completed. This means that longest running worker is the first one, thus 213# slave should be 7s behind the master. 214 215# Check that two workers are still running 216let $wait_condition= SELECT count(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock'; 217--source include/wait_condition.inc 218let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event' 219 OR State = 'update'; 220--source include/wait_condition.inc 221 222source include/wait_for_mts_checkpoint.inc; 223 224let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1); 225let $stop= `SELECT UNIX_TIMESTAMP()`; 226let $upper_bound= `SELECT $stop - $start`; 227let $assert_text= Seconds_Behind_Master must be between 7 and upper_bound; 228let $assert_cond= 7 <= $sbm AND $sbm <= $upper_bound; 229source include/assert.inc; 230 231connection slave1; 232UNLOCK TABLES; 233 234# Check that last worker is still running 235let $wait_condition= SELECT count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock'; 236--source include/wait_condition.inc 237let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event' 238 OR State = 'update'; 239--source include/wait_condition.inc 240 241source include/wait_for_mts_checkpoint.inc; 242 243let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1); 244let $stop= `SELECT UNIX_TIMESTAMP()`; 245let $upper_bound= `SELECT $stop - $start3`; 246let $assert_text= Seconds_Behind_Master must be between 2 and upper_bound; 247let $assert_cond= 2 <= $sbm AND $sbm <= $upper_bound; 248source include/assert.inc; 249 250connection slave3; 251UNLOCK TABLES; 252 253# Now all four workers have completed. Seconds_Behind_Master should be 0. 254 255let $wait_condition= SELECT count(*) = 4 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for an event from Coordinator'; 256--source include/wait_condition.inc 257 258source include/wait_for_mts_checkpoint.inc; 259 260let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1); 261let $assert_text= Seconds_Behind_Master must be 0; 262let $assert_cond= $sbm = 0; 263source include/assert.inc; 264 265## 266# cleanup 267## 268connection master; 269DROP TABLE test.t1; 270DROP DATABASE test2; 271DROP DATABASE test3; 272 273--source include/sync_slave_sql_with_master.inc 274 275SET @@global.slave_parallel_workers= @save.slave_parallel_workers; 276SET @@global.debug= "-d,dbug.mts.force_clock_diff_eq_0"; 277SET @@global.slave_transaction_retries= @save_slave_transaction_retries; 278 279--source include/rpl_end.inc 280 281