Debian Codegraph @

mysql-test/suite/binlog_encryption/rpl_skip_replication.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection slave;
4 include/stop_slave.inc
5 CHANGE MASTER TO MASTER_USE_GTID=NO;
6 include/start_slave.inc
7 connection master;
8 connection slave;
9 CREATE USER 'nonsuperuser'@'127.0.0.1';
10 GRANT ALTER,CREATE,DELETE,DROP,EVENT,INSERT,PROCESS,REPLICATION SLAVE,
11 SELECT,UPDATE ON *.* TO 'nonsuperuser'@'127.0.0.1';
12 connect nonpriv, 127.0.0.1, nonsuperuser,, test, $SLAVE_MYPORT,;
13 connection nonpriv;
14 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_MASTER;
15 ERROR 42000: Access denied; you need (at least one of) the REPLICATION SLAVE ADMIN privilege(s) for this operation
16 disconnect nonpriv;
17 connection slave;
18 DROP USER'nonsuperuser'@'127.0.0.1';
19 SELECT @@global.replicate_events_marked_for_skip;
20 @@global.replicate_events_marked_for_skip
21 REPLICATE
22 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_SLAVE;
23 ERROR HY000: This operation cannot be performed as you have a running slave ''; run STOP SLAVE '' first
24 SELECT @@global.replicate_events_marked_for_skip;
25 @@global.replicate_events_marked_for_skip
26 REPLICATE
27 STOP SLAVE;
28 SET SESSION replicate_events_marked_for_skip=FILTER_ON_MASTER;
29 ERROR HY000: Variable 'replicate_events_marked_for_skip' is a GLOBAL variable and should be set with SET GLOBAL
30 SELECT @@global.replicate_events_marked_for_skip;
31 @@global.replicate_events_marked_for_skip
32 REPLICATE
33 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_MASTER;
34 SELECT @@global.replicate_events_marked_for_skip;
35 @@global.replicate_events_marked_for_skip
36 FILTER_ON_MASTER
37 START SLAVE;
38 connection master;
39 SELECT @@skip_replication;
40 @@skip_replication
41 0
42 SET GLOBAL skip_replication=1;
43 ERROR HY000: Variable 'skip_replication' is a SESSION variable and can't be used with SET GLOBAL
44 SELECT @@skip_replication;
45 @@skip_replication
46 0
47 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=myisam;
48 CREATE TABLE t2 (a INT PRIMARY KEY, b INT) ENGINE=innodb;
49 INSERT INTO t1(a) VALUES (1);
50 INSERT INTO t2(a) VALUES (1);
51 SET skip_replication=1;
52 CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=myisam;
53 INSERT INTO t1(a) VALUES (2);
54 INSERT INTO t2(a) VALUES (2);
55 FLUSH NO_WRITE_TO_BINLOG LOGS;
56 connection slave;
57 connection slave;
58 SHOW TABLES;
59 Tables_in_test
60 t1
61 t2
62 SELECT * FROM t1;
63 a b
64 1 NULL
65 SELECT * FROM t2;
66 a b
67 1 NULL
68 connection master;
69 DROP TABLE t3;
70 FLUSH NO_WRITE_TO_BINLOG LOGS;
71 connection slave;
72 connection slave;
73 STOP SLAVE;
74 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_SLAVE;
75 START SLAVE;
76 connection master;
77 SET skip_replication=1;
78 CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=myisam;
79 INSERT INTO t1(a) VALUES (3);
80 INSERT INTO t2(a) VALUES (3);
81 FLUSH NO_WRITE_TO_BINLOG LOGS;
82 connection slave;
83 connection slave;
84 SHOW TABLES;
85 Tables_in_test
86 t1
87 t2
88 SELECT * FROM t1;
89 a b
90 1 NULL
91 SELECT * FROM t2;
92 a b
93 1 NULL
94 connection master;
95 DROP TABLE t3;
96 FLUSH NO_WRITE_TO_BINLOG LOGS;
97 connection slave;
98 connection slave;
99 STOP SLAVE;
100 SET GLOBAL replicate_events_marked_for_skip=REPLICATE;
101 START SLAVE;
102 connection master;
103 SET skip_replication=1;
104 CREATE TABLE t3 (a INT PRIMARY KEY, b INT) ENGINE=myisam;
105 INSERT INTO t3(a) VALUES(2);
106 connection slave;
107 connection slave;
108 SELECT * FROM t3;
109 a b
110 2 NULL
111 connection master;
112 DROP TABLE t3;
113 TRUNCATE t1;
114 connection slave;
115 connection slave;
116 RESET MASTER;
117 connection master;
118 SET skip_replication=0;
119 INSERT INTO t1 VALUES (1,0);
120 SET skip_replication=1;
121 INSERT INTO t1 VALUES (2,0);
122 SET skip_replication=0;
123 INSERT INTO t1 VALUES (3,0);
124 connection slave;
125 connection slave;
126 SELECT * FROM t1 ORDER by a;
127 a b
128 1 0
129 2 0
130 3 0
131 STOP SLAVE;
132 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_MASTER;
133 connection master;
134 TRUNCATE t1;
135 SELECT * FROM t1 ORDER by a;
136 a b
137 1 0
138 2 0
139 3 0
140 connection slave;
141 START SLAVE;
142 connection master;
143 connection slave;
144 connection slave;
145 SELECT * FROM t1 ORDER by a;
146 a b
147 1 0
148 3 0
149 connection master;
150 TRUNCATE t1;
151 connection slave;
152 connection slave;
153 STOP SLAVE;
154 SET GLOBAL sql_slave_skip_counter=6;
155 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_SLAVE;
156 START SLAVE;
157 connection master;
158 SET @old_binlog_format= @@binlog_format;
159 SET binlog_format= statement;
160 SET skip_replication=0;
161 INSERT INTO t1 VALUES (1,5);
162 SET skip_replication=1;
163 INSERT INTO t1 VALUES (2,5);
164 SET skip_replication=0;
165 INSERT INTO t1 VALUES (3,5);
166 INSERT INTO t1 VALUES (4,5);
167 SET binlog_format= @old_binlog_format;
168 connection slave;
169 connection slave;
170 SELECT * FROM t1;
171 a b
172 4 5
173 connection slave;
174 include/stop_slave.inc
175 SET @old_slave_binlog_format= @@global.binlog_format;
176 SET GLOBAL binlog_format= row;
177 include/start_slave.inc
178 connection master;
179 TRUNCATE t1;
180 SET @old_binlog_format= @@binlog_format;
181 SET binlog_format= row;
182 BINLOG 'wlZOTw8BAAAA8QAAAPUAAAAAAAQANS41LjIxLU1hcmlhREItZGVidWctbG9nAAAAAAAAAAAAAAAA
183 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
184 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
185 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
186 AAAAAAAAAAAA371saA==';
187 BINLOG 'wlZOTxMBAAAAKgAAAGMBAAAAgCkAAAAAAAEABHRlc3QAAnQxAAIDAwAC
188 wlZOTxcBAAAAJgAAAIkBAAAAgCkAAAAAAAEAAv/8AQAAAAgAAAA=';
189 BINLOG 'wlZOTxMBAAAAKgAAADwCAAAAACkAAAAAAAEABHRlc3QAAnQxAAIDAwAC
190 wlZOTxcBAAAAJgAAAGICAAAAACkAAAAAAAEAAv/8AgAAAAgAAAA=';
191 SET binlog_format= @old_binlog_format;
192 SELECT * FROM t1 ORDER BY a;
193 a b
194 1 8
195 2 8
196 connection slave;
197 connection slave;
198 SELECT * FROM t1 ORDER by a;
199 a b
200 2 8
201 include/stop_slave.inc
202 SET GLOBAL binlog_format= @old_slave_binlog_format;
203 include/start_slave.inc
204 connection master;
205 SET skip_replication=0;
206 BEGIN;
207 SET skip_replication=0;
208 ERROR HY000: Cannot modify @@session.skip_replication inside a transaction
209 SET skip_replication=1;
210 ERROR HY000: Cannot modify @@session.skip_replication inside a transaction
211 ROLLBACK;
212 SET skip_replication=1;
213 BEGIN;
214 SET skip_replication=0;
215 ERROR HY000: Cannot modify @@session.skip_replication inside a transaction
216 SET skip_replication=1;
217 ERROR HY000: Cannot modify @@session.skip_replication inside a transaction
218 COMMIT;
219 SET autocommit=0;
220 INSERT INTO t2(a) VALUES(100);
221 SET skip_replication=1;
222 ERROR HY000: Cannot modify @@session.skip_replication inside a transaction
223 ROLLBACK;
224 SET autocommit=1;
225 SET skip_replication=1;
226 CREATE FUNCTION foo (x INT) RETURNS INT BEGIN SET SESSION skip_replication=x; RETURN x; END|
227 CREATE PROCEDURE bar(x INT) BEGIN SET SESSION skip_replication=x; END|
228 CREATE FUNCTION baz (x INT) RETURNS INT BEGIN CALL bar(x); RETURN x; END|
229 SELECT foo(0);
230 ERROR HY000: Cannot modify @@session.skip_replication inside a stored function or trigger
231 SELECT baz(0);
232 ERROR HY000: Cannot modify @@session.skip_replication inside a stored function or trigger
233 SET @a= foo(1);
234 ERROR HY000: Cannot modify @@session.skip_replication inside a stored function or trigger
235 SET @a= baz(1);
236 ERROR HY000: Cannot modify @@session.skip_replication inside a stored function or trigger
237 UPDATE t2 SET b=foo(0);
238 ERROR HY000: Cannot modify @@session.skip_replication inside a stored function or trigger
239 UPDATE t2 SET b=baz(0);
240 ERROR HY000: Cannot modify @@session.skip_replication inside a stored function or trigger
241 INSERT INTO t1 VALUES (101, foo(1));
242 ERROR HY000: Cannot modify @@session.skip_replication inside a stored function or trigger
243 INSERT INTO t1 VALUES (101, baz(0));
244 ERROR HY000: Cannot modify @@session.skip_replication inside a stored function or trigger
245 SELECT @@skip_replication;
246 @@skip_replication
247 1
248 CALL bar(0);
249 SELECT @@skip_replication;
250 @@skip_replication
251 0
252 CALL bar(1);
253 SELECT @@skip_replication;
254 @@skip_replication
255 1
256 DROP FUNCTION foo;
257 DROP PROCEDURE bar;
258 DROP FUNCTION baz;
259 connection master;
260 SET skip_replication= 0;
261 TRUNCATE t1;
262 connection slave;
263 connection slave;
264 STOP SLAVE;
265 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_MASTER;
266 START SLAVE IO_THREAD;
267 connection master;
268 SET skip_replication= 1;
269 INSERT INTO t1(a) VALUES (1);
270 SET skip_replication= 0;
271 INSERT INTO t1(a) VALUES (2);
272 include/save_master_pos.inc
273 connection slave;
274 include/sync_io_with_master.inc
275 STOP SLAVE IO_THREAD;
276 SET GLOBAL replicate_events_marked_for_skip=REPLICATE;
277 START SLAVE;
278 connection master;
279 connection slave;
280 connection slave;
281 SELECT * FROM t1;
282 a b
283 2 NULL
284 connection master;
285 SET skip_replication= 0;
286 TRUNCATE t1;
287 connection slave;
288 connection slave;
289 STOP SLAVE;
290 SET GLOBAL replicate_events_marked_for_skip=FILTER_ON_SLAVE;
291 START SLAVE IO_THREAD;
292 connection master;
293 SET skip_replication= 1;
294 INSERT INTO t1(a) VALUES (1);
295 SET skip_replication= 0;
296 INSERT INTO t1(a) VALUES (2);
297 include/save_master_pos.inc
298 connection slave;
299 include/sync_io_with_master.inc
300 STOP SLAVE IO_THREAD;
301 SET GLOBAL replicate_events_marked_for_skip=REPLICATE;
302 START SLAVE;
303 connection master;
304 connection slave;
305 connection slave;
306 SELECT * FROM t1 ORDER BY a;
307 a b
308 1 NULL
309 2 NULL
310 connection master;
311 SET skip_replication=0;
312 DROP TABLE t1,t2;
313 connection slave;
314 STOP SLAVE;
315 SET GLOBAL replicate_events_marked_for_skip=REPLICATE;
316 START SLAVE;
317 include/rpl_end.inc