Debian Codegraph @

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

raw
1 #
2 # This include file is used by more than one test suite
3 # (currently rpl and binlog_encryption).
4 # Please check all dependent tests after modifying it.
5 #
6 # Usage:
7 #
8 # --let $use_remote_mysqlbinlog= 1 # optional
9 # --source suite/rpl/include/rpl_skip_replication.inc
10 #
11 # The script uses MYSQLBINLOG to verify certain results.
12 # By default, it uses binary logs directly. If it is undesirable,
13 # this behavior can be overridden by setting $use_remote_binlog
14 # as shown above.
15 # The value will be unset after every execution of the script,
16 # so if it is needed, it should be set explicitly before each call.
17 #
18
19 --source include/have_innodb.inc
20 --source include/master-slave.inc
21
22 --connection slave
23 --source include/stop_slave.inc
24 CHANGE MASTER TO MASTER_USE_GTID=NO;
25 --source include/start_slave.inc
26 --connection master
27
28 connection slave;
29 # Test that SUPER is required to change @@replicate_events_marked_for_skip.
30 CREATE USER 'nonsuperuser'@'127.0.0.1';
31 GRANT ALTER,CREATE,DELETE,DROP,EVENT,INSERT,PROCESS,REPLICATION SLAVE,
32 SELECT,UPDATE ON *.* TO 'nonsuperuser'@'127.0.0.1';
33 connect(nonpriv, 127.0.0.1, nonsuperuser,, test, $SLAVE_MYPORT,);
34 connection nonpriv;
35 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
36 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_MASTER;
37 disconnect nonpriv;
38 connection slave;
39 DROP USER'nonsuperuser'@'127.0.0.1';
40
41 SELECT @@global.replicate_events_marked_for_skip;
42 --error ER_SLAVE_MUST_STOP
43 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_SLAVE;
44 SELECT @@global.replicate_events_marked_for_skip;
45 STOP SLAVE;
46 --error ER_GLOBAL_VARIABLE
47 SET SESSION replicate_events_marked_for_skip=FILTER_ON_MASTER;
48 SELECT @@global.replicate_events_marked_for_skip;
49 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_MASTER;
50 SELECT @@global.replicate_events_marked_for_skip;
51 START SLAVE;
52
53 connection master;
54 SELECT @@skip_replication;
55 --error ER_LOCAL_VARIABLE
56 SET GLOBAL skip_replication=1;
57 SELECT @@skip_replication;
58
59 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=myisam;
60 CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=innodb;
61 INSERT INTO t1(a) VALUES (1);
62 INSERT INTO t2(a) VALUES (1);
63
64
65 # Test that master-side filtering works.
66 SET skip_replication=1;
67
68 CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=myisam;
69 INSERT INTO t1(a) VALUES (2);
70 INSERT INTO t2(a) VALUES (2);
71
72 # Inject a rotate event in the binlog stream sent to slave (otherwise we will
73 # fail sync_slave_with_master as the last event on the master is not present
74 # on the slave).
75 FLUSH NO_WRITE_TO_BINLOG LOGS;
76
77 sync_slave_with_master;
78 connection slave;
79 SHOW TABLES;
80 SELECT * FROM t1;
81 SELECT * FROM t2;
82
83 connection master;
84 DROP TABLE t3;
85
86 FLUSH NO_WRITE_TO_BINLOG LOGS;
87 sync_slave_with_master;
88
89
90 # Test that slave-side filtering works.
91 connection slave;
92 STOP SLAVE;
93 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_SLAVE;
94 START SLAVE;
95
96 connection master;
97 SET skip_replication=1;
98 CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=myisam;
99 INSERT INTO t1(a) VALUES (3);
100 INSERT INTO t2(a) VALUES (3);
101
102 # Inject a rotate event in the binlog stream sent to slave (otherwise we will
103 # fail sync_slave_with_master as the last event on the master is not present
104 # on the slave).
105 FLUSH NO_WRITE_TO_BINLOG LOGS;
106
107 sync_slave_with_master;
108 connection slave;
109 SHOW TABLES;
110 SELECT * FROM t1;
111 SELECT * FROM t2;
112
113 connection master;
114 DROP TABLE t3;
115
116 FLUSH NO_WRITE_TO_BINLOG LOGS;
117 sync_slave_with_master;
118 connection slave;
119 STOP SLAVE;
120 SET GLOBAL replicate_events_marked_for_skip=REPLICATE;
121 START SLAVE;
122
123
124 # Test that events with @@skip_replication=1 are not filtered when filtering is
125 # not set on slave.
126 connection master;
127 SET skip_replication=1;
128 CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=myisam;
129 INSERT INTO t3(a) VALUES(2);
130 sync_slave_with_master;
131 connection slave;
132 SELECT * FROM t3;
133 connection master;
134 DROP TABLE t3;
135
136 #
137 # Test that the slave will preserve the @@skip_replication flag in its
138 # own binlog.
139 #
140
141 TRUNCATE t1;
142 sync_slave_with_master;
143 connection slave;
144 RESET MASTER;
145
146 connection master;
147 SET skip_replication=0;
148 INSERT INTO t1 VALUES (1,0);
149 SET skip_replication=1;
150 INSERT INTO t1 VALUES (2,0);
151 SET skip_replication=0;
152 INSERT INTO t1 VALUES (3,0);
153
154 sync_slave_with_master;
155 connection slave;
156 # Since slave has @@replicate_events_marked_for_skip=REPLICATE, it should have
157 # applied all events.
158 SELECT * FROM t1 ORDER by a;
159
160 STOP SLAVE;
161 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_MASTER;
162 let $SLAVE_DATADIR= `select @@datadir`;
163
164 connection master;
165 TRUNCATE t1;
166
167 # Now apply the slave binlog to the master, to check that both the slave
168 # and mysqlbinlog will preserve the @@skip_replication flag.
169
170 --let $mysqlbinlog_args= $SLAVE_DATADIR/slave-bin.000001
171 if ($use_remote_mysqlbinlog)
172 {
173 --let $mysqlbinlog_args= --read-from-remote-server --protocol=tcp --host=127.0.0.1 --port=$SLAVE_MYPORT -uroot slave-bin.000001
174 --let $use_remote_mysqlbinlog= 0
175 }
176 --exec $MYSQL_BINLOG $mysqlbinlog_args > $MYSQLTEST_VARDIR/tmp/rpl_skip_replication.binlog
177 --exec $MYSQL test < $MYSQLTEST_VARDIR/tmp/rpl_skip_replication.binlog
178
179 # The master should have all three events.
180 SELECT * FROM t1 ORDER by a;
181
182 # The slave should be missing event 2, which is marked with the
183 # @@skip_replication flag.
184
185 connection slave;
186 START SLAVE;
187
188 connection master;
189 sync_slave_with_master;
190
191 connection slave;
192 SELECT * FROM t1 ORDER by a;
193
194 #
195 # Test that @@sql_slave_skip_counter does not count skipped @@skip_replication
196 # events.
197 #
198
199 connection master;
200 TRUNCATE t1;
201
202 sync_slave_with_master;
203 connection slave;
204 STOP SLAVE;
205 # We will skip two INSERTs (in addition to any skipped due to
206 # @@skip_replication). Since from 5.5 every statement is wrapped in
207 # BEGIN ... END, we need to skip 6 events for this.
208 SET GLOBAL sql_slave_skip_counter=6;
209 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_SLAVE;
210 START SLAVE;
211
212 connection master;
213 # Need to fix @@binlog_format to get consistent event count.
214 SET @old_binlog_format= @@binlog_format;
215 SET binlog_format= statement;
216 SET skip_replication=0;
217 INSERT INTO t1 VALUES (1,5);
218 SET skip_replication=1;
219 INSERT INTO t1 VALUES (2,5);
220 SET skip_replication=0;
221 INSERT INTO t1 VALUES (3,5);
222 INSERT INTO t1 VALUES (4,5);
223 SET binlog_format= @old_binlog_format;
224
225 sync_slave_with_master;
226 connection slave;
227
228 # The slave should have skipped the first three inserts (number 1 and 3 due
229 # to @@sql_slave_skip_counter=2, number 2 due to
230 # @@replicate_events_marked_for_skip=FILTER_ON_SLAVE). So only number 4
231 # should be left.
232 SELECT * FROM t1;
233
234
235 #
236 # Check that BINLOG statement preserves the @@skip_replication flag.
237 #
238 connection slave;
239 # Need row @@binlog_format for BINLOG statements containing row events.
240 --source include/stop_slave.inc
241 SET @old_slave_binlog_format= @@global.binlog_format;
242 SET GLOBAL binlog_format= row;
243 --source include/start_slave.inc
244
245 connection master;
246 TRUNCATE t1;
247
248 SET @old_binlog_format= @@binlog_format;
249 SET binlog_format= row;
250 # Format description log event.
251 BINLOG 'wlZOTw8BAAAA8QAAAPUAAAAAAAQANS41LjIxLU1hcmlhREItZGVidWctbG9nAAAAAAAAAAAAAAAA
252 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
253 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
254 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
255 AAAAAAAAAAAA371saA==';
256 # INSERT INTO t1 VALUES (1,8) # with @@skip_replication=1
257 BINLOG 'wlZOTxMBAAAAKgAAAGMBAAAAgCkAAAAAAAEABHRlc3QAAnQxAAIDAwAC
258 wlZOTxcBAAAAJgAAAIkBAAAAgCkAAAAAAAEAAv/8AQAAAAgAAAA=';
259 # INSERT INTO t1 VALUES (2,8) # with @@skip_replication=0
260 BINLOG 'wlZOTxMBAAAAKgAAADwCAAAAACkAAAAAAAEABHRlc3QAAnQxAAIDAwAC
261 wlZOTxcBAAAAJgAAAGICAAAAACkAAAAAAAEAAv/8AgAAAAgAAAA=';
262 SET binlog_format= @old_binlog_format;
263
264 SELECT * FROM t1 ORDER BY a;
265 sync_slave_with_master;
266 connection slave;
267 # Slave should have only the second insert, the first should be ignored due to
268 # the @@skip_replication flag.
269 SELECT * FROM t1 ORDER by a;
270
271 --source include/stop_slave.inc
272 SET GLOBAL binlog_format= @old_slave_binlog_format;
273 --source include/start_slave.inc
274
275
276 # Test that it is not possible to change @@skip_replication inside a
277 # transaction or statement, thereby replicating only parts of statements
278 # or transactions.
279 connection master;
280 SET skip_replication=0;
281
282 BEGIN;
283 --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SKIP_REPLICATION
284 SET skip_replication=0;
285 --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SKIP_REPLICATION
286 SET skip_replication=1;
287 ROLLBACK;
288 SET skip_replication=1;
289 BEGIN;
290 --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SKIP_REPLICATION
291 SET skip_replication=0;
292 --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SKIP_REPLICATION
293 SET skip_replication=1;
294 COMMIT;
295 SET autocommit=0;
296 INSERT INTO t2(a) VALUES(100);
297 --error ER_INSIDE_TRANSACTION_PREVENTS_SWITCH_SKIP_REPLICATION
298 SET skip_replication=1;
299 ROLLBACK;
300 SET autocommit=1;
301
302 SET skip_replication=1;
303 --delimiter |
304 CREATE FUNCTION foo (x INT) RETURNS INT BEGIN SET SESSION skip_replication=x; RETURN x; END|
305 CREATE PROCEDURE bar(x INT) BEGIN SET SESSION skip_replication=x; END|
306 CREATE FUNCTION baz (x INT) RETURNS INT BEGIN CALL bar(x); RETURN x; END|
307 --delimiter ;
308 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_SKIP_REPLICATION
309 SELECT foo(0);
310 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_SKIP_REPLICATION
311 SELECT baz(0);
312 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_SKIP_REPLICATION
313 SET @a= foo(1);
314 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_SKIP_REPLICATION
315 SET @a= baz(1);
316 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_SKIP_REPLICATION
317 UPDATE t2 SET b=foo(0);
318 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_SKIP_REPLICATION
319 UPDATE t2 SET b=baz(0);
320 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_SKIP_REPLICATION
321 INSERT INTO t1 VALUES (101, foo(1));
322 --error ER_STORED_FUNCTION_PREVENTS_SWITCH_SKIP_REPLICATION
323 INSERT INTO t1 VALUES (101, baz(0));
324 SELECT @@skip_replication;
325 CALL bar(0);
326 SELECT @@skip_replication;
327 CALL bar(1);
328 SELECT @@skip_replication;
329 DROP FUNCTION foo;
330 DROP PROCEDURE bar;
331 DROP FUNCTION baz;
332
333
334 # Test that master-side filtering happens on the master side, and that
335 # slave-side filtering happens on the slave.
336
337 # First test that events do not reach the slave when master-side filtering
338 # is configured. Do this by replicating first with only the IO thread running
339 # and master-side filtering; then change to no filtering and start the SQL
340 # thread. This should still skip the events, as master-side filtering
341 # means the events never reached the slave.
342 connection master;
343 SET skip_replication= 0;
344 TRUNCATE t1;
345 sync_slave_with_master;
346 connection slave;
347 STOP SLAVE;
348 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_MASTER;
349 START SLAVE IO_THREAD;
350 connection master;
351 SET skip_replication= 1;
352 INSERT INTO t1(a) VALUES (1);
353 SET skip_replication= 0;
354 INSERT INTO t1(a) VALUES (2);
355 --source include/save_master_pos.inc
356 connection slave;
357 --source include/sync_io_with_master.inc
358 STOP SLAVE IO_THREAD;
359 SET GLOBAL replicate_events_marked_for_skip=REPLICATE;
360 START SLAVE;
361 connection master;
362 sync_slave_with_master;
363 connection slave;
364 # Now only the second insert of (2) should be visible, as the first was
365 # filtered on the master, so even though the SQL thread ran without skipping
366 # events, it will never see the event in the first place.
367 SELECT * FROM t1;
368
369 # Now tests that when slave-side filtering is configured, events _do_ reach
370 # the slave.
371 connection master;
372 SET skip_replication= 0;
373 TRUNCATE t1;
374 sync_slave_with_master;
375 connection slave;
376 STOP SLAVE;
377 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_SLAVE;
378 START SLAVE IO_THREAD;
379 connection master;
380 SET skip_replication= 1;
381 INSERT INTO t1(a) VALUES (1);
382 SET skip_replication= 0;
383 INSERT INTO t1(a) VALUES (2);
384 --source include/save_master_pos.inc
385 connection slave;
386 --source include/sync_io_with_master.inc
387 STOP SLAVE IO_THREAD;
388 SET GLOBAL replicate_events_marked_for_skip=REPLICATE;
389 START SLAVE;
390 connection master;
391 sync_slave_with_master;
392 connection slave;
393 # Now both inserts should be visible. Since filtering was configured to be
394 # slave-side, the event is in the relay log, and when the SQL thread ran we
395 # had disabled filtering again.
396 SELECT * FROM t1 ORDER BY a;
397
398
399 # Clean up.
400 connection master;
401 SET skip_replication=0;
402 DROP TABLE t1,t2;
403 connection slave;
404 STOP SLAVE;
405 SET GLOBAL replicate_events_marked_for_skip=REPLICATE;
406 START SLAVE;
407
408 --source include/rpl_end.inc