Edit File - File Manager

Edit File: rpl_circular_for_4_hosts.test

# ==== Purpose ====
#
# Setup: circular replication on four hosts, i.e., topology
# server_1 -> server_2 -> server_3 -> server_4 -> server_1
#
# Tested properties:
# - Correctly configured autoinc works.
# - Manual failover works.
#
# ==== Related bugs and worklogs ====
#
# WL#3754
# BUG#49978

--source include/have_innodb.inc

# Use wait_for_slave_to_(start|stop) for current connections
let $keep_connection= 1;

# Set up circular ring and new names for servers
--echo *** Set up circular replication on four servers *** 
--let $rpl_topology= 1->2->3->4->1
--source include/rpl_init.inc
--echo

#set auto inc variables at each server
--let $_rpl_server= $rpl_server_count
while ($_rpl_server)
{
  --let $rpl_connection_name= server_$_rpl_server
  --source include/rpl_connection.inc
  eval SET auto_increment_increment= $rpl_server_count;
  eval SET auto_increment_offset= $_rpl_server;

--dec $_rpl_server
}

# Preparing data.
--echo *** Preparing data ***
--connection server_1
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b VARCHAR(100), c INT NOT NULL, PRIMARY KEY(a)) ENGINE=MyISAM;
CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT, b VARCHAR(100), c INT NOT NULL, PRIMARY KEY(a)) ENGINE=InnoDB;
--source include/rpl_sync.inc
--connection server_4
call mtr.add_suppression("Slave SQL.*Request to stop slave SQL Thread received while applying a group that has non-transactional changes; waiting for completion of the group");
--echo

#
# Testing
#

--echo *** Testing schema A->B->C->D->A ***
--echo
# insert data via all hosts
--connection server_1
INSERT INTO t1(b,c) VALUES('A',1);
--sync_slave_with_master server_2
INSERT INTO t1(b,c) VALUES('B',1);
--sync_slave_with_master server_3
INSERT INTO t1(b,c) VALUES('C',1);
--sync_slave_with_master server_4
INSERT INTO t1(b,c) VALUES('D',1);

--source include/rpl_sync.inc

--connection server_1
SELECT 'Master A',a,b FROM t1 WHERE c = 1 ORDER BY a,b;
--connection server_2
SELECT 'Master B',a,b FROM t1 WHERE c = 1 ORDER BY a,b;
--connection server_3
SELECT 'Master C',a,b FROM t1 WHERE c = 1 ORDER BY a,b;
--connection server_4
SELECT 'Master D',a,b FROM t1 WHERE c = 1 ORDER BY a,b;
--echo

--echo *** Testing schema A->B->D->A if C has failure ***
--echo 
--echo * Do failure for C and then make new connection B->D *

# Note: server_N has auto_increment_offset=N. Below, we insert value 6
# in the autoinc column on server_3 (and prevent it from replicating
# further using SQL_SLAVE_SKIP_COUNTER on server_4). Due to the
# auto_increment_offset setting, the autoinc value 6 is normally
# generated on server_2. When we later insert a row on server_2, we
# thus cause a duplicate key error on server_3.

# Do not replicate next event from C
--connection server_4
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
source include/start_slave.inc;
--connection server_3
INSERT INTO t1 VALUES(6,'C',2);
--sync_slave_with_master server_4

--connection server_2
INSERT INTO t1(b,c) VALUES('B',2);
# Wait while C will stop.
--connection server_3
# 1062 = ER_DUP_ENTRY
call mtr.add_suppression("Slave SQL.*Duplicate entry .6. for key .PRIMARY.* Error_code: 1062");
--let $slave_sql_errno= 1062
--source include/wait_for_slave_sql_error.inc
--connection server_1
INSERT INTO t1(b,c) VALUES('A',2);
--connection server_4
INSERT INTO t1(b,c) VALUES('D',2);

# Sync all servers except C
--connection server_2
let $wait_condition= SELECT COUNT(*)=3 FROM t1 WHERE a > 4;
--let $server_connection= server_1
--source include/wait_condition.inc

--echo
--echo * Data on servers (C failed) *
# Masters C,D shouldn't have correct data
--connection server_1
SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
--connection server_2
SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
--connection server_3
SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
--connection server_4
SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
--echo

--echo * Reconfigure replication to schema A->B->D->A *
# Exclude Master C 
--connection server_3
--source include/stop_slave_io.inc
--let $pos_c= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1)
--let $file_c= query_get_value(SHOW SLAVE STATUS, Master_Log_File, 1)

--connection server_4
--source include/stop_slave.inc

--let $rpl_topology= 1->2->4->1,2->3
--let $rpl_master_log_file= 4:$file_c
--let $rpl_master_log_pos= 4:$pos_c
--source include/rpl_change_topology.inc

#--replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2 $file_c LOG_FILE $pos_c LOG_POS 
#--eval CHANGE MASTER TO master_host='127.0.0.1',master_port=$SERVER_MYPORT_2,master_user='root',master_log_file='$file_c',master_log_pos=$pos_c
source include/start_slave.inc;
--connection server_2
--sync_slave_with_master server_4
--sync_slave_with_master server_1
--echo

--echo * Check data inserted before failure *
--connection server_1
SELECT 'Master A',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
--connection server_2
SELECT 'Master B',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
--connection server_3
SELECT 'Master C',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
--connection server_4
SELECT 'Master D',a,b FROM t1 WHERE c = 2 ORDER BY a,b;
--echo

--echo * Check data inserted after failure *
--connection server_1
INSERT INTO t1(b,c) VALUES('A',3);
--connection server_2
INSERT INTO t1(b,c) VALUES('B',3);
--connection server_4
INSERT INTO t1(b,c) VALUES('D',3);
connection server_1;

--let $rpl_only_running_threads= 1
--source include/rpl_sync.inc

--connection server_1
SELECT 'Master A',a,b FROM t1 WHERE c = 3 ORDER BY a,b;
--connection server_2
SELECT 'Master B',a,b FROM t1 WHERE c = 3 ORDER BY a,b;
--connection server_3
SELECT 'Master C',a,b FROM t1 WHERE c = 3 ORDER BY a,b;
--connection server_4
SELECT 'Master D',a,b FROM t1 WHERE c = 3 ORDER BY a,b;
--connection server_1
--echo

--echo *** Testing restoring scheme A->B->C->D->A after failure ***
--echo
# Master D will ignore a next event from C so that event will not be 
# distributed to other servers
--echo * Remove wrong event from C and restore B->C->D *
--connection server_4
source include/stop_slave.inc;
--connection server_3
DELETE FROM t1 WHERE a = 6;
--source include/start_slave.inc
--connection server_2
--sync_slave_with_master server_3
RESET MASTER;
--let $file_d= query_get_value(SHOW MASTER STATUS, File, 1)
--let $pos_d= query_get_value(SHOW MASTER STATUS, Position, 1)
--connection server_4
RESET SLAVE;
--let $rpl_topology= 1->2->3->4->1
--let $rpl_master_log_file= 4:$file_d
--let $rpl_master_log_pos= 4:$pos_d
--source include/rpl_change_topology.inc
#--replace_result $SERVER_MYPORT_3 SERVER_MYPORT_3 $file_d LOG_FILE $pos_d LOG_POS 
#--eval CHANGE MASTER TO master_host='127.0.0.1',master_port=$SERVER_MYPORT_3,master_user='root',master_log_file='$file_d',master_log_pos=$pos_d
--source include/start_slave.inc
--connection server_3
--sync_slave_with_master server_4
--source include/rpl_sync.inc

--echo
--echo * Check data inserted before restoring schema A->B->C->D->A *
--connection server_1
SELECT 'Master A',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b;
--sync_slave_with_master server_2
SELECT 'Master B',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b;
--sync_slave_with_master server_3
SELECT 'Master C',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b;
--sync_slave_with_master server_4
SELECT 'Master D',a,b FROM t1 WHERE c IN (2,3) ORDER BY a,b;
--sync_slave_with_master server_1
--echo

--echo * Check data inserted after restoring schema A->B->C->D->A *
--connection server_1
INSERT INTO t1(b,c) VALUES('A',4);
--connection server_2
INSERT INTO t1(b,c) VALUES('B',4);
--connection server_3
INSERT INTO t1(b,c) VALUES('C',4);
--connection server_4
INSERT INTO t1(b,c) VALUES('D',4);
--connection server_1

--source include/rpl_sync.inc

--connection server_1
SELECT 'Master A',a,b FROM t1 WHERE c = 4 ORDER BY a,b;
--connection server_2
SELECT 'Master B',a,b FROM t1 WHERE c = 4 ORDER BY a,b;
--connection server_3
SELECT 'Master C',a,b FROM t1 WHERE c = 4 ORDER BY a,b;
--connection server_4
SELECT 'Master D',a,b FROM t1 WHERE c = 4 ORDER BY a,b;
--connection server_1
--echo

--echo * Transactions with commits *
# Testing mixing of transactions and regular inserts
--connection server_1
BEGIN;
--connection server_3
BEGIN;
let $counter= 100;
--connection server_1
--disable_query_log
while ($counter) {
 --connection server_1
 INSERT INTO t2(b,c) VALUES('A',1);
 --connection server_2
 INSERT INTO t2(b,c) VALUES('B',1);
 --connection server_3
 INSERT INTO t2(b,c) VALUES('C',1);
 --connection server_4
 INSERT INTO t2(b,c) VALUES('D',1);
 dec $counter;
}
--connection server_1
COMMIT;
--connection server_3
COMMIT;
--connection server_1
--enable_query_log

--source include/rpl_sync.inc

--connection server_1
SELECT 'Master A',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b;
--connection server_2
SELECT 'Master B',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b;
--connection server_3
SELECT 'Master C',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b;
--connection server_4
SELECT 'Master D',b,COUNT(*) FROM t2 WHERE c = 1 GROUP BY b ORDER BY b;
--connection server_1
--echo

--echo * Transactions with rollbacks *
# Testing mixing of transactions with rollback and regular inserts
--connection server_1
BEGIN;
--connection server_3
BEGIN;
let $counter= 100;
--connection server_1
--disable_query_log
while ($counter) {
 --connection server_1
 INSERT INTO t2(b,c) VALUES('A',2);
 --connection server_2
 INSERT INTO t2(b,c) VALUES('B',2);
 --connection server_3
 INSERT INTO t2(b,c) VALUES('C',2);
 --connection server_4
 INSERT INTO t2(b,c) VALUES('D',2);
 dec $counter;
}
--connection server_1
ROLLBACK;
--connection server_3
ROLLBACK;
--connection server_1
--enable_query_log

--source include/rpl_sync.inc

--connection server_1
SELECT 'Master A',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b;
--connection server_2
SELECT 'Master B',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b;
--connection server_3
SELECT 'Master C',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b;
--connection server_4
SELECT 'Master D',b,COUNT(*) FROM t2 WHERE c = 2 GROUP BY b ORDER BY b;
--connection server_1

--echo

# Clean up
--echo *** Clean up ***
--connection server_1
DROP TABLE t1,t2;

--source include/rpl_end.inc

Back to File Manager