mirror of
https://github.com/MariaDB/server.git
synced 2025-02-05 13:22:17 +01:00
4102f1589c
MDEV-22531 Remove maria::implicit_commit()
MDEV-22607 Assertion `ha_info->ht() != binlog_hton' failed in
MYSQL_BIN_LOG::unlog_xa_prepare
From the handler point of view, Aria now looks like a transactional
engine. One effect of this is that we don't need to call
maria::implicit_commit() anymore.
This change also forces the server to call trans_commit_stmt() after doing
any read or writes to system tables. This work will also make it easier
to later allow users to have system tables in other engines than Aria.
To handle the case that Aria doesn't support rollback, a new
handlerton flag, HTON_NO_ROLLBACK, was added to engines that has
transactions without rollback (for the moment only binlog and Aria).
Other things
- Moved freeing of MARIA_SHARE to a separate function as the MARIA_SHARE
can be still part of a transaction even if the table has closed.
- Changed Aria checkpoint to use the new MARIA_SHARE free function. This
fixes a possible memory leak when using S3 tables
- Changed testing of binlog_hton to instead test for HTON_NO_ROLLBACK
- Removed checking of has_transaction_manager() in handler.cc as we can
assume that as the transaction was started by the engine, it does
support transactions.
- Added new class 'start_new_trans' that can be used to start indepdendent
sub transactions, for example while reading mysql.proc, using help or
status tables etc.
- open_system_tables...() and open_proc_table_for_Read() doesn't anymore
take a Open_tables_backup list. This is now handled by 'start_new_trans'.
- Split thd::has_transactions() to thd::has_transactions() and
thd::has_transactions_and_rollback()
- Added handlerton code to free cached transactions objects.
Needed by InnoDB.
squash! 2ed35999f2a2d84f1c786a21ade5db716b6f1bbc
1284 lines
70 KiB
Text
1284 lines
70 KiB
Text
#
|
|
#========================================================================
|
|
# 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;
|