path: root/mysql-test/suite/perfschema/t/transaction_nested_events.test

########## 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
--source include/maybe_pool_of_threads.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 1 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 2 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