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