1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
|
# ==== Purpose ====
#
# Verify that Seconds_Behind_Master is calculated correctly on a
# Multi-threaded Slave when replica_parallel_type = 'LOGICAL_CLOCK'.
# The test covers these scenarios:
#
# 1) With two workers, check if Seconds_Behind_Master is calculated
# correctly when two statements are applied in parallel on one database
# and both the workers are blocked to to lock.
#
# 2) With two workers, check if Seconds_Behind_Master is calculated
# correctly when the first worker is blocked.
#
# 3) With three workers, check if Seconds_Behind_Master is calculated
# correctly when two workers working on table from the same database and
# 3rd worker working on another database.
--source include/start_slave.inc
###############################################################################
# Scenario 1: On slave, two statements are applied in parallel on one database.
# Both workers are blocked.
###############################################################################
--echo # Scenario 1: With two workers, check if Seconds_Behind_Master is
--echo # calculated correctly when two statements are applied in parallel on
--echo # one database and both the workers are blocked to to lock.
connect (master2, localhost, root, , );
connection slave;
connection master;
USE test;
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
# We need one statement to be commited so that the parallel
# statements have a common parent commit
INSERT INTO t1 SET a=1;
--source include/sync_slave_sql_with_master.inc
connection slave1;
LOCK TABLE test.t1 WRITE, test.t2 WRITE;
--connection master1
let $start= `SELECT UNIX_TIMESTAMP()`;
BEGIN;
INSERT INTO t1 SET a=2;
--connection master2
BEGIN;
INSERT INTO t2 SET a=4;
--connection master1
COMMIT;
--connection master2
COMMIT;
connection master;
--source include/sync_slave_io_with_master.inc
# Wait until all workers are blocked by locks. It implies that all
# transactions are registered into the order commit queue.
let $wait_condition= SELECT count(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock';
--source include/wait_condition.inc
let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event'
OR State = 'update';
--source include/wait_condition.inc
# Sleep on slave so we were at least 3 seconds behind the master
--sleep 3
--source include/wait_for_mta_checkpoint.inc
let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1);
let $stop= `SELECT UNIX_TIMESTAMP()`;
let $upper_bound= `SELECT $stop - $start`;
let $assert_text= Seconds_Behind_Master must be between 3 and upper_bound;
let $assert_cond= 3 <= $sbm AND $sbm <= $upper_bound;
--source include/assert.inc
# Unlock tables and allow both workers to complete
connection slave1;
UNLOCK TABLES;
connection master;
--source include/sync_slave_sql_with_master.inc
let $wait_condition= SELECT count(*) = 3 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for an event from Coordinator';
--source include/wait_condition.inc
--source include/wait_for_mta_checkpoint.inc
# Now both workers have completed. Thus Seconds_Behind_Master shoud be equal to 0.
let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1);
let $assert_text= Seconds_Behind_Master must be 0;
let $assert_cond= $sbm = 0;
--source include/assert.inc
###############################################################################
# Scenario 2: Now only the first worker is blocked. Make sure there is a parent
# commit
###############################################################################
--echo # Scenario 2: With two workers, check if Seconds_Behind_Master is
--echo # calculated correctly when the first worker is blocked.
connection master;
INSERT INTO t1 SET a=1;
--source include/sync_slave_sql_with_master.inc
connection slave1;
LOCK TABLE test.t1 WRITE;
connection slave2;
LOCK TABLE test.t2 WRITE;
--connection master1
let $start= `SELECT UNIX_TIMESTAMP()`;
BEGIN;
INSERT INTO t1 SET a=2;
--connection master2
BEGIN;
INSERT INTO t2 SET a=3;
--connection master1
COMMIT;
--connection master2
COMMIT;
connection master;
--source include/sync_slave_io_with_master.inc
# Wait until both workers are queued and blocked.
let $wait_condition= SELECT count(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock';
--source include/wait_condition.inc
let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event'
OR State = 'update';
--source include/wait_condition.inc
# Now release one worker
connection slave1;
UNLOCK TABLES;
# Wait until released worker completes its taks
let $wait_condition= SELECT count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock';
--source include/wait_condition.inc
let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event'
OR State = 'update';
--source include/wait_condition.inc
# Sleep for 2 seconds so Seconds_Behind_Master was at least 2
--sleep 2
--source include/wait_for_mta_checkpoint.inc
let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1);
let $stop= `SELECT UNIX_TIMESTAMP()`;
let $upper_bound= `SELECT $stop - $start`;
let $assert_text= Seconds_Behind_Master must be between 2 and upper_bound;
let $assert_cond= 2 <= $sbm AND $sbm <= $upper_bound;
--source include/assert.inc
connection slave2;
UNLOCK TABLES;
--connection master
--source include/sync_slave_sql_with_master.inc
# All workers should be done by now.
let $wait_condition= SELECT count(*) = 3 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for an event from Coordinator';
--source include/wait_condition.inc
--source include/wait_for_mta_checkpoint.inc
let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1);
let $assert_text= Seconds_Behind_Master must be 0;
let $assert_cond= $sbm = 0;
--source include/assert.inc
###############################################################################
# Scenario 3: Now there are 3 workers working in pararell. Two workers working
# on table from the same database and a 3rd worker working on another database
###############################################################################
--echo # Scenario 3: With three workers, check if Seconds_Behind_Master is
--echo # calculated correctly when two workers working on table from the same
--echo # database and 3rd worker working on another database.
connection master;
CREATE DATABASE second_test;
CREATE TABLE second_test.t3 (f1 INT);
# Make sure there is a parent commit
INSERT INTO test.t1 SET a=1;
--sync_slave_with_master
# Lock all tables to block all workers.
connection slave1;
LOCK TABLE test.t1 WRITE;
connection slave2;
LOCK TABLE test.t2 WRITE;
connection slave3;
LOCK TABLE second_test.t3 WRITE;
connect (master_second_test, localhost, root, , );
--connection master_second_test
BEGIN;
INSERT INTO second_test.t3 VALUES (1);
--connection master1
let $start= `SELECT UNIX_TIMESTAMP()`;
BEGIN;
INSERT INTO test.t1 SET a=2;
--connection master2
BEGIN;
INSERT INTO test.t2 SET a=3;
--connection master_second_test
COMMIT;
--connection master1
COMMIT;
--connection master2
COMMIT;
--source include/sync_slave_io_with_master.inc
# Wait for all workers to be queued
# There should be two workers waiting for locks on test database
let $wait_condition= SELECT count(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock'
AND Info LIKE 'INSERT INTO test%';
--source include/wait_condition.inc
# And one worker waiting for the lock on second_database
let $wait_condition= SELECT count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock'
AND Info LIKE 'INSERT INTO second_test%';
--source include/wait_condition.inc
# Make sure that there are 3 overall at the same point in time
let $wait_condition= SELECT count(*) = 3 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock';
--source include/wait_condition.inc
let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event'
OR State = 'update';
--source include/wait_condition.inc
# Wait for 2 seconds so Second_Behind_Master is at least 2
--sleep 2
--source include/wait_for_mta_checkpoint.inc
let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1);
let $stop= `SELECT UNIX_TIMESTAMP()`;
let $upper_bound= `SELECT $stop - $start`;
let $assert_text= Seconds_Behind_Master must be between 2 and upper_bound;
let $assert_cond= 2 <= $sbm AND $sbm <= $upper_bound;
--source include/assert.inc
# Lets finish concurrent write on second_test and check if workers on test report
# correct Seconds_Behind_Master
connection slave3;
UNLOCK TABLES;
--sleep 1
connection slave;
# Wait till worker on second_test db finishes
let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock'
AND Info LIKE 'INSERT INTO second_test%';
--source include/wait_condition.inc
# There should be the remaining two workers hanging on test db
let $wait_condition= SELECT count(*) = 2 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock'
AND Info LIKE 'INSERT INTO test%';
--source include/wait_condition.inc
let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event'
OR State = 'update';
--source include/wait_condition.inc
--source include/wait_for_mta_checkpoint.inc
let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1);
let $stop= `SELECT UNIX_TIMESTAMP()`;
let $upper_bound= `SELECT $stop - $start`;
let $assert_text= Seconds_Behind_Master must be between 3 and upper_bound;
let $assert_cond= 3 <= $sbm AND $sbm <= $upper_bound;
--source include/assert.inc
# Lets finish worker writing to t1
connection slave1;
UNLOCK TABLES;
# There should be only one worker left
let $wait_condition= SELECT count(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for table metadata lock';
--source include/wait_condition.inc
let $wait_condition= SELECT count(*) = 0 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Executing event'
OR State = 'update';
--source include/wait_condition.inc
--sleep 1
--source include/wait_for_mta_checkpoint.inc
let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1);
let $stop= `SELECT UNIX_TIMESTAMP()`;
let $upper_bound= `SELECT $stop - $start`;
let $assert_text= Seconds_Behind_Master must be between 4 and upper_bound;
let $assert_cond= 4 <= $sbm AND $sbm <= $upper_bound;
--source include/assert.inc
# Release last worker
connection slave2;
UNLOCK TABLE;
# Wait for all workers to be done
let $wait_condition= SELECT count(*) = 3 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE State = 'Waiting for an event from Coordinator';
--source include/wait_condition.inc
# All catched up Seconds_Behind_Master should be 0. Lets wait some more to see if it does not increase
# Seconds_Behind_Master
--sleep 1
--source include/wait_for_mta_checkpoint.inc
let $sbm= query_get_value("SHOW SLAVE STATUS", Seconds_Behind_Master, 1);
let $assert_text= Seconds_Behind_Master must be 0;
let $assert_cond= $sbm = 0;
--source include/assert.inc
#
# Cleanup
#
connection master;
DROP TABLE test.t1;
DROP TABLE test.t2;
DROP DATABASE second_test;
--source include/sync_slave_sql_with_master.inc
--source include/stop_slave.inc
|
