Debian Codegraph @

mysql-test/suite/perfschema/r/transaction_nested_events.result

raw
1
2 #========================================================================
3 # STEP 1 - SETUP
4 #========================================================================
5
6 ## Setup control thread
7
8 SET SESSION AUTOCOMMIT= 1;
9 USE test;
10 DROP DATABASE IF EXISTS db;
11
12 ## Create test database, test tables, one transactional and one non-transactional
13 CREATE DATABASE db;
14 CREATE TABLE db.t1 (s1 int, s2 varchar(64)) ENGINE=INNODB;
15 CREATE TABLE db.nt1 (s1 int, s2 varchar(64)) ENGINE=MYISAM;
16 CREATE PROCEDURE db.tp_update() UPDATE t1 SET s1 = s1 + 1;
17 CREATE PROCEDURE db.tp_start() START TRANSACTION;
18 CREATE PROCEDURE db.tp_rollback() ROLLBACK;
19 CREATE PROCEDURE db.tp_commit() COMMIT;
20 CREATE FUNCTION db.fn_add(x INT, y INT) RETURNS INT
21 BEGIN
22 INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y");
23 RETURN x+y;
24 END |
25
26 ## Warm-up data-dictionary cache.
27
28 ## Setup connection 1
29 USE db;
30 SET SESSION AUTOCOMMIT = 1;
31 SELECT thread_id INTO @my_thread_id
32 FROM performance_schema.threads
33 WHERE processlist_id = connection_id();
34
35 ## Disable events from the control (default) connection
36 UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID();
37 SET @all_threads= 0;
38
39 ## Enable only transaction and statement instruments
40 UPDATE performance_schema.setup_instruments SET enabled='NO', timed='NO';
41 UPDATE performance_schema.setup_instruments SET enabled='YES'
42 WHERE name LIKE ('statement/%') OR name = 'transaction';
43
44 ## Clear statement and transaction history
45 CALL test.clear_history();
46
47 #========================================================================
48 # STEP 2 - BASIC TRANSACTION
49 #========================================================================
50 #
51 # STEP 2.1 - IMPLICIT
52 #
53 INSERT INTO t1 VALUES (210, "INSERT 210");
54 INSERT INTO t1 VALUES (211, "INSERT 211");
55 INSERT INTO t1 VALUES (212, "INSERT 212");
56 UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212;
57 #========================================================================
58 # Verify
59 #========================================================================
60 EVENTS_TRANSACTIONS_CURRENT
61
62 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
63 thread_id 8 8 transaction COMMITTED READ WRITE REPEATABLE READ YES 7 STATEMENT
64
65 EVENTS_TRANSACTIONS_HISTORY_LONG
66
67 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
68 thread_id 2 2 transaction COMMITTED READ WRITE REPEATABLE READ YES 1 STATEMENT
69 thread_id 4 4 transaction COMMITTED READ WRITE REPEATABLE READ YES 3 STATEMENT
70 thread_id 6 6 transaction COMMITTED READ WRITE REPEATABLE READ YES 5 STATEMENT
71 thread_id 8 8 transaction COMMITTED READ WRITE REPEATABLE READ YES 7 STATEMENT
72
73 EVENTS_STATEMENTS_HISTORY_LONG
74
75 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
76 thread_id 1 2 statement/sql/insert NULL NULL NULL 0 INSERT INTO t1 VALUES (210, "INSERT 210")
77 thread_id 3 4 statement/sql/insert NULL NULL NULL 0 INSERT INTO t1 VALUES (211, "INSERT 211")
78 thread_id 5 6 statement/sql/insert NULL NULL NULL 0 INSERT INTO t1 VALUES (212, "INSERT 212")
79 thread_id 7 8 statement/sql/update NULL NULL NULL 0 UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212
80
81 ## Combined statement and transaction event history ordered by event id
82
83 EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
84
85 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
86 thread_id 1 2 statement/sql/insert NULL NULL INSERT INTO t1 VALUES (210, "INSERT 210")
87 thread_id 2 2 transaction 1 STATEMENT <transaction started>
88 thread_id 3 4 statement/sql/insert NULL NULL INSERT INTO t1 VALUES (211, "INSERT 211")
89 thread_id 4 4 transaction 3 STATEMENT <transaction started>
90 thread_id 5 6 statement/sql/insert NULL NULL INSERT INTO t1 VALUES (212, "INSERT 212")
91 thread_id 6 6 transaction 5 STATEMENT <transaction started>
92 thread_id 7 8 statement/sql/update NULL NULL UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212
93 thread_id 8 8 transaction 7 STATEMENT <transaction started>
94
95 ## Clear statement and transaction history
96 CALL test.clear_history();
97 ## Reset db.t1
98 DELETE FROM db.t1;
99
100 #
101 # STEP 2.2 - EXPLICIT
102 #
103 START TRANSACTION;
104 INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221");
105 UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221;
106 COMMIT;
107 #========================================================================
108 # Verify
109 #========================================================================
110 EVENTS_TRANSACTIONS_CURRENT
111
112 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
113 thread_id 2 5 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
114
115 EVENTS_TRANSACTIONS_HISTORY_LONG
116
117 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
118 thread_id 2 5 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
119
120 EVENTS_STATEMENTS_HISTORY_LONG
121
122 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
123 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
124 thread_id 3 3 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221")
125 thread_id 4 4 statement/sql/update NULL 2 TRANSACTION 0 UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221
126 thread_id 5 5 statement/sql/commit NULL 2 TRANSACTION 0 COMMIT
127
128 ## Combined statement and transaction event history ordered by event id
129
130 EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
131
132 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
133 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
134 thread_id 2 5 transaction 1 STATEMENT <transaction started>
135 thread_id 3 3 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221")
136 thread_id 4 4 statement/sql/update 2 TRANSACTION UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221
137 thread_id 5 5 statement/sql/commit 2 TRANSACTION COMMIT
138
139 ## Clear statement and transaction history
140 CALL test.clear_history();
141 ## Reset db.t1
142 DELETE FROM db.t1;
143
144 #========================================================================
145 # STEP 3 - TRANSACTIONS AND STORED PROCEDURES
146 #========================================================================
147 #
148 # STEP 3.1 - STORED PROCEDURE STARTED WITHIN TRANSACTION
149 #
150
151 START TRANSACTION;
152 INSERT INTO t1 VALUES (310, "INSERT 310");
153 INSERT INTO t1 VALUES (311, "INSERT 311");
154 INSERT INTO t1 VALUES (312, "INSERT 312");
155 INSERT INTO t1 VALUES (313, "INSERT 313");
156 CALL tp_update();
157 COMMIT;
158 #========================================================================
159 # Verify
160 #========================================================================
161 EVENTS_TRANSACTIONS_CURRENT
162
163 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
164 thread_id 2 9 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
165
166 EVENTS_TRANSACTIONS_HISTORY_LONG
167
168 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
169 thread_id 2 9 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
170
171 EVENTS_STATEMENTS_HISTORY_LONG
172
173 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
174 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
175 thread_id 3 3 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (310, "INSERT 310")
176 thread_id 4 4 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (311, "INSERT 311")
177 thread_id 5 5 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (312, "INSERT 312")
178 thread_id 6 6 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (313, "INSERT 313")
179 thread_id 7 8 statement/sql/call_procedure NULL 2 TRANSACTION 0 CALL tp_update()
180 thread_id 8 8 statement/sp/stmt tp_update 7 STATEMENT 1 UPDATE t1 SET s1 = s1 + 1
181 thread_id 9 9 statement/sql/commit NULL 2 TRANSACTION 0 COMMIT
182
183 ## Combined statement and transaction event history ordered by event id
184
185 EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
186
187 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
188 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
189 thread_id 2 9 transaction 1 STATEMENT <transaction started>
190 thread_id 3 3 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (310, "INSERT 310")
191 thread_id 4 4 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (311, "INSERT 311")
192 thread_id 5 5 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (312, "INSERT 312")
193 thread_id 6 6 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (313, "INSERT 313")
194 thread_id 7 8 statement/sql/call_proced 2 TRANSACTION CALL tp_update()
195 thread_id 8 8 statement/sp/stmt 7 STATEMENT UPDATE t1 SET s1 = s1 + 1
196 thread_id 9 9 statement/sql/commit 2 TRANSACTION COMMIT
197
198 ## Clear statement and transaction history
199 CALL test.clear_history();
200 ## Reset db.t1
201 DELETE FROM db.t1;
202
203 #
204 # STEP 3.2 - TRANSACTION STARTED WITHIN STORED PROCEDURE
205 #
206
207 CALL tp_start();
208 INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321");
209 INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323");
210 UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320;
211
212 SELECT * FROM t1 ORDER BY s1;
213 s1 s2
214 320 INSERT 320
215 322 INSERT 321
216 323 INSERT 322
217 324 INSERT 323
218 COMMIT;
219 #========================================================================
220 # Verify
221 #========================================================================
222 EVENTS_TRANSACTIONS_CURRENT
223
224 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
225 thread_id 3 8 transaction COMMITTED READ WRITE REPEATABLE READ NO 2 STATEMENT
226
227 EVENTS_TRANSACTIONS_HISTORY_LONG
228
229 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
230 thread_id 3 8 transaction COMMITTED READ WRITE REPEATABLE READ NO 2 STATEMENT
231
232 EVENTS_STATEMENTS_HISTORY_LONG
233
234 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
235 thread_id 1 3 statement/sql/call_procedure NULL NULL NULL 0 CALL tp_start()
236 thread_id 2 3 statement/sp/stmt tp_start 1 STATEMENT 1 START TRANSACTION
237 thread_id 4 4 statement/sql/insert NULL 3 TRANSACTION 0 INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321")
238 thread_id 5 5 statement/sql/insert NULL 3 TRANSACTION 0 INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323")
239 thread_id 6 6 statement/sql/update NULL 3 TRANSACTION 0 UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320
240 thread_id 7 7 statement/sql/select NULL 3 TRANSACTION 0 SELECT * FROM t1 ORDER BY s1
241 thread_id 8 8 statement/sql/commit NULL 3 TRANSACTION 0 COMMIT
242
243 ## Combined statement and transaction event history ordered by event id
244
245 EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
246
247 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
248 thread_id 1 3 statement/sql/call_proced NULL NULL CALL tp_start()
249 thread_id 2 3 statement/sp/stmt 1 STATEMENT START TRANSACTION
250 thread_id 3 8 transaction 2 STATEMENT <transaction started>
251 thread_id 4 4 statement/sql/insert 3 TRANSACTION INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321")
252 thread_id 5 5 statement/sql/insert 3 TRANSACTION INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323")
253 thread_id 6 6 statement/sql/update 3 TRANSACTION UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320
254 thread_id 7 7 statement/sql/select 3 TRANSACTION SELECT * FROM t1 ORDER BY s1
255 thread_id 8 8 statement/sql/commit 3 TRANSACTION COMMIT
256
257 ## Clear statement and transaction history
258 CALL test.clear_history();
259 ## Reset db.t1
260 DELETE FROM db.t1;
261
262 #
263 # STEP 3.3 - TRANSACTION ENDED WITHIN STORED PROCEDURE
264 #
265
266 ## COMMIT within stored procedure
267 START TRANSACTION;
268 INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331");
269 INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333");
270 DELETE FROM t1 WHERE s1 > 331;
271 CALL tp_commit();
272
273 SELECT * FROM t1 ORDER BY s1;
274 s1 s2
275 330 INSERT 330
276 331 INSERT 331
277
278 ## ROLLBACK within stored procedure
279 START TRANSACTION;
280 UPDATE t1 SET s1 = s1*2 WHERE s1 > 331;
281 CALL tp_rollback();
282
283 SELECT * FROM t1 ORDER BY s1;
284 s1 s2
285 330 INSERT 330
286 331 INSERT 331
287 #========================================================================
288 # Verify
289 #========================================================================
290 EVENTS_TRANSACTIONS_CURRENT
291
292 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
293 thread_id 16 16 transaction COMMITTED READ WRITE REPEATABLE READ YES 15 STATEMENT
294
295 EVENTS_TRANSACTIONS_HISTORY_LONG
296
297 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
298 thread_id 2 7 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
299 thread_id 9 9 transaction COMMITTED READ WRITE REPEATABLE READ YES 8 STATEMENT
300 thread_id 11 14 transaction ROLLED BACK READ WRITE REPEATABLE READ NO 10 STATEMENT
301 thread_id 16 16 transaction COMMITTED READ WRITE REPEATABLE READ YES 15 STATEMENT
302
303 EVENTS_STATEMENTS_HISTORY_LONG
304
305 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
306 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
307 thread_id 3 3 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331")
308 thread_id 4 4 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333")
309 thread_id 5 5 statement/sql/delete NULL 2 TRANSACTION 0 DELETE FROM t1 WHERE s1 > 331
310 thread_id 6 7 statement/sql/call_procedure NULL 2 TRANSACTION 0 CALL tp_commit()
311 thread_id 7 7 statement/sp/stmt tp_commit 6 STATEMENT 1 COMMIT
312 thread_id 8 9 statement/sql/select NULL NULL NULL 0 SELECT * FROM t1 ORDER BY s1
313 thread_id 10 11 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
314 thread_id 12 12 statement/sql/update NULL 11 TRANSACTION 0 UPDATE t1 SET s1 = s1*2 WHERE s1 > 331
315 thread_id 13 14 statement/sql/call_procedure NULL 11 TRANSACTION 0 CALL tp_rollback()
316 thread_id 14 14 statement/sp/stmt tp_rollback 13 STATEMENT 1 ROLLBACK
317 thread_id 15 16 statement/sql/select NULL NULL NULL 0 SELECT * FROM t1 ORDER BY s1
318
319 ## Combined statement and transaction event history ordered by event id
320
321 EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
322
323 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
324 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
325 thread_id 2 7 transaction 1 STATEMENT <transaction started>
326 thread_id 3 3 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331")
327 thread_id 4 4 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333")
328 thread_id 5 5 statement/sql/delete 2 TRANSACTION DELETE FROM t1 WHERE s1 > 331
329 thread_id 6 7 statement/sql/call_proced 2 TRANSACTION CALL tp_commit()
330 thread_id 7 7 statement/sp/stmt 6 STATEMENT COMMIT
331 thread_id 8 9 statement/sql/select NULL NULL SELECT * FROM t1 ORDER BY s1
332 thread_id 9 9 transaction 8 STATEMENT <transaction started>
333 thread_id 10 11 statement/sql/begin NULL NULL START TRANSACTION
334 thread_id 11 14 transaction 10 STATEMENT <transaction started>
335 thread_id 12 12 statement/sql/update 11 TRANSACTION UPDATE t1 SET s1 = s1*2 WHERE s1 > 331
336 thread_id 13 14 statement/sql/call_proced 11 TRANSACTION CALL tp_rollback()
337 thread_id 14 14 statement/sp/stmt 13 STATEMENT ROLLBACK
338 thread_id 15 16 statement/sql/select NULL NULL SELECT * FROM t1 ORDER BY s1
339 thread_id 16 16 transaction 15 STATEMENT <transaction started>
340
341 ## Clear statement and transaction history
342 CALL test.clear_history();
343 ## Reset db.t1
344 DELETE FROM db.t1;
345
346 #========================================================================
347 # STEP 4 - TRANSACTIONS AND STORED FUNCTIONS
348 #========================================================================
349
350 #
351 # STEP 4.1 - FUNCTION WITHIN A TRANSACTION
352 #
353
354 ## Clear history
355 CALL test.clear_history();
356
357 START TRANSACTION;
358 INSERT INTO t1 VALUES (410, "INSERT 410");
359 INSERT INTO t1 VALUES (411, "INSERT 411");
360 INSERT INTO t1 VALUES (412, "INSERT 412");
361 DELETE FROM t1 WHERE s1 > 410;
362
363 SELECT * FROM t1 ORDER BY s1;
364 s1 s2
365 410 INSERT 410
366
367 SELECT fn_add(413, 414);
368 fn_add(413, 414)
369 827
370 COMMIT;
371
372 SELECT * FROM t1 ORDER BY s1;
373 s1 s2
374 410 INSERT 410
375 413 INSERT x
376 414 INSERT y
377 #========================================================================
378 # Verify
379 #========================================================================
380 EVENTS_TRANSACTIONS_CURRENT
381
382 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
383 thread_id 13 13 transaction COMMITTED READ WRITE REPEATABLE READ YES 12 STATEMENT
384
385 EVENTS_TRANSACTIONS_HISTORY_LONG
386
387 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
388 thread_id 2 11 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
389 thread_id 13 13 transaction COMMITTED READ WRITE REPEATABLE READ YES 12 STATEMENT
390
391 EVENTS_STATEMENTS_HISTORY_LONG
392
393 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
394 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
395 thread_id 3 3 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (410, "INSERT 410")
396 thread_id 4 4 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (411, "INSERT 411")
397 thread_id 5 5 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (412, "INSERT 412")
398 thread_id 6 6 statement/sql/delete NULL 2 TRANSACTION 0 DELETE FROM t1 WHERE s1 > 410
399 thread_id 7 7 statement/sql/select NULL 2 TRANSACTION 0 SELECT * FROM t1 ORDER BY s1
400 thread_id 8 10 statement/sql/select NULL 2 TRANSACTION 0 SELECT fn_add(413, 414)
401 thread_id 9 9 statement/sp/stmt fn_add 8 STATEMENT 1 INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
402 thread_id 10 10 statement/sp/freturn fn_add 8 STATEMENT 1 NULL
403 thread_id 11 11 statement/sql/commit NULL 2 TRANSACTION 0 COMMIT
404 thread_id 12 13 statement/sql/select NULL NULL NULL 0 SELECT * FROM t1 ORDER BY s1
405
406 ## Combined statement and transaction event history ordered by event id
407
408 EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
409
410 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
411 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
412 thread_id 2 11 transaction 1 STATEMENT <transaction started>
413 thread_id 3 3 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (410, "INSERT 410")
414 thread_id 4 4 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (411, "INSERT 411")
415 thread_id 5 5 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (412, "INSERT 412")
416 thread_id 6 6 statement/sql/delete 2 TRANSACTION DELETE FROM t1 WHERE s1 > 410
417 thread_id 7 7 statement/sql/select 2 TRANSACTION SELECT * FROM t1 ORDER BY s1
418 thread_id 8 10 statement/sql/select 2 TRANSACTION SELECT fn_add(413, 414)
419 thread_id 9 9 statement/sp/stmt 8 STATEMENT INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
420 thread_id 10 10 statement/sp/freturn 8 STATEMENT NULL
421 thread_id 11 11 statement/sql/commit 2 TRANSACTION COMMIT
422 thread_id 12 13 statement/sql/select NULL NULL SELECT * FROM t1 ORDER BY s1
423 thread_id 13 13 transaction 12 STATEMENT <transaction started>
424
425 ## Clear statement and transaction history
426 CALL test.clear_history();
427 ## Reset db.t1
428 DELETE FROM db.t1;
429
430
431 ## Again, but this time with a rollback
432
433 START TRANSACTION;
434 SELECT fn_add(415, 416);
435 fn_add(415, 416)
436 831
437
438 ROLLBACK;
439
440 SELECT * FROM t1 ORDER BY s1;
441 s1 s2
442 #========================================================================
443 # Verify
444 #========================================================================
445 EVENTS_TRANSACTIONS_CURRENT
446
447 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
448 thread_id 8 8 transaction COMMITTED READ WRITE REPEATABLE READ YES 7 STATEMENT
449
450 EVENTS_TRANSACTIONS_HISTORY_LONG
451
452 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
453 thread_id 2 6 transaction ROLLED BACK READ WRITE REPEATABLE READ NO 1 STATEMENT
454 thread_id 8 8 transaction COMMITTED READ WRITE REPEATABLE READ YES 7 STATEMENT
455
456 EVENTS_STATEMENTS_HISTORY_LONG
457
458 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
459 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
460 thread_id 3 5 statement/sql/select NULL 2 TRANSACTION 0 SELECT fn_add(415, 416)
461 thread_id 4 4 statement/sp/stmt fn_add 3 STATEMENT 1 INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
462 thread_id 5 5 statement/sp/freturn fn_add 3 STATEMENT 1 NULL
463 thread_id 6 6 statement/sql/rollback NULL 2 TRANSACTION 0 ROLLBACK
464 thread_id 7 8 statement/sql/select NULL NULL NULL 0 SELECT * FROM t1 ORDER BY s1
465
466 ## Combined statement and transaction event history ordered by event id
467
468 EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
469
470 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
471 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
472 thread_id 2 6 transaction 1 STATEMENT <transaction started>
473 thread_id 3 5 statement/sql/select 2 TRANSACTION SELECT fn_add(415, 416)
474 thread_id 4 4 statement/sp/stmt 3 STATEMENT INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
475 thread_id 5 5 statement/sp/freturn 3 STATEMENT NULL
476 thread_id 6 6 statement/sql/rollback 2 TRANSACTION ROLLBACK
477 thread_id 7 8 statement/sql/select NULL NULL SELECT * FROM t1 ORDER BY s1
478 thread_id 8 8 transaction 7 STATEMENT <transaction started>
479
480 ## Clear statement and transaction history
481 CALL test.clear_history();
482 ## Reset db.t1
483 DELETE FROM db.t1;
484
485 #
486 # STEP 4.2 - TRANSACTION CANNOT BE STARTED OR ENDED WITHIN FUNCTION
487 #
488 CREATE FUNCTION fn_err1() RETURNS VARCHAR(10) BEGIN START TRANSACTION ; RETURN invalid ; END|
489 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
490
491 ## Expect 0 transactions
492 SELECT COUNT(*) FROM performance_schema.events_transactions_history;
493 COUNT(*)
494 0
495
496 ## Expect stored function does not exist
497
498 SELECT fn_err1();
499 ERROR 42000: FUNCTION db.fn_err1 does not exist
500
501 ## Expect 0 transactions
502 SELECT COUNT(*) FROM performance_schema.events_transactions_history;
503 COUNT(*)
504 0
505
506 CREATE FUNCTION fn_err2() RETURNS VARCHAR(10) BEGIN COMMIT; RETURN invalid ; END|
507 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger.
508
509 ## Expect stored function does not exist
510
511 START TRANSACTION;
512 DELETE FROM t1 WHERE s1 > 320;
513 SELECT fn_err2();
514 ERROR 42000: FUNCTION db.fn_err2 does not exist
515
516 ## Expect 0 transactions
517 SELECT COUNT(*) FROM performance_schema.events_transactions_history;
518 COUNT(*)
519 0
520
521 ## Clear transaction and statement tables
522 CALL test.clear_history();
523 #========================================================================
524 # STEP 5 - TRANSACTIONS AND TRIGGERS
525 #========================================================================
526
527 #
528 # STEP 5.1 - FORCE STATEMENT ROLLBACK FROM TRIGGER
529 #
530 ## Create a trigger to force statement rollback
531
532 CREATE TRIGGER trigger_before_update BEFORE UPDATE ON t1
533 FOR EACH ROW
534 BEGIN
535 IF OLD.s1 >= 505 THEN
536 SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR";
537 END IF;
538 END;|
539
540 ## Warm-up data-dictionary cache.
541
542 ## Clear history
543 CALL test.clear_history();
544
545 ## Insert multiple rows, then update. Trigger will force rollback the
546 ## UPDATE statement, but the transaction should not roll back.
547
548 START TRANSACTION;
549 INSERT INTO t1 VALUES (500, "INSERT 500");
550 INSERT INTO t1 VALUES (501, "INSERT 501");
551 INSERT INTO t1 VALUES (502, "INSERT 502");
552 INSERT INTO t1 VALUES (503, "INSERT 503");
553 INSERT INTO t1 VALUES (504, "INSERT 504");
554 INSERT INTO t1 VALUES (505, "INSERT 505");
555
556 SELECT * FROM t1 ORDER BY s1;
557 s1 s2
558 500 INSERT 500
559 501 INSERT 501
560 502 INSERT 502
561 503 INSERT 503
562 504 INSERT 504
563 505 INSERT 505
564
565 ## Expect error when UPDATE hits record 505
566
567 UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500;
568 ERROR 45001: FORCE ERROR
569
570 ## Verify that INSERT succeeded, UPDATE failed and transaction did not rollback
571
572 SELECT * FROM t1 ORDER BY s1;
573 s1 s2
574 500 INSERT 500
575 501 INSERT 501
576 502 INSERT 502
577 503 INSERT 503
578 504 INSERT 504
579 505 INSERT 505
580 COMMIT;
581
582 DROP TRIGGER trigger_before_update;
583 #========================================================================
584 # Verify
585 #========================================================================
586 EVENTS_TRANSACTIONS_CURRENT
587
588 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
589 thread_id 21 21 transaction COMMITTED READ WRITE REPEATABLE READ NO 20 STATEMENT
590
591 EVENTS_TRANSACTIONS_HISTORY_LONG
592
593 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
594 thread_id 2 19 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
595 thread_id 21 21 transaction COMMITTED READ WRITE REPEATABLE READ NO 20 STATEMENT
596
597 EVENTS_STATEMENTS_HISTORY_LONG
598
599 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
600 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
601 thread_id 3 3 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (500, "INSERT 500")
602 thread_id 4 4 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (501, "INSERT 501")
603 thread_id 5 5 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (502, "INSERT 502")
604 thread_id 6 6 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (503, "INSERT 503")
605 thread_id 7 7 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (504, "INSERT 504")
606 thread_id 8 8 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (505, "INSERT 505")
607 thread_id 9 9 statement/sql/select NULL 2 TRANSACTION 0 SELECT * FROM t1 ORDER BY s1
608 thread_id 10 17 statement/sql/update NULL 2 TRANSACTION 0 UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500
609 thread_id 11 11 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
610 thread_id 12 12 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
611 thread_id 13 13 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
612 thread_id 14 14 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
613 thread_id 15 15 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
614 thread_id 16 16 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
615 thread_id 17 17 statement/sp/stmt trigger_befo 10 STATEMENT 1 SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR"
616 thread_id 18 18 statement/sql/select NULL 2 TRANSACTION 0 SELECT * FROM t1 ORDER BY s1
617 thread_id 19 19 statement/sql/commit NULL 2 TRANSACTION 0 COMMIT
618 thread_id 20 21 statement/sql/drop_trigger NULL NULL NULL 0 DROP TRIGGER trigger_before_update
619
620 ## Combined statement and transaction event history ordered by event id
621
622 EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
623
624 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
625 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
626 thread_id 2 19 transaction 1 STATEMENT <transaction started>
627 thread_id 3 3 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (500, "INSERT 500")
628 thread_id 4 4 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (501, "INSERT 501")
629 thread_id 5 5 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (502, "INSERT 502")
630 thread_id 6 6 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (503, "INSERT 503")
631 thread_id 7 7 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (504, "INSERT 504")
632 thread_id 8 8 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (505, "INSERT 505")
633 thread_id 9 9 statement/sql/select 2 TRANSACTION SELECT * FROM t1 ORDER BY s1
634 thread_id 10 17 statement/sql/update 2 TRANSACTION UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500
635 thread_id 11 11 statement/sp/jump_if_not 10 STATEMENT NULL
636 thread_id 12 12 statement/sp/jump_if_not 10 STATEMENT NULL
637 thread_id 13 13 statement/sp/jump_if_not 10 STATEMENT NULL
638 thread_id 14 14 statement/sp/jump_if_not 10 STATEMENT NULL
639 thread_id 15 15 statement/sp/jump_if_not 10 STATEMENT NULL
640 thread_id 16 16 statement/sp/jump_if_not 10 STATEMENT NULL
641 thread_id 17 17 statement/sp/stmt 10 STATEMENT SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR"
642 thread_id 18 18 statement/sql/select 2 TRANSACTION SELECT * FROM t1 ORDER BY s1
643 thread_id 19 19 statement/sql/commit 2 TRANSACTION COMMIT
644 thread_id 20 21 statement/sql/drop_trigge NULL NULL DROP TRIGGER trigger_before_update
645 thread_id 21 21 transaction 20 STATEMENT <transaction started>
646
647 ## Clear statement and transaction history
648 CALL test.clear_history();
649 ## Reset db.t1
650 DELETE FROM db.t1;
651
652 # TODO: Detect statement events from scheduled event
653 #=======================================================================
654 # Cleanup
655 #=======================================================================
656 DROP DATABASE db;
657 UPDATE performance_schema.setup_instruments SET enabled='YES', timed='YES';
658 DROP PROCEDURE clear_transaction_tables;
659 DROP PROCEDURE clear_transaction_history;
660 DROP PROCEDURE clear_statement_history;
661 DROP PROCEDURE clear_history;
662 DROP PROCEDURE transaction_verifier;