################################################################################ # Validate group_replication_consistency= 'BEFORE' behaviour. # # Test: # 00. The test requires three servers: M1, M2 and M3. # 01. Create two tables on the group. # 02. Lock table t1 on server2 to block a future update. # 03. Execute transaction T1, the transaction will block on # server2 due to the table lock. # 04. Wait until T1 is delivered to server2. # 05. Execute a read transaction on server2 with # group_replication_consistency= 'BEFORE' , it will wait # for T1 commit. # 06. Execute a write transaction on server2 with # group_replication_consistency= 'BEFORE' , it will wait # for T1 commit. # 07. Execute transaction T2 on server1, it will execute # on all servers 1 and 3. # 08. Unblock T1 on server2. # 09. Validate data. # 10. Clean up. ################################################################################ --source include/have_group_replication_plugin.inc --let $rpl_server_count= 3 --source include/group_replication.inc --echo --echo ############################################################ --echo # 01. Create two tables on the group. --let $rpl_connection_name= server1 --source include/rpl_connection.inc CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY); CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY); --source include/rpl_sync.inc --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. Execute transaction T1, the transaction will block on --echo # server2 due to the table lock. --let $rpl_connection_name= server1 --source include/rpl_connection.inc BEGIN; INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (100); COMMIT; --echo --echo ############################################################ --echo # 04. Wait until T1 is delivered to server2. --let $rpl_connection_name= server2 --source include/rpl_connection.inc --let $member2_uuid= query_get_value(SELECT @@SERVER_UUID, @@SERVER_UUID, 1) --let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_member_stats WHERE member_id='$member2_uuid' AND count_transactions_checked=3 AND count_transactions_remote_in_applier_queue=1 --source include/wait_condition.inc --echo --echo ############################################################ --echo # 05. Execute a read transaction on server2 with --echo # group_replication_consistency= 'BEFORE' , it will wait --echo # for T1 commit. --let $rpl_connection_name= server_2_1 --source include/rpl_connection.inc SET @@SESSION.group_replication_consistency= 'BEFORE'; --send SELECT COUNT(*) AS count FROM t2 WHERE c1 >= 99 --let $rpl_connection_name= server2 --source include/rpl_connection.inc --let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist WHERE state='Executing hook on transaction begin.' AND info='SELECT COUNT(*) AS count FROM t2 WHERE c1 >= 99' --source include/wait_condition.inc --echo --echo ############################################################ --echo # 06. Execute a write transaction on server2 with --echo # group_replication_consistency= 'BEFORE' , it will wait --echo # for T1 commit. --let $rpl_connection_name= slave --source include/rpl_connection.inc SET @@SESSION.group_replication_consistency= 'BEFORE'; --send UPDATE t2 SET c1=99 WHERE c1=100 --let $rpl_connection_name= server2 --source include/rpl_connection.inc --let $wait_condition=SELECT COUNT(*)=1 FROM information_schema.processlist WHERE state='Executing hook on transaction begin.' AND info='UPDATE t2 SET c1=99 WHERE c1=100' --source include/wait_condition.inc --echo --echo ############################################################ --echo # 07. Execute transaction T2 on server1, it will execute --echo # on servers 1 and 3. --echo # server2 will apply it in order not because of the --echo # consistency but due to --replica_preserve_commit_order --echo # or single thread applier which will make T2 be --echo # committed after the UPDATE. --let $rpl_connection_name= server1 --source include/rpl_connection.inc INSERT INTO t2 VALUES (2); --let $assert_text= 'There is a 2 in table t2' --let $assert_cond= [SELECT COUNT(*) AS count FROM t2 WHERE c1=2, count, 1] = 1 --source include/assert.inc --let $rpl_connection_name= server3 --source include/rpl_connection.inc --let $wait_condition=SELECT COUNT(*)=1 FROM t2 WHERE c1=2 --source include/wait_condition.inc --echo --echo ############################################################ --echo # 08. Unblock T1 on server2. --let $rpl_connection_name= server_2 --source include/rpl_connection.inc UNLOCK TABLES; --let $rpl_connection_name= server_2_1 --source include/rpl_connection.inc --reap SET @@SESSION.group_replication_consistency= DEFAULT; --let $rpl_connection_name= slave --source include/rpl_connection.inc --reap SET @@SESSION.group_replication_consistency= DEFAULT; --echo --echo ############################################################ --echo # 09. Validate data. --let $rpl_connection_name= server2 --source include/rpl_connection.inc --let $assert_text= 'There is a 1 in table t1' --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE c1=1, count, 1] = 1 --source include/assert.inc --let $assert_text= 'There is a 99 in table t2' --let $assert_cond= [SELECT COUNT(*) AS count FROM t2 WHERE c1=99, count, 1] = 1 --source include/assert.inc # Ensure that T2 is already committed on server2. --source include/rpl_sync.inc --let $rpl_connection_name= server2 --source include/rpl_connection.inc --let $assert_text= 'There is a 2 in table t2' --let $assert_cond= [SELECT COUNT(*) AS count FROM t2 WHERE c1=2, count, 1] = 1 --source include/assert.inc --let $rpl_connection_name= server1 --source include/rpl_connection.inc --let $assert_text= 'There is a 1 in table t1' --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE c1=1, count, 1] = 1 --source include/assert.inc --let $assert_text= 'There is a 99 in table t2' --let $assert_cond= [SELECT COUNT(*) AS count FROM t2 WHERE c1=99, count, 1] = 1 --source include/assert.inc --let $assert_text= 'There is a 2 in table t2' --let $assert_cond= [SELECT COUNT(*) AS count FROM t2 WHERE c1=2, count, 1] = 1 --source include/assert.inc --let $rpl_connection_name= server3 --source include/rpl_connection.inc --let $assert_text= 'There is a 1 in table t1' --let $assert_cond= [SELECT COUNT(*) AS count FROM t1 WHERE c1=1, count, 1] = 1 --source include/assert.inc --let $assert_text= 'There is a 99 in table t2' --let $assert_cond= [SELECT COUNT(*) AS count FROM t2 WHERE c1=99, count, 1] = 1 --source include/assert.inc --let $assert_text= 'There is a 2 in table t2' --let $assert_cond= [SELECT COUNT(*) AS count FROM t2 WHERE c1=2, count, 1] = 1 --source include/assert.inc --let $diff_tables=server1:t1, server2:t1, server3:t1 --source include/diff_tables.inc --let $diff_tables=server1:t2, server2:t2, server3:t2 --source include/diff_tables.inc --echo --echo ############################################################ --echo # 10. Clean up. --let $rpl_connection_name= server1 --source include/rpl_connection.inc DROP TABLE t1; DROP TABLE t2; --source include/group_replication_end.inc