Debian Codegraph @

mysql-test/suite/binlog/t/binlog_mysqlbinlog_do_server_ids.test

raw
1 #
2 # Purpose:
3 #
4 # This test validates that the option --do-server-ids for the mariadb-binlog
5 # command line tool correctly filters events by server id.
6 #
7 #
8 # Methodology:
9 #
10 # This test invokes mariadb-binlog using combinations of --do-server-ids with
11 # various combinations of other parameters to ensure it correctly filters by
12 # server id. Specifically, the following test cases are validated:
13 # Test Case 1) --do-server-ids with a single server id limits output to that
14 # single server
15 # Test Case 2) --do-server-ids with multiple server ids limits output to the
16 # provided servers
17 # Test Case 3) --do-server-ids when combined with --do-domain-ids should
18 # intersect the results
19 # Test Case 4) --do-server-ids when combined with --ignore-domain-ids should
20 # intersect the results
21 # Test Case 5) --do-server-ids when combined with a GTID range should
22 # intersect the results
23 # Test Case 6) --do-server-ids when combined with both --ignore-domain-ids
24 # and a GTID range should intersect all results
25 # Test Case 7) --do-server-ids when combined with both --do-domain-ids and
26 # a GTID range should intersect all results
27 # Test Case 8) --do-server-ids and --offset=<n> skips n events after the
28 # first GTID is found
29 # Test Case 9) --do-server-ids with --start-datetime=<T> where T occurs
30 # after the first GTID is found results in no events before T
31 # Test Case 10) --do-server-ids works with --read-from-remote-server
32 # Test Case 11) --do-server-ids works over multiple binary log input files
33 # Test Case 12) --do-server-ids re-specifications should override previous
34 # ones
35 # Test Case 13) --do-server-ids and --server-id should be aliases and a
36 # re-specification of one should override the former
37 # Test Case 14) --ignore-server-ids re-specifications should override
38 # previous ones
39 # Test Case 15) --do-domain-ids re-specifications should override previous
40 # ones
41 # Test Case 16) --ignore-domain-ids re-specifications should override
42 # previous ones
43 #
44 # Additionally, this test validates the following error scenarios:
45 # Error Case 1) --do-server-ids and --ignore-server-ids cannot be specified
46 # together
47 # Error Case 2) Invalid server ID number provided
48 #
49 #
50 # References:
51 #
52 # MDEV-20119: Implement the --do-domain-ids, --ignore-domain-ids, and
53 # --ignore-server-ids options for mysqlbinlog
54 #
55
56 --source include/have_log_bin.inc
57
58 --echo ###############################
59 --echo # Test Setup
60 --echo ###############################
61
62
63 # Save old state
64 let $ORIG_GTID_DOMAIN_ID = `select @@session.gtid_domain_id`;
65 let $ORIG_SERVER_ID = `select @@session.server_id`;
66
67 # Configure test variables
68 --let $MYSQLD_DATADIR=`select @@datadir`
69
70 --let table_inconsistent_err= "table data is inconsistent after replaying binlog events";
71 --let table_exists_error= "table exists but binlog playback should have excluded its creation";
72
73 # Initialize test data
74 set @a=UNIX_TIMESTAMP("1970-01-21 15:32:22");
75 SET timestamp=@a;
76 RESET MASTER;
77
78 SET @@session.gtid_domain_id= 0;
79 SET @@session.server_id= 1;
80 CREATE TABLE t1 (a int);
81
82 SET @@session.server_id= 2;
83 CREATE TABLE t2 (a int);
84 INSERT INTO t2 values (3);
85 --let t2_checksum= `CHECKSUM TABLE t2`
86
87 SET @@session.gtid_domain_id= 1;
88 SET @@session.server_id= 1;
89 CREATE TABLE t3 (a int);
90 INSERT INTO t3 values (4);
91 --let t3_checksum= `CHECKSUM TABLE t3`
92
93 SET @@session.server_id= 3;
94 SET timestamp=@a+1;
95 CREATE TABLE t4 (a int);
96 SET timestamp=@a+2;
97 INSERT INTO t4 values (5);
98 --let t4_checksum= `CHECKSUM TABLE t4`
99
100 SET @@session.gtid_domain_id= 0;
101 SET @@session.server_id= 1;
102 INSERT INTO t1 values (1);
103 --let t1_partial_checksum= `CHECKSUM TABLE t1`
104
105 SET @@session.gtid_domain_id= 2;
106 SET @@session.server_id= 1;
107 CREATE TABLE t5 (a int);
108 INSERT INTO t5 values (6);
109 --let t5_checksum= `CHECKSUM TABLE t5`
110
111 SET @@session.gtid_domain_id= 0;
112 SET @@session.server_id= 1;
113 INSERT INTO t1 values (2);
114 --let t1_checksum= `CHECKSUM TABLE t1`
115
116 FLUSH LOGS;
117
118 SET @@session.gtid_domain_id= 0;
119 SET @@session.server_id= 2;
120 CREATE TABLE t6 (a int);
121 INSERT INTO t6 values (1);
122 --let t6_checksum= `CHECKSUM TABLE t6`
123 FLUSH LOGS;
124
125 --let BINLOG_FILENAME= query_get_value(SHOW BINARY LOGS, Log_name, 1)
126 --let BINLOG_FILENAME2= query_get_value(SHOW BINARY LOGS, Log_name, 2)
127 --let BINLOG_FILE_PARAM= $MYSQLD_DATADIR/$BINLOG_FILENAME.orig
128 --let BINLOG_FILE_PARAM2= $MYSQLD_DATADIR/$BINLOG_FILENAME2.orig
129 --copy_file $MYSQLD_DATADIR/$BINLOG_FILENAME $BINLOG_FILE_PARAM
130 --copy_file $MYSQLD_DATADIR/$BINLOG_FILENAME2 $BINLOG_FILE_PARAM2
131
132 DROP TABLE t1;
133 DROP TABLE t2;
134 DROP TABLE t3;
135 DROP TABLE t4;
136 DROP TABLE t5;
137 DROP TABLE t6;
138 RESET MASTER;
139
140 --echo ###############################
141 --echo # Test Cases
142 --echo ###############################
143
144 --echo #
145 --echo # Test Case 1) --do-server-ids with a single server id limits output
146 --echo # to that single server
147 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=2 | MYSQL
148 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=2 | $MYSQL
149 if ($t2_checksum != `CHECKSUM TABLE t2`)
150 {
151 die $table_inconsistent_err;
152 }
153 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t3','t4','t5','t6')`)
154 {
155 die $table_exists_error;
156 }
157 DROP TABLE t2;
158
159
160 --echo #
161 --echo # Test Case 2) --do-server-ids with multiple server ids limits output
162 --echo # to the provided servers
163 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=2,3 | MYSQL
164 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=2,3 | $MYSQL
165 if ($t2_checksum != `CHECKSUM TABLE t2`)
166 {
167 die $table_inconsistent_err;
168 }
169 if ($t4_checksum != `CHECKSUM TABLE t4`)
170 {
171 die $table_inconsistent_err;
172 }
173 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t3','t5','t6')`)
174 {
175 die $table_exists_error;
176 }
177 DROP TABLE t2;
178 DROP TABLE t4;
179
180
181 --echo #
182 --echo # Test Case 3) --do-server-ids when combined with --do-domain-ids should
183 --echo # intersect the results
184 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=1 --do-domain-ids=0 | MYSQL
185 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=1 --do-domain-ids=0 | $MYSQL
186 if ($t1_checksum != `CHECKSUM TABLE t1`)
187 {
188 die $table_inconsistent_err;
189 }
190 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t2','t3','t4','t5','t6')`)
191 {
192 die $table_exists_error;
193 }
194 DROP TABLE t1;
195
196
197 --echo #
198 --echo # Test Case 4) --do-server-ids when combined with --ignore-domain-ids should
199 --echo # intersect the results
200 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=1 --ignore-domain-ids=0 | MYSQL
201 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=1 --ignore-domain-ids=0 | $MYSQL
202 if ($t3_checksum != `CHECKSUM TABLE t3`)
203 {
204 die $table_inconsistent_err;
205 }
206 if ($t5_checksum != `CHECKSUM TABLE t5`)
207 {
208 die $table_inconsistent_err;
209 }
210 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t2','t4','t6')`)
211 {
212 die $table_exists_error;
213 }
214 DROP TABLE t3;
215 DROP TABLE t5;
216
217
218 --echo #
219 --echo # Test Case 5) --do-server-ids when combined with a GTID range should
220 --echo # intersect the results
221 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=1 --stop-position=0-1-4 | MYSQL
222 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=1 --stop-position=0-1-4 | $MYSQL
223 if ($t1_partial_checksum != `CHECKSUM TABLE t1`)
224 {
225 die $table_inconsistent_err;
226 }
227 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t2','t3','t4','t5','t6')`)
228 {
229 die $table_exists_error;
230 }
231 DROP TABLE t1;
232
233
234 --echo #
235 --echo # Test Case 6) --do-server-ids when combined with both --ignore-domain-ids
236 --echo # and a GTID range should intersect all results
237 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=1 --ignore-domain-ids=0 --start-position=1-1-0 | MYSQL
238 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=1 --ignore-domain-ids=0 --start-position=1-1-0 | $MYSQL
239 if ($t3_checksum != `CHECKSUM TABLE t3`)
240 {
241 die $table_inconsistent_err;
242 }
243 if ($t5_checksum != `CHECKSUM TABLE t5`)
244 {
245 die $table_inconsistent_err;
246 }
247 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t2','t4','t6')`)
248 {
249 die $table_exists_error;
250 }
251 DROP TABLE t3;
252 DROP TABLE t5;
253
254
255 --echo #
256 --echo # Test Case 7) --do-server-ids when combined with both --do-domain-ids and
257 --echo # a GTID range should intersect all results
258 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=2 --do-domain-ids=0 --start-position=0-1-0 | MYSQL
259 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=2 --do-domain-ids=0 --start-position=0-1-0 | $MYSQL
260 if ($t2_checksum != `CHECKSUM TABLE t2`)
261 {
262 die $table_inconsistent_err;
263 }
264 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t3','t4','t5','t6')`)
265 {
266 die $table_exists_error;
267 }
268 DROP TABLE t2;
269
270
271
272 --echo #
273 --echo # Test Case 8) --do-server-ids and --offset=<n> skips n events after the
274 --echo # first GTID is found
275
276 # t4 needs to be specified because its creation should be skipped from
277 # --offset specification
278 CREATE TABLE t4 (a int);
279
280 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --offset=5 --do-server-ids=3 --do-domain-ids=1 | MYSQL
281 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --offset=5 --do-server-ids=3 --do-domain-ids=1 | $MYSQL
282 if ($t4_checksum != `CHECKSUM TABLE t4`)
283 {
284 die $table_inconsistent_err;
285 }
286 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t2','t3','t5','t6')`)
287 {
288 die $table_exists_error;
289 }
290 DROP TABLE t4;
291
292
293 --echo #
294 --echo # Test Case 9) --do-server-ids with --start-datetime=<T> where T occurs
295 --echo # after the first GTID is found results in no events before T
296
297 # t4 needs to be specified because its creation should be skipped from
298 # --start-datetime specification
299 CREATE TABLE t4 (a int);
300
301 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --start-datetime="1970-01-21 15:32:24" --do-server-ids=3 --do-domain-ids=1 | MYSQL
302 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --start-datetime="1970-01-21 15:32:24" --do-server-ids=3 --do-domain-ids=1 | $MYSQL
303 if ($t4_checksum != `CHECKSUM TABLE t4`)
304 {
305 die $table_inconsistent_err;
306 }
307 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t2','t3','t5','t6')`)
308 {
309 die $table_exists_error;
310 }
311 DROP TABLE t4;
312
313
314 --echo #
315 --echo # Test Case 10) --do-server-ids works with --read-from-remote-server
316
317 --echo # Setup test specific data
318 RESET MASTER;
319
320 SET @@session.gtid_domain_id= 0;
321 SET @@session.server_id= 1;
322 CREATE TABLE t1 (a int);
323 INSERT INTO t1 VALUES (1);
324
325 SET @@session.server_id= 2;
326 CREATE TABLE t2 (a int);
327 INSERT INTO t2 VALUES (1);
328 --let t11_t2_checksum= `CHECKSUM TABLE t2`
329
330 SET @@session.server_id= 1;
331 DROP TABLE t1;
332 DROP TABLE t2;
333
334 --echo # MYSQL_BINLOG BINLOG_FILENAME --read-from-remote-server --do-server-ids=2 | MYSQL
335 --exec $MYSQL_BINLOG $BINLOG_FILENAME --read-from-remote-server --do-server-ids=2 | $MYSQL
336 if ($t11_t2_checksum != `CHECKSUM TABLE t2`)
337 {
338 die $table_inconsistent_err;
339 }
340 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t3','t4','t5','t6')`)
341 {
342 die $table_exists_error;
343 }
344 DROP TABLE t2;
345
346
347 --echo #
348 --echo # Test Case 11) --do-server-ids works over multiple binary log input
349 --echo # files
350 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM BINLOG_FILE_PARAM2 --do-server-ids=2 | MYSQL
351 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM $BINLOG_FILE_PARAM2 --do-server-ids=2 | $MYSQL
352 if ($t2_checksum != `CHECKSUM TABLE t2`)
353 {
354 die $table_inconsistent_err;
355 }
356 if ($t6_checksum != `CHECKSUM TABLE t6`)
357 {
358 die $table_inconsistent_err;
359 }
360 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t3','t4','t5')`)
361 {
362 die $table_exists_error;
363 }
364 DROP TABLE t2;
365 DROP TABLE t6;
366
367 --echo #
368 --echo # Test Case 12) --do-server-ids re-specifications should override
369 --echo # previous ones
370 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=1 --do-server-ids=2 | MYSQL
371 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=1 --do-server-ids=2 | $MYSQL
372 if ($t2_checksum != `CHECKSUM TABLE t2`)
373 {
374 die $table_inconsistent_err;
375 }
376 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t3','t4','t5','t6')`)
377 {
378 die $table_exists_error;
379 }
380 DROP TABLE t2;
381
382 --echo #
383 --echo # Test Case 13) --do-server-ids and --server-id should be aliases and
384 --echo # a re-specification of one should override the former
385 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=1 --server-id=2 | MYSQL
386 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=1 --server-id=2 | $MYSQL
387 if ($t2_checksum != `CHECKSUM TABLE t2`)
388 {
389 die $table_inconsistent_err;
390 }
391 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t3','t4','t5','t6')`)
392 {
393 die $table_exists_error;
394 }
395 DROP TABLE t2;
396
397 --echo #
398 --echo # Test Case 14) --ignore-server-ids re-specifications should override
399 --echo # previous ones
400 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --ignore-server-ids=2 --ignore-server-ids=1,3 | MYSQL
401 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --ignore-server-ids=2 --ignore-server-ids=1,3 | $MYSQL
402 if ($t2_checksum != `CHECKSUM TABLE t2`)
403 {
404 die $table_inconsistent_err;
405 }
406 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t1','t3','t4','t5','t6')`)
407 {
408 die $table_exists_error;
409 }
410 DROP TABLE t2;
411
412 --echo #
413 --echo # Test Case 15) --do-domain-ids re-specifications should override
414 --echo # previous ones
415 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-domain-ids=1 --do-domain-ids=0 | MYSQL
416 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-domain-ids=1 --do-domain-ids=0 | $MYSQL
417 if ($t1_checksum != `CHECKSUM TABLE t1`)
418 {
419 die $table_inconsistent_err;
420 }
421 if ($t2_checksum != `CHECKSUM TABLE t2`)
422 {
423 die $table_inconsistent_err;
424 }
425 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t3','t4','t5','t6')`)
426 {
427 die $table_exists_error;
428 }
429 DROP TABLE t1,t2;
430
431 --echo #
432 --echo # Test Case 16) --ignore-domain-ids re-specifications should override
433 --echo # previous ones
434 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --ignore-domain-ids=0 --ignore-domain-ids=1,2 | MYSQL
435 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --ignore-domain-ids=0 --ignore-domain-ids=1,2 | $MYSQL
436 if ($t1_checksum != `CHECKSUM TABLE t1`)
437 {
438 die $table_inconsistent_err;
439 }
440 if ($t2_checksum != `CHECKSUM TABLE t2`)
441 {
442 die $table_inconsistent_err;
443 }
444 if (`SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'test' AND table_name IN ('t3','t4','t5','t6')`)
445 {
446 die $table_exists_error;
447 }
448 DROP TABLE t1,t2;
449
450 --echo ##############################
451 --echo # Error Cases
452 --echo ##############################
453
454 --echo #
455 --echo # Error Case 1:
456 --echo # --ignore-server-ids and --do-server-ids both specified
457 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=1 --ignore-server-ids=2
458 --error 1
459 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=1 --ignore-server-ids=2
460
461 --echo #
462 --echo # Error Case 2:
463 --echo # Invalid server ID number provided
464 --echo # MYSQL_BINLOG BINLOG_FILE_PARAM --do-server-ids=-1
465 --error 1
466 --exec $MYSQL_BINLOG $BINLOG_FILE_PARAM --do-server-ids=-1
467
468 --echo ##############################
469 --echo # Cleanup
470 --echo ##############################
471 --eval SET @@global.gtid_domain_id= $ORIG_GTID_DOMAIN_ID
472 --eval SET @@global.server_id= $ORIG_SERVER_ID
473
474 --remove_file $BINLOG_FILE_PARAM
475 --remove_file $BINLOG_FILE_PARAM2
476
477 --echo # End of tests