Debian Codegraph @

mysql-test/suite/rpl/t/rpl_delayed_slave.test

raw
1 # ==== Purpose ====
2 #
3 # Test the time-delayed replication feature, i.e.,
4 # CHANGE MASTER TO MASTER_DELAY=X:
5 #
6 # - Verify that slave has executed the events after but not before the
7 # delay timeout.
8 #
9 # - Verify that delay is correct when slave is already lagging
10 # due to slow queries.
11 #
12 # - Verify that Seconds_Behind_Master is greater than or equal to the
13 # delay if the slave still has unprocessed events in the relay log
14 # and more time than the delay has elapsed since the last event was
15 # executed on the master.
16 #
17 # - Verify that STOP SLAVE works instantly even during a delay, and
18 # that it does not cause the waited-for event to be executed too
19 # early on slave.
20 #
21 # - Verify that changing back to no delay works.
22 #
23 # - Verify that RESET SLAVE sets the delay to 0.
24 #
25 # - Verify that setting a bad value for the delay gives an error.
26 #
27 # ==== Implementation ====
28 #
29 # We run the slave with 10 seconds lag.
30 #
31 # In general, to test that a query has not been executed by the slave
32 # before this time, we wait until the slave IO thread has received the
33 # event, and then 5 seconds more, and check that the table has not
34 # been updated. To test that a query has been executed after this
35 # time, we wait 10 seconds more.
36 #
37 # To simulate that the slave lags due to slow queries, we invoke a
38 # stored function that executes SLEEP if @@gloval.server_id==2. This
39 # requires that we run with binlog_format=STATEMENT.
40 #
41 # ==== Related Bugs and Worklogs ====
42 #
43 # WL#344: Time-delayed replication
44 # BUG#28760: Simulating a replication lag
45 # [duplicate] BUG#22072: configurable delayed replication
46 # [duplicate] BUG#21639: Add Replication Delay parameter
47 # BUG#56442: Slave executes delayed statements when STOP SLAVE is issued
48 #
49 # ==== Issues with this Test Case ====
50 #
51 # The test is inherently timing-sensitive (i.e., contains races) and
52 # is likely to fail sporadically on a loaded host.
53 #
54 # The test takes a long time; it sleeps for around 20*10 seconds.
55
56 --source include/big_test.inc
57 --source include/not_valgrind.inc
58 --source include/master-slave.inc
59 # Needed so that sleeps get executed in the slave SQL thread.
60 --source include/have_binlog_format_statement.inc
61
62
63 call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
64 --connection slave
65 call mtr.add_suppression("Unsafe statement written to the binary log using statement format");
66 --source include/stop_slave.inc
67 CHANGE MASTER TO MASTER_USE_GTID=NO;
68 --source include/start_slave.inc
69 --connection master
70
71
72 # We assume that any simple operation takes zero time, with an error
73 # margin of $time1 seconds. Hence, if we run with a delay of $time2
74 # seconds, we expect that:
75 # - If we execute a query on master and wait $time1 seconds, then the
76 # query has been copied to slave but not yet executed.
77 # - If we execute a query on master and wait $time3 seconds, then the
78 # query has been executed.
79 --let $time1= 10
80 if (`SELECT '$max_query_execution_time' > 0`) {
81 --let $time1= $max_query_execution_time
82 }
83 --let $time2= `SELECT 2 * $time1`
84 --let $time3= `SELECT 3 * $time1`
85
86
87 --echo [on master]
88 CREATE TABLE t1 (a VARCHAR(100), b INT);
89 INSERT INTO t1 VALUES ("zero", 0);
90
91
92 --echo ==== Normal setup ====
93
94 --echo [on slave]
95 --sync_slave_with_master
96
97 --source include/stop_slave.inc
98
99 --echo # CHANGE MASTER TO MASTER_DELAY = 2*T
100 --disable_query_log
101 eval CHANGE MASTER TO MASTER_DELAY = $time2;
102 --enable_query_log
103
104 --source include/start_slave.inc
105
106 --let $assert_text= SHOW SLAVE STATUS should return the same delay that we set with CHANGE MASTER
107 --let $assert_cond= [SHOW SLAVE STATUS, SQL_Delay, 1] = $time2
108 --source include/rpl_assert.inc
109
110 --echo [on master]
111 --connection master
112 INSERT INTO t1 VALUES ('normal setup', 1);
113
114 --let $query_number= 1
115 --source include/delayed_slave_wait_on_query.inc
116
117
118 --echo ==== Slave lags "naturally" after master ====
119
120 --echo [on master]
121 --connection master
122
123 --disable_query_log
124 --echo # CREATE FUNCTION delay_on_slave(time_units INT) RETURNS INT BEGIN IF @@GLOBAL.server_id = 2 THEN RETURN SLEEP(time_units * T); ELSE RETURN 0; END IF; END
125 --eval CREATE FUNCTION delay_on_slave(time_units INT) RETURNS INT BEGIN IF @@GLOBAL.server_id = 2 THEN RETURN SLEEP(time_units * $time1); ELSE RETURN 0; END IF; END
126 --enable_query_log
127
128 INSERT INTO t1 SELECT delay_on_slave(3), 2;
129
130 --save_master_pos
131 INSERT INTO t1 VALUES ('slave is already lagging: this statement should execute immediately', 3);
132 INSERT INTO t1 SELECT delay_on_slave(2), 4;
133
134 --echo [on slave]
135 --source include/sync_slave_io_with_master.inc
136 --echo # sleep 1*T
137 --sleep $time1
138
139 --let $assert_text= No query executed
140 --let $assert_cond= MAX(b) = 1 FROM t1
141 --source include/rpl_assert.inc
142
143 --let $assert_text= Status should be 'Waiting until MASTER_DELAY...'
144 --let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" LIKE "Waiting until MASTER_DELAY%"
145 --source include/rpl_assert.inc
146
147 --echo # wait for first query to execute
148 --sync_with_master
149
150 --echo # sleep 1*T
151 --sleep $time1
152
153 --let $assert_text= Second query executed
154 --let $assert_cond= MAX(b) = 3 FROM t1
155 --source include/rpl_assert.inc
156
157 let $parallel= `SELECT @@GLOBAL.slave_parallel_threads`;
158 if (!$parallel)
159 {
160 let $assert_text= Status should be executing third query (i.e., 'User sleep');
161 let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" = "User sleep";
162 source include/rpl_assert.inc;
163 }
164
165 --echo # sleep 2*T
166 --sleep $time2
167
168 --let $assert_text= Third query executed
169 --let $assert_cond= MAX(b) = 4 FROM t1
170 --source include/rpl_assert.inc
171
172 --let $assert_text= Status should be 'Has read all relay log...'
173 --let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" LIKE "Slave has read all relay log%"
174 --source include/rpl_assert.inc
175
176
177 --echo ==== Seconds_Behind_Master ====
178
179 --echo # Bring slave to sync.
180 --source include/stop_slave.inc
181 CHANGE MASTER TO MASTER_DELAY = 0;
182 --source include/start_slave.inc
183
184 --connection master
185 INSERT INTO t1 VALUES ('Syncing slave', 5);
186 --sync_slave_with_master
187
188 --source include/stop_slave.inc
189 --echo # CHANGE MASTER TO MASTER_DELAY = 2*T
190 --disable_query_log
191 eval CHANGE MASTER TO MASTER_DELAY = $time2;
192 --enable_query_log
193 --source include/start_slave.inc
194
195 # Ensure that slave has started properly
196 --connection master
197 INSERT INTO t1 VALUES ('Syncing slave', 5);
198 --save_master_pos
199 --sync_slave_with_master
200
201 --connection master
202 INSERT INTO t1 VALUES (delay_on_slave(1), 6);
203 --save_master_pos
204 --connection slave
205
206 --echo # sleep 1*T
207 --sleep $time1
208
209 --let $assert_cond= [SHOW SLAVE STATUS, Seconds_Behind_Master, 1] >= 0 AND <1> < $time2
210 --let $assert_text= Seconds_Behind_Master should be between 0 and the 2*T
211 --source include/rpl_assert.inc
212
213 --echo # sleep 1*T
214 --sleep $time1
215
216 --let $assert_cond= [SHOW SLAVE STATUS, Seconds_Behind_Master, 1] >= $time2
217 --let $assert_text= Seconds_Behind_Master should be at least 2*T
218 --source include/rpl_assert.inc
219
220 --sync_with_master
221
222
223 --echo ==== STOP SLAVE / START SLAVE + DML ====
224
225 # Set up a longer delay.
226 --source include/stop_slave.inc
227
228 --echo # CHANGE MASTER TO MASTER_DELAY = 3*T
229 --disable_query_log
230 eval CHANGE MASTER TO MASTER_DELAY = $time3;
231 --enable_query_log
232
233 --source include/start_slave.inc
234
235 --echo [on master]
236 --connection master
237 INSERT INTO t1 VALUES ('stop slave and start slave: DML', 7);
238
239 --echo [on slave]
240 --connection slave
241 --echo # sleep 1*T
242 --sleep $time1
243 --let $timestamp_before_stop= `SELECT UNIX_TIMESTAMP()`
244 --let $relay_log_pos_before_stop= query_get_value(SHOW SLAVE STATUS, Relay_Log_Pos, 1)
245 --source include/stop_slave.inc
246
247 --let $assert_text= STOP SLAVE should finish quickly, not wait for the ongoing sleep to finish
248 --let $assert_cond= UNIX_TIMESTAMP() - $timestamp_before_stop < $time1
249 --source include/rpl_assert.inc
250
251 --let $assert_text= SQL thread position should not increase after STOP SLAVE
252 --let $assert_cond= [SHOW SLAVE STATUS, Relay_Log_Pos, 1] = $relay_log_pos_before_stop
253 --source include/rpl_assert.inc
254
255 --let $assert_text= Query should not be executed after STOP SLAVE
256 --let $assert_cond= MAX(b) = 6 FROM t1
257 --source include/rpl_assert.inc
258
259 --let $assert_text= Status should be '' after STOP SLAVE
260 --let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" = ""
261 --source include/rpl_assert.inc
262
263 --source include/start_slave.inc
264
265 --let $assert_text= START SLAVE should finish quickly
266 --let $assert_cond= UNIX_TIMESTAMP() - $timestamp_before_stop < $time1
267 --source include/rpl_assert.inc
268
269 --let $query_number= 7
270 --source include/delayed_slave_wait_on_query.inc
271
272
273 --echo ==== STOP SLAVE / START SLAVE + DDL ====
274
275 --echo This verifies BUG#56442
276
277 --echo [on master]
278 --connection master
279 CREATE TABLE t_check_dml_not_executed_prematurely (a INT);
280 --source include/save_master_pos.inc
281
282 --echo [on slave]
283 --connection slave
284 --echo # sleep 1*T
285 --sleep $time1
286
287 --let $timestamp_before_stop= `SELECT UNIX_TIMESTAMP()`
288 --let $relay_log_pos_before_stop= query_get_value(SHOW SLAVE STATUS, Relay_Log_Pos, 1)
289 --source include/stop_slave.inc
290
291 --let $assert_text= STOP SLAVE should finish quickly, not wait for the ongoing sleep to finish
292 --let $assert_cond= UNIX_TIMESTAMP() - $timestamp_before_stop < $time1
293 --source include/rpl_assert.inc
294
295 --let $assert_text= SQL thread position should not increase after STOP SLAVE
296 --let $assert_cond= [SHOW SLAVE STATUS, Relay_Log_Pos, 1] = $relay_log_pos_before_stop
297 --source include/rpl_assert.inc
298
299 --let $assert_text= Query should not be executed after STOP SLAVE
300 --let $assert_cond= COUNT(*) = 0 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "t_check_dml_not_executed_prematurely"
301 --source include/rpl_assert.inc
302
303 --let $assert_text= Status should be '' after STOP SLAVE
304 --let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" = ""
305 --source include/rpl_assert.inc
306
307 --source include/start_slave.inc
308
309 --let $assert_text= START SLAVE should finish quickly
310 --let $assert_cond= UNIX_TIMESTAMP() - $timestamp_before_stop < $time1
311 --source include/rpl_assert.inc
312
313 --echo # sleep 1*T
314 --sleep $time1
315
316 --let $assert_text= DDL Query should not be executed after START SLAVE
317 --let $assert_cond= COUNT(*) = 0 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "t_check_dml_not_executed_prematurely"
318 --source include/rpl_assert.inc
319
320 --let $assert_text= Status should be 'Waiting until MASTER_DELAY...'
321 --let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" LIKE "Waiting until MASTER_DELAY%"
322 --source include/rpl_assert.inc
323
324 --echo # sleep 1*T
325 --sleep $time1
326
327 --echo # sync with master (with timeout 1*T)
328 --source include/sync_with_master.inc
329
330 --let $assert_text= DDL Query should be executed
331 --let $assert_cond= COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = "t_check_dml_not_executed_prematurely"
332 --source include/rpl_assert.inc
333
334 --let $assert_text= Status should be 'Has read all relay log...'
335 --let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" LIKE "Slave has read all relay log%"
336 --source include/rpl_assert.inc
337
338 --source include/check_slave_is_running.inc
339
340
341 --echo ==== Change back to no delay ====
342
343 --echo [on slave]
344 --connection slave
345 --source include/stop_slave.inc
346 CHANGE MASTER TO MASTER_DELAY = 0;
347
348 --let $assert_text= Delay should be 0 when we set it to 0
349 --let $assert_cond= [SHOW SLAVE STATUS, SQL_Delay, 1] = 0
350 --source include/rpl_assert.inc
351
352 --source include/start_slave.inc
353
354 --echo [on master]
355 --connection master
356 INSERT INTO t1 VALUES ('change back to no delay', 8);
357
358 --echo [on slave]
359 --source include/sync_slave_io_with_master.inc
360 --echo # sleep 1*T
361 --sleep $time1
362
363 --let $assert_text= Query should be executed
364 --let $assert_cond= MAX(b) = 8 FROM t1
365 --source include/rpl_assert.inc
366
367 --let $assert_text= Status should be 'Slave has read all relay log...'
368 --let $assert_cond= "[SHOW SLAVE STATUS, Slave_SQL_Running_State, 1]" Like "Slave has read all relay log%"
369 --source include/rpl_assert.inc
370
371
372 --echo ==== Reset delay with RESET SLAVE ====
373
374 --source include/stop_slave.inc
375 CHANGE MASTER TO MASTER_DELAY = 71;
376 --source include/start_slave.inc
377
378 --let $assert_text= Delay should be 71 when we set it to 71
379 --let $assert_cond= [SHOW SLAVE STATUS, SQL_Delay, 1] = 71
380 --source include/rpl_assert.inc
381
382 --source include/stop_slave.inc
383 --let $master_use_gtid_option= No
384 --source include/reset_slave.inc
385 --echo [on master]
386 --connection master
387 RESET MASTER;
388 --echo [on slave]
389 --connection slave
390 --source include/start_slave.inc
391
392 --let $assert_text= Delay should be 0 after RESET SLAVE
393 --let $assert_cond= [SHOW SLAVE STATUS, SQL_Delay, 1] = 0
394 --source include/rpl_assert.inc
395
396
397 --echo ==== Set an invalid value for the delay ====
398
399 --source include/stop_slave.inc
400
401 --echo # Expect error for setting negative delay
402 --error ER_PARSE_ERROR
403 CHANGE MASTER TO MASTER_DELAY = -1;
404
405 --echo # Expect that it's ok to set delay of 2^31-1
406 CHANGE MASTER TO MASTER_DELAY = 2147483647;
407 --echo # Expect error for setting delay between 2^31 and 2^32-1
408 --error ER_MASTER_DELAY_VALUE_OUT_OF_RANGE
409 CHANGE MASTER TO MASTER_DELAY = 2147483648;
410
411 --echo # Expect error for setting delay to nonsense
412 --error ER_PARSE_ERROR
413 CHANGE MASTER TO MASTER_DELAY = blah;
414
415 # todo: CHANGE MASTER TO MASTER_DELAY = 999999999999999999999999999
416 # should give error
417
418 CHANGE MASTER TO MASTER_DELAY = 0;
419 --source include/start_slave.inc
420
421
422 --echo ==== Clean up ====
423
424 --echo [on master]
425 --connection master
426 DROP TABLE t1, t_check_dml_not_executed_prematurely;
427 DROP FUNCTION delay_on_slave;
428
429 --echo [on slave]
430 --sync_slave_with_master
431 SELECT @@GLOBAL.slave_parallel_mode;
432 SELECT @@GLOBAL.slave_parallel_threads;
433
434 --source include/rpl_end.inc