Debian Codegraph @

mysql-test/suite/rpl/r/rpl_invoked_features.result

raw
1 include/master-slave.inc
2 [connection master]
3 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
4 call mtr.add_suppression("Event Scheduler: .* Duplicate entry '10' for key 'a'");
5 # Create tables
6 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=myisam;
7 INSERT INTO t1 VALUES (1,1,'1');
8 INSERT INTO t1 VALUES (2,2,UUID());
9 CREATE TABLE t2 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=myisam;
10 INSERT INTO t2 VALUES (1,1,'1');
11 INSERT INTO t2 VALUES (2,2,UUID());
12 CREATE TABLE t11 (a INT NOT NULL PRIMARY KEY, b INT, c VARCHAR(64)) ENGINE=innodb;
13 INSERT INTO t11 VALUES (1,1,'1');
14 INSERT INTO t11 VALUES (2,2,UUID());
15 CREATE TABLE t12 (a INT UNIQUE, b INT, c VARCHAR(64)) ENGINE=innodb;
16 INSERT INTO t12 VALUES (1,1,'1');
17 INSERT INTO t12 VALUES (2,2,UUID());
18 # Create invoked features
19 CREATE VIEW v1 AS SELECT * FROM t1;
20 CREATE VIEW v11 AS SELECT * FROM t11;
21 CREATE TRIGGER t1_tr1 BEFORE INSERT ON t1 FOR EACH ROW
22 BEGIN
23 INSERT INTO t2 VALUES (NEW.a, NEW.b, NEW.c);
24 INSERT INTO t3 VALUES (NEW.a, NEW.b, NEW.c);
25 END|
26 CREATE TRIGGER t1_tr2 BEFORE UPDATE ON t1 FOR EACH ROW
27 BEGIN
28 UPDATE t2 SET c = '';
29 UPDATE t3 SET c = '';
30 END|
31 CREATE TRIGGER t11_tr1 BEFORE INSERT ON t11 FOR EACH ROW
32 BEGIN
33 INSERT INTO t12 VALUES (NEW.a, NEW.b, NEW.c);
34 INSERT INTO t13 VALUES (NEW.a, NEW.b, NEW.c);
35 END|
36 CREATE TRIGGER t11_tr2 BEFORE UPDATE ON t11 FOR EACH ROW
37 BEGIN
38 UPDATE t12 SET c = '';
39 UPDATE t13 SET c = '';
40 END|
41 CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DISABLE DO
42 BEGIN
43 ALTER EVENT e1 DISABLE;
44 CALL p1(10, '');
45 END|
46 Warnings:
47 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
48 CREATE EVENT e11 ON SCHEDULE EVERY 10 SECOND DISABLE DO
49 BEGIN
50 ALTER EVENT e11 DISABLE;
51 CALL p11(10, '');
52 END|
53 Warnings:
54 Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it.
55 CREATE FUNCTION f1 (x INT) RETURNS VARCHAR(64)
56 BEGIN
57 IF x > 5 THEN
58 RETURN UUID();
59 END IF;
60 RETURN '';
61 END|
62 CREATE FUNCTION f2 (x INT) RETURNS VARCHAR(64)
63 BEGIN
64 RETURN f1(x);
65 END|
66 CREATE PROCEDURE p1 (IN x INT, IN y VARCHAR(64))
67 BEGIN
68 INSERT IGNORE INTO t1 VALUES (x,x,y);
69 END|
70 CREATE PROCEDURE p11 (IN x INT, IN y VARCHAR(64))
71 BEGIN
72 INSERT IGNORE INTO t11 VALUES (x,x,y);
73 END|
74 # Do some actions for non-transactional tables
75 CREATE TABLE t3 SELECT * FROM v1;
76 INSERT INTO t1 VALUES (3,3,'');
77 UPDATE t1 SET c='2' WHERE a = 1;
78 INSERT INTO t1 VALUES(4,4,f1(4));
79 INSERT INTO t1 VALUES (100,100,'');
80 CALL p1(5, UUID());
81 INSERT INTO t1 VALUES (101,101,'');
82 INSERT INTO t1 VALUES(6,6,f1(6));
83 INSERT INTO t1 VALUES (102,102,'');
84 INSERT INTO t1 VALUES(7,7,f2(7));
85 INSERT INTO t1 VALUES (103,103,'');
86 # Do some actions for transactional tables
87 CREATE TABLE t13 SELECT * FROM v11;
88 INSERT INTO t11 VALUES (3,3,'');
89 UPDATE t11 SET c='2' WHERE a = 1;
90 INSERT INTO t11 VALUES(4,4,f1(4));
91 INSERT INTO t11 VALUES (100,100,'');
92 CALL p11(5, UUID());
93 INSERT INTO t11 VALUES (101,101,'');
94 INSERT INTO t11 VALUES(6,6,f1(6));
95 INSERT INTO t11 VALUES (102,102,'');
96 INSERT INTO t11 VALUES(7,7,f2(7));
97 INSERT INTO t11 VALUES (103,103,'');
98 # Scheduler is on
99 SET GLOBAL EVENT_SCHEDULER = on;
100 ALTER EVENT e1 ENABLE;
101 ALTER EVENT e11 ENABLE;
102 SET GLOBAL EVENT_SCHEDULER = off;
103 # Check original objects
104 SHOW TABLES LIKE 't%';
105 Tables_in_test (t%)
106 t1
107 t11
108 t12
109 t13
110 t2
111 t3
112 SELECT table_name FROM information_schema.views WHERE table_schema='test';
113 table_name
114 v1
115 v11
116 SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
117 trigger_name event_manipulation event_object_table
118 t11_tr1 INSERT t11
119 t11_tr2 UPDATE t11
120 t1_tr1 INSERT t1
121 t1_tr2 UPDATE t1
122 SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
123 routine_type routine_name
124 FUNCTION f1
125 FUNCTION f2
126 PROCEDURE p1
127 PROCEDURE p11
128 SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
129 event_name status
130 e1 DISABLED
131 e11 DISABLED
132
133 SELECT COUNT(*) FROM t1;
134 COUNT(*)
135 12
136 SELECT a,b FROM t1 ORDER BY a;
137 a b
138 1 1
139 2 2
140 3 3
141 4 4
142 5 5
143 6 6
144 7 7
145 10 10
146 100 100
147 101 101
148 102 102
149 103 103
150 SELECT COUNT(*) FROM t2;
151 COUNT(*)
152 12
153 SELECT a,b FROM t2 ORDER BY a;
154 a b
155 1 1
156 2 2
157 3 3
158 4 4
159 5 5
160 6 6
161 7 7
162 10 10
163 100 100
164 101 101
165 102 102
166 103 103
167 SELECT COUNT(*) FROM t3;
168 COUNT(*)
169 12
170 SELECT a,b FROM t3 ORDER BY a;
171 a b
172 1 1
173 2 2
174 3 3
175 4 4
176 5 5
177 6 6
178 7 7
179 10 10
180 100 100
181 101 101
182 102 102
183 103 103
184 SELECT a,b FROM v1 ORDER BY a;
185 a b
186 1 1
187 2 2
188 3 3
189 4 4
190 5 5
191 6 6
192 7 7
193 10 10
194 100 100
195 101 101
196 102 102
197 103 103
198 SELECT COUNT(*) FROM t11;
199 COUNT(*)
200 12
201 SELECT a,b FROM t11 ORDER BY a;
202 a b
203 1 1
204 2 2
205 3 3
206 4 4
207 5 5
208 6 6
209 7 7
210 10 10
211 100 100
212 101 101
213 102 102
214 103 103
215 SELECT COUNT(*) FROM t12;
216 COUNT(*)
217 12
218 SELECT a,b FROM t12 ORDER BY a;
219 a b
220 1 1
221 2 2
222 3 3
223 4 4
224 5 5
225 6 6
226 7 7
227 10 10
228 100 100
229 101 101
230 102 102
231 103 103
232 SELECT COUNT(*) FROM t13;
233 COUNT(*)
234 12
235 SELECT a,b FROM t13 ORDER BY a;
236 a b
237 1 1
238 2 2
239 3 3
240 4 4
241 5 5
242 6 6
243 7 7
244 10 10
245 100 100
246 101 101
247 102 102
248 103 103
249 SELECT a,b FROM v11 ORDER BY a;
250 a b
251 1 1
252 2 2
253 3 3
254 4 4
255 5 5
256 6 6
257 7 7
258 10 10
259 100 100
260 101 101
261 102 102
262 103 103
263 connection slave;
264 # Check replicated objects
265 SHOW TABLES LIKE 't%';
266 Tables_in_test (t%)
267 t1
268 t11
269 t12
270 t13
271 t2
272 t3
273 SELECT table_name FROM information_schema.views WHERE table_schema='test';
274 table_name
275 v1
276 v11
277 SELECT trigger_name, event_manipulation, event_object_table FROM information_schema.triggers WHERE trigger_schema='test';
278 trigger_name event_manipulation event_object_table
279 t11_tr1 INSERT t11
280 t11_tr2 UPDATE t11
281 t1_tr1 INSERT t1
282 t1_tr2 UPDATE t1
283 SELECT routine_type, routine_name FROM information_schema.routines WHERE routine_schema='test';
284 routine_type routine_name
285 FUNCTION f1
286 FUNCTION f2
287 PROCEDURE p1
288 PROCEDURE p11
289 SELECT event_name, status FROM information_schema.events WHERE event_schema='test';
290 event_name status
291 e1 SLAVESIDE_DISABLED
292 e11 SLAVESIDE_DISABLED
293 # Check replicated data
294 SELECT COUNT(*) FROM t1;
295 COUNT(*)
296 12
297 SELECT a,b FROM t1 ORDER BY a;
298 a b
299 1 1
300 2 2
301 3 3
302 4 4
303 5 5
304 6 6
305 7 7
306 10 10
307 100 100
308 101 101
309 102 102
310 103 103
311 SELECT COUNT(*) FROM t2;
312 COUNT(*)
313 12
314 SELECT a,b FROM t2 ORDER BY a;
315 a b
316 1 1
317 2 2
318 3 3
319 4 4
320 5 5
321 6 6
322 7 7
323 10 10
324 100 100
325 101 101
326 102 102
327 103 103
328 SELECT COUNT(*) FROM t3;
329 COUNT(*)
330 12
331 SELECT a,b FROM t3 ORDER BY a;
332 a b
333 1 1
334 2 2
335 3 3
336 4 4
337 5 5
338 6 6
339 7 7
340 10 10
341 100 100
342 101 101
343 102 102
344 103 103
345 SELECT a,b FROM v1 ORDER BY a;
346 a b
347 1 1
348 2 2
349 3 3
350 4 4
351 5 5
352 6 6
353 7 7
354 10 10
355 100 100
356 101 101
357 102 102
358 103 103
359 SELECT COUNT(*) FROM t11;
360 COUNT(*)
361 12
362 SELECT a,b FROM t11 ORDER BY a;
363 a b
364 1 1
365 2 2
366 3 3
367 4 4
368 5 5
369 6 6
370 7 7
371 10 10
372 100 100
373 101 101
374 102 102
375 103 103
376 SELECT COUNT(*) FROM t12;
377 COUNT(*)
378 12
379 SELECT a,b FROM t12 ORDER BY a;
380 a b
381 1 1
382 2 2
383 3 3
384 4 4
385 5 5
386 6 6
387 7 7
388 10 10
389 100 100
390 101 101
391 102 102
392 103 103
393 SELECT COUNT(*) FROM t13;
394 COUNT(*)
395 12
396 SELECT a,b FROM t13 ORDER BY a;
397 a b
398 1 1
399 2 2
400 3 3
401 4 4
402 5 5
403 6 6
404 7 7
405 10 10
406 100 100
407 101 101
408 102 102
409 103 103
410 SELECT a,b FROM v11 ORDER BY a;
411 a b
412 1 1
413 2 2
414 3 3
415 4 4
416 5 5
417 6 6
418 7 7
419 10 10
420 100 100
421 101 101
422 102 102
423 103 103
424 connection master;
425 # Remove UUID() before comparing and sort tables
426 UPDATE t1 SET c='';
427 UPDATE t2 SET c='';
428 UPDATE t3 SET c='';
429 UPDATE t11 SET c='';
430 UPDATE t12 SET c='';
431 UPDATE t13 SET c='';
432 ALTER TABLE t3 ORDER BY a;
433 ALTER TABLE t13 ORDER BY a;
434 connection slave;
435 # Compare a data from master and slave
436 connection master;
437 DROP VIEW IF EXISTS v1,v11;
438 DROP TABLE IF EXISTS t1,t2,t3,t11,t12,t13;
439 DROP PROCEDURE IF EXISTS p1;
440 DROP PROCEDURE IF EXISTS p11;
441 DROP FUNCTION IF EXISTS f1;
442 DROP FUNCTION IF EXISTS f2;
443 DROP EVENT IF EXISTS e1;
444 DROP EVENT IF EXISTS e11;
445 connection slave;
446 include/rpl_end.inc