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 connection default;
9 SET SESSION AUTOCOMMIT= 1;
10 USE test;
11 DROP DATABASE IF EXISTS db;
12 #
13 ## Create test database, test tables, one transactional and one non-transactional
14 CREATE DATABASE db;
15 CREATE TABLE db.t1 (s1 int, s2 varchar(64)) ENGINE=INNODB;
16 CREATE TABLE db.nt1 (s1 int, s2 varchar(64)) ENGINE=MYISAM;
17 #
18 ## Setup connection 1
19 connect con1, localhost, root,,;
20 USE db;
21 SET SESSION AUTOCOMMIT = 1;
22 SELECT thread_id INTO @my_thread_id
23 FROM performance_schema.threads
24 WHERE processlist_id = connection_id();
25 connection default;
26 #
27 ## Disable events from the control (default) connection
28 UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID();
29 SET @all_threads= 0;
30 #
31 ## Enable only transaction and statement instruments
32 UPDATE performance_schema.setup_instruments SET enabled='NO', timed='NO';
33 UPDATE performance_schema.setup_instruments SET enabled='YES'
34 WHERE name LIKE ('statement/%') OR name = 'transaction';
35 #
36 ## Clear statement and transaction history
37 CALL test.clear_history();
38 #
39 #========================================================================
40 # STEP 2 - BASIC TRANSACTION
41 #========================================================================
42 #
43 # STEP 2.1 - IMPLICIT
44 #
45 connection con1;
46 INSERT INTO t1 VALUES (210, "INSERT 210");
47 INSERT INTO t1 VALUES (211, "INSERT 211");
48 INSERT INTO t1 VALUES (212, "INSERT 212");
49 UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212;
50 connection default;
51 #========================================================================
52 # Verify
53 #========================================================================
54 SELECT event_id into @base_tx_event_id
55 from performance_schema.events_transactions_history_long
56 where (THREAD_ID = @con1_thread_id)
57 order by event_id limit 1;
58 SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
59 where (THREAD_ID = @con1_thread_id)
60 order by event_id limit 1;
61 select if(@base_tx_event_id < @base_stmt_event_id,
62 @base_tx_event_id - 1,
63 @base_stmt_event_id - 1)
64 into @base_event_id;
65 #
66 # EVENTS_TRANSACTIONS_CURRENT
67 #
68 SELECT THREAD_ID,
69 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
70 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
71 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
72 RPAD(STATE, 11, ' ') 'STATE ',
73 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
74 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
75 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
76 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
77 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
78 FROM performance_schema.events_transactions_current
79 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
80 ORDER BY thread_id, event_id;
81 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
82 thread_id 8 8 transaction COMMITTED READ WRITE REPEATABLE READ YES 7 STATEMENT
83 #
84 # EVENTS_TRANSACTIONS_HISTORY_LONG
85 #
86 SELECT THREAD_ID,
87 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
88 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
89 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
90 RPAD(STATE, 11, ' ') 'STATE ',
91 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
92 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
93 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
94 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
95 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
96 FROM performance_schema.events_transactions_history_long
97 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
98 ORDER BY thread_id, event_id;
99 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
100 thread_id 2 2 transaction COMMITTED READ WRITE REPEATABLE READ YES 1 STATEMENT
101 thread_id 4 4 transaction COMMITTED READ WRITE REPEATABLE READ YES 3 STATEMENT
102 thread_id 6 6 transaction COMMITTED READ WRITE REPEATABLE READ YES 5 STATEMENT
103 thread_id 8 8 transaction COMMITTED READ WRITE REPEATABLE READ YES 7 STATEMENT
104 #
105 # EVENTS_STATEMENTS_HISTORY_LONG
106 #
107 SELECT THREAD_ID,
108 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
109 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
110 RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ',
111 RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
112 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
113 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
114 LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
115 SQL_TEXT
116 FROM performance_schema.events_statements_history_long
117 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
118 ORDER BY thread_id, event_id;
119 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
120 thread_id 1 2 statement/sql/insert NULL NULL NULL 0 INSERT INTO t1 VALUES (210, "INSERT 210")
121 thread_id 3 4 statement/sql/insert NULL NULL NULL 0 INSERT INTO t1 VALUES (211, "INSERT 211")
122 thread_id 5 6 statement/sql/insert NULL NULL NULL 0 INSERT INTO t1 VALUES (212, "INSERT 212")
123 thread_id 7 8 statement/sql/update NULL NULL NULL 0 UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212
124 #
125 ### Combined statement and transaction event history ordered by event id
126 #
127 #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
128 #
129 SELECT THREAD_ID,
130 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
131 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
132 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
133 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
134 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
135 '<transaction started>' AS SQL_TXT
136 FROM performance_schema.events_transactions_history_long t
137 WHERE (t.thread_id = @con1_thread_id)
138 UNION
139 SELECT THREAD_ID,
140 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
141 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
142 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
143 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
144 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
145 SQL_TEXT
146 FROM performance_schema.events_statements_history_long s
147 WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
148 ORDER BY thread_id, r_event_id;
149 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
150 thread_id 1 2 statement/sql/insert NULL NULL INSERT INTO t1 VALUES (210, "INSERT 210")
151 thread_id 2 2 transaction 1 STATEMENT <transaction started>
152 thread_id 3 4 statement/sql/insert NULL NULL INSERT INTO t1 VALUES (211, "INSERT 211")
153 thread_id 4 4 transaction 3 STATEMENT <transaction started>
154 thread_id 5 6 statement/sql/insert NULL NULL INSERT INTO t1 VALUES (212, "INSERT 212")
155 thread_id 6 6 transaction 5 STATEMENT <transaction started>
156 thread_id 7 8 statement/sql/update NULL NULL UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212
157 thread_id 8 8 transaction 7 STATEMENT <transaction started>
158 #
159 ### Clear statement and transaction history
160 # CALL test.clear_history();
161 CALL test.clear_history();
162 ## Reset db.t1
163 DELETE FROM db.t1;
164 #
165 #
166 # STEP 2.2 - EXPLICIT
167 #
168 connection con1;
169 START TRANSACTION;
170 INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221");
171 UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221;
172 COMMIT;
173 connection default;
174 #========================================================================
175 # Verify
176 #========================================================================
177 SELECT event_id into @base_tx_event_id
178 from performance_schema.events_transactions_history_long
179 where (THREAD_ID = @con1_thread_id)
180 order by event_id limit 1;
181 SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
182 where (THREAD_ID = @con1_thread_id)
183 order by event_id limit 1;
184 select if(@base_tx_event_id < @base_stmt_event_id,
185 @base_tx_event_id - 1,
186 @base_stmt_event_id - 1)
187 into @base_event_id;
188 #
189 # EVENTS_TRANSACTIONS_CURRENT
190 #
191 SELECT THREAD_ID,
192 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
193 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
194 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
195 RPAD(STATE, 11, ' ') 'STATE ',
196 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
197 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
198 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
199 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
200 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
201 FROM performance_schema.events_transactions_current
202 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
203 ORDER BY thread_id, event_id;
204 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
205 thread_id 2 5 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
206 #
207 # EVENTS_TRANSACTIONS_HISTORY_LONG
208 #
209 SELECT THREAD_ID,
210 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
211 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
212 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
213 RPAD(STATE, 11, ' ') 'STATE ',
214 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
215 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
216 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
217 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
218 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
219 FROM performance_schema.events_transactions_history_long
220 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
221 ORDER BY thread_id, event_id;
222 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
223 thread_id 2 5 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
224 #
225 # EVENTS_STATEMENTS_HISTORY_LONG
226 #
227 SELECT THREAD_ID,
228 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
229 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
230 RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ',
231 RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
232 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
233 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
234 LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
235 SQL_TEXT
236 FROM performance_schema.events_statements_history_long
237 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
238 ORDER BY thread_id, event_id;
239 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
240 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
241 thread_id 3 3 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221")
242 thread_id 4 4 statement/sql/update NULL 2 TRANSACTION 0 UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221
243 thread_id 5 5 statement/sql/commit NULL 2 TRANSACTION 0 COMMIT
244 #
245 ### Combined statement and transaction event history ordered by event id
246 #
247 #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
248 #
249 SELECT THREAD_ID,
250 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
251 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
252 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
253 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
254 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
255 '<transaction started>' AS SQL_TXT
256 FROM performance_schema.events_transactions_history_long t
257 WHERE (t.thread_id = @con1_thread_id)
258 UNION
259 SELECT THREAD_ID,
260 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
261 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
262 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
263 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
264 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
265 SQL_TEXT
266 FROM performance_schema.events_statements_history_long s
267 WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
268 ORDER BY thread_id, r_event_id;
269 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
270 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
271 thread_id 2 5 transaction 1 STATEMENT <transaction started>
272 thread_id 3 3 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221")
273 thread_id 4 4 statement/sql/update 2 TRANSACTION UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221
274 thread_id 5 5 statement/sql/commit 2 TRANSACTION COMMIT
275 #
276 ### Clear statement and transaction history
277 # CALL test.clear_history();
278 CALL test.clear_history();
279 ## Reset db.t1
280 DELETE FROM db.t1;
281 #
282 #========================================================================
283 # STEP 3 - TRANSACTIONS AND STORED PROCEDURES
284 #========================================================================
285 #
286 # STEP 3.1 - STORED PROCEDURE STARTED WITHIN TRANSACTION
287 #
288 connection con1;
289 CREATE PROCEDURE tp_update() UPDATE t1 SET s1 = s1 + 1;
290 #
291 START TRANSACTION;
292 INSERT INTO t1 VALUES (310, "INSERT 310");
293 INSERT INTO t1 VALUES (311, "INSERT 311");
294 INSERT INTO t1 VALUES (312, "INSERT 312");
295 INSERT INTO t1 VALUES (313, "INSERT 313");
296 CALL tp_update();
297 COMMIT;
298 connection default;
299 #========================================================================
300 # Verify
301 #========================================================================
302 SELECT event_id into @base_tx_event_id
303 from performance_schema.events_transactions_history_long
304 where (THREAD_ID = @con1_thread_id)
305 order by event_id limit 1;
306 SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
307 where (THREAD_ID = @con1_thread_id)
308 order by event_id limit 1;
309 select if(@base_tx_event_id < @base_stmt_event_id,
310 @base_tx_event_id - 1,
311 @base_stmt_event_id - 1)
312 into @base_event_id;
313 #
314 # EVENTS_TRANSACTIONS_CURRENT
315 #
316 SELECT THREAD_ID,
317 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
318 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
319 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
320 RPAD(STATE, 11, ' ') 'STATE ',
321 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
322 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
323 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
324 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
325 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
326 FROM performance_schema.events_transactions_current
327 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
328 ORDER BY thread_id, event_id;
329 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
330 thread_id 10 12 transaction COMMITTED READ WRITE REPEATABLE READ NO 9 STATEMENT
331 #
332 # EVENTS_TRANSACTIONS_HISTORY_LONG
333 #
334 SELECT THREAD_ID,
335 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
336 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
337 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
338 RPAD(STATE, 11, ' ') 'STATE ',
339 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
340 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
341 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
342 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
343 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
344 FROM performance_schema.events_transactions_history_long
345 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
346 ORDER BY thread_id, event_id;
347 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
348 thread_id 2 2 transaction COMMITTED READ WRITE REPEATABLE READ YES 1 STATEMENT
349 thread_id 10 10 transaction COMMITTED READ WRITE REPEATABLE READ NO 9 STATEMENT
350 thread_id 10 12 transaction COMMITTED READ WRITE REPEATABLE READ NO 9 STATEMENT
351 #
352 # EVENTS_STATEMENTS_HISTORY_LONG
353 #
354 SELECT THREAD_ID,
355 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
356 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
357 RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ',
358 RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
359 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
360 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
361 LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
362 SQL_TEXT
363 FROM performance_schema.events_statements_history_long
364 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
365 ORDER BY thread_id, event_id;
366 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
367 thread_id 1 2 statement/sql/create_procedure NULL NULL NULL 0 CREATE PROCEDURE tp_update() UPDATE t1 SET s1 = s1 + 1
368 thread_id 3 4 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
369 thread_id 5 5 statement/sql/insert NULL 4 TRANSACTION 0 INSERT INTO t1 VALUES (310, "INSERT 310")
370 thread_id 6 6 statement/sql/insert NULL 4 TRANSACTION 0 INSERT INTO t1 VALUES (311, "INSERT 311")
371 thread_id 7 7 statement/sql/insert NULL 4 TRANSACTION 0 INSERT INTO t1 VALUES (312, "INSERT 312")
372 thread_id 8 8 statement/sql/insert NULL 4 TRANSACTION 0 INSERT INTO t1 VALUES (313, "INSERT 313")
373 thread_id 9 11 statement/sql/call_procedure NULL 4 TRANSACTION 0 CALL tp_update()
374 thread_id 11 11 statement/sp/stmt tp_update 9 STATEMENT 1 UPDATE t1 SET s1 = s1 + 1
375 thread_id 12 12 statement/sql/commit NULL NULL NULL 0 COMMIT
376 #
377 ### Combined statement and transaction event history ordered by event id
378 #
379 #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
380 #
381 SELECT THREAD_ID,
382 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
383 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
384 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
385 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
386 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
387 '<transaction started>' AS SQL_TXT
388 FROM performance_schema.events_transactions_history_long t
389 WHERE (t.thread_id = @con1_thread_id)
390 UNION
391 SELECT THREAD_ID,
392 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
393 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
394 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
395 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
396 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
397 SQL_TEXT
398 FROM performance_schema.events_statements_history_long s
399 WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
400 ORDER BY thread_id, r_event_id;
401 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
402 thread_id 1 2 statement/sql/create_proc NULL NULL CREATE PROCEDURE tp_update() UPDATE t1 SET s1 = s1 + 1
403 thread_id 2 2 transaction 1 STATEMENT <transaction started>
404 thread_id 3 4 statement/sql/begin NULL NULL START TRANSACTION
405 thread_id 5 5 statement/sql/insert 4 TRANSACTION INSERT INTO t1 VALUES (310, "INSERT 310")
406 thread_id 6 6 statement/sql/insert 4 TRANSACTION INSERT INTO t1 VALUES (311, "INSERT 311")
407 thread_id 7 7 statement/sql/insert 4 TRANSACTION INSERT INTO t1 VALUES (312, "INSERT 312")
408 thread_id 8 8 statement/sql/insert 4 TRANSACTION INSERT INTO t1 VALUES (313, "INSERT 313")
409 thread_id 9 11 statement/sql/call_proced 4 TRANSACTION CALL tp_update()
410 thread_id 10 10 transaction 9 STATEMENT <transaction started>
411 thread_id 10 12 transaction 9 STATEMENT <transaction started>
412 thread_id 11 11 statement/sp/stmt 9 STATEMENT UPDATE t1 SET s1 = s1 + 1
413 thread_id 12 12 statement/sql/commit NULL NULL COMMIT
414 #
415 ### Clear statement and transaction history
416 # CALL test.clear_history();
417 CALL test.clear_history();
418 ## Reset db.t1
419 DELETE FROM db.t1;
420 #
421 #
422 # STEP 3.2 - TRANSACTION STARTED WITHIN STORED PROCEDURE
423 #
424 connection con1;
425 CREATE PROCEDURE tp_start() START TRANSACTION;
426 #
427 CALL tp_start();
428 INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321");
429 INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323");
430 UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320;
431 #
432 SELECT * FROM t1 ORDER BY s1;
433 s1 s2
434 320 INSERT 320
435 322 INSERT 321
436 323 INSERT 322
437 324 INSERT 323
438 COMMIT;
439 connection default;
440 #========================================================================
441 # Verify
442 #========================================================================
443 SELECT event_id into @base_tx_event_id
444 from performance_schema.events_transactions_history_long
445 where (THREAD_ID = @con1_thread_id)
446 order by event_id limit 1;
447 SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
448 where (THREAD_ID = @con1_thread_id)
449 order by event_id limit 1;
450 select if(@base_tx_event_id < @base_stmt_event_id,
451 @base_tx_event_id - 1,
452 @base_stmt_event_id - 1)
453 into @base_event_id;
454 #
455 # EVENTS_TRANSACTIONS_CURRENT
456 #
457 SELECT THREAD_ID,
458 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
459 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
460 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
461 RPAD(STATE, 11, ' ') 'STATE ',
462 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
463 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
464 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
465 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
466 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
467 FROM performance_schema.events_transactions_current
468 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
469 ORDER BY thread_id, event_id;
470 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
471 thread_id 6 11 transaction COMMITTED READ WRITE REPEATABLE READ NO 5 STATEMENT
472 #
473 # EVENTS_TRANSACTIONS_HISTORY_LONG
474 #
475 SELECT THREAD_ID,
476 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
477 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
478 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
479 RPAD(STATE, 11, ' ') 'STATE ',
480 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
481 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
482 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
483 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
484 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
485 FROM performance_schema.events_transactions_history_long
486 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
487 ORDER BY thread_id, event_id;
488 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
489 thread_id 2 2 transaction COMMITTED READ WRITE REPEATABLE READ YES 1 STATEMENT
490 thread_id 4 4 transaction COMMITTED READ WRITE REPEATABLE READ YES 3 STATEMENT
491 thread_id 6 11 transaction COMMITTED READ WRITE REPEATABLE READ NO 5 STATEMENT
492 #
493 # EVENTS_STATEMENTS_HISTORY_LONG
494 #
495 SELECT THREAD_ID,
496 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
497 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
498 RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ',
499 RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
500 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
501 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
502 LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
503 SQL_TEXT
504 FROM performance_schema.events_statements_history_long
505 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
506 ORDER BY thread_id, event_id;
507 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
508 thread_id 1 2 statement/sql/create_procedure NULL NULL NULL 0 CREATE PROCEDURE tp_start() START TRANSACTION
509 thread_id 3 6 statement/sql/call_procedure NULL NULL NULL 0 CALL tp_start()
510 thread_id 5 6 statement/sp/stmt tp_start 3 STATEMENT 1 START TRANSACTION
511 thread_id 7 7 statement/sql/insert NULL 6 TRANSACTION 0 INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321")
512 thread_id 8 8 statement/sql/insert NULL 6 TRANSACTION 0 INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323")
513 thread_id 9 9 statement/sql/update NULL 6 TRANSACTION 0 UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320
514 thread_id 10 10 statement/sql/select NULL 6 TRANSACTION 0 SELECT * FROM t1 ORDER BY s1
515 thread_id 11 11 statement/sql/commit NULL 6 TRANSACTION 0 COMMIT
516 #
517 ### Combined statement and transaction event history ordered by event id
518 #
519 #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
520 #
521 SELECT THREAD_ID,
522 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
523 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
524 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
525 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
526 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
527 '<transaction started>' AS SQL_TXT
528 FROM performance_schema.events_transactions_history_long t
529 WHERE (t.thread_id = @con1_thread_id)
530 UNION
531 SELECT THREAD_ID,
532 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
533 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
534 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
535 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
536 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
537 SQL_TEXT
538 FROM performance_schema.events_statements_history_long s
539 WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
540 ORDER BY thread_id, r_event_id;
541 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
542 thread_id 1 2 statement/sql/create_proc NULL NULL CREATE PROCEDURE tp_start() START TRANSACTION
543 thread_id 2 2 transaction 1 STATEMENT <transaction started>
544 thread_id 3 6 statement/sql/call_proced NULL NULL CALL tp_start()
545 thread_id 4 4 transaction 3 STATEMENT <transaction started>
546 thread_id 5 6 statement/sp/stmt 3 STATEMENT START TRANSACTION
547 thread_id 6 11 transaction 5 STATEMENT <transaction started>
548 thread_id 7 7 statement/sql/insert 6 TRANSACTION INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321")
549 thread_id 8 8 statement/sql/insert 6 TRANSACTION INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323")
550 thread_id 9 9 statement/sql/update 6 TRANSACTION UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320
551 thread_id 10 10 statement/sql/select 6 TRANSACTION SELECT * FROM t1 ORDER BY s1
552 thread_id 11 11 statement/sql/commit 6 TRANSACTION COMMIT
553 #
554 ### Clear statement and transaction history
555 # CALL test.clear_history();
556 CALL test.clear_history();
557 ## Reset db.t1
558 DELETE FROM db.t1;
559 #
560 #
561 # STEP 3.3 - TRANSACTION ENDED WITHIN STORED PROCEDURE
562 #
563 connection con1;
564 CREATE PROCEDURE tp_rollback() ROLLBACK;
565 CREATE PROCEDURE tp_commit() COMMIT;
566 #
567 ## COMMIT within stored procedure
568 START TRANSACTION;
569 INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331");
570 INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333");
571 DELETE FROM t1 WHERE s1 > 331;
572 CALL tp_commit();
573 #
574 SELECT * FROM t1 ORDER BY s1;
575 s1 s2
576 330 INSERT 330
577 331 INSERT 331
578 #
579 ## ROLLBACK within stored procedure
580 START TRANSACTION;
581 UPDATE t1 SET s1 = s1*2 WHERE s1 > 331;
582 CALL tp_rollback();
583 #
584 SELECT * FROM t1 ORDER BY s1;
585 s1 s2
586 330 INSERT 330
587 331 INSERT 331
588 connection default;
589 #========================================================================
590 # Verify
591 #========================================================================
592 SELECT event_id into @base_tx_event_id
593 from performance_schema.events_transactions_history_long
594 where (THREAD_ID = @con1_thread_id)
595 order by event_id limit 1;
596 SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
597 where (THREAD_ID = @con1_thread_id)
598 order by event_id limit 1;
599 select if(@base_tx_event_id < @base_stmt_event_id,
600 @base_tx_event_id - 1,
601 @base_stmt_event_id - 1)
602 into @base_event_id;
603 #
604 # EVENTS_TRANSACTIONS_CURRENT
605 #
606 SELECT THREAD_ID,
607 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
608 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
609 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
610 RPAD(STATE, 11, ' ') 'STATE ',
611 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
612 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
613 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
614 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
615 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
616 FROM performance_schema.events_transactions_current
617 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
618 ORDER BY thread_id, event_id;
619 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
620 thread_id 22 22 transaction COMMITTED READ WRITE REPEATABLE READ YES 21 STATEMENT
621 #
622 # EVENTS_TRANSACTIONS_HISTORY_LONG
623 #
624 SELECT THREAD_ID,
625 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
626 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
627 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
628 RPAD(STATE, 11, ' ') 'STATE ',
629 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
630 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
631 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
632 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
633 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
634 FROM performance_schema.events_transactions_history_long
635 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
636 ORDER BY thread_id, event_id;
637 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
638 thread_id 2 2 transaction COMMITTED READ WRITE REPEATABLE READ YES 1 STATEMENT
639 thread_id 4 4 transaction COMMITTED READ WRITE REPEATABLE READ YES 3 STATEMENT
640 thread_id 11 11 transaction COMMITTED READ WRITE REPEATABLE READ NO 10 STATEMENT
641 thread_id 11 12 transaction COMMITTED READ WRITE REPEATABLE READ NO 10 STATEMENT
642 thread_id 14 14 transaction COMMITTED READ WRITE REPEATABLE READ YES 13 STATEMENT
643 thread_id 19 19 transaction COMMITTED READ WRITE REPEATABLE READ NO 18 STATEMENT
644 thread_id 19 20 transaction ROLLED BACK READ WRITE REPEATABLE READ NO 18 STATEMENT
645 thread_id 22 22 transaction COMMITTED READ WRITE REPEATABLE READ YES 21 STATEMENT
646 #
647 # EVENTS_STATEMENTS_HISTORY_LONG
648 #
649 SELECT THREAD_ID,
650 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
651 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
652 RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ',
653 RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
654 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
655 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
656 LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
657 SQL_TEXT
658 FROM performance_schema.events_statements_history_long
659 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
660 ORDER BY thread_id, event_id;
661 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
662 thread_id 1 2 statement/sql/create_procedure NULL NULL NULL 0 CREATE PROCEDURE tp_rollback() ROLLBACK
663 thread_id 3 4 statement/sql/create_procedure NULL NULL NULL 0 CREATE PROCEDURE tp_commit() COMMIT
664 thread_id 5 6 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
665 thread_id 7 7 statement/sql/insert NULL 6 TRANSACTION 0 INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331")
666 thread_id 8 8 statement/sql/insert NULL 6 TRANSACTION 0 INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333")
667 thread_id 9 9 statement/sql/delete NULL 6 TRANSACTION 0 DELETE FROM t1 WHERE s1 > 331
668 thread_id 10 12 statement/sql/call_procedure NULL 6 TRANSACTION 0 CALL tp_commit()
669 thread_id 12 12 statement/sp/stmt tp_commit 10 STATEMENT 1 COMMIT
670 thread_id 13 14 statement/sql/select NULL NULL NULL 0 SELECT * FROM t1 ORDER BY s1
671 thread_id 15 16 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
672 thread_id 17 17 statement/sql/update NULL 16 TRANSACTION 0 UPDATE t1 SET s1 = s1*2 WHERE s1 > 331
673 thread_id 18 20 statement/sql/call_procedure NULL 16 TRANSACTION 0 CALL tp_rollback()
674 thread_id 20 20 statement/sp/stmt tp_rollback 18 STATEMENT 1 ROLLBACK
675 thread_id 21 22 statement/sql/select NULL NULL NULL 0 SELECT * FROM t1 ORDER BY s1
676 #
677 ### Combined statement and transaction event history ordered by event id
678 #
679 #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
680 #
681 SELECT THREAD_ID,
682 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
683 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
684 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
685 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
686 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
687 '<transaction started>' AS SQL_TXT
688 FROM performance_schema.events_transactions_history_long t
689 WHERE (t.thread_id = @con1_thread_id)
690 UNION
691 SELECT THREAD_ID,
692 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
693 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
694 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
695 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
696 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
697 SQL_TEXT
698 FROM performance_schema.events_statements_history_long s
699 WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
700 ORDER BY thread_id, r_event_id;
701 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
702 thread_id 1 2 statement/sql/create_proc NULL NULL CREATE PROCEDURE tp_rollback() ROLLBACK
703 thread_id 2 2 transaction 1 STATEMENT <transaction started>
704 thread_id 3 4 statement/sql/create_proc NULL NULL CREATE PROCEDURE tp_commit() COMMIT
705 thread_id 4 4 transaction 3 STATEMENT <transaction started>
706 thread_id 5 6 statement/sql/begin NULL NULL START TRANSACTION
707 thread_id 7 7 statement/sql/insert 6 TRANSACTION INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331")
708 thread_id 8 8 statement/sql/insert 6 TRANSACTION INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333")
709 thread_id 9 9 statement/sql/delete 6 TRANSACTION DELETE FROM t1 WHERE s1 > 331
710 thread_id 10 12 statement/sql/call_proced 6 TRANSACTION CALL tp_commit()
711 thread_id 11 11 transaction 10 STATEMENT <transaction started>
712 thread_id 11 12 transaction 10 STATEMENT <transaction started>
713 thread_id 12 12 statement/sp/stmt 10 STATEMENT COMMIT
714 thread_id 13 14 statement/sql/select NULL NULL SELECT * FROM t1 ORDER BY s1
715 thread_id 14 14 transaction 13 STATEMENT <transaction started>
716 thread_id 15 16 statement/sql/begin NULL NULL START TRANSACTION
717 thread_id 17 17 statement/sql/update 16 TRANSACTION UPDATE t1 SET s1 = s1*2 WHERE s1 > 331
718 thread_id 18 20 statement/sql/call_proced 16 TRANSACTION CALL tp_rollback()
719 thread_id 19 19 transaction 18 STATEMENT <transaction started>
720 thread_id 19 20 transaction 18 STATEMENT <transaction started>
721 thread_id 20 20 statement/sp/stmt 18 STATEMENT ROLLBACK
722 thread_id 21 22 statement/sql/select NULL NULL SELECT * FROM t1 ORDER BY s1
723 thread_id 22 22 transaction 21 STATEMENT <transaction started>
724 #
725 ### Clear statement and transaction history
726 # CALL test.clear_history();
727 CALL test.clear_history();
728 ## Reset db.t1
729 DELETE FROM db.t1;
730 #
731 #========================================================================
732 # STEP 4 - TRANSACTIONS AND STORED FUNCTIONS
733 #========================================================================
734 #
735 #
736 # STEP 4.1 - FUNCTION WITHIN A TRANSACTION
737 #
738 connection con1;
739 CREATE FUNCTION fn_add(x INT, y INT) RETURNS INT
740 BEGIN
741 INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y");
742 RETURN x+y;
743 END |
744 #
745 ## Clear history
746 connection default;
747 CALL test.clear_history();
748 connection con1;
749 #
750 START TRANSACTION;
751 INSERT INTO t1 VALUES (410, "INSERT 410");
752 INSERT INTO t1 VALUES (411, "INSERT 411");
753 INSERT INTO t1 VALUES (412, "INSERT 412");
754 DELETE FROM t1 WHERE s1 > 410;
755 #
756 SELECT * FROM t1 ORDER BY s1;
757 s1 s2
758 410 INSERT 410
759 #
760 SELECT fn_add(413, 414);
761 fn_add(413, 414)
762 827
763 COMMIT;
764 #
765 SELECT * FROM t1 ORDER BY s1;
766 s1 s2
767 410 INSERT 410
768 413 INSERT x
769 414 INSERT y
770 connection default;
771 #========================================================================
772 # Verify
773 #========================================================================
774 SELECT event_id into @base_tx_event_id
775 from performance_schema.events_transactions_history_long
776 where (THREAD_ID = @con1_thread_id)
777 order by event_id limit 1;
778 SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
779 where (THREAD_ID = @con1_thread_id)
780 order by event_id limit 1;
781 select if(@base_tx_event_id < @base_stmt_event_id,
782 @base_tx_event_id - 1,
783 @base_stmt_event_id - 1)
784 into @base_event_id;
785 #
786 # EVENTS_TRANSACTIONS_CURRENT
787 #
788 SELECT THREAD_ID,
789 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
790 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
791 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
792 RPAD(STATE, 11, ' ') 'STATE ',
793 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
794 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
795 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
796 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
797 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
798 FROM performance_schema.events_transactions_current
799 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
800 ORDER BY thread_id, event_id;
801 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
802 thread_id 14 14 transaction COMMITTED READ WRITE REPEATABLE READ YES 13 STATEMENT
803 #
804 # EVENTS_TRANSACTIONS_HISTORY_LONG
805 #
806 SELECT THREAD_ID,
807 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
808 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
809 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
810 RPAD(STATE, 11, ' ') 'STATE ',
811 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
812 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
813 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
814 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
815 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
816 FROM performance_schema.events_transactions_history_long
817 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
818 ORDER BY thread_id, event_id;
819 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
820 thread_id 9 9 transaction COMMITTED READ WRITE REPEATABLE READ NO 8 STATEMENT
821 thread_id 9 12 transaction COMMITTED READ WRITE REPEATABLE READ NO 8 STATEMENT
822 thread_id 14 14 transaction COMMITTED READ WRITE REPEATABLE READ YES 13 STATEMENT
823 #
824 # EVENTS_STATEMENTS_HISTORY_LONG
825 #
826 SELECT THREAD_ID,
827 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
828 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
829 RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ',
830 RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
831 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
832 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
833 LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
834 SQL_TEXT
835 FROM performance_schema.events_statements_history_long
836 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
837 ORDER BY thread_id, event_id;
838 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
839 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
840 thread_id 3 3 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (410, "INSERT 410")
841 thread_id 4 4 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (411, "INSERT 411")
842 thread_id 5 5 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (412, "INSERT 412")
843 thread_id 6 6 statement/sql/delete NULL 2 TRANSACTION 0 DELETE FROM t1 WHERE s1 > 410
844 thread_id 7 7 statement/sql/select NULL 2 TRANSACTION 0 SELECT * FROM t1 ORDER BY s1
845 thread_id 8 11 statement/sql/select NULL 2 TRANSACTION 0 SELECT fn_add(413, 414)
846 thread_id 10 10 statement/sp/stmt fn_add 8 STATEMENT 1 INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
847 thread_id 11 11 statement/sp/freturn fn_add 8 STATEMENT 1 NULL
848 thread_id 12 12 statement/sql/commit NULL NULL NULL 0 COMMIT
849 thread_id 13 14 statement/sql/select NULL NULL NULL 0 SELECT * FROM t1 ORDER BY s1
850 #
851 ### Combined statement and transaction event history ordered by event id
852 #
853 #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
854 #
855 SELECT THREAD_ID,
856 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
857 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
858 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
859 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
860 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
861 '<transaction started>' AS SQL_TXT
862 FROM performance_schema.events_transactions_history_long t
863 WHERE (t.thread_id = @con1_thread_id)
864 UNION
865 SELECT THREAD_ID,
866 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
867 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
868 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
869 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
870 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
871 SQL_TEXT
872 FROM performance_schema.events_statements_history_long s
873 WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
874 ORDER BY thread_id, r_event_id;
875 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
876 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
877 thread_id 3 3 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (410, "INSERT 410")
878 thread_id 4 4 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (411, "INSERT 411")
879 thread_id 5 5 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (412, "INSERT 412")
880 thread_id 6 6 statement/sql/delete 2 TRANSACTION DELETE FROM t1 WHERE s1 > 410
881 thread_id 7 7 statement/sql/select 2 TRANSACTION SELECT * FROM t1 ORDER BY s1
882 thread_id 8 11 statement/sql/select 2 TRANSACTION SELECT fn_add(413, 414)
883 thread_id 9 9 transaction 8 STATEMENT <transaction started>
884 thread_id 9 12 transaction 8 STATEMENT <transaction started>
885 thread_id 10 10 statement/sp/stmt 8 STATEMENT INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
886 thread_id 11 11 statement/sp/freturn 8 STATEMENT NULL
887 thread_id 12 12 statement/sql/commit NULL NULL COMMIT
888 thread_id 13 14 statement/sql/select NULL NULL SELECT * FROM t1 ORDER BY s1
889 thread_id 14 14 transaction 13 STATEMENT <transaction started>
890 #
891 ### Clear statement and transaction history
892 # CALL test.clear_history();
893 CALL test.clear_history();
894 ## Reset db.t1
895 DELETE FROM db.t1;
896 #
897 connection con1;
898 #
899 ## Again, but this time with a rollback
900 #
901 START TRANSACTION;
902 SELECT fn_add(415, 416);
903 fn_add(415, 416)
904 831
905 #
906 ROLLBACK;
907 #
908 SELECT * FROM t1 ORDER BY s1;
909 s1 s2
910 connection default;
911 #========================================================================
912 # Verify
913 #========================================================================
914 SELECT event_id into @base_tx_event_id
915 from performance_schema.events_transactions_history_long
916 where (THREAD_ID = @con1_thread_id)
917 order by event_id limit 1;
918 SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
919 where (THREAD_ID = @con1_thread_id)
920 order by event_id limit 1;
921 select if(@base_tx_event_id < @base_stmt_event_id,
922 @base_tx_event_id - 1,
923 @base_stmt_event_id - 1)
924 into @base_event_id;
925 #
926 # EVENTS_TRANSACTIONS_CURRENT
927 #
928 SELECT THREAD_ID,
929 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
930 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
931 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
932 RPAD(STATE, 11, ' ') 'STATE ',
933 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
934 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
935 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
936 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
937 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
938 FROM performance_schema.events_transactions_current
939 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
940 ORDER BY thread_id, event_id;
941 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
942 thread_id 8 8 transaction COMMITTED READ WRITE REPEATABLE READ YES 7 STATEMENT
943 #
944 # EVENTS_TRANSACTIONS_HISTORY_LONG
945 #
946 SELECT THREAD_ID,
947 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
948 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
949 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
950 RPAD(STATE, 11, ' ') 'STATE ',
951 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
952 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
953 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
954 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
955 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
956 FROM performance_schema.events_transactions_history_long
957 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
958 ORDER BY thread_id, event_id;
959 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
960 thread_id 2 6 transaction ROLLED BACK READ WRITE REPEATABLE READ NO 1 STATEMENT
961 thread_id 8 8 transaction COMMITTED READ WRITE REPEATABLE READ YES 7 STATEMENT
962 #
963 # EVENTS_STATEMENTS_HISTORY_LONG
964 #
965 SELECT THREAD_ID,
966 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
967 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
968 RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ',
969 RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
970 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
971 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
972 LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
973 SQL_TEXT
974 FROM performance_schema.events_statements_history_long
975 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
976 ORDER BY thread_id, event_id;
977 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
978 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
979 thread_id 3 5 statement/sql/select NULL 2 TRANSACTION 0 SELECT fn_add(415, 416)
980 thread_id 4 4 statement/sp/stmt fn_add 3 STATEMENT 1 INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
981 thread_id 5 5 statement/sp/freturn fn_add 3 STATEMENT 1 NULL
982 thread_id 6 6 statement/sql/rollback NULL 2 TRANSACTION 0 ROLLBACK
983 thread_id 7 8 statement/sql/select NULL NULL NULL 0 SELECT * FROM t1 ORDER BY s1
984 #
985 ### Combined statement and transaction event history ordered by event id
986 #
987 #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
988 #
989 SELECT THREAD_ID,
990 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
991 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
992 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
993 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
994 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
995 '<transaction started>' AS SQL_TXT
996 FROM performance_schema.events_transactions_history_long t
997 WHERE (t.thread_id = @con1_thread_id)
998 UNION
999 SELECT THREAD_ID,
1000 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
1001 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
1002 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
1003 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
1004 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
1005 SQL_TEXT
1006 FROM performance_schema.events_statements_history_long s
1007 WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
1008 ORDER BY thread_id, r_event_id;
1009 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
1010 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
1011 thread_id 2 6 transaction 1 STATEMENT <transaction started>
1012 thread_id 3 5 statement/sql/select 2 TRANSACTION SELECT fn_add(415, 416)
1013 thread_id 4 4 statement/sp/stmt 3 STATEMENT INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
1014 thread_id 5 5 statement/sp/freturn 3 STATEMENT NULL
1015 thread_id 6 6 statement/sql/rollback 2 TRANSACTION ROLLBACK
1016 thread_id 7 8 statement/sql/select NULL NULL SELECT * FROM t1 ORDER BY s1
1017 thread_id 8 8 transaction 7 STATEMENT <transaction started>
1018 #
1019 ### Clear statement and transaction history
1020 # CALL test.clear_history();
1021 CALL test.clear_history();
1022 ## Reset db.t1
1023 DELETE FROM db.t1;
1024 #
1025 #
1026 # STEP 4.2 - TRANSACTION CANNOT BE STARTED OR ENDED WITHIN FUNCTION
1027 #
1028 connection con1;
1029 CREATE FUNCTION fn_err1() RETURNS VARCHAR(10) BEGIN START TRANSACTION ; RETURN 'invalid' ; END|
1030 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
1031 #
1032 ## Expect 0 transactions
1033 connection default;
1034 SELECT COUNT(*) FROM performance_schema.events_transactions_history;
1035 COUNT(*)
1036 0
1037 connection con1;
1038 #
1039 ## Expect stored function does not exist
1040 #
1041 SELECT fn_err1();
1042 ERROR 42000: FUNCTION db.fn_err1 does not exist
1043 #
1044 ## Expect 1 transactions
1045 connection default;
1046 SELECT COUNT(*) FROM performance_schema.events_transactions_history;
1047 COUNT(*)
1048 1
1049 connection con1;
1050 #
1051 CREATE FUNCTION fn_err2() RETURNS VARCHAR(10) BEGIN COMMIT; RETURN 'invalid' ; END|
1052 ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
1053 #
1054 ## Expect stored function does not exist
1055 #
1056 START TRANSACTION;
1057 DELETE FROM t1 WHERE s1 > 320;
1058 SELECT fn_err2();
1059 ERROR 42000: FUNCTION db.fn_err2 does not exist
1060 #
1061 ## Expect 2 transactions
1062 connection default;
1063 SELECT COUNT(*) FROM performance_schema.events_transactions_history;
1064 COUNT(*)
1065 2
1066 #
1067 ## Clear transaction and statement tables
1068 CALL test.clear_history();
1069 #========================================================================
1070 # STEP 5 - TRANSACTIONS AND TRIGGERS
1071 #========================================================================
1072 #
1073 #
1074 # STEP 5.1 - FORCE STATEMENT ROLLBACK FROM TRIGGER
1075 #
1076 connection con1;
1077 ## Create a trigger to force statement rollback
1078 #
1079 CREATE TRIGGER trigger_before_update BEFORE UPDATE ON t1
1080 FOR EACH ROW
1081 BEGIN
1082 IF OLD.s1 >= 505 THEN
1083 SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR";
1084 END IF;
1085 END;|
1086 #
1087 ## Clear history
1088 connection default;
1089 CALL test.clear_history();
1090 connection con1;
1091 #
1092 ## Insert multiple rows, then update. Trigger will force rollback the
1093 ## UPDATE statement, but the transaction should not roll back.
1094 #
1095 START TRANSACTION;
1096 INSERT INTO t1 VALUES (500, "INSERT 500");
1097 INSERT INTO t1 VALUES (501, "INSERT 501");
1098 INSERT INTO t1 VALUES (502, "INSERT 502");
1099 INSERT INTO t1 VALUES (503, "INSERT 503");
1100 INSERT INTO t1 VALUES (504, "INSERT 504");
1101 INSERT INTO t1 VALUES (505, "INSERT 505");
1102 #
1103 SELECT * FROM t1 ORDER BY s1;
1104 s1 s2
1105 500 INSERT 500
1106 501 INSERT 501
1107 502 INSERT 502
1108 503 INSERT 503
1109 504 INSERT 504
1110 505 INSERT 505
1111 #
1112 ## Expect error when UPDATE hits record 505
1113 #
1114 UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500;
1115 ERROR 45001: FORCE ERROR
1116 #
1117 ## Verify that INSERT succeeded, UPDATE failed and transaction did not rollback
1118 #
1119 SELECT * FROM t1 ORDER BY s1;
1120 s1 s2
1121 500 INSERT 500
1122 501 INSERT 501
1123 502 INSERT 502
1124 503 INSERT 503
1125 504 INSERT 504
1126 505 INSERT 505
1127 COMMIT;
1128 #
1129 DROP TRIGGER trigger_before_update;
1130 connection default;
1131 #========================================================================
1132 # Verify
1133 #========================================================================
1134 SELECT event_id into @base_tx_event_id
1135 from performance_schema.events_transactions_history_long
1136 where (THREAD_ID = @con1_thread_id)
1137 order by event_id limit 1;
1138 SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
1139 where (THREAD_ID = @con1_thread_id)
1140 order by event_id limit 1;
1141 select if(@base_tx_event_id < @base_stmt_event_id,
1142 @base_tx_event_id - 1,
1143 @base_stmt_event_id - 1)
1144 into @base_event_id;
1145 #
1146 # EVENTS_TRANSACTIONS_CURRENT
1147 #
1148 SELECT THREAD_ID,
1149 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
1150 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
1151 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
1152 RPAD(STATE, 11, ' ') 'STATE ',
1153 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
1154 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
1155 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
1156 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
1157 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
1158 FROM performance_schema.events_transactions_current
1159 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
1160 ORDER BY thread_id, event_id;
1161 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
1162 thread_id 21 21 transaction COMMITTED READ WRITE REPEATABLE READ YES 20 STATEMENT
1163 #
1164 # EVENTS_TRANSACTIONS_HISTORY_LONG
1165 #
1166 SELECT THREAD_ID,
1167 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
1168 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
1169 RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
1170 RPAD(STATE, 11, ' ') 'STATE ',
1171 RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
1172 RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
1173 RPAD(AUTOCOMMIT, 4, ' ') AUTO,
1174 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
1175 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
1176 FROM performance_schema.events_transactions_history_long
1177 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
1178 ORDER BY thread_id, event_id;
1179 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME STATE ACCESS_MODE ISOLATION_LEVEL AUTO R_NESTING_EVENT_ID NESTING_EVENT_TYPE
1180 thread_id 2 19 transaction COMMITTED READ WRITE REPEATABLE READ NO 1 STATEMENT
1181 thread_id 21 21 transaction COMMITTED READ WRITE REPEATABLE READ YES 20 STATEMENT
1182 #
1183 # EVENTS_STATEMENTS_HISTORY_LONG
1184 #
1185 SELECT THREAD_ID,
1186 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
1187 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
1188 RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ',
1189 RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
1190 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
1191 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
1192 LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
1193 SQL_TEXT
1194 FROM performance_schema.events_statements_history_long
1195 WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
1196 ORDER BY thread_id, event_id;
1197 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME OBJECT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE LEVEL SQL_TEXT
1198 thread_id 1 2 statement/sql/begin NULL NULL NULL 0 START TRANSACTION
1199 thread_id 3 3 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (500, "INSERT 500")
1200 thread_id 4 4 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (501, "INSERT 501")
1201 thread_id 5 5 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (502, "INSERT 502")
1202 thread_id 6 6 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (503, "INSERT 503")
1203 thread_id 7 7 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (504, "INSERT 504")
1204 thread_id 8 8 statement/sql/insert NULL 2 TRANSACTION 0 INSERT INTO t1 VALUES (505, "INSERT 505")
1205 thread_id 9 9 statement/sql/select NULL 2 TRANSACTION 0 SELECT * FROM t1 ORDER BY s1
1206 thread_id 10 17 statement/sql/update NULL 2 TRANSACTION 0 UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500
1207 thread_id 11 11 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
1208 thread_id 12 12 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
1209 thread_id 13 13 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
1210 thread_id 14 14 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
1211 thread_id 15 15 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
1212 thread_id 16 16 statement/sp/jump_if_not trigger_befo 10 STATEMENT 1 NULL
1213 thread_id 17 17 statement/sp/stmt trigger_befo 10 STATEMENT 1 SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR"
1214 thread_id 18 18 statement/sql/select NULL 2 TRANSACTION 0 SELECT * FROM t1 ORDER BY s1
1215 thread_id 19 19 statement/sql/commit NULL 2 TRANSACTION 0 COMMIT
1216 thread_id 20 21 statement/sql/drop_trigger NULL NULL NULL 0 DROP TRIGGER trigger_before_update
1217 #
1218 ### Combined statement and transaction event history ordered by event id
1219 #
1220 #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
1221 #
1222 SELECT THREAD_ID,
1223 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
1224 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
1225 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
1226 LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
1227 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
1228 '<transaction started>' AS SQL_TXT
1229 FROM performance_schema.events_transactions_history_long t
1230 WHERE (t.thread_id = @con1_thread_id)
1231 UNION
1232 SELECT THREAD_ID,
1233 LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
1234 LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
1235 RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
1236 LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
1237 RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
1238 SQL_TEXT
1239 FROM performance_schema.events_statements_history_long s
1240 WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
1241 ORDER BY thread_id, r_event_id;
1242 THREAD_ID R_EVENT_ID R_END_EVENT_ID EVENT_NAME R_NESTING_EVENT_ID NESTING_EVENT_TYPE SQL_TXT
1243 thread_id 1 2 statement/sql/begin NULL NULL START TRANSACTION
1244 thread_id 2 19 transaction 1 STATEMENT <transaction started>
1245 thread_id 3 3 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (500, "INSERT 500")
1246 thread_id 4 4 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (501, "INSERT 501")
1247 thread_id 5 5 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (502, "INSERT 502")
1248 thread_id 6 6 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (503, "INSERT 503")
1249 thread_id 7 7 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (504, "INSERT 504")
1250 thread_id 8 8 statement/sql/insert 2 TRANSACTION INSERT INTO t1 VALUES (505, "INSERT 505")
1251 thread_id 9 9 statement/sql/select 2 TRANSACTION SELECT * FROM t1 ORDER BY s1
1252 thread_id 10 17 statement/sql/update 2 TRANSACTION UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500
1253 thread_id 11 11 statement/sp/jump_if_not 10 STATEMENT NULL
1254 thread_id 12 12 statement/sp/jump_if_not 10 STATEMENT NULL
1255 thread_id 13 13 statement/sp/jump_if_not 10 STATEMENT NULL
1256 thread_id 14 14 statement/sp/jump_if_not 10 STATEMENT NULL
1257 thread_id 15 15 statement/sp/jump_if_not 10 STATEMENT NULL
1258 thread_id 16 16 statement/sp/jump_if_not 10 STATEMENT NULL
1259 thread_id 17 17 statement/sp/stmt 10 STATEMENT SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR"
1260 thread_id 18 18 statement/sql/select 2 TRANSACTION SELECT * FROM t1 ORDER BY s1
1261 thread_id 19 19 statement/sql/commit 2 TRANSACTION COMMIT
1262 thread_id 20 21 statement/sql/drop_trigge NULL NULL DROP TRIGGER trigger_before_update
1263 thread_id 21 21 transaction 20 STATEMENT <transaction started>
1264 #
1265 ### Clear statement and transaction history
1266 # CALL test.clear_history();
1267 CALL test.clear_history();
1268 ## Reset db.t1
1269 DELETE FROM db.t1;
1270 #
1271 # TODO: Detect statement events from scheduled event
1272 #=======================================================================
1273 # Cleanup
1274 #=======================================================================
1275 connection con1;
1276 disconnect con1;
1277 connection default;
1278 DROP DATABASE db;
1279 UPDATE performance_schema.setup_instruments SET enabled='YES', timed='YES';
1280 DROP PROCEDURE clear_transaction_tables;
1281 DROP PROCEDURE clear_transaction_history;
1282 DROP PROCEDURE clear_statement_history;
1283 DROP PROCEDURE clear_history;
1284 DROP PROCEDURE transaction_verifier;