diff options
Diffstat (limited to 'mysql-test/suite/perfschema/t/transaction_nested_events.test')
| -rw-r--r-- | mysql-test/suite/perfschema/t/transaction_nested_events.test | 385 |
1 files changed, 385 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/t/transaction_nested_events.test b/mysql-test/suite/perfschema/t/transaction_nested_events.test new file mode 100644 index 00000000000..11ab1d946c0 --- /dev/null +++ b/mysql-test/suite/perfschema/t/transaction_nested_events.test @@ -0,0 +1,385 @@ +########## suite/perfschema/t/transaction_nested_events.test ######### +# # +# This test checks the following # +# 1) nested events # +# 2) various scenarios of stored program & transaction combinations # +# # +# # +###################################################################### + +--source include/have_perfschema.inc +--source include/not_embedded.inc +--source include/have_innodb.inc +--source include/no_protocol.inc +--disable_query_log +--source ../include/transaction_setup.inc +--enable_query_log + +--echo +--echo #======================================================================== +--echo # STEP 1 - SETUP +--echo #======================================================================== + +--echo +--echo ## Setup control thread +--echo +--connection default +SET SESSION AUTOCOMMIT= 1; +--disable_warnings +USE test; +DROP DATABASE IF EXISTS db; +--enable_warnings +--echo +--echo ## 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; + +--echo +--echo ## Setup connection 1 +connect(con1, localhost, root,,); +USE db; +SET SESSION AUTOCOMMIT = 1; +eval $get_thread_id; +let $con1_thread_id= `SELECT @my_thread_id`; + +--connection default +--disable_query_log +eval SET @con1_thread_id= $con1_thread_id; +--enable_query_log + +--echo +--echo ## Disable events from the control (default) connection +UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID(); + +# Use this to select events from all threads +eval SET @all_threads= 0; + +--echo +--echo ## 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'; + +--echo +--echo ## Clear statement and transaction history +CALL test.clear_history(); + +--echo +--echo #======================================================================== +--echo # STEP 2 - BASIC TRANSACTION +--echo #======================================================================== +--echo # +--echo # STEP 2.1 - IMPLICIT +--echo # +--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; + +## Verify and reset +--source ../include/transaction_nested_events_verifier.inc + +--echo # +--echo # STEP 2.2 - EXPLICIT +--echo # +--connection con1 +START TRANSACTION; +INSERT INTO t1 VALUES (220, "INSERT 220"), (221, "INSERT 221"); +UPDATE t1 SET s2 = "UPDATE 221" WHERE s1 = 221; +COMMIT; + +## Verify and reset +--source ../include/transaction_nested_events_verifier.inc + +--echo #======================================================================== +--echo # STEP 3 - TRANSACTIONS AND STORED PROCEDURES +--echo #======================================================================== +--echo # +--echo # STEP 3.1 - STORED PROCEDURE STARTED WITHIN TRANSACTION +--echo # +--connection con1 +CREATE PROCEDURE tp_update() UPDATE t1 SET s1 = s1 + 1; +--echo +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; + +## Verify and reset +--source ../include/transaction_nested_events_verifier.inc + +--echo # +--echo # STEP 3.2 - TRANSACTION STARTED WITHIN STORED PROCEDURE +--echo # +--connection con1 +CREATE PROCEDURE tp_start() START TRANSACTION; +--echo +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; +--echo +SELECT * FROM t1 ORDER BY s1; +COMMIT; + +## Verify and reset +--source ../include/transaction_nested_events_verifier.inc + +--echo # +--echo # STEP 3.3 - TRANSACTION ENDED WITHIN STORED PROCEDURE +--echo # +--connection con1 +CREATE PROCEDURE tp_rollback() ROLLBACK; +CREATE PROCEDURE tp_commit() COMMIT; + +--echo +--echo ## 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(); +--echo +SELECT * FROM t1 ORDER BY s1; + +--echo +--echo ## ROLLBACK within stored procedure +START TRANSACTION; +UPDATE t1 SET s1 = s1*2 WHERE s1 > 331; +CALL tp_rollback(); +--echo +SELECT * FROM t1 ORDER BY s1; + +## Verify and reset +--source ../include/transaction_nested_events_verifier.inc + +--echo #======================================================================== +--echo # STEP 4 - TRANSACTIONS AND STORED FUNCTIONS +--echo #======================================================================== +--echo +--echo # +--echo # STEP 4.1 - FUNCTION WITHIN A TRANSACTION +--echo # +--connection con1 +DELIMITER |; +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 | +DELIMITER ;| + +--echo +--echo ## Clear history +--connection default +CALL test.clear_history(); +--connection con1 + +--echo +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; +--echo +SELECT * FROM t1 ORDER BY s1; +--echo +SELECT fn_add(413, 414); +COMMIT; +--echo +SELECT * FROM t1 ORDER BY s1; + +## Verify and reset +--source ../include/transaction_nested_events_verifier.inc + +--connection con1 +--echo +--echo ## Again, but this time with a rollback +--echo +START TRANSACTION; +SELECT fn_add(415, 416); +--echo +ROLLBACK; +--echo +SELECT * FROM t1 ORDER BY s1; + +## Verify and reset +--source ../include/transaction_nested_events_verifier.inc + +--echo # +--echo # STEP 4.2 - TRANSACTION CANNOT BE STARTED OR ENDED WITHIN FUNCTION +--echo # +--connection con1 +DELIMITER |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION fn_err1() RETURNS VARCHAR(10) BEGIN START TRANSACTION ; RETURN invalid ; END| +DELIMITER ;| + +--echo +--echo ## Expect 0 transactions +--connection default +SELECT COUNT(*) FROM performance_schema.events_transactions_history; +--connection con1 + +--echo +--echo ## Expect stored function does not exist +--echo +--error ER_SP_DOES_NOT_EXIST +SELECT fn_err1(); + +--echo +--echo ## Expect 0 transactions +--connection default +SELECT COUNT(*) FROM performance_schema.events_transactions_history; +--connection con1 + +--echo +DELIMITER |; +--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG +CREATE FUNCTION fn_err2() RETURNS VARCHAR(10) BEGIN COMMIT; RETURN invalid ; END| +DELIMITER ;| + +--echo +--echo ## Expect stored function does not exist +--echo +START TRANSACTION; +DELETE FROM t1 WHERE s1 > 320; +--error ER_SP_DOES_NOT_EXIST +SELECT fn_err2(); + +--echo +--echo ## Expect 0 transactions +--connection default +SELECT COUNT(*) FROM performance_schema.events_transactions_history; + +--echo +--echo ## Clear transaction and statement tables +CALL test.clear_history(); + +--echo #======================================================================== +--echo # STEP 5 - TRANSACTIONS AND TRIGGERS +--echo #======================================================================== +--echo +--echo # +--echo # STEP 5.1 - FORCE STATEMENT ROLLBACK FROM TRIGGER +--echo # +--connection con1 +--echo ## Create a trigger to force statement rollback +--echo +DELIMITER |; +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;| +DELIMITER ;| + +--echo +--echo ## Clear history +--connection default +CALL test.clear_history(); +--connection con1 + +--echo +--echo ## Insert multiple rows, then update. Trigger will force rollback the +--echo ## UPDATE statement, but the transaction should not roll back. +--echo +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"); +--echo +SELECT * FROM t1 ORDER BY s1; +--echo +--echo ## Expect error when UPDATE hits record 505 +--echo +--error ER_SIGNAL_EXCEPTION +UPDATE t1 SET s1 = s1 * 2 WHERE s1 >= 500; +--echo +--echo ## Verify that INSERT succeeded, UPDATE failed and transaction did not rollback +--echo +SELECT * FROM t1 ORDER BY s1; +COMMIT; +--echo +DROP TRIGGER trigger_before_update; + +## Verify and reset +--source ../include/transaction_nested_events_verifier.inc + +--echo # TODO: Detect statement events from scheduled event +--disable_parsing +--echo #======================================================================== +--echo # STEP 6 - SCHEDULED EVENTS +--echo #======================================================================== +--connection con1 + +# Check that the event_scheduler is really running +#--source include/running_event_scheduler.inc + +--echo +--echo ## Create a one-time event that will insert, update, commit and rollback. +--echo +DELIMITER |; +CREATE EVENT trx_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 SECOND DO +BEGIN + START TRANSACTION; + INSERT INTO t1 VALUES (600, "INSERT 600"); + INSERT INTO t1 VALUES (601, "INSERT 601"); + INSERT INTO t1 VALUES (602, "INSERT 602"); + COMMIT; + START TRANSACTION; + UPDATE t1 SET s1 = s1 + 1, s2 = 'THIS SHOULD NOT BE HERE'; + ROLLBACK; + START TRANSACTION; + INSERT INTO t1 VALUES (699, "INSERT 699"); + COMMIT; +END;| +DELIMITER ;| + +--echo +--echo ## Clear history +--connection default +CALL test.clear_history(); +--connection con1 +--echo +--echo ## Wait a few seconds for scheduled event to execute and finish. +# Check based on the expected content in t1. +let $wait_condition= SELECT COUNT(*) = 4 FROM t1; +--source include/wait_condition.inc +--echo +--echo ## Confirm that the scheduled event completed and the content of t1 is right. +--echo +SELECT * FROM t1 ORDER BY s1; +--echo +--echo ## Verify and reset +--echo +eval SET @all_threads= 1; +--source ../include/transaction_nested_events_verifier.inc +eval SET @all_threads= 0; + +--enable_parsing + +--echo #======================================================================= +--echo # Cleanup +--echo #======================================================================= +--connection con1 +--disconnect con1 +--source include/wait_until_disconnected.inc +--connection default +DROP DATABASE db; +UPDATE performance_schema.setup_instruments SET enabled='YES', timed='YES'; + +--source ../include/transaction_cleanup.inc + + + |