Debian Codegraph @

mysql-test/suite/galera/t/galera_sp_bf_abort.test

raw
1 #
2 # Test cases for stored procedure BF aborts.
3 #
4
5 --source include/galera_cluster.inc
6 --source include/galera_have_debug_sync.inc
7
8 --connection node_1
9
10 CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1));
11
12 # Control connection for Galera sync point management
13 --connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1
14 --connection node_1a
15 SET SESSION wsrep_sync_wait = 0;
16
17 --connection node_1
18 #
19 # Case 1a: Procedure does and UPDATE which will suffer BF abort
20 # but there is no actual conflict and non-conflicting INSERT.
21 # The expected outcome is that both UPDATE and INSERT will succedd
22 # and no errors are reported to the client, wsrep_local_replays is
23 # incremented by one.
24 #
25 DELIMITER |;
26 CREATE PROCEDURE proc_update_insert()
27 BEGIN
28 UPDATE t1 SET f2 = 'b';
29 INSERT INTO t1 VALUES (4, 'd');
30 END|
31 DELIMITER ;|
32
33 INSERT INTO t1 VALUES (1, 'a'), (3, 'a');
34 SET SESSION wsrep_sync_wait = 0;
35 --let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
36 --let $galera_sp_bf_abort_proc = proc_update_insert
37 --let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c')
38 --source galera_sp_bf_abort.inc
39 --connection node_1
40 --reap
41 SET SESSION wsrep_sync_wait = default;
42 SELECT * FROM t1;
43 --let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
44 --disable_query_log
45 --eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays;
46 --enable_query_log
47
48 DELETE FROM t1;
49
50 --connection node_1
51 #
52 # Case 1b: Procedure does and UPDATE which will suffer BF abort
53 # but there is no actual conflict and non-conflicting INSERT.
54 # An EXIT HANDLER is declared for the procedure.
55 # The expected outcome is that both UPDATE and INSERT will succedd
56 # and no errors are reported to the client, wsrep_local_replays is
57 # incremented by one.
58 #
59 DELIMITER |;
60 CREATE PROCEDURE proc_update_insert_with_exit_handler()
61 BEGIN
62 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
63 UPDATE t1 SET f2 = 'b';
64 INSERT INTO t1 VALUES (4, 'd');
65 END|
66 DELIMITER ;|
67
68 INSERT INTO t1 VALUES (1, 'a'), (3, 'a');
69 SET SESSION wsrep_sync_wait = 0;
70 --let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
71 --let $galera_sp_bf_abort_proc = proc_update_insert_with_exit_handler
72 --let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c')
73 --source galera_sp_bf_abort.inc
74 --connection node_1
75 --reap
76 SET SESSION wsrep_sync_wait = default;
77 SELECT * FROM t1;
78 --let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
79 --disable_query_log
80 --eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays;
81 --enable_query_log
82
83 DELETE FROM t1;
84
85 --connection node_1
86 #
87 # Case 1c: Procedure does and UPDATE which will suffer BF abort
88 # but there is no actual conflict and non-conflicting INSERT.
89 # A CONTINUE HANDLER is declared for the procedure.
90 # The expected outcome is that both UPDATE and INSERT will succedd
91 # and no errors are reported to the client, wsrep_local_replays is
92 # incremented by one.
93 #
94 DELIMITER |;
95 CREATE PROCEDURE proc_update_insert_with_continue_handler()
96 BEGIN
97
98 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
99 UPDATE t1 SET f2 = 'b';
100 INSERT INTO t1 VALUES (4, 'd');
101 END|
102 DELIMITER ;|
103
104 INSERT INTO t1 VALUES (1, 'a'), (3, 'a');
105 SET SESSION wsrep_sync_wait = 0;
106 --let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
107 --let $galera_sp_bf_abort_proc = proc_update_insert_with_continue_handler
108 --let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c')
109 --source galera_sp_bf_abort.inc
110 --connection node_1
111 --reap
112 SET SESSION wsrep_sync_wait = default;
113 SELECT * FROM t1;
114 --let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
115 --disable_query_log
116 --eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays;
117 --enable_query_log
118
119 DELETE FROM t1;
120
121 --connection node_1
122 #
123 # Case 2a: UPDATE and INSERT are run inside a transaction and the transaction
124 # will be BF aborted on COMMIT. The expected outcome is that the transaction
125 # succeeds and no errors are reported to the client, wsrep_local_replays
126 # is incremented by one.
127 #
128
129 DELIMITER |;
130 CREATE PROCEDURE proc_update_insert_transaction()
131 BEGIN
132 START TRANSACTION;
133 UPDATE t1 SET f2 = 'b';
134 INSERT INTO t1 VALUES (4, 'd');
135 COMMIT;
136 END|
137 DELIMITER ;|
138
139 INSERT INTO t1 VALUES (1, 'a'), (3, 'a');
140 --let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
141 --let $galera_sp_bf_abort_proc = proc_update_insert_transaction
142 --let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c')
143 SET SESSION wsrep_sync_wait = 0;
144 --source galera_sp_bf_abort.inc
145 --connection node_1
146 --reap
147 SET SESSION wsrep_sync_wait = default;
148 SELECT * FROM t1;
149 --let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
150 --disable_query_log
151 --eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays;
152 --enable_query_log
153
154 DELETE FROM t1;
155
156 --connection node_1
157 #
158 # Case 2b: UPDATE and INSERT are run inside a transaction and the transaction
159 # will be BF aborted on COMMIT. A CONTINUE HANDLER is declared for the
160 # procedure. The expected outcome is that the transaction
161 # succeeds and no errors are reported to the client, wsrep_local_replays
162 # is incremented by one.
163 #
164
165 DELIMITER |;
166 CREATE PROCEDURE proc_update_insert_transaction_with_continue_handler()
167 BEGIN
168 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
169 START TRANSACTION;
170 UPDATE t1 SET f2 = 'b';
171 INSERT INTO t1 VALUES (4, 'd');
172 COMMIT;
173 END|
174 DELIMITER ;|
175
176 INSERT INTO t1 VALUES (1, 'a'), (3, 'a');
177 --let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
178 --let $galera_sp_bf_abort_proc = proc_update_insert_transaction_with_continue_handler
179 --let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c')
180 SET SESSION wsrep_sync_wait = 0;
181 --source galera_sp_bf_abort.inc
182 --connection node_1
183 --reap
184 SET SESSION wsrep_sync_wait = default;
185 SELECT * FROM t1;
186 --let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
187 --disable_query_log
188 --eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays;
189 --enable_query_log
190
191 DELETE FROM t1;
192
193 --connection node_1
194 #
195 # Case 2c: UPDATE and INSERT are run inside a transaction and the transaction
196 # will be BF aborted on COMMIT. An EXIT HANDLE is declared for the procedure.
197 # The expected outcome is that the transaction succeeds and no errors are
198 # reported to the client, wsrep_local_replays is incremented by one.
199 #
200
201 DELIMITER |;
202 CREATE PROCEDURE proc_update_insert_transaction_with_exit_handler()
203 BEGIN
204 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
205 START TRANSACTION;
206 UPDATE t1 SET f2 = 'b';
207 INSERT INTO t1 VALUES (4, 'd');
208 COMMIT;
209 END|
210 DELIMITER ;|
211
212 INSERT INTO t1 VALUES (1, 'a'), (3, 'a');
213 --let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
214 --let $galera_sp_bf_abort_proc = proc_update_insert_transaction_with_exit_handler
215 --let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c')
216 SET SESSION wsrep_sync_wait = 0;
217 --source galera_sp_bf_abort.inc
218 --connection node_1
219 --reap
220 SET SESSION wsrep_sync_wait = default;
221 SELECT * FROM t1;
222 --let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
223 --disable_query_log
224 --eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays;
225 --enable_query_log
226
227 DELETE FROM t1;
228
229 --connection node_1
230
231 #
232 # Case 3a: Two INSERTs are run inside stored procedure, this time
233 # the first INSERT will have a BF abort and real conflict. The expected outcome
234 # is that the INSERT fails and an error is reported to the client.
235 # wsrep_local_replays is not incremented.
236 #
237 # Notice that the resulting error code may be both
238 # ER_DUP_ENTRY (procedure will exit with cert failure conflict state and
239 # will be) or ER_LOCK_DEADLOCK depending on timing.
240 #
241 DELIMITER |;
242 CREATE PROCEDURE proc_insert_insert_conflict()
243 BEGIN
244 INSERT INTO t1 VALUES (2, 'd');
245 INSERT INTO t1 VALUES (4, 'd');
246 END|
247 DELIMITER ;|
248
249 INSERT INTO t1 VALUES (1, 'a'), (3, 'a');
250 --let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
251 --let $galera_sp_bf_abort_proc = proc_insert_insert_conflict
252 --let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c')
253 SET SESSION wsrep_sync_wait = 0;
254 --source galera_sp_bf_abort.inc
255 --connection node_1
256 --error ER_DUP_ENTRY,ER_LOCK_DEADLOCK
257 --reap
258 SET SESSION wsrep_sync_wait = default;
259 SELECT * FROM t1;
260 --let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
261 --disable_query_log
262 --eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 0 AS wsrep_local_replays;
263 --enable_query_log
264
265 DELETE FROM t1;
266
267 --connection node_1
268
269 #
270 # Case 3b: Two INSERTs are run inside stored procedure, this time
271 # the first INSERT will have a BF abort and real conflict.
272 # An EXIT HANDLER is declared for the procedure. The expected outcome
273 # is that the INSERT fails and an error is reported to the client.
274 # wsrep_local_replays is not incremented.
275 #
276 DELIMITER |;
277 CREATE PROCEDURE proc_insert_insert_conflict_with_exit_handler()
278 BEGIN
279 DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT "Conflict exit handler";
280 INSERT INTO t1 VALUES (2, 'd');
281 INSERT INTO t1 VALUES (4, 'd');
282 END|
283 DELIMITER ;|
284
285 INSERT INTO t1 VALUES (1, 'a'), (3, 'a');
286 --let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
287 --let $galera_sp_bf_abort_proc = proc_insert_insert_conflict_with_exit_handler
288 --let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c')
289 SET SESSION wsrep_sync_wait = 0;
290 --source galera_sp_bf_abort.inc
291 --connection node_1
292 --reap
293 SET SESSION wsrep_sync_wait = default;
294 SELECT * FROM t1;
295 --let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
296 --disable_query_log
297 --eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 0 AS wsrep_local_replays;
298 --enable_query_log
299
300 DELETE FROM t1;
301
302 --connection node_1
303
304 #
305 # Case 3c: Two INSERTs are run inside stored procedure, this time
306 # the first INSERT will have a BF abort and real conflict.
307 # A CONTINUE HANDLER is declared for the procedure. The expected outcome
308 # is that the the first INSERT fails but the second is executed without
309 # errors. wsrep_local_replays is not incremented.
310 #
311 DELIMITER |;
312 CREATE PROCEDURE proc_insert_insert_conflict_with_continue_handler()
313 BEGIN
314 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT "Conflict continue handler";
315 INSERT INTO t1 VALUES (2, 'd');
316 INSERT INTO t1 VALUES (4, 'd');
317 END|
318 DELIMITER ;|
319
320 INSERT INTO t1 VALUES (1, 'a'), (3, 'a');
321 --let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
322 --let $galera_sp_bf_abort_proc = proc_insert_insert_conflict_with_continue_handler
323 --let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c')
324 SET SESSION wsrep_sync_wait = 0;
325 --source galera_sp_bf_abort.inc
326 --connection node_1
327 --reap
328 SET SESSION wsrep_sync_wait = default;
329 SELECT * FROM t1;
330 --let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'`
331 --disable_query_log
332 --eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 0 AS wsrep_local_replays;
333 --enable_query_log
334
335 DELETE FROM t1;
336
337
338 DROP PROCEDURE proc_update_insert;
339 DROP PROCEDURE proc_update_insert_with_continue_handler;
340 DROP PROCEDURE proc_update_insert_with_exit_handler;
341 DROP PROCEDURE proc_update_insert_transaction;
342 DROP PROCEDURE proc_update_insert_transaction_with_continue_handler;
343 DROP PROCEDURE proc_update_insert_transaction_with_exit_handler;
344 DROP PROCEDURE proc_insert_insert_conflict;
345 DROP PROCEDURE proc_insert_insert_conflict_with_exit_handler;
346 DROP PROCEDURE proc_insert_insert_conflict_with_continue_handler;
347 DROP TABLE t1;