#
#========================================================================
# STEP 1 - SETUP
#========================================================================
#
## Setup control thread
#
connection default;
SET SESSION AUTOCOMMIT= 1;
USE test;
DROP DATABASE IF EXISTS db;
#
## Create test database, test tables, one transactional and one non-transactional
CREATE DATABASE db;
CREATE TABLE db.t1 (s1 int, s2 varchar(64)) ENGINE=INNODB;
CREATE TABLE db.nt1 (s1 int, s2 varchar(64)) ENGINE=MYISAM;
#
## Setup connection 1
connect con1, localhost, root,,;
USE db;
SET SESSION AUTOCOMMIT = 1;
SELECT thread_id INTO @my_thread_id
FROM performance_schema.threads
WHERE processlist_id = connection_id();
connection default;
#
## Disable events from the control (default) connection
UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID();
SET @all_threads= 0;
#
## Enable only transaction and statement instruments
UPDATE performance_schema.setup_instruments SET enabled='NO', timed='NO';
UPDATE performance_schema.setup_instruments SET enabled='YES'
  WHERE name LIKE ('statement/%') OR name = 'transaction';
#
## Clear statement and transaction history
CALL test.clear_history();
#
#========================================================================
# STEP 2 - BASIC TRANSACTION
#========================================================================
#
# STEP 2.1 - IMPLICIT
#
connection con1;
INSERT INTO t1 VALUES (210, "INSERT 210");
INSERT INTO t1 VALUES (211, "INSERT 211");
INSERT INTO t1 VALUES (212, "INSERT 212");
UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212;
connection default;
#========================================================================
# Verify
#========================================================================
SELECT event_id into @base_tx_event_id
from performance_schema.events_transactions_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
select if(@base_tx_event_id < @base_stmt_event_id,
@base_tx_event_id - 1,
@base_stmt_event_id - 1)
into @base_event_id;
#
# EVENTS_TRANSACTIONS_CURRENT
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_current
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          8	          8	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  7	STATEMENT         
#
# EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          2	          2	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  1	STATEMENT         
thread_id	          4	          4	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  3	STATEMENT         
thread_id	          6	          6	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  5	STATEMENT         
thread_id	          8	          8	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  7	STATEMENT         
#
# EVENTS_STATEMENTS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME                    ',
RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME                    	OBJECT_NAME 	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	LEVEL	SQL_TEXT
thread_id	          1	          2	statement/sql/insert          	NULL        	               NULL	NULL              	    0	INSERT INTO t1 VALUES (210, "INSERT 210")
thread_id	          3	          4	statement/sql/insert          	NULL        	               NULL	NULL              	    0	INSERT INTO t1 VALUES (211, "INSERT 211")
thread_id	          5	          6	statement/sql/insert          	NULL        	               NULL	NULL              	    0	INSERT INTO t1 VALUES (212, "INSERT 212")
thread_id	          7	          8	statement/sql/update          	NULL        	               NULL	NULL              	    0	UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212
#
### Combined statement and transaction event history ordered by event id
#
#EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
'<transaction started>' AS SQL_TXT
FROM performance_schema.events_transactions_history_long t
WHERE (t.thread_id = @con1_thread_id)
UNION
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
SQL_TEXT
FROM performance_schema.events_statements_history_long s
WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, r_event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME               	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	SQL_TXT
thread_id	          1	          2	statement/sql/insert     	               NULL	NULL              	INSERT INTO t1 VALUES (210, "INSERT 210")
thread_id	          2	          2	transaction              	                  1	STATEMENT         	<transaction started>
thread_id	          3	          4	statement/sql/insert     	               NULL	NULL              	INSERT INTO t1 VALUES (211, "INSERT 211")
thread_id	          4	          4	transaction              	                  3	STATEMENT         	<transaction started>
thread_id	          5	          6	statement/sql/insert     	               NULL	NULL              	INSERT INTO t1 VALUES (212, "INSERT 212")
thread_id	          6	          6	transaction              	                  5	STATEMENT         	<transaction started>
thread_id	          7	          8	statement/sql/update     	               NULL	NULL              	UPDATE t1 SET s1 = s1 + 1 WHERE s1 = 212
thread_id	          8	          8	transaction              	                  7	STATEMENT         	<transaction started>
#
### Clear statement and transaction history
# CALL test.clear_history();
CALL test.clear_history();
## Reset db.t1
DELETE FROM db.t1;
#
#
# STEP 2.2 - EXPLICIT
#
connection con1;
START TRANSACTION;
INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221");
UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221;
COMMIT;
connection default;
#========================================================================
# Verify
#========================================================================
SELECT event_id into @base_tx_event_id
from performance_schema.events_transactions_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
select if(@base_tx_event_id < @base_stmt_event_id,
@base_tx_event_id - 1,
@base_stmt_event_id - 1)
into @base_event_id;
#
# EVENTS_TRANSACTIONS_CURRENT
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_current
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          2	          5	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  1	STATEMENT         
#
# EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          2	          5	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  1	STATEMENT         
#
# EVENTS_STATEMENTS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME                    ',
RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME                    	OBJECT_NAME 	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	LEVEL	SQL_TEXT
thread_id	          1	          2	statement/sql/begin           	NULL        	               NULL	NULL              	    0	START TRANSACTION
thread_id	          3	          3	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221")
thread_id	          4	          4	statement/sql/update          	NULL        	                  2	TRANSACTION       	    0	UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221
thread_id	          5	          5	statement/sql/commit          	NULL        	                  2	TRANSACTION       	    0	COMMIT
#
### Combined statement and transaction event history ordered by event id
#
#EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
'<transaction started>' AS SQL_TXT
FROM performance_schema.events_transactions_history_long t
WHERE (t.thread_id = @con1_thread_id)
UNION
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
SQL_TEXT
FROM performance_schema.events_statements_history_long s
WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, r_event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME               	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	SQL_TXT
thread_id	          1	          2	statement/sql/begin      	               NULL	NULL              	START TRANSACTION
thread_id	          2	          5	transaction              	                  1	STATEMENT         	<transaction started>
thread_id	          3	          3	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221")
thread_id	          4	          4	statement/sql/update     	                  2	TRANSACTION       	UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221
thread_id	          5	          5	statement/sql/commit     	                  2	TRANSACTION       	COMMIT
#
### Clear statement and transaction history
# CALL test.clear_history();
CALL test.clear_history();
## Reset db.t1
DELETE FROM db.t1;
#
#========================================================================
# STEP 3 - TRANSACTIONS AND STORED PROCEDURES
#========================================================================
#
# STEP 3.1 - STORED PROCEDURE STARTED WITHIN TRANSACTION
#
connection con1;
CREATE PROCEDURE tp_update() UPDATE t1 SET s1 = s1 + 1;
#
START TRANSACTION;
INSERT INTO t1 VALUES (310, "INSERT 310");
INSERT INTO t1 VALUES (311, "INSERT 311");
INSERT INTO t1 VALUES (312, "INSERT 312");
INSERT INTO t1 VALUES (313, "INSERT 313");
CALL tp_update();
COMMIT;
connection default;
#========================================================================
# Verify
#========================================================================
SELECT event_id into @base_tx_event_id
from performance_schema.events_transactions_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
select if(@base_tx_event_id < @base_stmt_event_id,
@base_tx_event_id - 1,
@base_stmt_event_id - 1)
into @base_event_id;
#
# EVENTS_TRANSACTIONS_CURRENT
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_current
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	         10	         12	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  9	STATEMENT         
#
# EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          2	          2	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  1	STATEMENT         
thread_id	         10	         10	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  9	STATEMENT         
thread_id	         10	         12	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  9	STATEMENT         
#
# EVENTS_STATEMENTS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME                    ',
RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME                    	OBJECT_NAME 	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	LEVEL	SQL_TEXT
thread_id	          1	          2	statement/sql/create_procedure	NULL        	               NULL	NULL              	    0	CREATE PROCEDURE tp_update() UPDATE t1 SET s1 = s1 + 1
thread_id	          3	          4	statement/sql/begin           	NULL        	               NULL	NULL              	    0	START TRANSACTION
thread_id	          5	          5	statement/sql/insert          	NULL        	                  4	TRANSACTION       	    0	INSERT INTO t1 VALUES (310, "INSERT 310")
thread_id	          6	          6	statement/sql/insert          	NULL        	                  4	TRANSACTION       	    0	INSERT INTO t1 VALUES (311, "INSERT 311")
thread_id	          7	          7	statement/sql/insert          	NULL        	                  4	TRANSACTION       	    0	INSERT INTO t1 VALUES (312, "INSERT 312")
thread_id	          8	          8	statement/sql/insert          	NULL        	                  4	TRANSACTION       	    0	INSERT INTO t1 VALUES (313, "INSERT 313")
thread_id	          9	         11	statement/sql/call_procedure  	NULL        	                  4	TRANSACTION       	    0	CALL tp_update()
thread_id	         11	         11	statement/sp/stmt             	tp_update   	                  9	STATEMENT         	    1	UPDATE t1 SET s1 = s1 + 1
thread_id	         12	         12	statement/sql/commit          	NULL        	               NULL	NULL              	    0	COMMIT
#
### Combined statement and transaction event history ordered by event id
#
#EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
'<transaction started>' AS SQL_TXT
FROM performance_schema.events_transactions_history_long t
WHERE (t.thread_id = @con1_thread_id)
UNION
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
SQL_TEXT
FROM performance_schema.events_statements_history_long s
WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, r_event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME               	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	SQL_TXT
thread_id	          1	          2	statement/sql/create_proc	               NULL	NULL              	CREATE PROCEDURE tp_update() UPDATE t1 SET s1 = s1 + 1
thread_id	          2	          2	transaction              	                  1	STATEMENT         	<transaction started>
thread_id	          3	          4	statement/sql/begin      	               NULL	NULL              	START TRANSACTION
thread_id	          5	          5	statement/sql/insert     	                  4	TRANSACTION       	INSERT INTO t1 VALUES (310, "INSERT 310")
thread_id	          6	          6	statement/sql/insert     	                  4	TRANSACTION       	INSERT INTO t1 VALUES (311, "INSERT 311")
thread_id	          7	          7	statement/sql/insert     	                  4	TRANSACTION       	INSERT INTO t1 VALUES (312, "INSERT 312")
thread_id	          8	          8	statement/sql/insert     	                  4	TRANSACTION       	INSERT INTO t1 VALUES (313, "INSERT 313")
thread_id	          9	         11	statement/sql/call_proced	                  4	TRANSACTION       	CALL tp_update()
thread_id	         10	         10	transaction              	                  9	STATEMENT         	<transaction started>
thread_id	         10	         12	transaction              	                  9	STATEMENT         	<transaction started>
thread_id	         11	         11	statement/sp/stmt        	                  9	STATEMENT         	UPDATE t1 SET s1 = s1 + 1
thread_id	         12	         12	statement/sql/commit     	               NULL	NULL              	COMMIT
#
### Clear statement and transaction history
# CALL test.clear_history();
CALL test.clear_history();
## Reset db.t1
DELETE FROM db.t1;
#
#
# STEP 3.2 - TRANSACTION STARTED WITHIN STORED PROCEDURE
#
connection con1;
CREATE PROCEDURE tp_start() START TRANSACTION;
#
CALL tp_start();
INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321");
INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323");
UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320;
#
SELECT * FROM t1 ORDER BY s1;
s1	s2
320	INSERT 320
322	INSERT 321
323	INSERT 322
324	INSERT 323
COMMIT;
connection default;
#========================================================================
# Verify
#========================================================================
SELECT event_id into @base_tx_event_id
from performance_schema.events_transactions_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
select if(@base_tx_event_id < @base_stmt_event_id,
@base_tx_event_id - 1,
@base_stmt_event_id - 1)
into @base_event_id;
#
# EVENTS_TRANSACTIONS_CURRENT
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_current
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          6	         11	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  5	STATEMENT         
#
# EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          2	          2	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  1	STATEMENT         
thread_id	          4	          4	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  3	STATEMENT         
thread_id	          6	         11	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  5	STATEMENT         
#
# EVENTS_STATEMENTS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME                    ',
RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME                    	OBJECT_NAME 	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	LEVEL	SQL_TEXT
thread_id	          1	          2	statement/sql/create_procedure	NULL        	               NULL	NULL              	    0	CREATE PROCEDURE tp_start() START TRANSACTION
thread_id	          3	          6	statement/sql/call_procedure  	NULL        	               NULL	NULL              	    0	CALL tp_start()
thread_id	          5	          6	statement/sp/stmt             	tp_start    	                  3	STATEMENT         	    1	START TRANSACTION
thread_id	          7	          7	statement/sql/insert          	NULL        	                  6	TRANSACTION       	    0	INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321")
thread_id	          8	          8	statement/sql/insert          	NULL        	                  6	TRANSACTION       	    0	INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323")
thread_id	          9	          9	statement/sql/update          	NULL        	                  6	TRANSACTION       	    0	UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320
thread_id	         10	         10	statement/sql/select          	NULL        	                  6	TRANSACTION       	    0	SELECT * FROM t1 ORDER BY s1
thread_id	         11	         11	statement/sql/commit          	NULL        	                  6	TRANSACTION       	    0	COMMIT
#
### Combined statement and transaction event history ordered by event id
#
#EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
'<transaction started>' AS SQL_TXT
FROM performance_schema.events_transactions_history_long t
WHERE (t.thread_id = @con1_thread_id)
UNION
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
SQL_TEXT
FROM performance_schema.events_statements_history_long s
WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, r_event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME               	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	SQL_TXT
thread_id	          1	          2	statement/sql/create_proc	               NULL	NULL              	CREATE PROCEDURE tp_start() START TRANSACTION
thread_id	          2	          2	transaction              	                  1	STATEMENT         	<transaction started>
thread_id	          3	          6	statement/sql/call_proced	               NULL	NULL              	CALL tp_start()
thread_id	          4	          4	transaction              	                  3	STATEMENT         	<transaction started>
thread_id	          5	          6	statement/sp/stmt        	                  3	STATEMENT         	START TRANSACTION
thread_id	          6	         11	transaction              	                  5	STATEMENT         	<transaction started>
thread_id	          7	          7	statement/sql/insert     	                  6	TRANSACTION       	INSERT INTO t1 VALUES (320, "INSERT 320"),(321, "INSERT 321")
thread_id	          8	          8	statement/sql/insert     	                  6	TRANSACTION       	INSERT INTO t1 VALUES (322, "INSERT 322"),(323, "INSERT 323")
thread_id	          9	          9	statement/sql/update     	                  6	TRANSACTION       	UPDATE t1 SET s1 = s1 + 1 WHERE s1 > 320
thread_id	         10	         10	statement/sql/select     	                  6	TRANSACTION       	SELECT * FROM t1 ORDER BY s1
thread_id	         11	         11	statement/sql/commit     	                  6	TRANSACTION       	COMMIT
#
### Clear statement and transaction history
# CALL test.clear_history();
CALL test.clear_history();
## Reset db.t1
DELETE FROM db.t1;
#
#
# STEP 3.3 - TRANSACTION ENDED WITHIN STORED PROCEDURE
#
connection con1;
CREATE PROCEDURE tp_rollback() ROLLBACK;
CREATE PROCEDURE tp_commit() COMMIT;
#
## COMMIT within stored procedure
START TRANSACTION;
INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331");
INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333");
DELETE FROM t1 WHERE s1 > 331;
CALL tp_commit();
#
SELECT * FROM t1 ORDER BY s1;
s1	s2
330	INSERT 330
331	INSERT 331
#
## ROLLBACK within stored procedure
START TRANSACTION;
UPDATE t1 SET s1 = s1*2 WHERE s1 > 331;
CALL tp_rollback();
#
SELECT * FROM t1 ORDER BY s1;
s1	s2
330	INSERT 330
331	INSERT 331
connection default;
#========================================================================
# Verify
#========================================================================
SELECT event_id into @base_tx_event_id
from performance_schema.events_transactions_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
select if(@base_tx_event_id < @base_stmt_event_id,
@base_tx_event_id - 1,
@base_stmt_event_id - 1)
into @base_event_id;
#
# EVENTS_TRANSACTIONS_CURRENT
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_current
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	         22	         22	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                 21	STATEMENT         
#
# EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          2	          2	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  1	STATEMENT         
thread_id	          4	          4	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  3	STATEMENT         
thread_id	         11	         11	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                 10	STATEMENT         
thread_id	         11	         12	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                 10	STATEMENT         
thread_id	         14	         14	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                 13	STATEMENT         
thread_id	         19	         19	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                 18	STATEMENT         
thread_id	         19	         20	transaction	ROLLED BACK	READ WRITE 	REPEATABLE READ 	NO  	                 18	STATEMENT         
thread_id	         22	         22	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                 21	STATEMENT         
#
# EVENTS_STATEMENTS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME                    ',
RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME                    	OBJECT_NAME 	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	LEVEL	SQL_TEXT
thread_id	          1	          2	statement/sql/create_procedure	NULL        	               NULL	NULL              	    0	CREATE PROCEDURE tp_rollback() ROLLBACK
thread_id	          3	          4	statement/sql/create_procedure	NULL        	               NULL	NULL              	    0	CREATE PROCEDURE tp_commit() COMMIT
thread_id	          5	          6	statement/sql/begin           	NULL        	               NULL	NULL              	    0	START TRANSACTION
thread_id	          7	          7	statement/sql/insert          	NULL        	                  6	TRANSACTION       	    0	INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331")
thread_id	          8	          8	statement/sql/insert          	NULL        	                  6	TRANSACTION       	    0	INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333")
thread_id	          9	          9	statement/sql/delete          	NULL        	                  6	TRANSACTION       	    0	DELETE FROM t1 WHERE s1 > 331
thread_id	         10	         12	statement/sql/call_procedure  	NULL        	                  6	TRANSACTION       	    0	CALL tp_commit()
thread_id	         12	         12	statement/sp/stmt             	tp_commit   	                 10	STATEMENT         	    1	COMMIT
thread_id	         13	         14	statement/sql/select          	NULL        	               NULL	NULL              	    0	SELECT * FROM t1 ORDER BY s1
thread_id	         15	         16	statement/sql/begin           	NULL        	               NULL	NULL              	    0	START TRANSACTION
thread_id	         17	         17	statement/sql/update          	NULL        	                 16	TRANSACTION       	    0	UPDATE t1 SET s1 = s1*2 WHERE s1 > 331
thread_id	         18	         20	statement/sql/call_procedure  	NULL        	                 16	TRANSACTION       	    0	CALL tp_rollback()
thread_id	         20	         20	statement/sp/stmt             	tp_rollback 	                 18	STATEMENT         	    1	ROLLBACK
thread_id	         21	         22	statement/sql/select          	NULL        	               NULL	NULL              	    0	SELECT * FROM t1 ORDER BY s1
#
### Combined statement and transaction event history ordered by event id
#
#EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
'<transaction started>' AS SQL_TXT
FROM performance_schema.events_transactions_history_long t
WHERE (t.thread_id = @con1_thread_id)
UNION
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
SQL_TEXT
FROM performance_schema.events_statements_history_long s
WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, r_event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME               	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	SQL_TXT
thread_id	          1	          2	statement/sql/create_proc	               NULL	NULL              	CREATE PROCEDURE tp_rollback() ROLLBACK
thread_id	          2	          2	transaction              	                  1	STATEMENT         	<transaction started>
thread_id	          3	          4	statement/sql/create_proc	               NULL	NULL              	CREATE PROCEDURE tp_commit() COMMIT
thread_id	          4	          4	transaction              	                  3	STATEMENT         	<transaction started>
thread_id	          5	          6	statement/sql/begin      	               NULL	NULL              	START TRANSACTION
thread_id	          7	          7	statement/sql/insert     	                  6	TRANSACTION       	INSERT INTO t1 VALUES (330, "INSERT 330"),(331, "INSERT 331")
thread_id	          8	          8	statement/sql/insert     	                  6	TRANSACTION       	INSERT INTO t1 VALUES (332, "INSERT 332"),(333, "INSERT 333")
thread_id	          9	          9	statement/sql/delete     	                  6	TRANSACTION       	DELETE FROM t1 WHERE s1 > 331
thread_id	         10	         12	statement/sql/call_proced	                  6	TRANSACTION       	CALL tp_commit()
thread_id	         11	         11	transaction              	                 10	STATEMENT         	<transaction started>
thread_id	         11	         12	transaction              	                 10	STATEMENT         	<transaction started>
thread_id	         12	         12	statement/sp/stmt        	                 10	STATEMENT         	COMMIT
thread_id	         13	         14	statement/sql/select     	               NULL	NULL              	SELECT * FROM t1 ORDER BY s1
thread_id	         14	         14	transaction              	                 13	STATEMENT         	<transaction started>
thread_id	         15	         16	statement/sql/begin      	               NULL	NULL              	START TRANSACTION
thread_id	         17	         17	statement/sql/update     	                 16	TRANSACTION       	UPDATE t1 SET s1 = s1*2 WHERE s1 > 331
thread_id	         18	         20	statement/sql/call_proced	                 16	TRANSACTION       	CALL tp_rollback()
thread_id	         19	         19	transaction              	                 18	STATEMENT         	<transaction started>
thread_id	         19	         20	transaction              	                 18	STATEMENT         	<transaction started>
thread_id	         20	         20	statement/sp/stmt        	                 18	STATEMENT         	ROLLBACK
thread_id	         21	         22	statement/sql/select     	               NULL	NULL              	SELECT * FROM t1 ORDER BY s1
thread_id	         22	         22	transaction              	                 21	STATEMENT         	<transaction started>
#
### Clear statement and transaction history
# CALL test.clear_history();
CALL test.clear_history();
## Reset db.t1
DELETE FROM db.t1;
#
#========================================================================
# STEP 4 - TRANSACTIONS AND STORED FUNCTIONS
#========================================================================
#
#
# STEP 4.1 - FUNCTION WITHIN A TRANSACTION
#
connection con1;
CREATE FUNCTION fn_add(x INT, y INT) RETURNS INT
BEGIN
INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y");
RETURN x+y;
END |
#
## Clear history
connection default;
CALL test.clear_history();
connection con1;
#
START TRANSACTION;
INSERT INTO t1 VALUES (410, "INSERT 410");
INSERT INTO t1 VALUES (411, "INSERT 411");
INSERT INTO t1 VALUES (412, "INSERT 412");
DELETE FROM t1 WHERE s1 > 410;
#
SELECT * FROM t1 ORDER BY s1;
s1	s2
410	INSERT 410
#
SELECT fn_add(413, 414);
fn_add(413, 414)
827
COMMIT;
#
SELECT * FROM t1 ORDER BY s1;
s1	s2
410	INSERT 410
413	INSERT x
414	INSERT y
connection default;
#========================================================================
# Verify
#========================================================================
SELECT event_id into @base_tx_event_id
from performance_schema.events_transactions_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
select if(@base_tx_event_id < @base_stmt_event_id,
@base_tx_event_id - 1,
@base_stmt_event_id - 1)
into @base_event_id;
#
# EVENTS_TRANSACTIONS_CURRENT
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_current
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	         14	         14	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                 13	STATEMENT         
#
# EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          9	          9	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  8	STATEMENT         
thread_id	          9	         12	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  8	STATEMENT         
thread_id	         14	         14	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                 13	STATEMENT         
#
# EVENTS_STATEMENTS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME                    ',
RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME                    	OBJECT_NAME 	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	LEVEL	SQL_TEXT
thread_id	          1	          2	statement/sql/begin           	NULL        	               NULL	NULL              	    0	START TRANSACTION
thread_id	          3	          3	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (410, "INSERT 410")
thread_id	          4	          4	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (411, "INSERT 411")
thread_id	          5	          5	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (412, "INSERT 412")
thread_id	          6	          6	statement/sql/delete          	NULL        	                  2	TRANSACTION       	    0	DELETE FROM t1 WHERE s1 > 410
thread_id	          7	          7	statement/sql/select          	NULL        	                  2	TRANSACTION       	    0	SELECT * FROM t1 ORDER BY s1
thread_id	          8	         11	statement/sql/select          	NULL        	                  2	TRANSACTION       	    0	SELECT fn_add(413, 414)
thread_id	         10	         10	statement/sp/stmt             	fn_add      	                  8	STATEMENT         	    1	INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
thread_id	         11	         11	statement/sp/freturn          	fn_add      	                  8	STATEMENT         	    1	NULL
thread_id	         12	         12	statement/sql/commit          	NULL        	               NULL	NULL              	    0	COMMIT
thread_id	         13	         14	statement/sql/select          	NULL        	               NULL	NULL              	    0	SELECT * FROM t1 ORDER BY s1
#
### Combined statement and transaction event history ordered by event id
#
#EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
'<transaction started>' AS SQL_TXT
FROM performance_schema.events_transactions_history_long t
WHERE (t.thread_id = @con1_thread_id)
UNION
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
SQL_TEXT
FROM performance_schema.events_statements_history_long s
WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, r_event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME               	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	SQL_TXT
thread_id	          1	          2	statement/sql/begin      	               NULL	NULL              	START TRANSACTION
thread_id	          3	          3	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (410, "INSERT 410")
thread_id	          4	          4	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (411, "INSERT 411")
thread_id	          5	          5	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (412, "INSERT 412")
thread_id	          6	          6	statement/sql/delete     	                  2	TRANSACTION       	DELETE FROM t1 WHERE s1 > 410
thread_id	          7	          7	statement/sql/select     	                  2	TRANSACTION       	SELECT * FROM t1 ORDER BY s1
thread_id	          8	         11	statement/sql/select     	                  2	TRANSACTION       	SELECT fn_add(413, 414)
thread_id	          9	          9	transaction              	                  8	STATEMENT         	<transaction started>
thread_id	          9	         12	transaction              	                  8	STATEMENT         	<transaction started>
thread_id	         10	         10	statement/sp/stmt        	                  8	STATEMENT         	INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
thread_id	         11	         11	statement/sp/freturn     	                  8	STATEMENT         	NULL
thread_id	         12	         12	statement/sql/commit     	               NULL	NULL              	COMMIT
thread_id	         13	         14	statement/sql/select     	               NULL	NULL              	SELECT * FROM t1 ORDER BY s1
thread_id	         14	         14	transaction              	                 13	STATEMENT         	<transaction started>
#
### Clear statement and transaction history
# CALL test.clear_history();
CALL test.clear_history();
## Reset db.t1
DELETE FROM db.t1;
#
connection con1;
#
## Again, but this time with a rollback
#
START TRANSACTION;
SELECT fn_add(415, 416);
fn_add(415, 416)
831
#
ROLLBACK;
#
SELECT * FROM t1 ORDER BY s1;
s1	s2
connection default;
#========================================================================
# Verify
#========================================================================
SELECT event_id into @base_tx_event_id
from performance_schema.events_transactions_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
select if(@base_tx_event_id < @base_stmt_event_id,
@base_tx_event_id - 1,
@base_stmt_event_id - 1)
into @base_event_id;
#
# EVENTS_TRANSACTIONS_CURRENT
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_current
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          8	          8	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  7	STATEMENT         
#
# EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          2	          6	transaction	ROLLED BACK	READ WRITE 	REPEATABLE READ 	NO  	                  1	STATEMENT         
thread_id	          8	          8	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                  7	STATEMENT         
#
# EVENTS_STATEMENTS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME                    ',
RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME                    	OBJECT_NAME 	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	LEVEL	SQL_TEXT
thread_id	          1	          2	statement/sql/begin           	NULL        	               NULL	NULL              	    0	START TRANSACTION
thread_id	          3	          5	statement/sql/select          	NULL        	                  2	TRANSACTION       	    0	SELECT fn_add(415, 416)
thread_id	          4	          4	statement/sp/stmt             	fn_add      	                  3	STATEMENT         	    1	INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
thread_id	          5	          5	statement/sp/freturn          	fn_add      	                  3	STATEMENT         	    1	NULL
thread_id	          6	          6	statement/sql/rollback        	NULL        	                  2	TRANSACTION       	    0	ROLLBACK
thread_id	          7	          8	statement/sql/select          	NULL        	               NULL	NULL              	    0	SELECT * FROM t1 ORDER BY s1
#
### Combined statement and transaction event history ordered by event id
#
#EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
'<transaction started>' AS SQL_TXT
FROM performance_schema.events_transactions_history_long t
WHERE (t.thread_id = @con1_thread_id)
UNION
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
SQL_TEXT
FROM performance_schema.events_statements_history_long s
WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, r_event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME               	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	SQL_TXT
thread_id	          1	          2	statement/sql/begin      	               NULL	NULL              	START TRANSACTION
thread_id	          2	          6	transaction              	                  1	STATEMENT         	<transaction started>
thread_id	          3	          5	statement/sql/select     	                  2	TRANSACTION       	SELECT fn_add(415, 416)
thread_id	          4	          4	statement/sp/stmt        	                  3	STATEMENT         	INSERT INTO t1 VALUES (x, "INSERT x"),(y, "INSERT y")
thread_id	          5	          5	statement/sp/freturn     	                  3	STATEMENT         	NULL
thread_id	          6	          6	statement/sql/rollback   	                  2	TRANSACTION       	ROLLBACK
thread_id	          7	          8	statement/sql/select     	               NULL	NULL              	SELECT * FROM t1 ORDER BY s1
thread_id	          8	          8	transaction              	                  7	STATEMENT         	<transaction started>
#
### Clear statement and transaction history
# CALL test.clear_history();
CALL test.clear_history();
## Reset db.t1
DELETE FROM db.t1;
#
#
# STEP 4.2 - TRANSACTION CANNOT BE STARTED OR ENDED WITHIN FUNCTION
#
connection con1;
CREATE FUNCTION fn_err1() RETURNS VARCHAR(10) BEGIN START TRANSACTION ; RETURN 'invalid' ; END|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
#
## Expect 0 transactions
connection default;
SELECT COUNT(*) FROM performance_schema.events_transactions_history;
COUNT(*)
0
connection con1;
#
## Expect stored function does not exist
#
SELECT fn_err1();
ERROR 42000: FUNCTION db.fn_err1 does not exist
#
## Expect 1 transactions
connection default;
SELECT COUNT(*) FROM performance_schema.events_transactions_history;
COUNT(*)
1
connection con1;
#
CREATE FUNCTION fn_err2() RETURNS VARCHAR(10) BEGIN COMMIT; RETURN 'invalid' ; END|
ERROR HY000: Explicit or implicit commit is not allowed in stored function or trigger
#
## Expect stored function does not exist
#
START TRANSACTION;
DELETE FROM t1 WHERE s1 > 320;
SELECT fn_err2();
ERROR 42000: FUNCTION db.fn_err2 does not exist
#
## Expect 2 transactions
connection default;
SELECT COUNT(*) FROM performance_schema.events_transactions_history;
COUNT(*)
2
#
## Clear transaction and statement tables
CALL test.clear_history();
#========================================================================
# STEP 5 - TRANSACTIONS AND TRIGGERS
#========================================================================
#
#
# STEP 5.1 - FORCE STATEMENT ROLLBACK FROM TRIGGER
#
connection con1;
## Create a trigger to force statement rollback
#
CREATE TRIGGER trigger_before_update BEFORE UPDATE ON t1
FOR EACH ROW
BEGIN
IF OLD.s1 >= 505 THEN
SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR";
END IF;
END;|
#
## Clear history
connection default;
CALL test.clear_history();
connection con1;
#
## Insert multiple rows, then update. Trigger will force rollback the
## UPDATE statement, but the transaction should not roll back.
#
START TRANSACTION;
INSERT INTO t1 VALUES (500, "INSERT 500");
INSERT INTO t1 VALUES (501, "INSERT 501");
INSERT INTO t1 VALUES (502, "INSERT 502");
INSERT INTO t1 VALUES (503, "INSERT 503");
INSERT INTO t1 VALUES (504, "INSERT 504");
INSERT INTO t1 VALUES (505, "INSERT 505");
#
SELECT * FROM t1 ORDER BY s1;
s1	s2
500	INSERT 500
501	INSERT 501
502	INSERT 502
503	INSERT 503
504	INSERT 504
505	INSERT 505
#
## Expect error when UPDATE hits record 505
#
UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500;
ERROR 45001: FORCE ERROR
#
## Verify that INSERT succeeded, UPDATE failed and transaction did not rollback
#
SELECT * FROM t1 ORDER BY s1;
s1	s2
500	INSERT 500
501	INSERT 501
502	INSERT 502
503	INSERT 503
504	INSERT 504
505	INSERT 505
COMMIT;
#
DROP TRIGGER trigger_before_update;
connection default;
#========================================================================
# Verify
#========================================================================
SELECT event_id into @base_tx_event_id
from performance_schema.events_transactions_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
select if(@base_tx_event_id < @base_stmt_event_id,
@base_tx_event_id - 1,
@base_stmt_event_id - 1)
into @base_event_id;
#
# EVENTS_TRANSACTIONS_CURRENT
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_current
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	         21	         21	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                 20	STATEMENT         
#
# EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE      ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME 	STATE      	ACCESS_MODE	ISOLATION_LEVEL 	AUTO	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE
thread_id	          2	         19	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	NO  	                  1	STATEMENT         
thread_id	         21	         21	transaction	COMMITTED  	READ WRITE 	REPEATABLE READ 	YES 	                 20	STATEMENT         
#
# EVENTS_STATEMENTS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME                    ',
RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME                    	OBJECT_NAME 	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	LEVEL	SQL_TEXT
thread_id	          1	          2	statement/sql/begin           	NULL        	               NULL	NULL              	    0	START TRANSACTION
thread_id	          3	          3	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (500, "INSERT 500")
thread_id	          4	          4	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (501, "INSERT 501")
thread_id	          5	          5	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (502, "INSERT 502")
thread_id	          6	          6	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (503, "INSERT 503")
thread_id	          7	          7	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (504, "INSERT 504")
thread_id	          8	          8	statement/sql/insert          	NULL        	                  2	TRANSACTION       	    0	INSERT INTO t1 VALUES (505, "INSERT 505")
thread_id	          9	          9	statement/sql/select          	NULL        	                  2	TRANSACTION       	    0	SELECT * FROM t1 ORDER BY s1
thread_id	         10	         17	statement/sql/update          	NULL        	                  2	TRANSACTION       	    0	UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500
thread_id	         11	         11	statement/sp/jump_if_not      	trigger_befo	                 10	STATEMENT         	    1	NULL
thread_id	         12	         12	statement/sp/jump_if_not      	trigger_befo	                 10	STATEMENT         	    1	NULL
thread_id	         13	         13	statement/sp/jump_if_not      	trigger_befo	                 10	STATEMENT         	    1	NULL
thread_id	         14	         14	statement/sp/jump_if_not      	trigger_befo	                 10	STATEMENT         	    1	NULL
thread_id	         15	         15	statement/sp/jump_if_not      	trigger_befo	                 10	STATEMENT         	    1	NULL
thread_id	         16	         16	statement/sp/jump_if_not      	trigger_befo	                 10	STATEMENT         	    1	NULL
thread_id	         17	         17	statement/sp/stmt             	trigger_befo	                 10	STATEMENT         	    1	SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR"
thread_id	         18	         18	statement/sql/select          	NULL        	                  2	TRANSACTION       	    0	SELECT * FROM t1 ORDER BY s1
thread_id	         19	         19	statement/sql/commit          	NULL        	                  2	TRANSACTION       	    0	COMMIT
thread_id	         20	         21	statement/sql/drop_trigger    	NULL        	               NULL	NULL              	    0	DROP TRIGGER trigger_before_update
#
### Combined statement and transaction event history ordered by event id
#
#EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
#
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
'<transaction started>' AS SQL_TXT
FROM performance_schema.events_transactions_history_long t
WHERE (t.thread_id = @con1_thread_id)
UNION
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME               ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
SQL_TEXT
FROM performance_schema.events_statements_history_long s
WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, r_event_id;
THREAD_ID	R_EVENT_ID	R_END_EVENT_ID	EVENT_NAME               	R_NESTING_EVENT_ID	NESTING_EVENT_TYPE	SQL_TXT
thread_id	          1	          2	statement/sql/begin      	               NULL	NULL              	START TRANSACTION
thread_id	          2	         19	transaction              	                  1	STATEMENT         	<transaction started>
thread_id	          3	          3	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (500, "INSERT 500")
thread_id	          4	          4	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (501, "INSERT 501")
thread_id	          5	          5	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (502, "INSERT 502")
thread_id	          6	          6	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (503, "INSERT 503")
thread_id	          7	          7	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (504, "INSERT 504")
thread_id	          8	          8	statement/sql/insert     	                  2	TRANSACTION       	INSERT INTO t1 VALUES (505, "INSERT 505")
thread_id	          9	          9	statement/sql/select     	                  2	TRANSACTION       	SELECT * FROM t1 ORDER BY s1
thread_id	         10	         17	statement/sql/update     	                  2	TRANSACTION       	UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500
thread_id	         11	         11	statement/sp/jump_if_not 	                 10	STATEMENT         	NULL
thread_id	         12	         12	statement/sp/jump_if_not 	                 10	STATEMENT         	NULL
thread_id	         13	         13	statement/sp/jump_if_not 	                 10	STATEMENT         	NULL
thread_id	         14	         14	statement/sp/jump_if_not 	                 10	STATEMENT         	NULL
thread_id	         15	         15	statement/sp/jump_if_not 	                 10	STATEMENT         	NULL
thread_id	         16	         16	statement/sp/jump_if_not 	                 10	STATEMENT         	NULL
thread_id	         17	         17	statement/sp/stmt        	                 10	STATEMENT         	SIGNAL sqlstate '45001' SET message_text = "FORCE ERROR"
thread_id	         18	         18	statement/sql/select     	                  2	TRANSACTION       	SELECT * FROM t1 ORDER BY s1
thread_id	         19	         19	statement/sql/commit     	                  2	TRANSACTION       	COMMIT
thread_id	         20	         21	statement/sql/drop_trigge	               NULL	NULL              	DROP TRIGGER trigger_before_update
thread_id	         21	         21	transaction              	                 20	STATEMENT         	<transaction started>
#
### Clear statement and transaction history
# CALL test.clear_history();
CALL test.clear_history();
## Reset db.t1
DELETE FROM db.t1;
#
# TODO: Detect statement events from scheduled event
#=======================================================================
# Cleanup
#=======================================================================
connection con1;
disconnect con1;
connection default;
DROP DATABASE db;
UPDATE performance_schema.setup_instruments SET enabled='YES', timed='YES';
DROP PROCEDURE clear_transaction_tables;
DROP PROCEDURE clear_transaction_history;
DROP PROCEDURE clear_statement_history;
DROP PROCEDURE clear_history;
DROP PROCEDURE transaction_verifier;