# ==== Purpose ==== # # WL#12649: Automatic reconnection for Async replication between InnoDB Clusters # # It tests that asynchronous replication connection is re-established to new # sender in case the current asynchronous replication connection fails. # # ==== Implementation ==== # 1. Create a chain replication between server 1, 2, 3 and 4. # 2. Insert data on server1 and verify that its synced to the end of # chain i.e. server4 # 3. Verify Asynchronous Replication Connection Failover is initially # disabled. Also verify Asynchronous Replication is initially # connected to server 3. # 4. Enable Asynchronous Replication Connection Failover and set retry # count and retry time to 2 and verify its enabled in # performance_schema.replication_connection_configuration. # 5. Stop server 3. # 6. Verify that asynchronous replication fails with error # ER_RPL_ASYNC_RECONNECT_FAIL_NO_SOURCE as there is no source added to # table performance_schema.replication_asynchronous_connection_failover to # connect. # 7. Verify that asynchronous replication tries connecting to server 3 # two (retry_count=2) times before it fails. # 8. Add three asynchronous connection failover servers and # verify that they are inserted successfully in # performance_schema.replication_asynchronous_connection_failover. # 9. Restart replica IO so asynchronous replication connection failover # can execute and connect to new sender/source. # 10. Verify that asynchronous replication channel gets connected to # server 2 and also verify that asynchronous replication tries # connecting to server 3 two (retry_count=2) times. # 11. Insert more data on server1 and verify that its synced to the end # of chain i.e. server 4. # 12. Stop server 2. # 13. Verify that asynchronous replication channel gets connected to # server 1 and also verify that asynchronous replication tries # connecting to server 2 two (retry_count=2) times. # 14. Insert more data on server1 and verify that its synced to the # server 4. # 15. Stop server 1. # 16. Even after all source are stopped the replica IO thread keeps # on re-trying to connect with all logged source in # performance_schema.replication_asynchronous_connection_failover # table. It tries connecting to server 3, 2 and again server 3. # 17. Stop replica IO # 18. Clean up # --source include/big_test.inc --source include/have_debug.inc --source include/have_debug_sync.inc --echo # --echo # 1. Create a chain replication between server 1, 2, 3 and 4. --echo #. --let $rpl_server_count= 4 --let $rpl_topology= 1->2->3->4 --source include/rpl_init.inc --echo # --echo # 2. Insert data on server1 and verify that its synced to the end of --echo # chain i.e. server4 --echo # --let $rpl_connection_name= server_1 --source include/rpl_connection.inc CREATE TABLE t1 (a INT) ENGINE = InnoDB; INSERT INTO t1 VALUES (1); --source include/rpl_sync.inc --let $rpl_connection_name= server_4 --source include/rpl_connection.inc SET @old_debug= @@GLOBAL.DEBUG; SET GLOBAL debug = '+d,async_conn_failover_disable_weight_check'; --let $assert_text= 'There is one row on replica' --let $assert_cond= [SELECT COUNT(*) count FROM t1, count, 1] = 1 --source include/assert.inc --echo # --echo # 3. Verify Asynchronous Replication Connection Failover is initially --echo # disabled. Also verify Asynchronous Replication is initially --echo # connected to server 3. --echo # --let $assert_text= 'Asynchronous Replication Connection Failover is disabled' --let $assert_cond= [SELECT SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration, SOURCE_CONNECTION_AUTO_FAILOVER, 1] = 0 --source include/assert.inc --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_3 --source include/wait_condition.inc --echo # --echo # 4. Enable Asynchronous Replication Connection Failover and set retry --echo # count and retry time to 2 and verify its enabled in --echo # performance_schema.replication_connection_configuration. --echo # --source include/stop_slave.inc CHANGE REPLICATION SOURCE TO SOURCE_CONNECT_RETRY=1, SOURCE_RETRY_COUNT=2, SOURCE_CONNECTION_AUTO_FAILOVER=1; --source include/start_slave.inc # Verify Asynchronous Replication Connection Failover is enabled. --let $assert_text= 'Asynchronous Replication Connection Failover is enabled' --let $assert_cond= [SELECT SOURCE_CONNECTION_AUTO_FAILOVER FROM performance_schema.replication_connection_configuration, SOURCE_CONNECTION_AUTO_FAILOVER, 1] = 1 --source include/assert.inc --echo # --echo # 5. Stop server 3. --echo # --let $rpl_server_number= 3 --source include/rpl_stop_server.inc --echo # --echo # 6. Verify that asynchronous replication fails with error --echo # ER_RPL_ASYNC_RECONNECT_FAIL_NO_SOURCE as there is no source --echo # added to table --echo # performance_schema.replication_asynchronous_connection_failover to --echo # connect. --echo # --let $rpl_connection_name= server_4 --source include/rpl_connection.inc --let $slave_io_errno=2003 --source include/wait_for_slave_io_error.inc --echo # --echo # 7. Verify that asynchronous replication tries connecting to server 3 --echo # two (retry_count=2) times before it fails. --echo # --let $assert_text= Assert that the IO thread tried two times connecting to server_3 --let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.4.err --let $assert_select= .*Error reconnecting to source 'root@127.0.0.1:$SERVER_MYPORT_3'. This was attempt .*, with a delay of 1 seconds between attempts.* --let $assert_only_after= CURRENT_TEST: rpl_gtid.rpl_async_conn_failover --let $assert_count= 2 --source include/assert_grep.inc --echo # --echo # 8. Add three asynchronous connection failover servers and --echo # verify that they are inserted successfully in --echo # performance_schema.replication_asynchronous_connection_failover. --echo # --replace_result $SERVER_MYPORT_3 SERVER_MYPORT_3 --eval SELECT asynchronous_connection_failover_add_source('', '127.0.0.1', $SERVER_MYPORT_3, '', 60); --replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 --eval SELECT asynchronous_connection_failover_add_source('', '127.0.0.1', $SERVER_MYPORT_2, '', 50); --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 --eval SELECT asynchronous_connection_failover_add_source('', '127.0.0.1', $SERVER_MYPORT_1, '', 40); --let $assert_text= 'There are three rows in performance_schema.replication_asynchronous_connection_failover' --let $assert_cond= [SELECT COUNT(*) count FROM performance_schema.replication_asynchronous_connection_failover, count, 1] = 3 --source include/assert.inc --echo # --echo # 9. Restart replica IO so asynchronous replication connection failover --echo # can execute and connect to new sender/source. --echo # --source include/start_slave_io.inc --echo # --echo # 10. Verify that asynchronous replication channel gets connected to --echo # server 2 and also verify that asynchronous replication tries --echo # connecting to server 3 two (retry_count=2) times. --echo # --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_2 --source include/wait_condition.inc # Verify that it tried connecting to server_3 two (retry_count=2) times. --let $assert_text= Assert that the IO thread tried two times connecting to server_3 --let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.4.err --let $assert_select= .*Error connecting to source 'root@127.0.0.1:$SERVER_MYPORT_3'. This was attempt .*, with a delay of 1 seconds between attempts.* --let $assert_only_after= CURRENT_TEST: rpl_gtid.rpl_async_conn_failover --let $assert_count= 4 --source include/assert_grep.inc --echo # --echo # 11. Insert more data on server1 and verify that its synced to the end --echo # of chain i.e. server 4. --echo # --let $rpl_connection_name= server_1 --source include/rpl_connection.inc INSERT INTO t1 VALUES (2); # Check data is synced to server 4 --let $rpl_connection_name= server_4 --source include/rpl_connection.inc --let $wait_condition= SELECT COUNT(*) = 2 FROM t1 --source include/wait_condition.inc --echo # --echo # 12. Stop server 2. --echo # --let $rpl_server_number= 2 --source include/rpl_stop_server.inc --echo # --echo # 13. Verify that asynchronous replication channel gets connected to --echo # server 1 and also verify that asynchronous replication tries --echo # connecting to server 2 two (retry_count=2) times. --echo # --let $rpl_connection_name= server_4 --source include/rpl_connection.inc --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_1 --source include/wait_condition.inc # Verify that it tried connecting to server_2 two (retry_count=2) times. --let $assert_text= Assert that the IO thread tried two times connecting to server_2 --let $assert_file=$MYSQLTEST_VARDIR/log/mysqld.4.err --let $assert_select= .*Error reconnecting to source 'root@127.0.0.1:$SERVER_MYPORT_2'. This was attempt .*, with a delay of 1 seconds between attempts.* --let $assert_only_after= CURRENT_TEST: rpl_gtid.rpl_async_conn_failover --let $assert_count= 2 --source include/assert_grep.inc --echo # --echo # 14. Insert more data on server1 and verify that its synced to the --echo # server 4. --echo # --let $rpl_connection_name= server_1 --source include/rpl_connection.inc INSERT INTO t1 VALUES (3); # Check data is synced to server 4 --let $rpl_connection_name= server_4 --source include/rpl_connection.inc --let $wait_condition= SELECT COUNT(*) = 3 FROM t1 --source include/wait_condition.inc --echo # --echo # 15. Stop server 1. --echo # --let $rpl_connection_name= server_4 --source include/rpl_connection.inc --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_1 --source include/wait_condition.inc SET GLOBAL debug = '+d,replica_retry_count_exceed'; --let $rpl_server_number= 1 --source include/rpl_stop_server.inc --echo # --echo # 16. Even after all source are stopped the replica IO thread keeps --echo # on re-trying to connect with all logged source in --echo # performance_schema.replication_asynchronous_connection_failover --echo # table. It tries connecting to server 3, 2, 1, and again 3, 2 1. --echo # --let $rpl_connection_name= server_4 --source include/rpl_connection.inc SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed'; --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_3 --source include/wait_condition.inc SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed'; SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed'; --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_2 --source include/wait_condition.inc SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed'; SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed'; --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_1 --source include/wait_condition.inc SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed'; SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed'; --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_3 --source include/wait_condition.inc SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed'; SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed'; --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_2 --source include/wait_condition.inc SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed'; SET DEBUG_SYNC= 'now WAIT_FOR wait_for_retry_count_exceed'; --let $wait_condition= SELECT COUNT(*) = 1 FROM performance_schema.replication_connection_configuration WHERE CHANNEL_NAME='' AND PORT=$SERVER_MYPORT_1 --source include/wait_condition.inc SET @@GLOBAL.DEBUG= @old_debug; SET DEBUG_SYNC= 'now SIGNAL continue_retry_count_exceed'; SET DEBUG_SYNC= 'RESET'; --echo # --echo # 17. Stop replica IO --echo # --source include/stop_slave.inc --echo # --echo # 18. Clean up --echo # CHANGE REPLICATION SOURCE TO SOURCE_CONNECTION_AUTO_FAILOVER=0; --replace_result $SERVER_MYPORT_3 SERVER_MYPORT_3 --eval SELECT asynchronous_connection_failover_delete_source('', '127.0.0.1', $SERVER_MYPORT_3, ''); --replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 --eval SELECT asynchronous_connection_failover_delete_source('', '127.0.0.1', $SERVER_MYPORT_2, ''); --replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1 --eval SELECT asynchronous_connection_failover_delete_source('', '127.0.0.1', $SERVER_MYPORT_1, ''); --let $rpl_server_number=1 --source include/rpl_start_server.inc --let $_rpl_server= 2 while ($_rpl_server < $rpl_server_count) { --let $rpl_connection_name= server_$_rpl_server --source include/rpl_connection.inc --let $rpl_server_number= $_rpl_server --source include/rpl_start_server.inc --disable_warnings --source include/start_slave.inc --enable_warnings --inc $_rpl_server } --let $rpl_connection_name= server_4 --source include/rpl_connection.inc --source include/start_slave.inc --let $rpl_connection_name= server_1 --source include/rpl_connection.inc DROP TABLE t1; --source include/rpl_sync.inc --let $rpl_skip_sync= 1 --let $rpl_only_running_threads= 1 --source include/rpl_end.inc