################################################################################ # Validate that BEFORE, AFTER and BEFORE_AND_AFTER values can only be used on # group_replication_consistency on ONLINE members. # # Test: # 00. The test requires two servers: M1 and M2. # 01. Create two tables on each server before start the group. # 02. Lock table t1 on server2 to block a future update. # 03. Bootstrap group on server1. # 04. Execute transactions T1 on server1. # T1 will block server2 on RECOVERING state. # 05. Start group on server2, it will be on RECOVERING state. # 06. Unlock table t1 on server2. # Member will move to ONLINE state. # 07. Execute transactions T2 on server1. # T2 will make server2 move to ERROR state due to # duplicate key. # 08. Stop group on server2. # Member will move to OFFLINE state. # 09. Clean up. ################################################################################ --source include/have_group_replication_plugin.inc --let $rpl_skip_group_replication_start= 1 --source include/group_replication.inc --echo --echo ############################################################ --echo # 01. Create two tables on each server before start the group. --let $rpl_connection_name= server1 --source include/rpl_connection.inc SET SESSION sql_log_bin = 0; CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY); CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY); SET SESSION sql_log_bin = 1; --let $rpl_connection_name= server2 --source include/rpl_connection.inc SET SESSION sql_log_bin = 0; CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY); CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY); INSERT INTO t2 VALUES (1); call mtr.add_suppression("Replica SQL for channel 'group_replication_applier': Could not execute Write_rows event on table"); call mtr.add_suppression("Replica SQL for channel 'group_replication_applier': Worker [0-9] failed executing transaction*"); call mtr.add_suppression("Replica SQL for channel 'group_replication_applier': ... The replica coordinator and worker threads are stopped,*"); call mtr.add_suppression("The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group."); call mtr.add_suppression("Fatal error during execution on the Applier process of Group Replication. The server will now leave the group."); call mtr.add_suppression("The server was automatically set into read only mode after an error was detected."); call mtr.add_suppression("Skipping leave operation: concurrent attempt to leave the group is on-going."); SET SESSION sql_log_bin = 1; --echo --echo ############################################################ --echo # 02. Lock table t1 on server2 to block a future update. --let $rpl_connection_name= server_2 --source include/rpl_connection.inc LOCK table t1 READ; --echo --echo ############################################################ --echo # 03. Bootstrap group on server1. --let $rpl_connection_name= server1 --source include/rpl_connection.inc --source include/start_and_bootstrap_group_replication.inc --echo --echo ############################################################ --echo # 04. Execute transactions T1 on server1. --echo # T1 will block server2 on RECOVERING state. --let $rpl_connection_name= server1 --source include/rpl_connection.inc # T1 INSERT INTO t1 VALUES (1); --echo --echo ############################################################ --echo # 05. Start group on server2, it will be on RECOVERING state. --let $rpl_connection_name= server2 --source include/rpl_connection.inc --let $group_replication_start_member_state= RECOVERING --source include/start_group_replication.inc SET @@SESSION.group_replication_consistency= 'BEFORE'; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED BEGIN; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED COMMIT; SET @@SESSION.group_replication_consistency= 'AFTER'; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED BEGIN; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED COMMIT; SET @@SESSION.group_replication_consistency= 'BEFORE_AND_AFTER'; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED BEGIN; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED COMMIT; --echo --echo ############################################################ --echo # 06. Unlock table t1 on server2. --echo # Member will move to ONLINE state. --let $rpl_connection_name= server_2 --source include/rpl_connection.inc UNLOCK TABLES; --let $rpl_connection_name= server2 --source include/rpl_connection.inc --let $group_replication_member_state= ONLINE --source include/gr_wait_for_member_state.inc SET @@SESSION.group_replication_consistency= 'BEFORE'; SELECT COUNT(*) AS count FROM t1; BEGIN; SELECT COUNT(*) AS count FROM t1; COMMIT; SET @@SESSION.group_replication_consistency= 'AFTER'; SELECT COUNT(*) AS count FROM t1; BEGIN; SELECT COUNT(*) AS count FROM t1; COMMIT; SET @@SESSION.group_replication_consistency= 'BEFORE_AND_AFTER'; SELECT COUNT(*) AS count FROM t1; BEGIN; SELECT COUNT(*) AS count FROM t1; COMMIT; --echo --echo ############################################################ --echo # 07. Execute transactions T2 on server1. --echo # T2 will make server2 move to ERROR state due to --echo # duplicate key. --let $rpl_connection_name= server1 --source include/rpl_connection.inc # T2 INSERT INTO t2 VALUES (1); --let $rpl_connection_name= server2 --source include/rpl_connection.inc --let $group_replication_member_state= ERROR --source include/gr_wait_for_member_state.inc SET @@SESSION.group_replication_consistency= 'BEFORE'; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED BEGIN; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED COMMIT; SET @@SESSION.group_replication_consistency= 'AFTER'; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED BEGIN; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED COMMIT; SET @@SESSION.group_replication_consistency= 'BEFORE_AND_AFTER'; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED BEGIN; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED SELECT COUNT(*) AS count FROM t1; --error ER_GRP_TRX_CONSISTENCY_NOT_ALLOWED COMMIT; --echo --echo ############################################################ --echo # 08. Stop group on server2. --echo # Member will move to OFFLINE state. --source include/stop_group_replication.inc --let $group_replication_member_state= OFFLINE --source include/gr_wait_for_member_state.inc SET @@SESSION.group_replication_consistency= 'BEFORE'; SELECT COUNT(*) AS count FROM t1; BEGIN; SELECT COUNT(*) AS count FROM t1; COMMIT; SET @@SESSION.group_replication_consistency= 'AFTER'; SELECT COUNT(*) AS count FROM t1; BEGIN; SELECT COUNT(*) AS count FROM t1; COMMIT; SET @@SESSION.group_replication_consistency= 'BEFORE_AND_AFTER'; SELECT COUNT(*) AS count FROM t1; BEGIN; SELECT COUNT(*) AS count FROM t1; COMMIT; SET @@SESSION.group_replication_consistency= DEFAULT; --echo --echo ############################################################ --echo # 09. Clean up. --let $rpl_connection_name= server1 --source include/rpl_connection.inc DROP TABLE t1; DROP TABLE t2; --let $rpl_connection_name= server2 --source include/rpl_connection.inc DROP TABLE t1; DROP TABLE t2; --source include/group_replication_end.inc