mirror of
https://github.com/MariaDB/server.git
synced 2025-08-01 03:47:19 +03:00
bead24b7f3
Remove one of the major sources of race condiitons in mariadb-test. Normally, mariadb_close() sends COM_QUIT to the server and immediately disconnects. In mariadb-test it means the test can switch to another connection and sends queries to the server before the server even started parsing the COM_QUIT packet and these queries can see the connection as fully active, as it didn't reach dispatch_command yet. This is a major source of instability in tests and many - but not all, still less than a half - tests employ workarounds. The correct one is a pair count_sessions.inc/wait_until_count_sessions.inc. Also very popular was wait_until_disconnected.inc, which was completely useless, because it verifies that the connection is closed, and after disconnect it always is, it didn't verify whether the server processed COM_QUIT. Sadly the placebo was as widely used as the real thing. Let's fix this by making mariadb-test `disconnect` command _to wait_ for the server to confirm. This makes almost all workarounds redundant. In some cases count_sessions.inc/wait_until_count_sessions.inc is still needed, though, as only `disconnect` command is changed: * after external tools, like `exec $MYSQL` * after failed `connect` command * replication, after `STOP SLAVE` * Federated/CONNECT/SPIDER/etc after `DROP TABLE` and also in some XA tests, because an XA transaction is dissociated from the THD very late, after the server has closed the client connection. Collateral cleanups: fix comments, remove some redundant statements: * DROP IF EXISTS if nothing is known to exist * DROP table/view before DROP DATABASE * REVOKE privileges before DROP USER etc
386 lines
11 KiB
Plaintext
386 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 #
|
|
# #
|
|
# #
|
|
######################################################################
|
|
|
|
--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 #=======================================================================
|
|
--disconnect con1
|
|
--connection default
|
|
DROP DATABASE db;
|
|
UPDATE performance_schema.setup_instruments SET enabled='YES', timed='YES';
|
|
|
|
--source ../include/transaction_cleanup.inc
|
|
|
|
|
|
|