406 lines
11 KiB
Plaintext
406 lines
11 KiB
Plaintext
########## suite/perfschema/t/transaction_nested_events.test #########
|
|
# #
|
|
# This test checks the following #
|
|
# 1) nested events #
|
|
# 2) various scenarios of stored program & transaction combinations #
|
|
# #
|
|
# #
|
|
######################################################################
|
|
|
|
# Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
|
|
--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=XENGINE;
|
|
CREATE TABLE db.nt1 (s1 int, s2 varchar(64)) ENGINE=MYISAM;
|
|
CREATE PROCEDURE db.tp_update() UPDATE t1 SET s1 = s1 + 1;
|
|
CREATE PROCEDURE db.tp_start() START TRANSACTION;
|
|
CREATE PROCEDURE db.tp_rollback() ROLLBACK;
|
|
CREATE PROCEDURE db.tp_commit() COMMIT;
|
|
DELIMITER |;
|
|
CREATE FUNCTION db.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 ## Warm-up data-dictionary cache.
|
|
--disable_result_log
|
|
--disable_query_log
|
|
SELECT * FROM db.t1;
|
|
SELECT * FROM db.nt1;
|
|
SHOW CREATE PROCEDURE db.tp_update;
|
|
SHOW CREATE PROCEDURE db.tp_start;
|
|
SHOW CREATE PROCEDURE db.tp_rollback;
|
|
SHOW CREATE PROCEDURE db.tp_commit;
|
|
SHOW CREATE FUNCTION db.fn_add;
|
|
--enable_query_log
|
|
--enable_result_log
|
|
|
|
--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
|
|
--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
|
|
--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
|
|
|
|
--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
|
|
--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 ## Warm-up data-dictionary cache.
|
|
--disable_result_log
|
|
--disable_query_log
|
|
SHOW CREATE TABLE t1;
|
|
--enable_query_log
|
|
--enable_result_log
|
|
|
|
--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_testcase BUG#0000
|
|
--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_testcase
|
|
|
|
--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
|
|
|
|
|
|
|