Debian Codegraph @

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

raw
1 ###############################################################################
2 # Bug#76727: SLAVE ASSERTION IN UNPACK_ROW WITH ROLLBACK TO
3 # SAVEPOINT IN ERROR HANDLER
4 #
5 # Problem:
6 # ========
7 # "SAVEPOINT", "ROLLBACK TO savepoint" wipe out table map on slave during
8 # execution binary log events. For trigger the map is written to binary log once
9 # for all trigger body and if trigger contains "SAVEPOINT" or
10 # "ROLLBACK TO savepoint" statements any trigger's events after these
11 # statements will not have table map. This results in an assert on slave.
12 #
13 # Test:
14 # =====
15 # Test case 1:
16 # Create a trigger with exception handler which rolls back to a savepoint.
17 # Test proves that there will not be any assert during execution of rolling
18 # back to savepoint.
19 #
20 # Test case 2:
21 # Create a trigger which calls a procedure which in turn calls an exception
22 # handler which rolls back to a savepoint. Prove that it doesn't cause any
23 # assertion during execution.
24 #
25 # Test case 3:
26 # Create a simple trigger which does SAVEPOINT and ROLLBACK TO SAVEPOINT
27 # and doesn't follow with any other DML statement. Prove that it doesn't cause
28 # any assertion during execution.
29 #
30 # Test case 4:
31 # Create a trigger with SAVEPOINT and follows with a DML without ROLLBACK TO
32 # savepoint. Ensure that data is replicated properly.
33 #
34 # Test case 5:
35 # Create a trigger with SAVEPOINT and it does nothing. Do few DMLs following
36 # the trigger ensure that the data is replicated properly
37 #
38 # Test case 6:
39 # Create a stored function which does SAVEPOINT and ROLLBACK TO
40 # SAVEPOINT. Do few inserts following the stored function call and ensure that
41 # no assert is generated on slave and all the rows are replicated to slave.
42 #
43 # Test case 7:
44 # Create a stored function which creates a SAVEPOINT alone and follows with
45 # DMLs without ROLLBACK TO savepoint. Ensure that data is replicated properly.
46 #
47 # Test case 8:
48 # Create a stored function which has SAVEPOINT inside it and does noting. It
49 # should follow with other DMLs. Ensure that data is replicated properly.
50 ###############################################################################
51 --source include/have_binlog_format_row.inc
52 --source include/have_innodb.inc
53 --source include/master-slave.inc
54
55 --echo #Test case 1:
56 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB;
57 CREATE TABLE t2 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB;
58 CREATE TABLE t3 (f1 INTEGER PRIMARY KEY) ENGINE=INNODB;
59 DELIMITER |;
60
61 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW
62 BEGIN
63 DECLARE EXIT HANDLER FOR SQLEXCEPTION
64 BEGIN
65 ROLLBACK TO event_logging_1;
66 INSERT t3 VALUES (1);
67 END;
68 SAVEPOINT event_logging_1;
69 INSERT INTO t2 VALUES (1);
70 RELEASE SAVEPOINT event_logging_1;
71 END|
72 DELIMITER ;|
73
74 INSERT INTO t2 VALUES (1);
75 INSERT INTO t1 VALUES (1);
76
77 --source include/show_binlog_events.inc
78
79 --sync_slave_with_master
80
81 connection master;
82
83 DROP TRIGGER tr1;
84 DELETE FROM t1;
85 DELETE FROM t2;
86 DELETE FROM t3;
87
88 --echo # Test case 2:
89
90 DELIMITER |;
91
92 CREATE PROCEDURE p1()
93 BEGIN
94 DECLARE EXIT HANDLER FOR SQLEXCEPTION
95 BEGIN
96 ROLLBACK TO event_logging_2;
97 INSERT t3 VALUES (3);
98 END;
99 SAVEPOINT event_logging_2;
100 INSERT INTO t2 VALUES (1);
101 RELEASE SAVEPOINT event_logging_2;
102 END|
103
104 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CALL p1()|
105
106 DELIMITER ;|
107
108 INSERT INTO t2 VALUES (1);
109 INSERT INTO t1 VALUES (1);
110
111 --source include/show_binlog_events.inc
112
113 --sync_slave_with_master
114
115 connection master;
116
117 DROP TABLE t1;
118 DROP TABLE t2;
119 DROP TABLE t3;
120
121 DROP PROCEDURE p1;
122
123 --echo # Test case 3:
124 --source include/rpl_reset.inc
125 connection master;
126
127 CREATE TABLE t (f1 int(10) unsigned NOT NULL, PRIMARY KEY (f1)) ENGINE=InnoDB;
128
129 --delimiter |
130 CREATE TRIGGER t_insert_trig AFTER INSERT ON t FOR EACH ROW
131 BEGIN
132 SAVEPOINT savepoint_1;
133 ROLLBACK TO savepoint_1;
134 END |
135 --delimiter ;
136
137 INSERT INTO t VALUES (2);
138 INSERT INTO t VALUES (3);
139
140 --source include/show_binlog_events.inc
141
142 SELECT * FROM t;
143
144 --source include/sync_slave_sql_with_master.inc
145
146 SELECT * FROM t;
147
148 connection master;
149 DROP TABLE t;
150
151 --echo # Test case 4:
152 --source include/rpl_reset.inc
153 connection master;
154 CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
155 CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
156
157 --delimiter |
158 CREATE TRIGGER t_insert_trig BEFORE INSERT ON t FOR EACH ROW
159 BEGIN
160 SAVEPOINT savepoint_1;
161 INSERT INTO t1 VALUES (5);
162 END |
163 --delimiter ;
164
165 INSERT INTO t VALUES (2), (3);
166 INSERT INTO t1 VALUES (30);
167 --source include/show_binlog_events.inc
168
169 SELECT * FROM t;
170 SELECT * FROM t1;
171 --source include/sync_slave_sql_with_master.inc
172
173 SELECT * FROM t;
174 SELECT * FROM t1;
175
176 connection master;
177 DROP TABLE t;
178 DROP TABLE t1;
179
180 --echo # Test case 5:
181 --source include/rpl_reset.inc
182 connection master;
183 CREATE TABLE t (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
184 CREATE TABLE t1 (f1 int(10) unsigned NOT NULL) ENGINE=InnoDB;
185
186 --delimiter |
187 CREATE TRIGGER t_insert_trig BEFORE INSERT ON t
188 FOR EACH ROW
189 BEGIN
190
191 SAVEPOINT savepoint_1;
192 END |
193
194 --delimiter ;
195
196 INSERT INTO t VALUES (2), (3);
197 INSERT INTO t1 VALUES (30);
198 --source include/show_binlog_events.inc
199
200 SELECT * FROM t;
201 SELECT * FROM t1;
202 --source include/sync_slave_sql_with_master.inc
203
204 SELECT * FROM t;
205 SELECT * FROM t1;
206
207 connection master;
208 DROP TABLE t;
209 DROP TABLE t1;
210
211 --echo # Test case 6:
212 --source include/rpl_reset.inc
213 connection master;
214 CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB;
215 CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB;
216
217 --delimiter |
218
219 CREATE FUNCTION f1() RETURNS INT
220 BEGIN
221 SAVEPOINT event_logging_2;
222 INSERT INTO t1 VALUES (1);
223 ROLLBACK TO event_logging_2;
224 RETURN 0;
225 END|
226
227 --delimiter ;
228
229 BEGIN;
230 INSERT INTO t2 VALUES (1), (f1()), (2), (4);
231 COMMIT;
232 INSERT INTO t2 VALUES (10);
233 --source include/show_binlog_events.inc
234
235 connection master;
236 SELECT * FROM t2;
237 SELECT * FROM t1;
238 --source include/sync_slave_sql_with_master.inc
239 SELECT * FROM t2;
240 SELECT * FROM t1;
241
242 connection master;
243 DROP TABLE t1;
244 DROP TABLE t2;
245 DROP FUNCTION f1;
246
247 --echo # Test case 7:
248 --source include/rpl_reset.inc
249 connection master;
250 CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB;
251 CREATE TABLE t2 (f1 INTEGER ) ENGINE=INNODB;
252
253 --delimiter |
254
255 CREATE FUNCTION f1() RETURNS INT
256 BEGIN
257 SAVEPOINT event_logging_2;
258 INSERT INTO t1 VALUES (1);
259 RETURN 0;
260 END|
261
262 --delimiter ;
263
264 BEGIN;
265 INSERT INTO t2 VALUES (1), (f1()), (2), (4);
266 COMMIT;
267 INSERT INTO t2 VALUES (10);
268 --source include/show_binlog_events.inc
269
270 connection master;
271 SELECT * FROM t2;
272 SELECT * FROM t1;
273 --source include/sync_slave_sql_with_master.inc
274 SELECT * FROM t2;
275 SELECT * FROM t1;
276
277 connection master;
278 DROP TABLE t1;
279 DROP TABLE t2;
280 DROP FUNCTION f1;
281
282 --echo # Test case 8:
283 --source include/rpl_reset.inc
284 connection master;
285 CREATE TABLE t1 (f1 INTEGER ) ENGINE=INNODB;
286
287 --delimiter |
288
289 CREATE FUNCTION f1() RETURNS INT
290 BEGIN
291 SAVEPOINT event_logging_2;
292 RETURN 0;
293 END|
294
295 --delimiter ;
296
297 BEGIN;
298 INSERT INTO t1 VALUES (1), (f1()), (2), (4);
299 COMMIT;
300 INSERT INTO t1 VALUES (10);
301 --source include/show_binlog_events.inc
302
303 connection master;
304 SELECT * FROM t1;
305 --source include/sync_slave_sql_with_master.inc
306 SELECT * FROM t1;
307
308 connection master;
309 DROP TABLE t1;
310 DROP FUNCTION f1;
311
312 --source include/rpl_end.inc