diff options
author | sjaakola <seppo.jaakola@iki.fi> | 2020-06-01 12:34:33 +0300 |
---|---|---|
committer | Julius Goryavsky <julius.goryavsky@mariadb.com> | 2020-06-03 15:34:44 +0200 |
commit | 8ec0e9111a49fe451109e5a558aeb603eca82259 (patch) | |
tree | e8cd0278fdcf6266078543d9baf0047450f0f9d6 /mysql-test/suite/galera/t | |
parent | 40dbf0ea0e678370f4c34700e9868b3560cf8301 (diff) | |
download | mariadb-git-8ec0e9111a49fe451109e5a558aeb603eca82259.tar.gz |
MDEV-22763 backporting MDEV-20225 fix into 10.1
Backported the support for aborting and replaying stored procedure and fix for trigger
key assigments from 10.4 version.
Backported also two mtr tests: wsrep_sp_bf_abort and MDEV-20225
Diffstat (limited to 'mysql-test/suite/galera/t')
-rw-r--r-- | mysql-test/suite/galera/t/MDEV-20225.test | 49 | ||||
-rw-r--r-- | mysql-test/suite/galera/t/galera_sp_bf_abort.inc | 36 | ||||
-rw-r--r-- | mysql-test/suite/galera/t/galera_sp_bf_abort.test | 347 |
3 files changed, 432 insertions, 0 deletions
diff --git a/mysql-test/suite/galera/t/MDEV-20225.test b/mysql-test/suite/galera/t/MDEV-20225.test new file mode 100644 index 00000000000..5fbd0965217 --- /dev/null +++ b/mysql-test/suite/galera/t/MDEV-20225.test @@ -0,0 +1,49 @@ +# +# MDEV-20225 - Verify that DROP TRIGGER gets keys assigned corresponding +# to all affected tables. +# + +--source include/galera_cluster.inc +--source include/have_innodb.inc + +CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +CREATE TABLE t2 (f1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT, f2 INT) ENGINE=InnoDB; + +CREATE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 VALUES (NULL, NEW.f1); + +--connection node_2 +SET SESSION wsrep_sync_wait = 0; +SET GLOBAL wsrep_slave_threads = 2; +SET GLOBAL debug_dbug = 'd,sync.mdev_20225'; + +--let $galera_connection_name = node_1a +--let $galera_server_number = 1 +--source include/galera_connect.inc +DROP TRIGGER tr1; + +--connection node_2 +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE = 'debug sync point: now' +--source include/wait_condition.inc + + +--connection node_1 +INSERT INTO t1 VALUES (NULL); +# We must rely on sleep here. If the bug is fixed, the second applier +# is not allowed to go past apply monitor which would trigger the bug, +# so there is no sync point or condition to wait. +--sleep 1 + +--connection node_2 +SET GLOBAL debug_dbug = 'RESET'; +SET DEBUG_SYNC = 'now SIGNAL signal.mdev_20225_continue'; +SET DEBUG_SYNC = 'RESET'; +SET GLOBAL wsrep_slave_threads = 1; + +--let $wait_condition = SELECT COUNT(*) = 1 FROM test.t1; +--source include/wait_condition.inc + +# Trigger should now be dropped on node_2. +SHOW TRIGGERS; + +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/suite/galera/t/galera_sp_bf_abort.inc b/mysql-test/suite/galera/t/galera_sp_bf_abort.inc new file mode 100644 index 00000000000..7ca8ecf20a9 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sp_bf_abort.inc @@ -0,0 +1,36 @@ +# +# Issue an INSERT for gap between 1 and 3 to node_2 and wait until it hits +# apply monitor sync point on node_1 +# + +--connection node_1a +--let $galera_sync_point = apply_monitor_slave_enter_sync +--source include/galera_set_sync_point.inc + +--connection node_2 +--eval $galera_sp_bf_abort_conflict + +--connection node_1a +--source include/galera_wait_sync_point.inc +--source include/galera_clear_sync_point.inc + +# Send a procedure to node_1 which should take a gap lock between +# rows 1 and 3. It does not conflict with INSERT from node_2 in +# certification. Park the UPDATE after replicate and let INSERT to +# continue applying, generating a BF abort. + +--let $galera_sync_point = after_replicate_sync +--source include/galera_set_sync_point.inc + +--connection node_1 +--send_eval CALL $galera_sp_bf_abort_proc + +--connection node_1a +--let $galera_sync_point = after_replicate_sync apply_monitor_slave_enter_sync +--source include/galera_wait_sync_point.inc +--source include/galera_clear_sync_point.inc + +--let $galera_sync_point = apply_monitor_slave_enter_sync +--source include/galera_signal_sync_point.inc +--let $galera_sync_point = after_replicate_sync +--source include/galera_signal_sync_point.inc diff --git a/mysql-test/suite/galera/t/galera_sp_bf_abort.test b/mysql-test/suite/galera/t/galera_sp_bf_abort.test new file mode 100644 index 00000000000..484e2ca478d --- /dev/null +++ b/mysql-test/suite/galera/t/galera_sp_bf_abort.test @@ -0,0 +1,347 @@ +# +# Test cases for stored procedure BF aborts. +# + +--source include/galera_cluster.inc +--source include/have_innodb.inc + +--connection node_1 + +CREATE TABLE t1 (f1 INTEGER PRIMARY KEY, f2 CHAR(1)); + +# Control connection for Galera sync point management +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--connection node_1a +SET SESSION wsrep_sync_wait = 0; + +--connection node_1 +# +# Case 1a: Procedure does and UPDATE which will suffer BF abort +# but there is no actual conflict and non-conflicting INSERT. +# The expected outcome is that both UPDATE and INSERT will succedd +# and no errors are reported to the client, wsrep_local_replays is +# incremented by one. +# +DELIMITER |; +CREATE PROCEDURE proc_update_insert() +BEGIN + UPDATE t1 SET f2 = 'b'; + INSERT INTO t1 VALUES (4, 'd'); +END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1, 'a'), (3, 'a'); +SET SESSION wsrep_sync_wait = 0; +--let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--let $galera_sp_bf_abort_proc = proc_update_insert +--let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c') +--source galera_sp_bf_abort.inc +--connection node_1 +--reap +SET SESSION wsrep_sync_wait = default; +SELECT * FROM t1; +--let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--disable_query_log +--eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays; +--enable_query_log + +DELETE FROM t1; + +--connection node_1 +# +# Case 1b: Procedure does and UPDATE which will suffer BF abort +# but there is no actual conflict and non-conflicting INSERT. +# An EXIT HANDLER is declared for the procedure. +# The expected outcome is that both UPDATE and INSERT will succedd +# and no errors are reported to the client, wsrep_local_replays is +# incremented by one. +# +DELIMITER |; +CREATE PROCEDURE proc_update_insert_with_exit_handler() +BEGIN + DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END; + UPDATE t1 SET f2 = 'b'; + INSERT INTO t1 VALUES (4, 'd'); +END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1, 'a'), (3, 'a'); +SET SESSION wsrep_sync_wait = 0; +--let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--let $galera_sp_bf_abort_proc = proc_update_insert_with_exit_handler +--let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c') +--source galera_sp_bf_abort.inc +--connection node_1 +--reap +SET SESSION wsrep_sync_wait = default; +SELECT * FROM t1; +--let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--disable_query_log +--eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays; +--enable_query_log + +DELETE FROM t1; + +--connection node_1 +# +# Case 1c: Procedure does and UPDATE which will suffer BF abort +# but there is no actual conflict and non-conflicting INSERT. +# A CONTINUE HANDLER is declared for the procedure. +# The expected outcome is that both UPDATE and INSERT will succedd +# and no errors are reported to the client, wsrep_local_replays is +# incremented by one. +# +DELIMITER |; +CREATE PROCEDURE proc_update_insert_with_continue_handler() +BEGIN + + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; + UPDATE t1 SET f2 = 'b'; + INSERT INTO t1 VALUES (4, 'd'); +END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1, 'a'), (3, 'a'); +SET SESSION wsrep_sync_wait = 0; +--let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--let $galera_sp_bf_abort_proc = proc_update_insert_with_continue_handler +--let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c') +--source galera_sp_bf_abort.inc +--connection node_1 +--reap +SET SESSION wsrep_sync_wait = default; +SELECT * FROM t1; +--let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--disable_query_log +--eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays; +--enable_query_log + +DELETE FROM t1; + +--connection node_1 +# +# Case 2a: UPDATE and INSERT are run inside a transaction and the transaction +# will be BF aborted on COMMIT. The expected outcome is that the transaction +# succeeds and no errors are reported to the client, wsrep_local_replays +# is incremented by one. +# + +DELIMITER |; +CREATE PROCEDURE proc_update_insert_transaction() +BEGIN + START TRANSACTION; + UPDATE t1 SET f2 = 'b'; + INSERT INTO t1 VALUES (4, 'd'); + COMMIT; +END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1, 'a'), (3, 'a'); +--let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--let $galera_sp_bf_abort_proc = proc_update_insert_transaction +--let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c') +SET SESSION wsrep_sync_wait = 0; +--source galera_sp_bf_abort.inc +--connection node_1 +--reap +SET SESSION wsrep_sync_wait = default; +SELECT * FROM t1; +--let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--disable_query_log +--eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays; +--enable_query_log + +DELETE FROM t1; + +--connection node_1 +# +# Case 2b: UPDATE and INSERT are run inside a transaction and the transaction +# will be BF aborted on COMMIT. A CONTINUE HANDLER is declared for the +# procedure. The expected outcome is that the transaction +# succeeds and no errors are reported to the client, wsrep_local_replays +# is incremented by one. +# + +DELIMITER |; +CREATE PROCEDURE proc_update_insert_transaction_with_continue_handler() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; + START TRANSACTION; + UPDATE t1 SET f2 = 'b'; + INSERT INTO t1 VALUES (4, 'd'); + COMMIT; +END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1, 'a'), (3, 'a'); +--let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--let $galera_sp_bf_abort_proc = proc_update_insert_transaction_with_continue_handler +--let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c') +SET SESSION wsrep_sync_wait = 0; +--source galera_sp_bf_abort.inc +--connection node_1 +--reap +SET SESSION wsrep_sync_wait = default; +SELECT * FROM t1; +--let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--disable_query_log +--eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays; +--enable_query_log + +DELETE FROM t1; + +--connection node_1 +# +# Case 2c: UPDATE and INSERT are run inside a transaction and the transaction +# will be BF aborted on COMMIT. An EXIT HANDLE is declared for the procedure. +# The expected outcome is that the transaction succeeds and no errors are +# reported to the client, wsrep_local_replays is incremented by one. +# + +DELIMITER |; +CREATE PROCEDURE proc_update_insert_transaction_with_exit_handler() +BEGIN + DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END; + START TRANSACTION; + UPDATE t1 SET f2 = 'b'; + INSERT INTO t1 VALUES (4, 'd'); + COMMIT; +END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1, 'a'), (3, 'a'); +--let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--let $galera_sp_bf_abort_proc = proc_update_insert_transaction_with_exit_handler +--let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c') +SET SESSION wsrep_sync_wait = 0; +--source galera_sp_bf_abort.inc +--connection node_1 +--reap +SET SESSION wsrep_sync_wait = default; +SELECT * FROM t1; +--let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--disable_query_log +--eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 1 AS wsrep_local_replays; +--enable_query_log + +DELETE FROM t1; + +--connection node_1 + +# +# Case 3a: Two INSERTs are run inside stored procedure, this time +# the first INSERT will have a BF abort and real conflict. The expected outcome +# is that the INSERT fails and an error is reported to the client. +# wsrep_local_replays is not incremented. +# +# Notice that the resulting error code may be both +# ER_DUP_ENTRY (procedure will exit with cert failure conflict state and +# will be) or ER_LOCK_DEADLOCK depending on timing. +# +DELIMITER |; +CREATE PROCEDURE proc_insert_insert_conflict() +BEGIN + INSERT INTO t1 VALUES (2, 'd'); + INSERT INTO t1 VALUES (4, 'd'); +END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1, 'a'), (3, 'a'); +--let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--let $galera_sp_bf_abort_proc = proc_insert_insert_conflict +--let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c') +SET SESSION wsrep_sync_wait = 0; +--source galera_sp_bf_abort.inc +--connection node_1 +--error ER_DUP_ENTRY,ER_LOCK_DEADLOCK, ER_ERROR_DURING_COMMIT +--reap +SET SESSION wsrep_sync_wait = default; +SELECT * FROM t1; +--let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--disable_query_log +--eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 0 AS wsrep_local_replays; +--enable_query_log + +DELETE FROM t1; + +--connection node_1 + +# +# Case 3b: Two INSERTs are run inside stored procedure, this time +# the first INSERT will have a BF abort and real conflict. +# An EXIT HANDLER is declared for the procedure. The expected outcome +# is that the INSERT fails and an error is reported to the client. +# wsrep_local_replays is not incremented. +# +DELIMITER |; +CREATE PROCEDURE proc_insert_insert_conflict_with_exit_handler() +BEGIN + DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT "Conflict exit handler"; + INSERT INTO t1 VALUES (2, 'd'); + INSERT INTO t1 VALUES (4, 'd'); +END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1, 'a'), (3, 'a'); +--let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--let $galera_sp_bf_abort_proc = proc_insert_insert_conflict_with_exit_handler +--let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c') +SET SESSION wsrep_sync_wait = 0; +--source galera_sp_bf_abort.inc +--connection node_1 +--reap +SET SESSION wsrep_sync_wait = default; +SELECT * FROM t1; +--let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--disable_query_log +--eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 0 AS wsrep_local_replays; +--enable_query_log + +DELETE FROM t1; + +--connection node_1 + +# +# Case 3c: Two INSERTs are run inside stored procedure, this time +# the first INSERT will have a BF abort and real conflict. +# A CONTINUE HANDLER is declared for the procedure. The expected outcome +# is that the the first INSERT fails but the second is executed without +# errors. wsrep_local_replays is not incremented. +# +DELIMITER |; +CREATE PROCEDURE proc_insert_insert_conflict_with_continue_handler() +BEGIN + DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SELECT "Conflict continue handler"; + INSERT INTO t1 VALUES (2, 'd'); + INSERT INTO t1 VALUES (4, 'd'); +END| +DELIMITER ;| + +INSERT INTO t1 VALUES (1, 'a'), (3, 'a'); +--let $wsrep_local_replays_orig = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--let $galera_sp_bf_abort_proc = proc_insert_insert_conflict_with_continue_handler +--let $galera_sp_bf_abort_conflict = INSERT INTO t1 VALUES (2, 'c') +SET SESSION wsrep_sync_wait = 0; +--source galera_sp_bf_abort.inc +--connection node_1 +--reap +SET SESSION wsrep_sync_wait = default; +SELECT * FROM t1; +--let $wsrep_local_replays_curr = `SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_replays'` +--disable_query_log +--eval SELECT $wsrep_local_replays_curr - $wsrep_local_replays_orig = 0 AS wsrep_local_replays; +--enable_query_log + +DELETE FROM t1; + + +DROP PROCEDURE proc_update_insert; +DROP PROCEDURE proc_update_insert_with_continue_handler; +DROP PROCEDURE proc_update_insert_with_exit_handler; +DROP PROCEDURE proc_update_insert_transaction; +DROP PROCEDURE proc_update_insert_transaction_with_continue_handler; +DROP PROCEDURE proc_update_insert_transaction_with_exit_handler; +DROP PROCEDURE proc_insert_insert_conflict; +DROP PROCEDURE proc_insert_insert_conflict_with_exit_handler; +DROP PROCEDURE proc_insert_insert_conflict_with_continue_handler; +DROP TABLE t1; |