diff options
Diffstat (limited to 'mysql-test/suite/rpl')
22 files changed, 1417 insertions, 18 deletions
diff --git a/mysql-test/suite/rpl/include/rpl_parallel_deadlock_corrupt_binlog.inc b/mysql-test/suite/rpl/include/rpl_parallel_deadlock_corrupt_binlog.inc index 3a135ef5cc4..592ffd3b068 100644 --- a/mysql-test/suite/rpl/include/rpl_parallel_deadlock_corrupt_binlog.inc +++ b/mysql-test/suite/rpl/include/rpl_parallel_deadlock_corrupt_binlog.inc @@ -22,6 +22,7 @@ CREATE TABLE t2 (a int PRIMARY KEY) ENGINE=InnoDB; SET GLOBAL slave_parallel_threads=1; SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,slave_discard_xid_for_gtid_0_x_1000"; +CALL mtr.add_suppression("Unexpected break of being relay-logged GTID"); --connection server_1 INSERT INTO t2 VALUES (101); diff --git a/mysql-test/suite/rpl/include/rpl_ssl.inc b/mysql-test/suite/rpl/include/rpl_ssl.inc index aff5499c8e5..bd77f213ae1 100644 --- a/mysql-test/suite/rpl/include/rpl_ssl.inc +++ b/mysql-test/suite/rpl/include/rpl_ssl.inc @@ -37,7 +37,7 @@ select * from t1; # The slave is synced and waiting/reading from master # SHOW SLAVE STATUS will show "Waiting for master to send event" -let $status_items= Master_SSL_Allowed, Master_SSL_CA_Path, Master_SSL_CA_File, Master_SSL_Cert, Master_SSL_Key; +let $status_items= Master_SSL_Allowed, Master_SSL_CA_Path, Master_SSL_CA_File, Master_SSL_Crl, Master_SSL_Crlpath, Master_SSL_Cert, Master_SSL_Key; source include/show_slave_status.inc; source include/check_slave_is_running.inc; diff --git a/mysql-test/suite/rpl/r/rpl_gtid_grouping.result b/mysql-test/suite/rpl/r/rpl_gtid_grouping.result new file mode 100644 index 00000000000..ad7d6116c49 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_gtid_grouping.result @@ -0,0 +1,54 @@ +include/master-slave.inc +[connection master] +connection slave; +call mtr.add_suppression("Unexpected break of being relay-logged GTID 0-27697-1000"); +call mtr.add_suppression("Relay log write failure: could not queue event from master"); +call mtr.add_suppression("The current group of events starts with a non-GTID"); +include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=slave_pos; +include/start_slave.inc +connection master; +CREATE TABLE t (a INT) ENGINE=innodb; +INSERT INTO t VALUES(1); +### A. Simulate an unnoticeable loss of Xid event +connection slave; +SET @@global.debug_dbug="+d,slave_discard_xid_for_gtid_0_x_1000"; +connection master; +SET @@gtid_seq_no=1000; +set @@server_id=27697; +INSERT INTO t VALUES(1000); +set @@server_id=default; +INSERT INTO t VALUES(1001); +## Prove the error occurs. +connection slave; +include/wait_for_slave_io_error.inc [errno=1595] +## Prove the slave recovers after the simulation condtion is lifted. +SET @@global.debug_dbug=default; +include/start_slave.inc +### B. Do the same to GTID event. +connection slave; +SET @@global.debug_dbug="+d,slave_discard_gtid_0_x_1002"; +connection master; +SET @@gtid_seq_no=1002; +set @@server_id=27697; +INSERT INTO t VALUES(1002); +set @@server_id=default; +INSERT INTO t VALUES(1003); +## Prove the error occurs. +connection slave; +include/wait_for_slave_io_error.inc [errno=1595] +## Prove the slave recovers after the simulation condtion is lifted. +SET @@global.debug_dbug=default; +include/start_slave.inc +connection master; +connection slave; +include/diff_tables.inc [master:t,slave:t] +"===== Clean up =====" +connection slave; +include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=no; +include/start_slave.inc +connection master; +DROP TABLE t; +SET GLOBAL LOG_WARNINGS=default; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_iodku,stmt.rdiff b/mysql-test/suite/rpl/r/rpl_iodku,stmt.rdiff new file mode 100644 index 00000000000..e31f1e5d991 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_iodku,stmt.rdiff @@ -0,0 +1,27 @@ +--- r/rpl_iodku.result 2022-05-04 18:51:24.956414404 +0300 ++++ r/rpl_iodku,stmt.reject 2022-05-04 18:51:49.520106231 +0300 +@@ -1,10 +1,15 @@ + include/master-slave.inc + [connection master] ++call mtr.add_suppression("Unsafe statement written to the binary log using statement"); + CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, a INT, b INT, c INT, + UNIQUE (a), UNIQUE (b)) ENGINE=innodb; + INSERT INTO t1 (`a`,`c`) VALUES (1,1), (2,1) ON DUPLICATE KEY UPDATE c = 1; ++Warnings: ++Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe + # UNSAFE + INSERT INTO t1 (`a`,`c`) VALUES (3, 1),(2,1), (1,1) ON DUPLICATE KEY UPDATE c = a * 10 + VALUES(c); ++Warnings: ++Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe + SELECT * from t1; + id a b c + 1 1 NULL 11 +@@ -17,6 +22,8 @@ + INSERT INTO t1 VALUES (1,10,1); + # eligable for the statement format run unsafe warning + INSERT INTO t1 VALUES (2,20,2) ON DUPLICATE KEY UPDATE c = 100; ++Warnings: ++Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe + # not eligable: no warning in the statement format run + INSERT INTO t1 (`a`,`c`) VALUES (3, 1) ON DUPLICATE KEY UPDATE c = 99; + SELECT * from t1; diff --git a/mysql-test/suite/rpl/r/rpl_iodku.result b/mysql-test/suite/rpl/r/rpl_iodku.result new file mode 100644 index 00000000000..55348da1439 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_iodku.result @@ -0,0 +1,32 @@ +include/master-slave.inc +[connection master] +CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, a INT, b INT, c INT, +UNIQUE (a), UNIQUE (b)) ENGINE=innodb; +INSERT INTO t1 (`a`,`c`) VALUES (1,1), (2,1) ON DUPLICATE KEY UPDATE c = 1; +# UNSAFE +INSERT INTO t1 (`a`,`c`) VALUES (3, 1),(2,1), (1,1) ON DUPLICATE KEY UPDATE c = a * 10 + VALUES(c); +SELECT * from t1; +id a b c +1 1 NULL 11 +2 2 NULL 21 +3 3 NULL 1 +connection slave; +include/diff_tables.inc [master:t1,slave:t1] +connection master; +CREATE OR REPLACE TABLE t1 (a INT, b INT, c INT, UNIQUE (a), UNIQUE (b)) ENGINE=innodb; +INSERT INTO t1 VALUES (1,10,1); +# eligable for the statement format run unsafe warning +INSERT INTO t1 VALUES (2,20,2) ON DUPLICATE KEY UPDATE c = 100; +# not eligable: no warning in the statement format run +INSERT INTO t1 (`a`,`c`) VALUES (3, 1) ON DUPLICATE KEY UPDATE c = 99; +SELECT * from t1; +a b c +1 10 1 +2 20 2 +3 NULL 1 +connection slave; +include/diff_tables.inc [master:t1,slave:t1] +connection master; +DROP TABLE t1; +connection slave; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_mdev_17614.result b/mysql-test/suite/rpl/r/rpl_mdev_17614.result index 39057334926..ba077111522 100644 --- a/mysql-test/suite/rpl/r/rpl_mdev_17614.result +++ b/mysql-test/suite/rpl/r/rpl_mdev_17614.result @@ -1,5 +1,6 @@ include/master-slave.inc [connection master] +# Case 1: UNSAFE call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT, UNIQUE(b), c int) engine=innodb; @@ -37,6 +38,7 @@ drop table t1; connection slave; start slave; include/wait_for_slave_to_start.inc +# Case 2: UNSAFE connection master; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, UNIQUE(b), c int) engine=innodb; @@ -45,8 +47,12 @@ connection master; INSERT INTO t1 VALUES (default, 1, 1); BEGIN; INSERT INTO t1 VALUES (default, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe connection master1; INSERT INTO t1 VALUES(default, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe connection master; COMMIT; SELECT * FROM t1; @@ -62,6 +68,7 @@ a b c connection master; drop table t1; connection slave; +# Case 3A: UNSAFE connection master; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, UNIQUE(b), c int, d int ) engine=innodb; @@ -93,6 +100,67 @@ a b c d connection master; drop table t1; connection slave; +# Case 3B: UNSAFE - all column specified. +connection master; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +connection slave; +connection master; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +connection master1; +INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe +connection master; +COMMIT; +SELECT * FROM t1; +a b c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +connection slave; +#same data as master +SELECT * FROM t1; +a b c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +connection master; +drop table t1; +connection slave; +# Case 3C: SAFE - only one unique key (PK) specified. +connection master; +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +connection slave; +connection master; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 (`a`, `c`, `d`) VALUES (2, 2, 2) ON DUPLICATE KEY UPDATE c=99; +connection master1; +INSERT INTO t1 (`a`, `c`, `d`) VALUES(3, 2, 3) ON DUPLICATE KEY UPDATE c=100; +connection master; +COMMIT; +SELECT * FROM t1; +a b c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +connection slave; +#same data as master +SELECT * FROM t1; +a b c d +1 1 1 1 +2 NULL 2 2 +3 NULL 2 3 +connection master; +drop table t1; +connection slave; +# Case 4: UNSAFE connection master; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, UNIQUE(b), c int) engine=innodb; @@ -101,8 +169,12 @@ connection master; INSERT INTO t1 VALUES (1, 1, 1); BEGIN; INSERT INTO t1 VALUES (2, 1, 2) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe connection master1; INSERT INTO t1 VALUES(2, 2, 3) ON DUPLICATE KEY UPDATE b=VALUES(b), c=VALUES(c); +Warnings: +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe connection master; COMMIT; SELECT * FROM t1; diff --git a/mysql-test/suite/rpl/r/rpl_mysql_manager_race_condition.result b/mysql-test/suite/rpl/r/rpl_mysql_manager_race_condition.result new file mode 100644 index 00000000000..1172d8e39a9 --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mysql_manager_race_condition.result @@ -0,0 +1,27 @@ +include/master-slave.inc +[connection master] +connection master; +# Create a GTID event so the binlog background thread will submit a +# mysql handler job the next time mysqld is restarted. +create table t1 (a int); +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +# Set a debug point that forces the main mysqld thread to sleep before +# anything is initialized for the mysql handle manager +# Restart the slave mysqld instance so it re-initializes with the +# binlog background thread submitting a mysql handler job and the +# mysql handler initialization suspending for a second. Without the fix +# associated with this test/patch, the following restart will error +# with a failed assertion. +include/rpl_restart_server.inc [server_number=2 parameters: --debug_dbug="+d,delay_start_handle_manager"] +include/start_slave.inc +# +# Cleanup +# +connection master; +drop table t1; +include/save_master_gtid.inc +connection slave; +include/sync_with_master_gtid.inc +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_parallel_deadlock_corrupt_binlog.result b/mysql-test/suite/rpl/r/rpl_parallel_deadlock_corrupt_binlog.result index 74d1d53b67c..f3203cfe8e6 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_deadlock_corrupt_binlog.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_deadlock_corrupt_binlog.result @@ -15,6 +15,7 @@ include/stop_slave.inc SET GLOBAL slave_parallel_threads=1; SET @old_dbug= @@GLOBAL.debug_dbug; SET GLOBAL debug_dbug="+d,slave_discard_xid_for_gtid_0_x_1000"; +CALL mtr.add_suppression("Unexpected break of being relay-logged GTID"); connection server_1; INSERT INTO t2 VALUES (101); INSERT INTO t2 VALUES (102); diff --git a/mysql-test/suite/rpl/r/rpl_parallel_temptable.result b/mysql-test/suite/rpl/r/rpl_parallel_temptable.result index 52efed22541..1a1c12f836d 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_temptable.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_temptable.result @@ -132,8 +132,13 @@ connection server_1; INSERT INTO t1 VALUES (0, 1); include/save_master_gtid.inc connection server_2; +set @@sql_log_bin=0; +call mtr.add_suppression("Unexpected break of being relay-logged GTID 1-1-32 event group by the current GTID event 0-1-4"); +set @@sql_log_bin=1; +set @@global.debug_dbug="+d,slave_discard_xid_for_gtid_0_x_1000"; include/start_slave.inc include/sync_with_master_gtid.inc +set @@global.debug_dbug=""; SELECT * FROM t1 ORDER BY a; a b 0 1 diff --git a/mysql-test/suite/rpl/r/rpl_semi_sync_shutdown_await_ack.result b/mysql-test/suite/rpl/r/rpl_semi_sync_shutdown_await_ack.result new file mode 100644 index 00000000000..719b61b796b --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_semi_sync_shutdown_await_ack.result @@ -0,0 +1,517 @@ +############################# +# Common setup for all tests +############################# +# Note: Simulated slave delay is hardcoded to 800 milliseconds +# Note: Simulated master shutdown delay is hardcoded to 500 milliseconds +include/rpl_init.inc [topology=1->2, 1->3] +connection server_1; +# Slaves which simulate an error will produce a timeout on the primary +call mtr.add_suppression("Timeout waiting"); +call mtr.add_suppression("did not exit"); +# Suppress slave errors related to the simulated error +connection server_2; +call mtr.add_suppression("reply failed"); +call mtr.add_suppression("Replication event checksum verification"); +call mtr.add_suppression("Relay log write failure"); +call mtr.add_suppression("Failed to kill the active semi-sync connection"); +connection server_3; +call mtr.add_suppression("reply failed"); +call mtr.add_suppression("Replication event checksum verification"); +call mtr.add_suppression("Relay log write failure"); +call mtr.add_suppression("Failed to kill the active semi-sync connection"); +connection server_1; +CREATE TABLE t1 (a int); +connection server_2; +connection server_3; +connect server_1_con2, localhost, root,,; +############################# +# Test cases +############################# +# +# Test Case 1) If both replicas simulate a delay that is within the +# allowed timeout, the primary should delay killing the suspended thread +# until an ACK is received (Rpl_semi_sync_master_yes_tx should be 1). +# +connection server_1; +#-- +#-- Semi-sync Setup +connection server_1; +#-- Enable semi-sync on slaves +let slave_last= 3 +connection server_2; +set global rpl_semi_sync_slave_enabled = 1; +include/stop_slave.inc +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status ON +connection server_3; +set global rpl_semi_sync_slave_enabled = 1; +include/stop_slave.inc +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status ON +#-- Enable semi-sync on master +connection server_1; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; +set @@global.rpl_semi_sync_master_timeout= 1600; +#-- Wait for master to recognize semi-sync slaves +connection server_1; +#-- Master should have semi-sync enabled with 2 connections +show status like 'Rpl_semi_sync_master_status'; +Variable_name Value +Rpl_semi_sync_master_status ON +show status like 'Rpl_semi_sync_master_clients'; +Variable_name Value +Rpl_semi_sync_master_clients 2 +#-- Prepare servers to simulate delay or error +connection server_1; +SET @@GLOBAL.debug_dbug= ""; +connection server_2; +SET @@GLOBAL.debug_dbug= "+d,simulate_delay_semisync_slave_reply"; +connection server_3; +SET @@GLOBAL.debug_dbug= "+d,simulate_delay_semisync_slave_reply"; +#-- +#-- Test begins +connection server_1; +#-- Begin semi-sync transaction +INSERT INTO t1 VALUES (1); +connection server_1_con2; +#-- Wait until master recognizes a connection is awaiting semi-sync ACK +show status like 'Rpl_semi_sync_master_wait_sessions'; +Variable_name Value +Rpl_semi_sync_master_wait_sessions 1 +#-- Give enough time after timeout/ack received to query yes_tx/no_tx +SET @@GLOBAL.debug_dbug= "+d,delay_shutdown_phase_2_after_semisync_wait"; +#-- Begin master shutdown +SHUTDOWN WAIT FOR ALL SLAVES; +connection server_1; +#-- Ensure either ACK was received (yes_tx=1) or timeout (no_tx=1) +show status like 'Rpl_semi_sync_master_yes_tx'; +Variable_name Value +Rpl_semi_sync_master_yes_tx 1 +show status like 'Rpl_semi_sync_master_no_tx'; +Variable_name Value +Rpl_semi_sync_master_no_tx 0 +connection server_1_con2; +# Check logs to ensure shutdown was delayed +FOUND 1 /Delaying shutdown to await semi-sync ACK/ in mysqld.1.err +# Validate slave data is in correct state +connection server_2; +select count(*)=1 from t1; +count(*)=1 +1 +connection server_3; +select count(*)=1 from t1; +count(*)=1 +1 +# +#-- Re-synchronize slaves with master and disable semi-sync +#-- Stop slaves +connection server_2; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0; +include/stop_slave.inc +connection server_3; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0; +include/stop_slave.inc +#-- Bring the master back up +connection server_1_con2; +connection default; +connection server_1; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 0; +show status like 'Rpl_semi_sync_master_status'; +Variable_name Value +Rpl_semi_sync_master_status OFF +TRUNCATE TABLE t1; +#-- Bring slaves back up +connection server_2; +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status OFF +SELECT COUNT(*)=0 from t1; +COUNT(*)=0 +1 +connection server_3; +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status OFF +SELECT COUNT(*)=0 from t1; +COUNT(*)=0 +1 +# +# Test Case 2) If both replicas simulate an error before sending an ACK, +# the primary should delay killing the suspended thread until the +# timeout is reached (Rpl_semi_sync_master_no_tx should be 1). +# +connection server_1; +#-- +#-- Semi-sync Setup +connection server_1; +#-- Enable semi-sync on slaves +let slave_last= 3 +connection server_2; +set global rpl_semi_sync_slave_enabled = 1; +include/stop_slave.inc +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status ON +connection server_3; +set global rpl_semi_sync_slave_enabled = 1; +include/stop_slave.inc +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status ON +#-- Enable semi-sync on master +connection server_1; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; +set @@global.rpl_semi_sync_master_timeout= 500; +#-- Wait for master to recognize semi-sync slaves +connection server_1; +#-- Master should have semi-sync enabled with 2 connections +show status like 'Rpl_semi_sync_master_status'; +Variable_name Value +Rpl_semi_sync_master_status ON +show status like 'Rpl_semi_sync_master_clients'; +Variable_name Value +Rpl_semi_sync_master_clients 2 +#-- Prepare servers to simulate delay or error +connection server_1; +SET @@GLOBAL.debug_dbug= "+d,mysqld_delay_kill_threads_phase_1"; +connection server_2; +SET @@GLOBAL.debug_dbug= "+d,corrupt_queue_event"; +connection server_3; +SET @@GLOBAL.debug_dbug= "+d,corrupt_queue_event"; +#-- +#-- Test begins +connection server_1; +#-- Begin semi-sync transaction +INSERT INTO t1 VALUES (1); +connection server_1_con2; +#-- Wait until master recognizes a connection is awaiting semi-sync ACK +show status like 'Rpl_semi_sync_master_wait_sessions'; +Variable_name Value +Rpl_semi_sync_master_wait_sessions 1 +#-- Give enough time after timeout/ack received to query yes_tx/no_tx +SET @@GLOBAL.debug_dbug= "+d,delay_shutdown_phase_2_after_semisync_wait"; +#-- Begin master shutdown +SHUTDOWN WAIT FOR ALL SLAVES; +connection server_1; +#-- Ensure either ACK was received (yes_tx=1) or timeout (no_tx=1) +show status like 'Rpl_semi_sync_master_yes_tx'; +Variable_name Value +Rpl_semi_sync_master_yes_tx 0 +show status like 'Rpl_semi_sync_master_no_tx'; +Variable_name Value +Rpl_semi_sync_master_no_tx 1 +connection server_1_con2; +# Check logs to ensure shutdown was delayed +FOUND 2 /Delaying shutdown to await semi-sync ACK/ in mysqld.1.err +# Validate slave data is in correct state +connection server_2; +select count(*)=0 from t1; +count(*)=0 +1 +connection server_3; +select count(*)=0 from t1; +count(*)=0 +1 +# +#-- Re-synchronize slaves with master and disable semi-sync +#-- Stop slaves +connection server_2; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0; +include/stop_slave.inc +connection server_3; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0; +include/stop_slave.inc +#-- Bring the master back up +connection server_1_con2; +connection default; +connection server_1; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 0; +show status like 'Rpl_semi_sync_master_status'; +Variable_name Value +Rpl_semi_sync_master_status OFF +TRUNCATE TABLE t1; +#-- Bring slaves back up +connection server_2; +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status OFF +SELECT COUNT(*)=0 from t1; +COUNT(*)=0 +1 +connection server_3; +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status OFF +SELECT COUNT(*)=0 from t1; +COUNT(*)=0 +1 +# +# Test Case 3) If one replica simulates a delay within the allowed +# timeout and the other simulates an error before sending an ACK, the +# primary should delay killing the suspended thread until it receives an +# ACK from the delayed slave (Rpl_semi_sync_master_yes_tx should be 1). +# +connection server_1; +#-- +#-- Semi-sync Setup +connection server_1; +#-- Enable semi-sync on slaves +let slave_last= 3 +connection server_2; +set global rpl_semi_sync_slave_enabled = 1; +include/stop_slave.inc +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status ON +connection server_3; +set global rpl_semi_sync_slave_enabled = 1; +include/stop_slave.inc +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status ON +#-- Enable semi-sync on master +connection server_1; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; +set @@global.rpl_semi_sync_master_timeout= 1600; +#-- Wait for master to recognize semi-sync slaves +connection server_1; +#-- Master should have semi-sync enabled with 2 connections +show status like 'Rpl_semi_sync_master_status'; +Variable_name Value +Rpl_semi_sync_master_status ON +show status like 'Rpl_semi_sync_master_clients'; +Variable_name Value +Rpl_semi_sync_master_clients 2 +#-- Prepare servers to simulate delay or error +connection server_1; +SET @@GLOBAL.debug_dbug= "+d,mysqld_delay_kill_threads_phase_1"; +connection server_2; +SET @@GLOBAL.debug_dbug= "+d,corrupt_queue_event"; +connection server_3; +SET @@GLOBAL.debug_dbug= "+d,simulate_delay_semisync_slave_reply"; +#-- +#-- Test begins +connection server_1; +#-- Begin semi-sync transaction +INSERT INTO t1 VALUES (1); +connection server_1_con2; +#-- Wait until master recognizes a connection is awaiting semi-sync ACK +show status like 'Rpl_semi_sync_master_wait_sessions'; +Variable_name Value +Rpl_semi_sync_master_wait_sessions 1 +#-- Give enough time after timeout/ack received to query yes_tx/no_tx +SET @@GLOBAL.debug_dbug= "+d,delay_shutdown_phase_2_after_semisync_wait"; +#-- Begin master shutdown +SHUTDOWN WAIT FOR ALL SLAVES; +connection server_1; +#-- Ensure either ACK was received (yes_tx=1) or timeout (no_tx=1) +show status like 'Rpl_semi_sync_master_yes_tx'; +Variable_name Value +Rpl_semi_sync_master_yes_tx 1 +show status like 'Rpl_semi_sync_master_no_tx'; +Variable_name Value +Rpl_semi_sync_master_no_tx 0 +connection server_1_con2; +# Check logs to ensure shutdown was delayed +FOUND 3 /Delaying shutdown to await semi-sync ACK/ in mysqld.1.err +# Validate slave data is in correct state +connection server_2; +select count(*)=0 from t1; +count(*)=0 +1 +connection server_3; +select count(*)=1 from t1; +count(*)=1 +1 +# +#-- Re-synchronize slaves with master and disable semi-sync +#-- Stop slaves +connection server_2; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0; +include/stop_slave.inc +connection server_3; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0; +include/stop_slave.inc +#-- Bring the master back up +connection server_1_con2; +connection default; +connection server_1; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 0; +show status like 'Rpl_semi_sync_master_status'; +Variable_name Value +Rpl_semi_sync_master_status OFF +TRUNCATE TABLE t1; +#-- Bring slaves back up +connection server_2; +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status OFF +SELECT COUNT(*)=0 from t1; +COUNT(*)=0 +1 +connection server_3; +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status OFF +SELECT COUNT(*)=0 from t1; +COUNT(*)=0 +1 +# +# Test Case 4) If a replica errors before sending an ACK, it will cause +# the IO thread to stop and handle the error. During error handling, if +# semi-sync is active, the replica will form a new connection with the +# primary to kill the active connection. However, if the primary is +# shutting down, it may kill the new connection, thereby leaving the +# active semi-sync connection in-tact. The slave should notice this, and +# not issue a `QUIT` command to the primary, which would otherwise be +# sent to kill an active connection. This test case validates that the +# slave does not send a `QUIT` in this case (Rpl_semi_sync_master_yes_tx +# should be 1 because server_3 will send the ACK within a valid timeout). +# +connection server_1; +#-- +#-- Semi-sync Setup +connection server_1; +#-- Enable semi-sync on slaves +let slave_last= 3 +connection server_2; +set global rpl_semi_sync_slave_enabled = 1; +include/stop_slave.inc +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status ON +connection server_3; +set global rpl_semi_sync_slave_enabled = 1; +include/stop_slave.inc +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status ON +#-- Enable semi-sync on master +connection server_1; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; +set @@global.rpl_semi_sync_master_timeout= 1600; +#-- Wait for master to recognize semi-sync slaves +connection server_1; +#-- Master should have semi-sync enabled with 2 connections +show status like 'Rpl_semi_sync_master_status'; +Variable_name Value +Rpl_semi_sync_master_status ON +show status like 'Rpl_semi_sync_master_clients'; +Variable_name Value +Rpl_semi_sync_master_clients 2 +#-- Prepare servers to simulate delay or error +connection server_1; +SET @@GLOBAL.debug_dbug= "+d,mysqld_delay_kill_threads_phase_1"; +connection server_2; +SET @@GLOBAL.debug_dbug= "+d,corrupt_queue_event,slave_delay_killing_semisync_connection"; +connection server_3; +SET @@GLOBAL.debug_dbug= "+d,simulate_delay_semisync_slave_reply"; +#-- +#-- Test begins +connection server_1; +#-- Begin semi-sync transaction +INSERT INTO t1 VALUES (1); +connection server_1_con2; +#-- Wait until master recognizes a connection is awaiting semi-sync ACK +show status like 'Rpl_semi_sync_master_wait_sessions'; +Variable_name Value +Rpl_semi_sync_master_wait_sessions 1 +#-- Give enough time after timeout/ack received to query yes_tx/no_tx +SET @@GLOBAL.debug_dbug= "+d,delay_shutdown_phase_2_after_semisync_wait"; +#-- Begin master shutdown +SHUTDOWN WAIT FOR ALL SLAVES; +connection server_1; +#-- Ensure either ACK was received (yes_tx=1) or timeout (no_tx=1) +show status like 'Rpl_semi_sync_master_yes_tx'; +Variable_name Value +Rpl_semi_sync_master_yes_tx 1 +show status like 'Rpl_semi_sync_master_no_tx'; +Variable_name Value +Rpl_semi_sync_master_no_tx 0 +connection server_1_con2; +# Check logs to ensure shutdown was delayed +FOUND 4 /Delaying shutdown to await semi-sync ACK/ in mysqld.1.err +# Validate slave data is in correct state +connection server_2; +select count(*)=0 from t1; +count(*)=0 +1 +connection server_3; +select count(*)=1 from t1; +count(*)=1 +1 +# +#-- Re-synchronize slaves with master and disable semi-sync +#-- Stop slaves +connection server_2; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0; +include/stop_slave.inc +connection server_3; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0; +include/stop_slave.inc +#-- Bring the master back up +connection server_1_con2; +connection default; +connection server_1; +SET @@GLOBAL.debug_dbug= ""; +SET @@GLOBAL.rpl_semi_sync_master_enabled = 0; +show status like 'Rpl_semi_sync_master_status'; +Variable_name Value +Rpl_semi_sync_master_status OFF +TRUNCATE TABLE t1; +#-- Bring slaves back up +connection server_2; +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status OFF +SELECT COUNT(*)=0 from t1; +COUNT(*)=0 +1 +connection server_3; +include/start_slave.inc +show status like 'Rpl_semi_sync_slave_status'; +Variable_name Value +Rpl_semi_sync_slave_status OFF +SELECT COUNT(*)=0 from t1; +COUNT(*)=0 +1 +############################# +# Cleanup +############################# +connection server_2; +include/stop_slave.inc +include/start_slave.inc +connection server_3; +include/stop_slave.inc +include/start_slave.inc +connection server_1; +drop table t1; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/r/rpl_ssl.result b/mysql-test/suite/rpl/r/rpl_ssl.result index 0b3a6cd0eca..ce9e4d486cf 100644 --- a/mysql-test/suite/rpl/r/rpl_ssl.result +++ b/mysql-test/suite/rpl/r/rpl_ssl.result @@ -23,6 +23,8 @@ t Master_SSL_Allowed = 'Yes' Master_SSL_CA_Path = '' Master_SSL_CA_File = 'MYSQL_TEST_DIR/std_data/cacert.pem' +Master_SSL_Crl = '' +Master_SSL_Crlpath = '' Master_SSL_Cert = 'MYSQL_TEST_DIR/std_data/client-cert.pem' Master_SSL_Key = 'MYSQL_TEST_DIR/std_data/client-key.pem' include/check_slave_is_running.inc @@ -37,6 +39,8 @@ include/wait_for_slave_to_start.inc Master_SSL_Allowed = 'Yes' Master_SSL_CA_Path = '' Master_SSL_CA_File = 'MYSQL_TEST_DIR/std_data/cacert.pem' +Master_SSL_Crl = '' +Master_SSL_Crlpath = '' Master_SSL_Cert = 'MYSQL_TEST_DIR/std_data/client-cert.pem' Master_SSL_Key = 'MYSQL_TEST_DIR/std_data/client-key.pem' include/check_slave_is_running.inc diff --git a/mysql-test/suite/rpl/r/rpl_unsafe_statements.result b/mysql-test/suite/rpl/r/rpl_unsafe_statements.result index 27065fffaa6..941984bf329 100644 --- a/mysql-test/suite/rpl/r/rpl_unsafe_statements.result +++ b/mysql-test/suite/rpl/r/rpl_unsafe_statements.result @@ -1,6 +1,5 @@ include/master-slave.inc [connection master] -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); CREATE TABLE t1(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TABLE t2(id INT AUTO_INCREMENT, i INT, PRIMARY KEY (id)) ENGINE=INNODB; CREATE TRIGGER trig1 AFTER INSERT ON t1 @@ -50,13 +49,9 @@ connection master; DROP TABLE t1; CREATE TABLE t1(i INT, j INT, UNIQUE KEY(i), UNIQUE KEY(j)) ENGINE=INNODB; INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1; -Warnings: -Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe START TRANSACTION; LOCK TABLES t1 WRITE; INSERT INTO t1 (i,j) VALUES (1,2) ON DUPLICATE KEY UPDATE j=j+1; -Warnings: -Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE on a table with more than one UNIQUE KEY is unsafe UNLOCK TABLES; COMMIT; connection slave; diff --git a/mysql-test/suite/rpl/t/rpl_gtid_grouping.test b/mysql-test/suite/rpl/t/rpl_gtid_grouping.test new file mode 100644 index 00000000000..66448c4f96c --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_gtid_grouping.test @@ -0,0 +1,97 @@ +# ==== Purpose ==== +# +# Test verifies that replicated transaction boundaries are set properly +# at receiving from master time. +# +# ==== Implementation ==== +# +# A. Simulate an unnoticeable loss of Xid event to observe a slave error, +# then restart slave to recover from the failure. +# B. Do the same to GTID event. +# +# ==== References ==== +# +# MDEV-27697 slave must recognize incomplete replication event group +# +--source include/have_binlog_format_mixed.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/master-slave.inc + +--connection slave +call mtr.add_suppression("Unexpected break of being relay-logged GTID 0-27697-1000"); +call mtr.add_suppression("Relay log write failure: could not queue event from master"); +call mtr.add_suppression("The current group of events starts with a non-GTID"); + +--source include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=slave_pos; +--source include/start_slave.inc + +--connection master +CREATE TABLE t (a INT) ENGINE=innodb; +INSERT INTO t VALUES(1); +save_master_pos; + +--echo ### A. Simulate an unnoticeable loss of Xid event +--sync_slave_with_master +SET @@global.debug_dbug="+d,slave_discard_xid_for_gtid_0_x_1000"; + +--connection master +SET @@gtid_seq_no=1000; +set @@server_id=27697; +INSERT INTO t VALUES(1000); +set @@server_id=default; +INSERT INTO t VALUES(1001); + +--echo ## Prove the error occurs. +--connection slave +# ER_SLAVE_RELAY_LOG_WRITE_FAILURE +--let $slave_io_errno = 1595 +--source include/wait_for_slave_io_error.inc +## EOP + +--echo ## Prove the slave recovers after the simulation condtion is lifted. +SET @@global.debug_dbug=default; +--source include/start_slave.inc + +--echo ### B. Do the same to GTID event. +--connection slave +SET @@global.debug_dbug="+d,slave_discard_gtid_0_x_1002"; + +--connection master +SET @@gtid_seq_no=1002; +set @@server_id=27697; +INSERT INTO t VALUES(1002); +set @@server_id=default; +INSERT INTO t VALUES(1003); + +--echo ## Prove the error occurs. +--connection slave +# ER_SLAVE_RELAY_LOG_WRITE_FAILURE +--let $slave_io_errno = 1595 +--source include/wait_for_slave_io_error.inc +## EOP + +--echo ## Prove the slave recovers after the simulation condtion is lifted. +SET @@global.debug_dbug=default; +--source include/start_slave.inc + +--connection master +save_master_pos; + +--sync_slave_with_master +## EOP + +--let $diff_tables=master:t,slave:t +--source include/diff_tables.inc + +--echo "===== Clean up =====" +--connection slave +--source include/stop_slave.inc +CHANGE MASTER TO MASTER_USE_GTID=no; +--source include/start_slave.inc + +--connection master +DROP TABLE t; +SET GLOBAL LOG_WARNINGS=default; +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_iodku.test b/mysql-test/suite/rpl/t/rpl_iodku.test new file mode 100644 index 00000000000..815b927c350 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_iodku.test @@ -0,0 +1,50 @@ +--source include/have_innodb.inc +--source include/master-slave.inc + +if (`select @@binlog_format = "statement"`) +{ + call mtr.add_suppression("Unsafe statement written to the binary log using statement"); +} + +## MDEV-28310 loss of binlog event for multi-record IODKU +# Check that the duplicate key error does not cause +# loss of replication event for IODKU that specifies values +# for at least two unique columns per record. +# "Implicit" NULL value of the auto-increment column also counts. + +CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, a INT, b INT, c INT, + UNIQUE (a), UNIQUE (b)) ENGINE=innodb; +INSERT INTO t1 (`a`,`c`) VALUES (1,1), (2,1) ON DUPLICATE KEY UPDATE c = 1; +--echo # UNSAFE +# because of two keys involved: a UK and PK even though implicitly via auto-inc +INSERT INTO t1 (`a`,`c`) VALUES (3, 1),(2,1), (1,1) ON DUPLICATE KEY UPDATE c = a * 10 + VALUES(c); +SELECT * from t1; + +--sync_slave_with_master +--let $diff_tables = master:t1,slave:t1 +--source include/diff_tables.inc + +## MDEV-21810 MBR: Unexpected "Unsafe statement" warning for unsafe IODKU +# Unnecessary unsafe statement warning is not error-logged anymore. + + +--connection master +CREATE OR REPLACE TABLE t1 (a INT, b INT, c INT, UNIQUE (a), UNIQUE (b)) ENGINE=innodb; +INSERT INTO t1 VALUES (1,10,1); +--echo # eligable for the statement format run unsafe warning +INSERT INTO t1 VALUES (2,20,2) ON DUPLICATE KEY UPDATE c = 100; +--echo # not eligable: no warning in the statement format run +INSERT INTO t1 (`a`,`c`) VALUES (3, 1) ON DUPLICATE KEY UPDATE c = 99; +SELECT * from t1; + +--sync_slave_with_master +--let $diff_tables = master:t1,slave:t1 +--source include/diff_tables.inc + +# Cleanup +--connection master +DROP TABLE t1; +--sync_slave_with_master + + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17614.test b/mysql-test/suite/rpl/t/rpl_mdev_17614.test index 9b86c8c15b5..c11aad3305e 100644 --- a/mysql-test/suite/rpl/t/rpl_mdev_17614.test +++ b/mysql-test/suite/rpl/t/rpl_mdev_17614.test @@ -2,15 +2,22 @@ source include/have_debug.inc; source include/have_innodb.inc; -- source include/have_binlog_format_statement.inc source include/master-slave.inc; -# MDEV-17614 -# INSERT on dup key update is replication unsafe -# There can be three case -# 1. 2 unique key, Replication is unsafe. -# 2. 2 unique key , with one auto increment key, Safe to replicate because Innodb will acquire gap lock -# 3. n no of unique keys (n>1) but insert is only in 1 unique key -# 4. 2 unique key , with one auto increment key(but user gives auto inc value), unsafe to replicate +# MDEV-17614 INSERT on dup key update is replication unsafe +# +# The following cases are tested below: +# 1. 2 unique key, replication is UNSAFE +# 2. 2 unique key, with one auto increment key and implicit value to it. +# It is UNSAFE because autoinc column values of being inserted records +# are revealed dynamically, so unknown at the binlog-format decision time +# and hence this pessimistic expectation +# 3. 2 unique keys +# A. insert is only in 1 unique key, still all colums are specified => UNSAFE +# B. both unique keys are specified => UNSAFE +# C. only one unique key is specified => SAFE (motivated by MDEV-28310) +# 4. 2 unique key, with one auto increment key(but user gives auto inc value) => +# UNSAFE to replicate -# Case 1 +--echo # Case 1: UNSAFE call mtr.add_suppression("Unsafe statement written to the binary log using statement format"); CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY , b INT, UNIQUE(b), c int) engine=innodb; @@ -42,7 +49,8 @@ drop table t1; connection slave; start slave; --source include/wait_for_slave_to_start.inc -# Case 2 + +--echo # Case 2: UNSAFE --connection master CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, UNIQUE(b), c int) engine=innodb; @@ -64,7 +72,7 @@ connection master; drop table t1; --sync_slave_with_master -# Case 3 +--echo # Case 3A: UNSAFE --connection master CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, UNIQUE(b), c int, d int ) engine=innodb; @@ -85,7 +93,50 @@ connection master; drop table t1; --sync_slave_with_master -# Case 4 +--echo # Case 3B: UNSAFE - all column specified. +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 VALUES (2, NULL, 2, 2) ON DUPLICATE KEY UPDATE c=VALUES(c); + --connection master1 + INSERT INTO t1 VALUES(3, NULL, 2, 3) ON DUPLICATE KEY UPDATE c=VALUES(c); +--connection master +COMMIT; +SELECT * FROM t1; +--sync_slave_with_master +--echo #same data as master +SELECT * FROM t1; +connection master; +drop table t1; +--sync_slave_with_master + + +--echo # Case 3C: SAFE - only one unique key (PK) specified. +--connection master +CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, +UNIQUE(b), c int, d int ) engine=innodb; +sync_slave_with_master; +connection master; +INSERT INTO t1 VALUES (1, 1, 1, 1); +BEGIN; +INSERT INTO t1 (`a`, `c`, `d`) VALUES (2, 2, 2) ON DUPLICATE KEY UPDATE c=99; + --connection master1 + INSERT INTO t1 (`a`, `c`, `d`) VALUES(3, 2, 3) ON DUPLICATE KEY UPDATE c=100; +--connection master +COMMIT; +SELECT * FROM t1; +--sync_slave_with_master +--echo #same data as master +SELECT * FROM t1; +connection master; +drop table t1; +--sync_slave_with_master + +--echo # Case 4: UNSAFE --connection master CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY auto_increment, b INT, UNIQUE(b), c int) engine=innodb; diff --git a/mysql-test/suite/rpl/t/rpl_mysql_manager_race_condition-slave.opt b/mysql-test/suite/rpl/t/rpl_mysql_manager_race_condition-slave.opt new file mode 100644 index 00000000000..d127ef62043 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mysql_manager_race_condition-slave.opt @@ -0,0 +1 @@ +--gtid-cleanup-batch-size=1 diff --git a/mysql-test/suite/rpl/t/rpl_mysql_manager_race_condition.test b/mysql-test/suite/rpl/t/rpl_mysql_manager_race_condition.test new file mode 100644 index 00000000000..751da3158b7 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mysql_manager_race_condition.test @@ -0,0 +1,67 @@ +# +# Purpose: +# This test ensures that, during mysqld initialization, the mysql handle +# manager starts before the binlog background thread. This is because the +# binlog background thread uses the mysql handle manager, and if the background +# thread tries to submit a job to the handle manager before it is +# initialized/started, mysqld can crash (the actual behavior is undefined). +# This race condition lead to the problem described in MDEV-26473. +# +# Methodology: +# This test ensures that the binlog background thread cannot be started +# before the mysql manager is started. Specifically, it forces a path in +# the binlog background thread to call mysql_manager_submit() by reducing +# --gtid-cleanup-batch-size to be 1 (which submits a job to delete unused rows +# from the mysql.gtid_slave_pos* tables). With this path forced, the main +# mysqld thread is suspended just before its handle manager initialization to +# allow time for the binlog thread to call mysql_manager_submit. The fix +# associated with this test should enforce that the binlog background thread is +# not created before the handle manager is initialized. +# +# References: +# MDEV-26473 mysqld got exception 0xc0000005 (rpl_slave_state/rpl_load_gtid_slave_state) +# + +--source include/have_debug.inc +--source include/master-slave.inc + +# The race condition discovered from MDEV-26473 is binlog format independent. +# We use ROW format though because it was used by the reporter. +--source include/have_binlog_format_row.inc + +--connection master + +--echo # Create a GTID event so the binlog background thread will submit a +--echo # mysql handler job the next time mysqld is restarted. +create table t1 (a int); +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--echo # Set a debug point that forces the main mysqld thread to sleep before +--echo # anything is initialized for the mysql handle manager +--let $rpl_server_parameters=--debug_dbug="+d,delay_start_handle_manager" + + +--echo # Restart the slave mysqld instance so it re-initializes with the +--echo # binlog background thread submitting a mysql handler job and the +--echo # mysql handler initialization suspending for a second. Without the fix +--echo # associated with this test/patch, the following restart will error +--echo # with a failed assertion. +--source include/rpl_restart_server.inc +--source include/start_slave.inc + + +--echo # +--echo # Cleanup +--echo # + +--connection master +drop table t1; +--source include/save_master_gtid.inc + +--connection slave +--source include/sync_with_master_gtid.inc + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_parallel_temptable.test b/mysql-test/suite/rpl/t/rpl_parallel_temptable.test index 04165ee4752..edb854842e1 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_temptable.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_temptable.test @@ -201,9 +201,16 @@ INSERT INTO t1 VALUES (0, 1); # execution of format_description event will not wait infinitely # for a commit of the incomplete group that never happens. +# Apart from the suppression, MDEV-27697 refinement to the original test needs +# an allowance to one time accept malformed event group. +set @@sql_log_bin=0; +call mtr.add_suppression("Unexpected break of being relay-logged GTID 1-1-32 event group by the current GTID event 0-1-4"); +set @@sql_log_bin=1; +set @@global.debug_dbug="+d,slave_discard_xid_for_gtid_0_x_1000"; --source include/start_slave.inc #--sync_with_master --source include/sync_with_master_gtid.inc +set @@global.debug_dbug=""; SELECT * FROM t1 ORDER BY a; SHOW STATUS LIKE 'Slave_open_temp_tables'; diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_shutdown_await_ack.cnf b/mysql-test/suite/rpl/t/rpl_semi_sync_shutdown_await_ack.cnf new file mode 100644 index 00000000000..2cf1b1786bd --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_shutdown_await_ack.cnf @@ -0,0 +1,14 @@ +!include ../my.cnf + +[mysqld.1] +log_warnings=9 + +[mysqld.2] +log_warnings=9 + +[mysqld.3] +log_warnings=9 + +[ENV] +SERVER_MYPORT_3= @mysqld.3.port +SERVER_MYSOCK_3= @mysqld.3.socket diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_shutdown_await_ack.inc b/mysql-test/suite/rpl/t/rpl_semi_sync_shutdown_await_ack.inc new file mode 100644 index 00000000000..a232f68540d --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_shutdown_await_ack.inc @@ -0,0 +1,163 @@ +# +# Helper file to ensure that a primary waits for all ACKS (or timeout) from its +# replicas before shutting down. +# +# Parameters: +# server_1_dbug (string) Debug setting for primary (server 1) +# server_2_dbug (string) Debug setting to simulate delay or error on +# the first replica (server 2) +# server_3_dbug (string) Debug setting to simulate delay or error on +# the second replica (server 3) +# semisync_timeout (int) Rpl_semi_sync_master_timeout to use +# server_2_expect_row_count (int) The number of rows expected on the first +# replica after the shutdown +# server_3_expect_row_count (int) The number of rows expected on the second +# replica after the shutdown +# + +--connection server_1 +let $log_error_file= `SELECT @@GLOBAL.log_error`; + +--echo #-- +--echo #-- Semi-sync Setup + +--connection server_1 +--save_master_pos + +echo #-- Enable semi-sync on slaves +let slave_last= 3; +--let i= 2 +while (`SELECT $i <= $slave_last`) +{ + --connection server_$i + --sync_with_master + + set global rpl_semi_sync_slave_enabled = 1; + source include/stop_slave.inc; + source include/start_slave.inc; + show status like 'Rpl_semi_sync_slave_status'; + + --inc $i +} + +--echo #-- Enable semi-sync on master +--connection server_1 +SET @@GLOBAL.rpl_semi_sync_master_enabled = 1; +--eval set @@global.rpl_semi_sync_master_timeout= $semisync_timeout + +--echo #-- Wait for master to recognize semi-sync slaves +--connection server_1 +let $status_var= Rpl_semi_sync_master_clients; +let $status_var_value= 2; +source include/wait_for_status_var.inc; + +--echo #-- Master should have semi-sync enabled with 2 connections +show status like 'Rpl_semi_sync_master_status'; +show status like 'Rpl_semi_sync_master_clients'; + +--echo #-- Prepare servers to simulate delay or error +--connection server_1 +--eval SET @@GLOBAL.debug_dbug= $server_1_dbug +--connection server_2 +--eval SET @@GLOBAL.debug_dbug= $server_2_dbug +--connection server_3 +--eval SET @@GLOBAL.debug_dbug= $server_3_dbug + +--echo #-- +--echo #-- Test begins + +--connection server_1 +--echo #-- Begin semi-sync transaction +--send INSERT INTO t1 VALUES (1) + +--connection server_1_con2 +--echo #-- Wait until master recognizes a connection is awaiting semi-sync ACK +let $status_var= Rpl_semi_sync_master_wait_sessions; +let $status_var_value= 1; +source include/wait_for_status_var.inc; +show status like 'Rpl_semi_sync_master_wait_sessions'; + +--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +wait +EOF + +--echo #-- Give enough time after timeout/ack received to query yes_tx/no_tx +SET @@GLOBAL.debug_dbug= "+d,delay_shutdown_phase_2_after_semisync_wait"; + +--echo #-- Begin master shutdown +--send SHUTDOWN WAIT FOR ALL SLAVES + +--connection server_1 +--reap +--echo #-- Ensure either ACK was received (yes_tx=1) or timeout (no_tx=1) +show status like 'Rpl_semi_sync_master_yes_tx'; +show status like 'Rpl_semi_sync_master_no_tx'; + +--connection server_1_con2 +--reap +--source include/wait_until_disconnected.inc + +--echo # Check logs to ensure shutdown was delayed +--let SEARCH_FILE=$log_error_file +--let SEARCH_PATTERN=Delaying shutdown to await semi-sync ACK +--source include/search_pattern_in_file.inc + +--echo # Validate slave data is in correct state +--connection server_2 +--eval select count(*)=$server_2_expect_row_count from t1 +--connection server_3 +--eval select count(*)=$server_3_expect_row_count from t1 + +--echo # +--echo #-- Re-synchronize slaves with master and disable semi-sync + +--echo #-- Stop slaves + +--connection server_2 +--eval SET @@GLOBAL.debug_dbug= "$sav_server_2_dbug" +--eval SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0 +source include/stop_slave.inc; + +--connection server_3 +--eval SET @@GLOBAL.debug_dbug= "$sav_server_3_dbug" +--eval SET @@GLOBAL.rpl_semi_sync_slave_enabled= 0 +source include/stop_slave.inc; + +--echo #-- Bring the master back up +--connection server_1_con2 +--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +restart +EOF + +--enable_reconnect +--source include/wait_until_connected_again.inc + +--connection default +--enable_reconnect +--source include/wait_until_connected_again.inc + +--connection server_1 +--enable_reconnect +--source include/wait_until_connected_again.inc + +--eval SET @@GLOBAL.debug_dbug= "$sav_master_dbug" +let $status_var= Rpl_semi_sync_master_clients; +let $status_var_value= 0; +source include/wait_for_status_var.inc; +--eval SET @@GLOBAL.rpl_semi_sync_master_enabled = 0 +show status like 'Rpl_semi_sync_master_status'; + +TRUNCATE TABLE t1; +--save_master_pos + +--echo #-- Bring slaves back up +--let i= 2 +while (`SELECT $i <= $slave_last`) +{ + --connection server_$i + source include/start_slave.inc; + show status like 'Rpl_semi_sync_slave_status'; + --sync_with_master + SELECT COUNT(*)=0 from t1; + --inc $i +} diff --git a/mysql-test/suite/rpl/t/rpl_semi_sync_shutdown_await_ack.test b/mysql-test/suite/rpl/t/rpl_semi_sync_shutdown_await_ack.test new file mode 100644 index 00000000000..5e9cda6466e --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_semi_sync_shutdown_await_ack.test @@ -0,0 +1,214 @@ +# +# Purpose: +# This test validates that data is consistent between a primary and replica +# in semi-sync mode when the primary is issued `SHUTDOWN WAIT FOR SLAVES` +# during an active communication. More specifically, the primary should not +# kill the connection until it is sure a replica has received all binlog +# data, i.e. once the primary receives the ACK. If a primary is issued a +# shutdown before receiving an ACK, it should wait until either 1) the ACK is +# received, or 2) the configured timeout (rpl_semi_sync_master_timeout) is +# reached. +# +# Methodology: +# Using a topology consisting of one primary with two replicas, all in +# semi-sync mode, we use DEBUG_DBUG to simulate an error or delay on the +# replicas during an active communication while the primary is issued +# `SHUTDOWN WAIT FOR SLAVES`. We create four test cases to ensure the primary +# will correctly wait for the communication to finish, and use the semi-sync +# status variables Rpl_semi_sync_master_yes_tx and Rpl_semi_sync_master_no_tx +# to ensure the connection was not prematurely killed due to the shutdown. +# Test Case 1) If both replicas simulate a delay that is within the allowed +# timeout, the primary should delay killing the suspended thread +# until an ACK is received (Rpl_semi_sync_master_yes_tx should +# be 1). +# Test Case 2) If both replicas simulate an error before sending an ACK, the +# primary should delay killing the suspended thread until the +# the timeout is reached (Rpl_semi_sync_master_no_tx should be +# 1). +# Test Case 3) If one replica simulates a delay within the allowed timeout +# and the other simulates an error before sending an ACK, the +# primary should delay killing the suspended thread until it +# receives an ACK from the delayed slave +# (Rpl_semi_sync_master_yes_tx should be 1). +# Test Case 4) If a replica errors before sending an ACK, it will cause the +# IO thread to stop and handle the error. During error handling, +# if semi-sync is active, the replica will form a new connection +# with the primary to kill the active connection. However, if +# the primary is shutting down, it may kill the new connection, +# thereby leaving the active semi-sync connection in-tact. The +# slave should notice this, and not issue a `QUIT` command to +# the primary, which would otherwise be sent to kill an active +# connection. This test case validates that the slave does not +# send a `QUIT` in this case (Rpl_semi_sync_master_yes_tx should +# be 1 because server_3 will send the ACK within a valid +# timeout). +# +# References: +# MDEV-11853: semisync thread can be killed after sync binlog but before ACK +# in the sync state +# MDEV-28114: Semi-sync Master ACK Receiver Thread Can Error on COM_QUIT +# + +--echo ############################# +--echo # Common setup for all tests +--echo ############################# + +--echo # Note: Simulated slave delay is hardcoded to 800 milliseconds +--echo # Note: Simulated master shutdown delay is hardcoded to 500 milliseconds + +--source include/have_debug.inc +--let $rpl_topology=1->2, 1->3 +--source include/rpl_init.inc + +--connection server_1 + +--echo # Slaves which simulate an error will produce a timeout on the primary +call mtr.add_suppression("Timeout waiting"); +call mtr.add_suppression("did not exit"); + +--let $sav_master_timeout= `SELECT @@global.rpl_semi_sync_master_timeout` +--let $sav_enabled_master= `SELECT @@GLOBAL.rpl_semi_sync_master_enabled` +--let $sav_master_dbug= `SELECT @@GLOBAL.debug_dbug` + +--echo # Suppress slave errors related to the simulated error +--connection server_2 +call mtr.add_suppression("reply failed"); +call mtr.add_suppression("Replication event checksum verification"); +call mtr.add_suppression("Relay log write failure"); +call mtr.add_suppression("Failed to kill the active semi-sync connection"); +--let $sav_enabled_server_2=`SELECT @@GLOBAL.rpl_semi_sync_slave_enabled` +--let $sav_server_2_dbug= `SELECT @@GLOBAL.debug_dbug` + +--connection server_3 +call mtr.add_suppression("reply failed"); +call mtr.add_suppression("Replication event checksum verification"); +call mtr.add_suppression("Relay log write failure"); +call mtr.add_suppression("Failed to kill the active semi-sync connection"); +--let $sav_enabled_server_3=`SELECT @@GLOBAL.rpl_semi_sync_slave_enabled` +--let $sav_server_3_dbug= `SELECT @@GLOBAL.debug_dbug` + +--connection server_1 +CREATE TABLE t1 (a int); +--save_master_pos + +--let i= 2 +--let slave_last= 3 +while (`SELECT $i <= $slave_last`) +{ + --connection server_$i + --sync_with_master + --inc $i +} + +# Set up the connection used to issue the shutdown +--connect(server_1_con2, localhost, root,,) + + +--echo ############################# +--echo # Test cases +--echo ############################# + +--echo # +--echo # Test Case 1) If both replicas simulate a delay that is within the +--echo # allowed timeout, the primary should delay killing the suspended thread +--echo # until an ACK is received (Rpl_semi_sync_master_yes_tx should be 1). +--echo # +--let server_1_dbug= "" +--let server_2_dbug= "+d,simulate_delay_semisync_slave_reply" +--let server_3_dbug= "+d,simulate_delay_semisync_slave_reply" +--let semisync_timeout= 1600 +--let server_2_expect_row_count= 1 +--let server_3_expect_row_count= 1 +--source rpl_semi_sync_shutdown_await_ack.inc + +--echo # +--echo # Test Case 2) If both replicas simulate an error before sending an ACK, +--echo # the primary should delay killing the suspended thread until the +--echo # timeout is reached (Rpl_semi_sync_master_no_tx should be 1). +--echo # +--let server_1_dbug= "+d,mysqld_delay_kill_threads_phase_1" +--let server_2_dbug= "+d,corrupt_queue_event" +--let server_3_dbug= "+d,corrupt_queue_event" +--let semisync_timeout= 500 +--let server_2_expect_row_count= 0 +--let server_3_expect_row_count= 0 +--source rpl_semi_sync_shutdown_await_ack.inc + +--echo # +--echo # Test Case 3) If one replica simulates a delay within the allowed +--echo # timeout and the other simulates an error before sending an ACK, the +--echo # primary should delay killing the suspended thread until it receives an +--echo # ACK from the delayed slave (Rpl_semi_sync_master_yes_tx should be 1). +--echo # +--let server_1_dbug= "+d,mysqld_delay_kill_threads_phase_1" +--let server_2_dbug= "+d,corrupt_queue_event" +--let server_3_dbug= "+d,simulate_delay_semisync_slave_reply" +--let semisync_timeout= 1600 +--let server_2_expect_row_count= 0 +--let server_3_expect_row_count= 1 +--source rpl_semi_sync_shutdown_await_ack.inc + +--echo # +--echo # Test Case 4) If a replica errors before sending an ACK, it will cause +--echo # the IO thread to stop and handle the error. During error handling, if +--echo # semi-sync is active, the replica will form a new connection with the +--echo # primary to kill the active connection. However, if the primary is +--echo # shutting down, it may kill the new connection, thereby leaving the +--echo # active semi-sync connection in-tact. The slave should notice this, and +--echo # not issue a `QUIT` command to the primary, which would otherwise be +--echo # sent to kill an active connection. This test case validates that the +--echo # slave does not send a `QUIT` in this case (Rpl_semi_sync_master_yes_tx +--echo # should be 1 because server_3 will send the ACK within a valid timeout). +--echo # + +# mysqld_delay_kill_threads_phase1 ensures that server_2 will have enough time +# to start a new connection that has the intent to kill the active semi-sync +# connection +--let server_1_dbug= "+d,mysqld_delay_kill_threads_phase_1" + +# slave_delay_killing_semisync_connection ensures that the primary has force +# killed its current connection before it is able to issue `KILL` +--let server_2_dbug= "+d,corrupt_queue_event,slave_delay_killing_semisync_connection" +--let server_3_dbug= "+d,simulate_delay_semisync_slave_reply" +--let semisync_timeout= 1600 +--let server_2_expect_row_count= 0 +--let server_3_expect_row_count= 1 +--source rpl_semi_sync_shutdown_await_ack.inc + +--echo ############################# +--echo # Cleanup +--echo ############################# + +--connection server_2 +source include/stop_slave.inc; +source include/start_slave.inc; + +--disable_query_log +--eval SET @@GLOBAL.rpl_semi_sync_slave_enabled = $sav_enabled_server_2 +--eval SET @@GLOBAL.debug_dbug= "$sav_server_2_dbug" +--enable_query_log + +--connection server_3 +source include/stop_slave.inc; +source include/start_slave.inc; + +--disable_query_log +--eval SET @@GLOBAL.rpl_semi_sync_slave_enabled = $sav_enabled_server_3 +--eval SET @@GLOBAL.debug_dbug= "$sav_server_3_dbug" +--enable_query_log + + +--connection server_1 +let $status_var= Rpl_semi_sync_master_clients; +let $status_var_value= 0; +source include/wait_for_status_var.inc; + +--disable_query_log +--eval SET @@GLOBAL.rpl_semi_sync_master_timeout= $sav_master_timeout +--eval SET @@GLOBAL.rpl_semi_sync_master_enabled= $sav_enabled_master +--eval SET @@GLOBAL.debug_dbug= "$sav_master_dbug" +--enable_query_log + +drop table t1; + +--source include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_unsafe_statements.test b/mysql-test/suite/rpl/t/rpl_unsafe_statements.test index 9185e566b9c..40c9b9bb25f 100644 --- a/mysql-test/suite/rpl/t/rpl_unsafe_statements.test +++ b/mysql-test/suite/rpl/t/rpl_unsafe_statements.test @@ -25,7 +25,7 @@ --source include/have_innodb.inc --source include/have_binlog_format_mixed.inc --source include/master-slave.inc -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); + # Case-1: BINLOG_STMT_UNSAFE_AUTOINC_COLUMNS # Statement is unsafe because it invokes a trigger or a # stored function that inserts into an AUTO_INCREMENT column. |