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