gr_acf_receiver_on_spm.test

################################################################################
#
# Validate that:
# -  The Automatic Connection failover feature for Receiver works when
#    asynchronous replication receiver is a Primary member.
# -  Whenever a running managed channel receiver who is also the Primary on a
#    Single-Primary mode goes in OFFLINE/ERROR group replication state, then
#    the new selected PRIMARY of the receiver group MUST establish asynchronous
#    replication connection with the sender.
#
# Test:
# 0. The test requires 5 servers:
#      server1: single-primary group A primary
#      server2: single-primary group A secondary
#      server3: single-primary group B primary
#      server4: single-primary group B secondary
#      server5: stand alone server
# 1. Create a replication channel to replicate from server4 to
#    to server1 on both server1 and 2.
# 2. Add group A replication failover managed source into secondary
#    member in group B, server4.
#    It will be allowed but the configuration will not be propagated.
# 3. Deploy a 2 members receiver group in single primary mode.
# 4. Error adding managed group to secondary server2.
# 5. Deploy a 2 members sender group in single primary mode.
# 6. Error starting a replication channel on secondary member server2.
# 7. Add replication connection details of managed channel ch1
#    to replication_asynchronous_connection_failover_managed table
#    through asynchronous_connection_failover_add_managed() UDF.
# 8. Verify replication_asynchronous_connection_failover on
#    server1 has two rows and member action configuration version
#    value for replication_asynchronous_connection_failover
#    and for replication_asynchronous_connection_failover_managed.
#    And the channel 'ch1' is connected to server3.
# 9. Verify replication_asynchronous_connection_failover on
#    server2 has two rows and member action configuration version
#    value for replication_asynchronous_connection_failover
#    and for replication_asynchronous_connection_failover_managed.
# 10. Stop group replication on existing primary (server1), the new
#     primary will establish asynchronous replication channel (ch1)
#     connection with sender which is server3.
#     And the channel 'ch1' is connected to server3.
# 11. Delete replication connection details for managed channel ch1
#     from replication_asynchronous_connection_failover_managed
#     and replication_asynchronous_connection_failover table
#     through asynchronous_connection_failover_delete_managed() UDF.
# 12. Verify replication_asynchronous_connection_failover on
#     server2 is empty and member action configuration version
#     value for replication_asynchronous_connection_failover
#     and for replication_asynchronous_connection_failover_managed.
#     And the channel 'ch1' is connected to server3.
# 13. Clean up.
--source include/big_test.inc
--source include/have_group_replication_plugin.inc
--let $rpl_skip_group_replication_start= 1
--let $rpl_server_count= 4
--source include/group_replication.inc

--let $group_A= `SELECT UUID()`
--let $group_B= `SELECT UUID()`

# Use the appropriate XCom port, that does depend on
# `group_replication_communication_stack`.
--let $have_xcom_stack= `SELECT @@group_replication_communication_stack LIKE 'XCOM'`
--let $group_a_group_seeds= "localhost:$SERVER_GR_PORT_1,localhost:$SERVER_GR_PORT_2"
--let $group_b_group_seeds= "localhost:$SERVER_GR_PORT_3,localhost:$SERVER_GR_PORT_4"
if (!$have_xcom_stack) {
  --let $group_a_group_seeds= "localhost:$SERVER_MYPORT_1,localhost:$SERVER_MYPORT_2"
  --let $group_b_group_seeds= "localhost:$SERVER_MYPORT_3,localhost:$SERVER_MYPORT_4"
}

--let $rpl_connection_name= server2
--source include/connection.inc
SET SESSION sql_log_bin = 0;
call mtr.add_suppression("Can't start replica IO THREAD of channel 'ch1' when group replication is running with single-primary mode on a secondary member.");
call mtr.add_suppression("Can't start replica SQL THREAD of channel 'ch1' when group replication is running with single-primary mode on a secondary member.");
call mtr.add_suppression("Run function 'thread_start' in plugin 'group_replication' failed");
call mtr.add_suppression("Run function 'applier_start' in plugin 'group_replication' failed");
call mtr.add_suppression("Failed to run 'thread_start' hook");
call mtr.add_suppression("Failed to run 'applier_start' hook");
SET SESSION sql_log_bin = 1;


--echo
--echo # 1. Create a replication channel to replicate from server4 to
--echo #    to server1 on both server1 and 2.
--echo
--let $rpl_connection_name= server1
--source include/connection.inc
--replace_result $SERVER_MYPORT_4 SERVER_4_PORT
--eval CHANGE REPLICATION SOURCE TO SOURCE_HOST='127.0.0.1', SOURCE_USER='root', SOURCE_AUTO_POSITION=1, SOURCE_PORT=$SERVER_MYPORT_4, SOURCE_CONNECT_RETRY=1, SOURCE_RETRY_COUNT=1, SOURCE_CONNECTION_AUTO_FAILOVER=1 FOR CHANNEL 'ch1'


--let $rpl_connection_name= server2
--source include/connection.inc
--replace_result $SERVER_MYPORT_4 SERVER_4_PORT
--eval CHANGE REPLICATION SOURCE TO SOURCE_HOST='127.0.0.1', SOURCE_USER='root', SOURCE_AUTO_POSITION=1, SOURCE_PORT=$SERVER_MYPORT_4, SOURCE_CONNECT_RETRY=1, SOURCE_RETRY_COUNT=1, SOURCE_CONNECTION_AUTO_FAILOVER=1 FOR CHANNEL 'ch1'


--echo
--echo # 2. Add server4 using asynchronous_connection_failover_add_managed
--echo #    UDF on stand alone server2.
--echo #    It will be allowed but the configuration will not be propagated.
--echo
--let $rpl_connection_name= server2
--source include/connection.inc

--replace_result $SERVER_MYPORT_4 SERVER_MYPORT_4 $group_A GROUP_NAME_SERVER1
--eval SELECT asynchronous_connection_failover_add_managed('ch1', 'GroupReplication', '$group_A', '127.0.0.1', $SERVER_MYPORT_4, '', 90, 70);


--let $assert_text= 'There is one row in performance_schema.replication_asynchronous_connection_failover'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover, count, 1] = 1
--source include/assert.inc

--let $assert_text= 'The version of replication_asynchronous_connection_failover must be 1'
--let $assert_cond= [SELECT version FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover\', version, 1] = 1
--source include/assert.inc

--let $assert_text= 'There is one row in performance_schema.replication_asynchronous_connection_failover_managed'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover_managed WHERE managed_name="$group_A", count, 1] = 1
--source include/assert.inc

--let $assert_text= 'The version of replication_asynchronous_connection_failover_managed must be 1'
--let $assert_cond= [SELECT version FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover_managed\', version, 1] = 1
--source include/assert.inc


--let $rpl_connection_name= server1
--source include/connection.inc

--let $assert_text= 'There are no rows in performance_schema.replication_asynchronous_connection_failover'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover, count, 1] = 0
--source include/assert.inc

--let $assert_text= 'There is no version of replication_asynchronous_connection_failover'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover\', count, 1] = 0
--source include/assert.inc

--let $assert_text= 'There are no rows in performance_schema.replication_asynchronous_connection_failover_managed'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover_managed, count, 1] = 0
--source include/assert.inc

--let $assert_text= 'There is no version of replication_asynchronous_connection_failover_managed'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover_managed\', count, 1] = 0
--source include/assert.inc


--echo
--echo # 3. Deploy a 2 members receiver group in single primary mode.
--echo #    Since server1 will bootstrap the group, server1 configuration will
--echo #    be the group configuration, which will override server2
--echo #    configuration when server2 joins the group.
--echo
--let $rpl_connection_name= server1
--source include/connection.inc
--let $group_replication_group_name = $group_A
--disable_query_log
--eval SET GLOBAL group_replication_group_seeds= $group_a_group_seeds
--enable_query_log
--source include/start_and_bootstrap_group_replication.inc

--let $assert_text= 'The performance_schema.replication_asynchronous_connection_failover table is empty'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover, count, 1] = "0"
--source include/assert.inc

--let $assert_text= 'The version of replication_asynchronous_connection_failover must be 0'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover\', count, 1] = 0
--source include/assert.inc

--let $assert_text= 'The performance_schema.replication_asynchronous_connection_failover_managed table is empty'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover_managed WHERE managed_name="$group_A", count, 1] = "0"
--source include/assert.inc

--let $assert_text= 'The version of replication_asynchronous_connection_failover_managed must be 0'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover_managed\', count, 1] = 0
--source include/assert.inc


--let $rpl_connection_name= server2
--source include/connection.inc
--let $group_replication_group_name = $group_A
--disable_query_log
--eval SET GLOBAL group_replication_group_seeds= $group_a_group_seeds
--enable_query_log
--source include/start_group_replication.inc

--let $assert_text= 'The performance_schema.replication_asynchronous_connection_failover table is empty'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover, count, 1] = "0"
--source include/assert.inc

--let $assert_text= 'The version of replication_asynchronous_connection_failover must be 0'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover\', count, 1] = "0"
--source include/assert.inc

--let $assert_text= 'The performance_schema.replication_asynchronous_connection_failover_managed table is empty'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover_managed WHERE managed_name="$group_A", count, 1] = "0"
--source include/assert.inc

--let $assert_text= 'The version of replication_asynchronous_connection_failover_managed must be 0'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover_managed\', count, 1] = "0"
--source include/assert.inc


--echo
--echo # 4. Error adding managed group to secondary server2.
--echo
--replace_result $SERVER_MYPORT_5 SERVER_MYPORT_5 $group_A GROUP_A
--error ER_CANT_INITIALIZE_UDF
--eval SELECT asynchronous_connection_failover_add_managed('ch1', 'GroupReplication', '$group_A', '127.0.0.1', $SERVER_MYPORT_5, '', 90, 70);

--replace_result $SERVER_MYPORT_5 SERVER_MYPORT_5
--error ER_CANT_INITIALIZE_UDF
--eval SELECT asynchronous_connection_failover_add_source('ch1', '127.0.0.1', $SERVER_MYPORT_5, '', 50)

--let $assert_text= 'There are no rows in performance_schema.replication_asynchronous_connection_failover'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover, count, 1] = 0
--source include/assert.inc

--let $assert_text= 'There is no version of replication_asynchronous_connection_failover'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover\', count, 1] = 0
--source include/assert.inc

--let $assert_text= 'There are no rows in performance_schema.replication_asynchronous_connection_failover_managed'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover_managed WHERE managed_name="$group_A", count, 1] = 0
--source include/assert.inc

--let $assert_text= 'There is no version of replication_asynchronous_connection_failover_managed'
--let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_group_configuration_version WHERE name=\'replication_asynchronous_connection_failover_managed\', count, 1] = 0
--source include/assert.inc


--echo #
--echo # 5. Deploy a 2 members sender group in single primary mode.
--echo #
--let $rpl_connection_name= server3
--source include/connection.inc
--let $group_replication_group_name = $group_B
--disable_query_log
--eval SET GLOBAL group_replication_group_seeds= $group_b_group_seeds
--enable_query_log
--source include/start_and_bootstrap_group_replication.inc


--let $rpl_connection_name= server4
--source include/connection.inc
--let $group_replication_group_name = $group_B
--disable_query_log
--eval SET GLOBAL group_replication_group_seeds= $group_b_group_seeds
--enable_query_log
--source include/start_group_replication.inc


--echo
--echo # 6. Error starting a replication channel on secondary member server2.
--echo
--let $rpl_connection_name= server2
--source include/connection.inc

START REPLICA FOR CHANNEL 'ch1';

--let $slave_sql_errno= 13117
--let $rpl_channel_name= 'ch1'
--source include/rpl/wait_for_applier_error.inc

--let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.2.err
--let $assert_count = 1
--let $assert_select = Can\'t start replica IO THREAD of channel \'ch1\' when group replication is running with single-primary mode on a secondary member
--let $assert_text = Server executed GTID set is logged.
--source include/assert_grep.inc

--let $assert_file= $MYSQLTEST_VARDIR/log/mysqld.2.err
--let $assert_count = 1
--let $assert_select = Can\'t start replica SQL THREAD of channel \'ch1\' when group replication is running with single-primary mode on a secondary member
--let $assert_text = Server executed GTID set is logged.
--source include/assert_grep.inc

STOP REPLICA FOR CHANNEL 'ch1';


--echo
--echo # 7. Add replication connection details of managed channel ch1
--echo #    to replication_asynchronous_connection_failover_managed table
--echo #    through asynchronous_connection_failover_add_managed() UDF.
--echo
--let $rpl_connection_name= server1
--source include/connection.inc
--replace_result $SERVER_MYPORT_4 SERVER_MYPORT_4 $group_B GROUP_NAME_SERVER2
--eval SELECT asynchronous_connection_failover_add_managed('ch1', 'GroupReplication', "$group_B",'127.0.0.1', $SERVER_MYPORT_4, '', 80, 60)


--echo
--echo # 8. Verify replication_asynchronous_connection_failover on
--echo #    server1 has two rows and member action configuration version
--echo #    value for replication_asynchronous_connection_failover
--echo #    and for replication_asynchronous_connection_failover_managed.
--echo #    And the channel 'ch1' is connected to server3.
--echo
--let $wait_condition= SELECT COUNT(*)=2 FROM performance_schema.replication_asynchronous_connection_failover
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_asynchronous_connection_failover_managed
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_configuration_version where name='replication_asynchronous_connection_failover' and version>=2
--source include/wait_condition.inc
--let $failover_version_step8= query_get_value(SELECT VERSION FROM performance_schema.replication_group_configuration_version where name='replication_asynchronous_connection_failover', VERSION, 1)

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_configuration_version where name='replication_asynchronous_connection_failover_managed' and version=1
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_connection_configuration where CHANNEL_NAME='ch1' and HOST='127.0.0.1' and PORT=$SERVER_MYPORT_3
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_connection_status where CHANNEL_NAME='ch1' and SERVICE_STATE='ON'
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_applier_status where CHANNEL_NAME='ch1' and SERVICE_STATE='ON'
--source include/wait_condition.inc




--echo
--echo #  9. Verify replication_asynchronous_connection_failover on
--echo #     server2 has two rows and member action configuration version
--echo #     value for replication_asynchronous_connection_failover
--echo #     and for replication_asynchronous_connection_failover_managed.
--echo
--let $rpl_connection_name= server2
--source include/connection.inc

--let $wait_condition= SELECT COUNT(*)=2 FROM performance_schema.replication_asynchronous_connection_failover
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_asynchronous_connection_failover_managed
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_configuration_version where name='replication_asynchronous_connection_failover' and version>=$failover_version_step8
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_configuration_version where name='replication_asynchronous_connection_failover_managed' and version=1
--source include/wait_condition.inc


--echo
--echo # 10. Stop group replication on existing primary (server1), the new
--echo #     primary will establish asynchronous replication channel (ch1)
--echo #     connection with sender which is server3.
--echo #     And the channel 'ch1' is connected to server3.
--echo
--let $server2_uuid= `SELECT @@server_uuid`

--let $rpl_connection_name= server1
--source include/connection.inc

--source include/stop_group_replication.inc

--let $rpl_connection_name= server2
--source include/connection.inc

--let $group_replication_expected_uuid= $server2_uuid
--source include/gr_wait_primary_member_uuid.inc

--let $wait_condition=SELECT COUNT(*)=1 FROM performance_schema.replication_group_members where MEMBER_STATE="ONLINE"
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_connection_status where CHANNEL_NAME='ch1' and SERVICE_STATE='ON'
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_applier_status where CHANNEL_NAME='ch1' and SERVICE_STATE='ON'
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_connection_configuration where CHANNEL_NAME='ch1' and HOST='127.0.0.1' and PORT=$SERVER_MYPORT_3
--source include/wait_condition.inc


--echo
--echo # 11. Delete replication connection details for managed channel ch1
--echo #     from replication_asynchronous_connection_failover_managed
--echo #     and replication_asynchronous_connection_failover table
--echo #     through asynchronous_connection_failover_delete_managed() UDF.
--echo
--let $rpl_connection_name= server2
--source include/connection.inc

--replace_result $group_B GROUP_NAME_SERVER2
--eval SELECT asynchronous_connection_failover_delete_managed('ch1', '$group_B')


--echo
--echo # 12. Verify replication_asynchronous_connection_failover on
--echo #     server2 is empty and member action configuration version
--echo #     value for replication_asynchronous_connection_failover
--echo #     and for replication_asynchronous_connection_failover_managed.
--echo #     And the channel 'ch1' is connected to server3.
--echo
--let $rpl_connection_name= server2
--source include/connection.inc

--let $wait_condition= SELECT COUNT(*)=0 FROM performance_schema.replication_asynchronous_connection_failover
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=0 FROM performance_schema.replication_asynchronous_connection_failover_managed
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_group_configuration_version where name='replication_asynchronous_connection_failover' and version>=3
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_connection_status where CHANNEL_NAME='ch1' and SERVICE_STATE='ON'
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_applier_status where CHANNEL_NAME='ch1' and SERVICE_STATE='ON'
--source include/wait_condition.inc

--let $wait_condition= SELECT COUNT(*)=1 FROM performance_schema.replication_connection_configuration where CHANNEL_NAME='ch1' and HOST='127.0.0.1' and PORT=$SERVER_MYPORT_3
--source include/wait_condition.inc


--echo #
--echo # 13. Clean up.
--echo #
--let $rpl_connection_name= server1
--source include/connection.inc

--let $rpl_channel_name='ch1'
--let $rpl_reset_slave_all= 1
--let $rpl_multi_source= 1
--source include/rpl/reset_replica.inc
--let $rpl_channel_name=
--let $rpl_reset_slave_all=
--let $rpl_multi_source=

--let $rpl_connection_name= server2
--source include/connection.inc

--let $rpl_channel_name='ch1'
--source include/rpl/stop_replica.inc

--let $rpl_reset_slave_all= 1
--let $rpl_multi_source= 1
--source include/rpl/reset_replica.inc
--let $rpl_channel_name=
--let $rpl_reset_slave_all=
--let $rpl_multi_source=

--let $rpl_connection_name= server4
--source include/connection.inc
--source include/stop_group_replication.inc

--let $rpl_connection_name= server3
--source include/connection.inc
--source include/stop_group_replication.inc

--let $rpl_connection_name= server2
--source include/connection.inc
--source include/stop_group_replication.inc

--let $rpl_skip_sync = 1
--source include/group_replication_end.inc