Debian Codegraph @

mysql-test/suite/rpl/r/rpl_stm_mix_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 # Query # # use `test`; INSERT INTO t2 VALUES (1)
41 master-bin.000001 # Xid # # COMMIT /* XID */
42 master-bin.000001 # Query # # BEGIN
43 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1)
44 master-bin.000001 # Xid # # COMMIT /* XID */
45 [connection master]
46 DROP TRIGGER tr1;
47 DELETE FROM t1;
48 DELETE FROM t2;
49 DELETE FROM t3;
50 # Test case 2:
51 CREATE PROCEDURE p1()
52 BEGIN
53 DECLARE EXIT HANDLER FOR SQLEXCEPTION
54 BEGIN
55 ROLLBACK TO event_logging_2;
56 INSERT t3 VALUES (3);
57 END;
58 SAVEPOINT event_logging_2;
59 INSERT INTO t2 VALUES (1);
60 RELEASE SAVEPOINT event_logging_2;
61 END|
62 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1()|
63 INSERT INTO t2 VALUES (1);
64 INSERT INTO t1 VALUES (1);
65 include/show_binlog_events.inc
66 Log_name Pos Event_type Server_id End_log_pos Info
67 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER PRIMARY KEY)
68 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER PRIMARY KEY)
69 master-bin.000001 # Query # # use `test`; CREATE TABLE t3 (f1 INTEGER PRIMARY KEY)
70 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW
71 BEGIN
72 DECLARE EXIT HANDLER FOR SQLEXCEPTION
73 BEGIN
74 ROLLBACK TO event_logging_1;
75 INSERT t3 VALUES (1);
76 END;
77 SAVEPOINT event_logging_1;
78 INSERT INTO t2 VALUES (1);
79 RELEASE SAVEPOINT event_logging_1;
80 END
81 master-bin.000001 # Query # # BEGIN
82 master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (1)
83 master-bin.000001 # Xid # # COMMIT /* XID */
84 master-bin.000001 # Query # # BEGIN
85 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1)
86 master-bin.000001 # Xid # # COMMIT /* XID */
87 master-bin.000001 # Query # # use `test`; DROP TRIGGER tr1
88 master-bin.000001 # Query # # BEGIN
89 master-bin.000001 # Query # # use `test`; DELETE FROM t1
90 master-bin.000001 # Xid # # COMMIT /* XID */
91 master-bin.000001 # Query # # BEGIN
92 master-bin.000001 # Query # # use `test`; DELETE FROM t2
93 master-bin.000001 # Xid # # COMMIT /* XID */
94 master-bin.000001 # Query # # BEGIN
95 master-bin.000001 # Query # # use `test`; DELETE FROM t3
96 master-bin.000001 # Xid # # COMMIT /* XID */
97 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
98 BEGIN
99 DECLARE EXIT HANDLER FOR SQLEXCEPTION
100 BEGIN
101 ROLLBACK TO event_logging_2;
102 INSERT t3 VALUES (3);
103 END;
104 SAVEPOINT event_logging_2;
105 INSERT INTO t2 VALUES (1);
106 RELEASE SAVEPOINT event_logging_2;
107 END
108 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1()
109 master-bin.000001 # Query # # BEGIN
110 master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (1)
111 master-bin.000001 # Xid # # COMMIT /* XID */
112 master-bin.000001 # Query # # BEGIN
113 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1)
114 master-bin.000001 # Xid # # COMMIT /* XID */
115 [connection master]
116 DROP TABLE t1;
117 DROP TABLE t2;
118 DROP TABLE t3;
119 DROP PROCEDURE p1;
120 # Test case 3:
121 include/rpl_reset.inc
122 [connection master]
123 CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB;
124 Warnings:
125 Warning 1681 Integer display width is deprecated and will be removed in a future release.
126 CREATE TRIGGER t_insert_trig AFTER INSERT ON t
127 FOR EACH ROW
128 BEGIN
129 SAVEPOINT savepoint_1;
130 ROLLBACK TO savepoint_1;
131 END |
132 INSERT INTO t VALUES (2);
133 INSERT INTO t VALUES (3);
134 include/show_binlog_events.inc
135 Log_name Pos Event_type Server_id End_log_pos Info
136 master-bin.000001 # Query # # use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB
137 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER t_insert_trig AFTER INSERT ON t
138 FOR EACH ROW
139 BEGIN
140 SAVEPOINT savepoint_1;
141 ROLLBACK TO savepoint_1;
142 END
143 master-bin.000001 # Query # # BEGIN
144 master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (2)
145 master-bin.000001 # Xid # # COMMIT /* XID */
146 master-bin.000001 # Query # # BEGIN
147 master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (3)
148 master-bin.000001 # Xid # # COMMIT /* XID */
149 SELECT * FROM t;
150 f1
151 2
152 3
153 include/sync_slave_sql_with_master.inc
154 SELECT * FROM t;
155 f1
156 2
157 3
158 [connection master]
159 DROP TABLE t;
160 # Test case 4:
161 include/rpl_reset.inc
162 [connection master]
163 CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
164 Warnings:
165 Warning 1681 Integer display width is deprecated and will be removed in a future release.
166 CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
167 Warnings:
168 Warning 1681 Integer display width is deprecated and will be removed in a future release.
169 CREATE TRIGGER t_insert_trig BEFORE INSERT ON t
170 FOR EACH ROW
171 BEGIN
172 SAVEPOINT savepoint_1;
173 INSERT INTO t1 VALUES (5);
174 END |
175 INSERT INTO t VALUES (2), (3);
176 INSERT INTO t1 VALUES (30);
177 include/show_binlog_events.inc
178 Log_name Pos Event_type Server_id End_log_pos Info
179 master-bin.000001 # Query # # use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
180 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
181 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER t_insert_trig BEFORE INSERT ON t
182 FOR EACH ROW
183 BEGIN
184 SAVEPOINT savepoint_1;
185 INSERT INTO t1 VALUES (5);
186 END
187 master-bin.000001 # Query # # BEGIN
188 master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (2), (3)
189 master-bin.000001 # Xid # # COMMIT /* XID */
190 master-bin.000001 # Query # # BEGIN
191 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (30)
192 master-bin.000001 # Xid # # COMMIT /* XID */
193 SELECT * FROM t;
194 f1
195 2
196 3
197 SELECT * FROM t1;
198 f1
199 5
200 5
201 30
202 include/sync_slave_sql_with_master.inc
203 SELECT * FROM t;
204 f1
205 2
206 3
207 SELECT * FROM t1;
208 f1
209 5
210 5
211 30
212 [connection master]
213 DROP TABLE t;
214 DROP TABLE t1;
215 # Test case 5:
216 include/rpl_reset.inc
217 [connection master]
218 CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
219 Warnings:
220 Warning 1681 Integer display width is deprecated and will be removed in a future release.
221 CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
222 Warnings:
223 Warning 1681 Integer display width is deprecated and will be removed in a future release.
224 CREATE TRIGGER t_insert_trig BEFORE INSERT ON t
225 FOR EACH ROW
226 BEGIN
227 SAVEPOINT savepoint_1;
228 END |
229 INSERT INTO t VALUES (2), (3);
230 INSERT INTO t1 VALUES (30);
231 include/show_binlog_events.inc
232 Log_name Pos Event_type Server_id End_log_pos Info
233 master-bin.000001 # Query # # use `test`; CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
234 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB
235 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` TRIGGER t_insert_trig BEFORE INSERT ON t
236 FOR EACH ROW
237 BEGIN
238 SAVEPOINT savepoint_1;
239 END
240 master-bin.000001 # Query # # BEGIN
241 master-bin.000001 # Query # # use `test`; INSERT INTO t VALUES (2), (3)
242 master-bin.000001 # Xid # # COMMIT /* XID */
243 master-bin.000001 # Query # # BEGIN
244 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (30)
245 master-bin.000001 # Xid # # COMMIT /* XID */
246 SELECT * FROM t;
247 f1
248 2
249 3
250 SELECT * FROM t1;
251 f1
252 30
253 include/sync_slave_sql_with_master.inc
254 SELECT * FROM t;
255 f1
256 2
257 3
258 SELECT * FROM t1;
259 f1
260 30
261 [connection master]
262 DROP TABLE t;
263 DROP TABLE t1;
264 # Test case 6:
265 include/rpl_reset.inc
266 [connection master]
267 CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB;
268 CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB;
269 CREATE FUNCTION f1() RETURNS INT
270 BEGIN
271 SAVEPOINT event_logging_2;
272 INSERT INTO t1 VALUES (1);
273 ROLLBACK TO event_logging_2;
274 RETURN 0;
275 END|
276 BEGIN;
277 INSERT INTO t2 VALUES (1), (f1()), (2), (4);
278 COMMIT;
279 INSERT INTO t2 VALUES (10);
280 include/show_binlog_events.inc
281 Log_name Pos Event_type Server_id End_log_pos Info
282 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB
283 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB
284 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int
285 BEGIN
286 SAVEPOINT event_logging_2;
287 INSERT INTO t1 VALUES (1);
288 ROLLBACK TO event_logging_2;
289 RETURN 0;
290 END
291 master-bin.000001 # Query # # BEGIN
292 master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (1), (f1()), (2), (4)
293 master-bin.000001 # Xid # # COMMIT /* XID */
294 master-bin.000001 # Query # # BEGIN
295 master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (10)
296 master-bin.000001 # Xid # # COMMIT /* XID */
297 [connection master]
298 SELECT * FROM t2;
299 f1
300 1
301 0
302 2
303 4
304 10
305 SELECT * FROM t1;
306 f1
307 include/sync_slave_sql_with_master.inc
308 SELECT * FROM t2;
309 f1
310 1
311 0
312 2
313 4
314 10
315 SELECT * FROM t1;
316 f1
317 [connection master]
318 DROP TABLE t1;
319 DROP TABLE t2;
320 DROP FUNCTION f1;
321 # Test case 7:
322 include/rpl_reset.inc
323 [connection master]
324 CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB;
325 CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB;
326 CREATE FUNCTION f1() RETURNS INT
327 BEGIN
328 SAVEPOINT event_logging_2;
329 INSERT INTO t1 VALUES (1);
330 RETURN 0;
331 END|
332 BEGIN;
333 INSERT INTO t2 VALUES (1), (f1()), (2), (4);
334 COMMIT;
335 INSERT INTO t2 VALUES (10);
336 include/show_binlog_events.inc
337 Log_name Pos Event_type Server_id End_log_pos Info
338 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB
339 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB
340 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int
341 BEGIN
342 SAVEPOINT event_logging_2;
343 INSERT INTO t1 VALUES (1);
344 RETURN 0;
345 END
346 master-bin.000001 # Query # # BEGIN
347 master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (1), (f1()), (2), (4)
348 master-bin.000001 # Xid # # COMMIT /* XID */
349 master-bin.000001 # Query # # BEGIN
350 master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (10)
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 1
363 include/sync_slave_sql_with_master.inc
364 SELECT * FROM t2;
365 f1
366 1
367 0
368 2
369 4
370 10
371 SELECT * FROM t1;
372 f1
373 1
374 [connection master]
375 DROP TABLE t1;
376 DROP TABLE t2;
377 DROP FUNCTION f1;
378 # Test case 8:
379 include/rpl_reset.inc
380 [connection master]
381 CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB;
382 CREATE FUNCTION f1() RETURNS INT
383 BEGIN
384 SAVEPOINT event_logging_2;
385 RETURN 0;
386 END|
387 BEGIN;
388 INSERT INTO t1 VALUES (1), (f1()), (2), (4);
389 COMMIT;
390 INSERT INTO t1 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 DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int
395 BEGIN
396 SAVEPOINT event_logging_2;
397 RETURN 0;
398 END
399 master-bin.000001 # Query # # BEGIN
400 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1), (f1()), (2), (4)
401 master-bin.000001 # Xid # # COMMIT /* XID */
402 master-bin.000001 # Query # # BEGIN
403 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (10)
404 master-bin.000001 # Xid # # COMMIT /* XID */
405 [connection master]
406 SELECT * FROM t1;
407 f1
408 1
409 0
410 2
411 4
412 10
413 include/sync_slave_sql_with_master.inc
414 SELECT * FROM t1;
415 f1
416 1
417 0
418 2
419 4
420 10
421 [connection master]
422 DROP TABLE t1;
423 DROP FUNCTION f1;
424 include/rpl_end.inc