Debian Codegraph @

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

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 #Test case 1:
7 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY);
8 CREATE TABLE t2 (f1 INTEGER PRIMARY KEY);
9 CREATE TABLE t3 (f1 INTEGER PRIMARY KEY);
10 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW
11 BEGIN
12 DECLARE EXIT HANDLER FOR SQLEXCEPTION
13 BEGIN
14 ROLLBACK TO event_logging_1;
15 INSERT t3 VALUES (1);
16 END;
17 SAVEPOINT event_logging_1;
18 INSERT INTO t2 VALUES (1);
19 RELEASE SAVEPOINT event_logging_1;
20 END|
21 INSERT INTO t2 VALUES (1);
22 INSERT INTO t1 VALUES (1);
23 include/show_binlog_events.inc
24 Log_name Pos Event_type Server_id End_log_pos Info
25 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY)
26 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER PRIMARY KEY)
27 master-bin.000001 # Query # # use `test`; CREATE TABLE t3 (f1 INTEGER PRIMARY KEY)
28 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW
29 BEGIN
30 DECLARE EXIT HANDLER FOR SQLEXCEPTION
31 BEGIN
32 ROLLBACK TO event_logging_1;
33 INSERT t3 VALUES (1);
34 END;
35 SAVEPOINT event_logging_1;
36 INSERT INTO t2 VALUES (1);
37 RELEASE SAVEPOINT event_logging_1;
38 END
39 master-bin.000001 # Query # # BEGIN
40 master-bin.000001 # Table_map # # table_id: # (test.t2)
41 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
42 master-bin.000001 # Xid # # COMMIT /* XID */
43 master-bin.000001 # Query # # BEGIN
44 master-bin.000001 # Table_map # # table_id: # (test.t1)
45 master-bin.000001 # Table_map # # table_id: # (test.t2)
46 master-bin.000001 # Table_map # # table_id: # (test.t3)
47 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
48 master-bin.000001 # Query # # SAVEPOINT `event_logging_1`
49 master-bin.000001 # Table_map # # table_id: # (test.t1)
50 master-bin.000001 # Table_map # # table_id: # (test.t2)
51 master-bin.000001 # Table_map # # table_id: # (test.t3)
52 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
53 master-bin.000001 # Xid # # COMMIT /* XID */
54 [connection master]
55 DROP TRIGGER tr1;
56 DELETE FROM t1;
57 DELETE FROM t2;
58 DELETE FROM t3;
59 # Test case 2:
60 CREATE PROCEDURE p1()
61 BEGIN
62 DECLARE EXIT HANDLER FOR SQLEXCEPTION
63 BEGIN
64 ROLLBACK TO event_logging_2;
65 INSERT t3 VALUES (3);
66 END;
67 SAVEPOINT event_logging_2;
68 INSERT INTO t2 VALUES (1);
69 RELEASE SAVEPOINT event_logging_2;
70 END|
71 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1()|
72 INSERT INTO t2 VALUES (1);
73 INSERT INTO t1 VALUES (1);
74 include/show_binlog_events.inc
75 Log_name Pos Event_type Server_id End_log_pos Info
76 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY)
77 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER PRIMARY KEY)
78 master-bin.000001 # Query # # use `test`; CREATE TABLE t3 (f1 INTEGER PRIMARY KEY)
79 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW
80 BEGIN
81 DECLARE EXIT HANDLER FOR SQLEXCEPTION
82 BEGIN
83 ROLLBACK TO event_logging_1;
84 INSERT t3 VALUES (1);
85 END;
86 SAVEPOINT event_logging_1;
87 INSERT INTO t2 VALUES (1);
88 RELEASE SAVEPOINT event_logging_1;
89 END
90 master-bin.000001 # Query # # BEGIN
91 master-bin.000001 # Table_map # # table_id: # (test.t2)
92 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
93 master-bin.000001 # Xid # # COMMIT /* XID */
94 master-bin.000001 # Query # # BEGIN
95 master-bin.000001 # Table_map # # table_id: # (test.t1)
96 master-bin.000001 # Table_map # # table_id: # (test.t2)
97 master-bin.000001 # Table_map # # table_id: # (test.t3)
98 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
99 master-bin.000001 # Query # # SAVEPOINT `event_logging_1`
100 master-bin.000001 # Table_map # # table_id: # (test.t1)
101 master-bin.000001 # Table_map # # table_id: # (test.t2)
102 master-bin.000001 # Table_map # # table_id: # (test.t3)
103 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
104 master-bin.000001 # Xid # # COMMIT /* XID */
105 master-bin.000001 # Query # # use `test`; DROP TRIGGER tr1
106 master-bin.000001 # Query # # BEGIN
107 master-bin.000001 # Table_map # # table_id: # (test.t1)
108 master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
109 master-bin.000001 # Xid # # COMMIT /* XID */
110 master-bin.000001 # Query # # BEGIN
111 master-bin.000001 # Table_map # # table_id: # (test.t2)
112 master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
113 master-bin.000001 # Xid # # COMMIT /* XID */
114 master-bin.000001 # Query # # BEGIN
115 master-bin.000001 # Table_map # # table_id: # (test.t3)
116 master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
117 master-bin.000001 # Xid # # COMMIT /* XID */
118 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
119 BEGIN
120 DECLARE EXIT HANDLER FOR SQLEXCEPTION
121 BEGIN
122 ROLLBACK TO event_logging_2;
123 INSERT t3 VALUES (3);
124 END;
125 SAVEPOINT event_logging_2;
126 INSERT INTO t2 VALUES (1);
127 RELEASE SAVEPOINT event_logging_2;
128 END
129 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1()
130 master-bin.000001 # Query # # BEGIN
131 master-bin.000001 # Table_map # # table_id: # (test.t2)
132 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
133 master-bin.000001 # Xid # # COMMIT /* XID */
134 master-bin.000001 # Query # # BEGIN
135 master-bin.000001 # Table_map # # table_id: # (test.t1)
136 master-bin.000001 # Table_map # # table_id: # (test.t2)
137 master-bin.000001 # Table_map # # table_id: # (test.t3)
138 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
139 master-bin.000001 # Query # # SAVEPOINT `event_logging_2`
140 master-bin.000001 # Table_map # # table_id: # (test.t1)
141 master-bin.000001 # Table_map # # table_id: # (test.t2)
142 master-bin.000001 # Table_map # # table_id: # (test.t3)
143 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
144 master-bin.000001 # Xid # # COMMIT /* XID */
145 [connection master]
146 DROP TABLE t1;
147 DROP TABLE t2;
148 DROP TABLE t3;
149 DROP PROCEDURE p1;
150 # Test case 3:
151 include/rpl_reset.inc
152 [connection master]
153 CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB;
154 Warnings:
155 Warning 1681 Integer display width is deprecated and will be removed in a future release.
156 CREATE TRIGGER t_insert_trig AFTER INSERT ON t
157 FOR EACH ROW
158 BEGIN
159 SAVEPOINT savepoint_1;
160 ROLLBACK TO savepoint_1;
161 END |
162 INSERT INTO t VALUES (2);
163 INSERT INTO t VALUES (3);
164 include/show_binlog_events.inc
165 Log_name Pos Event_type Server_id End_log_pos Info
166 master-bin.000001 # Query # # use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB
167 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER t_insert_trig AFTER INSERT ON t
168 FOR EACH ROW
169 BEGIN
170 SAVEPOINT savepoint_1;
171 ROLLBACK TO savepoint_1;
172 END
173 master-bin.000001 # Query # # BEGIN
174 master-bin.000001 # Table_map # # table_id: # (test.t)
175 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
176 master-bin.000001 # Query # # SAVEPOINT `savepoint_1`
177 master-bin.000001 # Xid # # COMMIT /* XID */
178 master-bin.000001 # Query # # BEGIN
179 master-bin.000001 # Table_map # # table_id: # (test.t)
180 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
181 master-bin.000001 # Query # # SAVEPOINT `savepoint_1`
182 master-bin.000001 # Xid # # COMMIT /* XID */
183 SELECT * FROM t;
184 f1
185 2
186 3
187 include/sync_slave_sql_with_master.inc
188 SELECT * FROM t;
189 f1
190 2
191 3
192 [connection master]
193 DROP TABLE t;
194 # Test case 4:
195 include/rpl_reset.inc
196 [connection master]
197 CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
198 Warnings:
199 Warning 1681 Integer display width is deprecated and will be removed in a future release.
200 CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
201 Warnings:
202 Warning 1681 Integer display width is deprecated and will be removed in a future release.
203 CREATE TRIGGER t_insert_trig BEFORE INSERT ON t
204 FOR EACH ROW
205 BEGIN
206 SAVEPOINT savepoint_1;
207 INSERT INTO t1 VALUES (5);
208 END |
209 INSERT INTO t VALUES (2), (3);
210 INSERT INTO t1 VALUES (30);
211 include/show_binlog_events.inc
212 Log_name Pos Event_type Server_id End_log_pos Info
213 master-bin.000001 # Query # # use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
214 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
215 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER t_insert_trig BEFORE INSERT ON t
216 FOR EACH ROW
217 BEGIN
218 SAVEPOINT savepoint_1;
219 INSERT INTO t1 VALUES (5);
220 END
221 master-bin.000001 # Query # # BEGIN
222 master-bin.000001 # Table_map # # table_id: # (test.t)
223 master-bin.000001 # Table_map # # table_id: # (test.t1)
224 master-bin.000001 # Write_rows # # table_id: #
225 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
226 master-bin.000001 # Query # # SAVEPOINT `savepoint_1`
227 master-bin.000001 # Table_map # # table_id: # (test.t)
228 master-bin.000001 # Table_map # # table_id: # (test.t1)
229 master-bin.000001 # Write_rows # # table_id: #
230 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
231 master-bin.000001 # Xid # # COMMIT /* XID */
232 master-bin.000001 # Query # # BEGIN
233 master-bin.000001 # Table_map # # table_id: # (test.t1)
234 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
235 master-bin.000001 # Xid # # COMMIT /* XID */
236 SELECT * FROM t;
237 f1
238 2
239 3
240 SELECT * FROM t1;
241 f1
242 5
243 5
244 30
245 include/sync_slave_sql_with_master.inc
246 SELECT * FROM t;
247 f1
248 2
249 3
250 SELECT * FROM t1;
251 f1
252 5
253 5
254 30
255 [connection master]
256 DROP TABLE t;
257 DROP TABLE t1;
258 # Test case 5:
259 include/rpl_reset.inc
260 [connection master]
261 CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
262 Warnings:
263 Warning 1681 Integer display width is deprecated and will be removed in a future release.
264 CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
265 Warnings:
266 Warning 1681 Integer display width is deprecated and will be removed in a future release.
267 CREATE TRIGGER t_insert_trig BEFORE INSERT ON t
268 FOR EACH ROW
269 BEGIN
270 SAVEPOINT savepoint_1;
271 END |
272 INSERT INTO t VALUES (2), (3);
273 INSERT INTO t1 VALUES (30);
274 include/show_binlog_events.inc
275 Log_name Pos Event_type Server_id End_log_pos Info
276 master-bin.000001 # Query # # use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
277 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
278 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER t_insert_trig BEFORE INSERT ON t
279 FOR EACH ROW
280 BEGIN
281 SAVEPOINT savepoint_1;
282 END
283 master-bin.000001 # Query # # BEGIN
284 master-bin.000001 # Table_map # # table_id: # (test.t)
285 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
286 master-bin.000001 # Query # # SAVEPOINT `savepoint_1`
287 master-bin.000001 # Table_map # # table_id: # (test.t)
288 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
289 master-bin.000001 # Xid # # COMMIT /* XID */
290 master-bin.000001 # Query # # BEGIN
291 master-bin.000001 # Table_map # # table_id: # (test.t1)
292 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
293 master-bin.000001 # Xid # # COMMIT /* XID */
294 SELECT * FROM t;
295 f1
296 2
297 3
298 SELECT * FROM t1;
299 f1
300 30
301 include/sync_slave_sql_with_master.inc
302 SELECT * FROM t;
303 f1
304 2
305 3
306 SELECT * FROM t1;
307 f1
308 30
309 [connection master]
310 DROP TABLE t;
311 DROP TABLE t1;
312 # Test case 6:
313 include/rpl_reset.inc
314 [connection master]
315 CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB;
316 CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB;
317 CREATE FUNCTION f1() RETURNS INT
318 BEGIN
319 SAVEPOINT event_logging_2;
320 INSERT INTO t1 VALUES (1);
321 ROLLBACK TO event_logging_2;
322 RETURN 0;
323 END|
324 BEGIN;
325 INSERT INTO t2 VALUES (1), (f1()), (2), (4);
326 COMMIT;
327 INSERT INTO t2 VALUES (10);
328 include/show_binlog_events.inc
329 Log_name Pos Event_type Server_id End_log_pos Info
330 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB
331 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB
332 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int
333 BEGIN
334 SAVEPOINT event_logging_2;
335 INSERT INTO t1 VALUES (1);
336 ROLLBACK TO event_logging_2;
337 RETURN 0;
338 END
339 master-bin.000001 # Query # # BEGIN
340 master-bin.000001 # Table_map # # table_id: # (test.t2)
341 master-bin.000001 # Table_map # # table_id: # (test.t1)
342 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
343 master-bin.000001 # Query # # SAVEPOINT `event_logging_2`
344 master-bin.000001 # Table_map # # table_id: # (test.t2)
345 master-bin.000001 # Table_map # # table_id: # (test.t1)
346 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
347 master-bin.000001 # Xid # # COMMIT /* XID */
348 master-bin.000001 # Query # # BEGIN
349 master-bin.000001 # Table_map # # table_id: # (test.t2)
350 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
351 master-bin.000001 # Xid # # COMMIT /* XID */
352 [connection master]
353 SELECT * FROM t2;
354 f1
355 1
356 0
357 2
358 4
359 10
360 SELECT * FROM t1;
361 f1
362 include/sync_slave_sql_with_master.inc
363 SELECT * FROM t2;
364 f1
365 1
366 0
367 2
368 4
369 10
370 SELECT * FROM t1;
371 f1
372 [connection master]
373 DROP TABLE t1;
374 DROP TABLE t2;
375 DROP FUNCTION f1;
376 # Test case 7:
377 include/rpl_reset.inc
378 [connection master]
379 CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB;
380 CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB;
381 CREATE FUNCTION f1() RETURNS INT
382 BEGIN
383 SAVEPOINT event_logging_2;
384 INSERT INTO t1 VALUES (1);
385 RETURN 0;
386 END|
387 BEGIN;
388 INSERT INTO t2 VALUES (1), (f1()), (2), (4);
389 COMMIT;
390 INSERT INTO t2 VALUES (10);
391 include/show_binlog_events.inc
392 Log_name Pos Event_type Server_id End_log_pos Info
393 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB
394 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB
395 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int
396 BEGIN
397 SAVEPOINT event_logging_2;
398 INSERT INTO t1 VALUES (1);
399 RETURN 0;
400 END
401 master-bin.000001 # Query # # BEGIN
402 master-bin.000001 # Table_map # # table_id: # (test.t2)
403 master-bin.000001 # Table_map # # table_id: # (test.t1)
404 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
405 master-bin.000001 # Query # # SAVEPOINT `event_logging_2`
406 master-bin.000001 # Table_map # # table_id: # (test.t2)
407 master-bin.000001 # Table_map # # table_id: # (test.t1)
408 master-bin.000001 # Write_rows # # table_id: #
409 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
410 master-bin.000001 # Xid # # COMMIT /* XID */
411 master-bin.000001 # Query # # BEGIN
412 master-bin.000001 # Table_map # # table_id: # (test.t2)
413 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
414 master-bin.000001 # Xid # # COMMIT /* XID */
415 [connection master]
416 SELECT * FROM t2;
417 f1
418 1
419 0
420 2
421 4
422 10
423 SELECT * FROM t1;
424 f1
425 1
426 include/sync_slave_sql_with_master.inc
427 SELECT * FROM t2;
428 f1
429 1
430 0
431 2
432 4
433 10
434 SELECT * FROM t1;
435 f1
436 1
437 [connection master]
438 DROP TABLE t1;
439 DROP TABLE t2;
440 DROP FUNCTION f1;
441 # Test case 8:
442 include/rpl_reset.inc
443 [connection master]
444 CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB;
445 CREATE FUNCTION f1() RETURNS INT
446 BEGIN
447 SAVEPOINT event_logging_2;
448 RETURN 0;
449 END|
450 BEGIN;
451 INSERT INTO t1 VALUES (1), (f1()), (2), (4);
452 COMMIT;
453 INSERT INTO t1 VALUES (10);
454 include/show_binlog_events.inc
455 Log_name Pos Event_type Server_id End_log_pos Info
456 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB
457 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int
458 BEGIN
459 SAVEPOINT event_logging_2;
460 RETURN 0;
461 END
462 master-bin.000001 # Query # # BEGIN
463 master-bin.000001 # Table_map # # table_id: # (test.t1)
464 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
465 master-bin.000001 # Query # # SAVEPOINT `event_logging_2`
466 master-bin.000001 # Table_map # # table_id: # (test.t1)
467 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
468 master-bin.000001 # Xid # # COMMIT /* XID */
469 master-bin.000001 # Query # # BEGIN
470 master-bin.000001 # Table_map # # table_id: # (test.t1)
471 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
472 master-bin.000001 # Xid # # COMMIT /* XID */
473 [connection master]
474 SELECT * FROM t1;
475 f1
476 1
477 0
478 2
479 4
480 10
481 include/sync_slave_sql_with_master.inc
482 SELECT * FROM t1;
483 f1
484 1
485 0
486 2
487 4
488 10
489 [connection master]
490 DROP TABLE t1;
491 DROP FUNCTION f1;
492 include/rpl_end.inc