Adding upstream version 1:10.11.6.upstream/1%10.11.6

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 387 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 00000000..1be7dc0f
--- /dev/null
+++ b/mysql-test/suite/perfschema/t/transaction_nested_events.test
@@ -0,0 +1,387 @@
+########## 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
+
+
+