Debian Codegraph @

mysql-test/suite/perfschema/t/transaction_nested_events.test

raw
1 ########## suite/perfschema/t/transaction_nested_events.test #########
2 # #
3 # This test checks the following #
4 # 1) nested events #
5 # 2) various scenarios of stored program & transaction combinations #
6 # #
7 # #
8 ######################################################################
9
10 --source include/have_myisam.inc
11 --source include/force_myisam_default.inc
12
13 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
14 --source include/no_protocol.inc
15 --disable_query_log
16 --source ../include/transaction_setup.inc
17 --enable_query_log
18
19 --echo
20 --echo #========================================================================
21 --echo # STEP 1 - SETUP
22 --echo #========================================================================
23
24 --echo
25 --echo ## Setup control thread
26 --echo
27 --connection default
28 SET SESSION AUTOCOMMIT= 1;
29 --disable_warnings
30 USE test;
31 DROP DATABASE IF EXISTS db;
32 --enable_warnings
33 --echo
34 --echo ## Create test database, test tables, one transactional and one non-transactional
35 CREATE DATABASE db;
36 CREATE TABLE db.t1 (s1 int, s2 varchar(64)) ENGINE=INNODB;
37 CREATE TABLE db.nt1 (s1 int, s2 varchar(64)) ENGINE=MYISAM;
38 CREATE PROCEDURE db.tp_update() UPDATE t1 SET s1 = s1 + 1;
39 CREATE PROCEDURE db.tp_start() START TRANSACTION;
40 CREATE PROCEDURE db.tp_rollback() ROLLBACK;
41 CREATE PROCEDURE db.tp_commit() COMMIT;
42 DELIMITER |;
43 CREATE FUNCTION db.fn_add(x INT, y INT) RETURNS INT
44 BEGIN
45 INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y");
46 RETURN x+y;
47 END |
48 DELIMITER ;|
49
50
51 --echo
52 --echo ## Warm-up data-dictionary cache.
53 --disable_result_log
54 --disable_query_log
55 SELECT * FROM db.t1;
56 SELECT * FROM db.nt1;
57 SHOW CREATE PROCEDURE db.tp_update;
58 SHOW CREATE PROCEDURE db.tp_start;
59 SHOW CREATE PROCEDURE db.tp_rollback;
60 SHOW CREATE PROCEDURE db.tp_commit;
61 SHOW CREATE FUNCTION db.fn_add;
62 --enable_query_log
63 --enable_result_log
64
65 --echo
66 --echo ## Setup connection 1
67 connect(con1, localhost, root,,);
68 USE db;
69 SET SESSION AUTOCOMMIT = 1;
70 eval $get_thread_id;
71 let $con1_thread_id= `SELECT @my_thread_id`;
72
73 --connection default
74 --disable_query_log
75 eval SET @con1_thread_id= $con1_thread_id;
76 --enable_query_log
77
78 --echo
79 --echo ## Disable events from the control (default) connection
80 UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID();
81
82 # Use this to select events from all threads
83 eval SET @all_threads= 0;
84
85 --echo
86 --echo ## Enable only transaction and statement instruments
87 UPDATE performance_schema.setup_instruments SET enabled='NO', timed='NO';
88 UPDATE performance_schema.setup_instruments SET enabled='YES'
89 WHERE name LIKE ('statement/%') OR name = 'transaction';
90
91 --echo
92 --echo ## Clear statement and transaction history
93 CALL test.clear_history();
94
95 --echo
96 --echo #========================================================================
97 --echo # STEP 2 - BASIC TRANSACTION
98 --echo #========================================================================
99 --echo #
100 --echo # STEP 2.1 - IMPLICIT
101 --echo #
102 --connection con1
103 INSERT INTO t1 VALUES (210, "INSERT 210");
104 INSERT INTO t1 VALUES (211, "INSERT 211");
105 INSERT INTO t1 VALUES (212, "INSERT 212");
106 UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212;
107
108 ## Verify and reset
109 --source ../include/transaction_nested_events_verifier.inc
110
111 --echo #
112 --echo # STEP 2.2 - EXPLICIT
113 --echo #
114 --connection con1
115 START TRANSACTION;
116 INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221");
117 UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221;
118 COMMIT;
119
120 ## Verify and reset
121 --source ../include/transaction_nested_events_verifier.inc
122
123 --echo #========================================================================
124 --echo # STEP 3 - TRANSACTIONS AND STORED PROCEDURES
125 --echo #========================================================================
126 --echo #
127 --echo # STEP 3.1 - STORED PROCEDURE STARTED WITHIN TRANSACTION
128 --echo #
129 --connection con1
130 --echo
131 START TRANSACTION;
132 INSERT INTO t1 VALUES (310, "INSERT 310");
133 INSERT INTO t1 VALUES (311, "INSERT 311");
134 INSERT INTO t1 VALUES (312, "INSERT 312");
135 INSERT INTO t1 VALUES (313, "INSERT 313");
136 CALL tp_update();
137 COMMIT;
138
139 ## Verify and reset
140 --source ../include/transaction_nested_events_verifier.inc
141
142 --echo #
143 --echo # STEP 3.2 - TRANSACTION STARTED WITHIN STORED PROCEDURE
144 --echo #
145 --connection con1
146 --echo
147 CALL tp_start();
148 INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321");
149 INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323");
150 UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320;
151 --echo
152 SELECT * FROM t1 ORDER BY s1;
153 COMMIT;
154
155 ## Verify and reset
156 --source ../include/transaction_nested_events_verifier.inc
157
158 --echo #
159 --echo # STEP 3.3 - TRANSACTION ENDED WITHIN STORED PROCEDURE
160 --echo #
161 --connection con1
162
163 --echo
164 --echo ## COMMIT within stored procedure
165 START TRANSACTION;
166 INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331");
167 INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333");
168 DELETE FROM t1 WHERE s1 > 331;
169 CALL tp_commit();
170 --echo
171 SELECT * FROM t1 ORDER BY s1;
172
173 --echo
174 --echo ## ROLLBACK within stored procedure
175 START TRANSACTION;
176 UPDATE t1 SET s1 = s1*2 WHERE s1 > 331;
177 CALL tp_rollback();
178 --echo
179 SELECT * FROM t1 ORDER BY s1;
180
181 ## Verify and reset
182 --source ../include/transaction_nested_events_verifier.inc
183
184 --echo #========================================================================
185 --echo # STEP 4 - TRANSACTIONS AND STORED FUNCTIONS
186 --echo #========================================================================
187 --echo
188 --echo #
189 --echo # STEP 4.1 - FUNCTION WITHIN A TRANSACTION
190 --echo #
191 --connection con1
192 --echo
193 --echo ## Clear history
194 --connection default
195 CALL test.clear_history();
196 --connection con1
197
198 --echo
199 START TRANSACTION;
200 INSERT INTO t1 VALUES (410, "INSERT 410");
201 INSERT INTO t1 VALUES (411, "INSERT 411");
202 INSERT INTO t1 VALUES (412, "INSERT 412");
203 DELETE FROM t1 WHERE s1 > 410;
204 --echo
205 SELECT * FROM t1 ORDER BY s1;
206 --echo
207 SELECT fn_add(413, 414);
208 COMMIT;
209 --echo
210 SELECT * FROM t1 ORDER BY s1;
211
212 ## Verify and reset
213 --source ../include/transaction_nested_events_verifier.inc
214
215 --connection con1
216 --echo
217 --echo ## Again, but this time with a rollback
218 --echo
219 START TRANSACTION;
220 SELECT fn_add(415, 416);
221 --echo
222 ROLLBACK;
223 --echo
224 SELECT * FROM t1 ORDER BY s1;
225
226 ## Verify and reset
227 --source ../include/transaction_nested_events_verifier.inc
228
229 --echo #
230 --echo # STEP 4.2 - TRANSACTION CANNOT BE STARTED OR ENDED WITHIN FUNCTION
231 --echo #
232 --connection con1
233 DELIMITER |;
234 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
235 CREATE FUNCTION fn_err1() RETURNS VARCHAR(10) BEGIN START TRANSACTION ; RETURN invalid ; END|
236 DELIMITER ;|
237
238 --echo
239 --echo ## Expect 0 transactions
240 --connection default
241 SELECT COUNT(*) FROM performance_schema.events_transactions_history;
242 --connection con1
243
244 --echo
245 --echo ## Expect stored function does not exist
246 --echo
247 --error ER_SP_DOES_NOT_EXIST
248 SELECT fn_err1();
249
250 --echo
251 --echo ## Expect 0 transactions
252 --connection default
253 SELECT COUNT(*) FROM performance_schema.events_transactions_history;
254 --connection con1
255
256 --echo
257 DELIMITER |;
258 --error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
259 CREATE FUNCTION fn_err2() RETURNS VARCHAR(10) BEGIN COMMIT; RETURN invalid ; END|
260 DELIMITER ;|
261
262 --echo
263 --echo ## Expect stored function does not exist
264 --echo
265 START TRANSACTION;
266 DELETE FROM t1 WHERE s1 > 320;
267 --error ER_SP_DOES_NOT_EXIST
268 SELECT fn_err2();
269
270 --echo
271 --echo ## Expect 0 transactions
272 --connection default
273 SELECT COUNT(*) FROM performance_schema.events_transactions_history;
274
275 --echo
276 --echo ## Clear transaction and statement tables
277 CALL test.clear_history();
278
279 --echo #========================================================================
280 --echo # STEP 5 - TRANSACTIONS AND TRIGGERS
281 --echo #========================================================================
282 --echo
283 --echo #
284 --echo # STEP 5.1 - FORCE STATEMENT ROLLBACK FROM TRIGGER
285 --echo #
286 --connection con1
287 --echo ## Create a trigger to force statement rollback
288 --echo
289 DELIMITER |;
290 CREATE TRIGGER trigger_before_update BEFORE UPDATE ON t1
291 FOR EACH ROW
292 BEGIN
293 IF OLD.s1 >= 505 THEN
294 SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR";
295 END IF;
296 END;|
297 DELIMITER ;|
298
299 --echo
300 --echo ## Warm-up data-dictionary cache.
301 --disable_result_log
302 --disable_query_log
303 SHOW CREATE TABLE t1;
304 --enable_query_log
305 --enable_result_log
306
307 --echo
308 --echo ## Clear history
309 --connection default
310 CALL test.clear_history();
311 --connection con1
312
313 --echo
314 --echo ## Insert multiple rows, then update. Trigger will force rollback the
315 --echo ## UPDATE statement, but the transaction should not roll back.
316 --echo
317 START TRANSACTION;
318 INSERT INTO t1 VALUES (500, "INSERT 500");
319 INSERT INTO t1 VALUES (501, "INSERT 501");
320 INSERT INTO t1 VALUES (502, "INSERT 502");
321 INSERT INTO t1 VALUES (503, "INSERT 503");
322 INSERT INTO t1 VALUES (504, "INSERT 504");
323 INSERT INTO t1 VALUES (505, "INSERT 505");
324 --echo
325 SELECT * FROM t1 ORDER BY s1;
326 --echo
327 --echo ## Expect error when UPDATE hits record 505
328 --echo
329 --error ER_SIGNAL_EXCEPTION
330 UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500;
331 --echo
332 --echo ## Verify that INSERT succeeded, UPDATE failed and transaction did not rollback
333 --echo
334 SELECT * FROM t1 ORDER BY s1;
335 COMMIT;
336 --echo
337 DROP TRIGGER trigger_before_update;
338
339 ## Verify and reset
340 --source ../include/transaction_nested_events_verifier.inc
341
342 --echo # TODO: Detect statement events from scheduled event
343 --disable_testcase BUG#0000
344 --echo #========================================================================
345 --echo # STEP 6 - SCHEDULED EVENTS
346 --echo #========================================================================
347 --connection con1
348
349 # Check that the event_scheduler is really running
350 #--source include/running_event_scheduler.inc
351
352 --echo
353 --echo ## Create a one-time event that will insert, update, commit and rollback.
354 --echo
355 DELIMITER |;
356 CREATE EVENT trx_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 SECOND DO
357 BEGIN
358 START TRANSACTION;
359 INSERT INTO t1 VALUES (600, "INSERT 600");
360 INSERT INTO t1 VALUES (601, "INSERT 601");
361 INSERT INTO t1 VALUES (602, "INSERT 602");
362 COMMIT;
363 START TRANSACTION;
364 UPDATE t1 SET s1 = s1 + 1, s2 = 'THIS SHOULD NOT BE HERE';
365 ROLLBACK;
366 START TRANSACTION;
367 INSERT INTO t1 VALUES (699, "INSERT 699");
368 COMMIT;
369 END;|
370 DELIMITER ;|
371
372 --echo
373 --echo ## Clear history
374 --connection default
375 CALL test.clear_history();
376 --connection con1
377 --echo
378 --echo ## Wait a few seconds for scheduled event to execute and finish.
379 # Check based on the expected content in t1.
380 let $wait_condition= SELECT COUNT(*) = 4 FROM t1;
381 --source include/wait_condition.inc
382 --echo
383 --echo ## Confirm that the scheduled event completed and the content of t1 is right.
384 --echo
385 SELECT * FROM t1 ORDER BY s1;
386 --echo
387 --echo ## Verify and reset
388 --echo
389 eval SET @all_threads= 1;
390 --source ../include/transaction_nested_events_verifier.inc
391 eval SET @all_threads= 0;
392
393 --enable_testcase
394
395 --echo #=======================================================================
396 --echo # Cleanup
397 --echo #=======================================================================
398 --connection con1
399 --disconnect con1
400 --source include/wait_until_disconnected.inc
401 --connection default
402 DROP DATABASE db;
403 UPDATE performance_schema.setup_instruments SET enabled='YES', timed='YES';
404
405 --source ../include/transaction_cleanup.inc
406
407
408