#At file:///home/acorreia/workspace.sun/repository.mysql/mysql-5.1-rpl/
2669 Alfranio Correia 2008-10-07
BUG#38360 - Created a new test case that aims at verifying that replication works with
an intermediate slave using BLACKHOLE storage ngine.
added:
mysql-test/include/hierarchical_rpl_for_4_hosts_init.inc
mysql-test/include/hierarchical_rpl_for_4_hosts_sync.inc
mysql-test/suite/rpl/r/rpl_hierarchical_for_4_hosts.result
mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.cnf
mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.test
per-file messages:
mysql-test/include/hierarchical_rpl_for_4_hosts_init.inc
Configures an hierarchical replication topology and starts the replication
threads.
mysql-test/include/hierarchical_rpl_for_4_hosts_sync.inc
Verifies if all replicas are synchronized.
mysql-test/suite/rpl/r/rpl_hierarchical_for_4_hosts.result
Has the expected results.
mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.cnf
Introduces two additional servers: slave1 --> replica c and
slave2 --> replica d.
mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.test
Aims at testing the BLACKHOLE (BUG#38360).
=== added file 'mysql-test/include/hierarchical_rpl_for_4_hosts_init.inc'
--- a/mysql-test/include/hierarchical_rpl_for_4_hosts_init.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/hierarchical_rpl_for_4_hosts_init.inc 2008-10-07 13:42:38 +0000
@@ -0,0 +1,93 @@
+#############################################################
+# Purpose: Setting up hierarchical replication as follows:
+# A
+# |
+# B
+# / \
+# C D
+#
+# Notes:
+# 2. Although the test uses new names for servers the file names
+# used for logging are still old:
+# master_a -> master.[log|err]
+# master_b -> slave.[log|err]
+# master_c -> slave1.[log|err]
+# master_d -> slave2.[log|err]
+#
+#############################################################
+# Configure replica a
+# Debugging --echo *** Configuring replica a $MASTER_MYPORT ***
+--echo *** Configuring replica a ***
+connect (master_a,127.0.0.1,root,,test,$MASTER_MYPORT,);
+USE test;
+RESET MASTER;
+--disable_warnings
+STOP SLAVE;
+--enable_warnings
+RESET SLAVE;
+let $_binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
+--echo
+
+# Configure replica b
+# Debugging --echo *** Configuring replica b $SLAVE_MYPORT connecting to master $MASTER_MYPORT ***
+--echo *** Configuring replica b ***
+connect (master_b,127.0.0.1,root,,test,$SLAVE_MYPORT,);
+USE test;
+RESET MASTER;
+--disable_warnings
+STOP SLAVE;
+--enable_warnings
+RESET SLAVE;
+--replace_result $MASTER_MYPORT MASTER_A_PORT $_binlog_file MASTER_A_LOG_FILE
+--eval CHANGE MASTER TO master_host='127.0.0.1',master_port=$MASTER_MYPORT,master_user='root',MASTER_LOG_FILE='$_binlog_file'
+let $_binlog_file= query_get_value(SHOW MASTER STATUS, File, 1);
+--echo
+
+# master c
+# Debugging --echo *** Configuring replica c $SLAVE_MYPORT1 connecting to master $SLAVE_MYPORT ***
+--echo *** Configuring replica c ***
+connect (master_c,127.0.0.1,root,,test,$SLAVE_MYPORT1,);
+USE test;
+RESET MASTER;
+--disable_warnings
+STOP SLAVE;
+--enable_warnings
+RESET SLAVE;
+--replace_result $SLAVE_MYPORT MASTER_B_PORT $_binlog_file MASTER_B_LOG_FILE
+--eval CHANGE MASTER TO master_host='127.0.0.1',master_port=$SLAVE_MYPORT,master_user='root',MASTER_LOG_FILE='$_binlog_file'
+--echo
+
+# master d
+#Debugging --echo *** Configuring replica c $SLAVE_MYPORT2 connecting to master $SLAVE_MYPORT ***
+--echo *** Configuring replica d ***
+connect (master_d,127.0.0.1,root,,test,$SLAVE_MYPORT2,);
+USE test;
+RESET MASTER;
+--disable_warnings
+STOP SLAVE;
+--enable_warnings
+RESET SLAVE;
+--replace_result $SLAVE_MYPORT MASTER_B_PORT $_binlog_file MASTER_B_LOG_FILE
+--eval CHANGE MASTER TO master_host='127.0.0.1',master_port=$SLAVE_MYPORT,master_user='root',MASTER_LOG_FILE='$_binlog_file'
+--echo
+
+# Start hierarchical replication
+--echo *** Starting hiearchical replication ***
+--connection master_b
+connect(slave,127.0.0.1,root,,test,$SLAVE_MYPORT);
+START SLAVE;
+--source include/wait_for_slave_to_start.inc
+--disconnect slave
+
+--connection master_c
+connect(slave,127.0.0.1,root,,test,$SLAVE_MYPORT1);
+START SLAVE;
+--source include/wait_for_slave_to_start.inc
+--disconnect slave
+
+--connection master_d
+connect(slave,127.0.0.1,root,,test,$SLAVE_MYPORT2);
+START SLAVE;
+--source include/wait_for_slave_to_start.inc
+--disconnect slave
+--echo
=== added file 'mysql-test/include/hierarchical_rpl_for_4_hosts_sync.inc'
--- a/mysql-test/include/hierarchical_rpl_for_4_hosts_sync.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/hierarchical_rpl_for_4_hosts_sync.inc 2008-10-07 13:42:38 +0000
@@ -0,0 +1,21 @@
+#############################################################
+# Purpose: Syncing all hosts for hierarchical replication
+# based on the following diagram:
+# A
+# |
+# B
+# / \
+# C D
+# Notes: see include/hierarchical_rpl_for_4_hosts_init.inc
+#
+#############################################################
+
+# Make the full traversal of sync
+--connection master_a
+--disable_query_log
+--sync_slave_with_master master_b
+--sync_slave_with_master master_c
+--connection master_b
+--sync_slave_with_master master_d
+--connection master_a
+--enable_query_log
=== added file 'mysql-test/suite/rpl/r/rpl_hierarchical_for_4_hosts.result'
--- a/mysql-test/suite/rpl/r/rpl_hierarchical_for_4_hosts.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_hierarchical_for_4_hosts.result 2008-10-07 13:42:38 +0000
@@ -0,0 +1,307 @@
+*** Setting up hierarchical replication with four nodes ***
+*** Configuring replica a ***
+USE test;
+RESET MASTER;
+STOP SLAVE;
+RESET SLAVE;
+
+*** Configuring replica b ***
+USE test;
+RESET MASTER;
+STOP SLAVE;
+RESET SLAVE;
+CHANGE MASTER TO master_host='127.0.0.1',master_port=MASTER_A_PORT,master_user='root',MASTER_LOG_FILE='MASTER_A_LOG_FILE';
+
+*** Configuring replica c ***
+USE test;
+RESET MASTER;
+STOP SLAVE;
+RESET SLAVE;
+CHANGE MASTER TO master_host='127.0.0.1',master_port=MASTER_B_PORT,master_user='root',MASTER_LOG_FILE='MASTER_B_LOG_FILE';
+
+*** Configuring replica d ***
+USE test;
+RESET MASTER;
+STOP SLAVE;
+RESET SLAVE;
+CHANGE MASTER TO master_host='127.0.0.1',master_port=MASTER_B_PORT,master_user='root',MASTER_LOG_FILE='MASTER_B_LOG_FILE';
+
+*** Starting hiearchical replication ***
+START SLAVE;
+START SLAVE;
+START SLAVE;
+
+*** Creating database schema ***
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL);
+CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL, INDEX (a,b));
+CREATE TABLE t3 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL, INDEX (a));
+CREATE TABLE t4 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL, PRIMARY KEY(a,b));
+CREATE TABLE t5 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL, PRIMARY KEY(a));
+
+*** Verifying database schema ***
+*** Checking database schema for replica a, c and d ***
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` varchar(100) DEFAULT NULL,
+ `d` int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` varchar(100) DEFAULT NULL,
+ `d` int(11) NOT NULL,
+ KEY `a` (`a`,`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` varchar(100) DEFAULT NULL,
+ `d` int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` varchar(100) DEFAULT NULL,
+ `d` int(11) NOT NULL,
+ KEY `a` (`a`,`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` varchar(100) DEFAULT NULL,
+ `d` int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` varchar(100) DEFAULT NULL,
+ `d` int(11) NOT NULL,
+ KEY `a` (`a`,`b`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+
+*** Checking database schema for replica b ***
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` varchar(100) DEFAULT NULL,
+ `d` int(11) NOT NULL
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t2;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `a` int(11) NOT NULL,
+ `b` int(11) NOT NULL,
+ `c` varchar(100) DEFAULT NULL,
+ `d` int(11) NOT NULL,
+ KEY `a` (`a`,`b`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+
+*** Testing the hiearchical replication ***
+INSERT INTO t1(a,b,c,d) VALUES(1,1,'A',1);
+INSERT INTO t2(a,b,c,d) VALUES(2,2,'A',1);
+INSERT INTO t3(a,b,c,d) VALUES(3,3,'A',1);
+INSERT INTO t4(a,b,c,d) VALUES(4,4,'A',1);
+INSERT INTO t4(a,b,c,d) VALUES(5,5,'A',1);
+INSERT INTO t5(a,b,c,d) VALUES(6,6,'A',1);
+INSERT INTO t1(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t1(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t1(a,b,c,d) VALUES(9,9,'B',1);
+INSERT INTO t1(a,b,c,d) VALUES(10,10,'B',1);
+INSERT INTO t1(a,b,c,d) VALUES(11,11,'B',1);
+INSERT INTO t2(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t2(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t2(a,b,c,d) VALUES(9,9,'B',1);
+INSERT INTO t3(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t3(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t3(a,b,c,d) VALUES(9,9,'B',1);
+INSERT INTO t4(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t4(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t4(a,b,c,d) VALUES(9,9,'B',1);
+INSERT INTO t5(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t5(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t5(a,b,c,d) VALUES(9,9,'B',1);
+DELETE FROM t1 WHERE a = 7;
+DELETE FROM t1 WHERE a = 8 and b = 8;
+DELETE FROM t1 WHERE a = 9 and b = 9 and c = 'B';
+DELETE FROM t1 WHERE c = 'B';
+DELETE FROM t2 WHERE a = 7;
+DELETE FROM t2 WHERE a = 8 and b = 8;
+DELETE FROM t2 WHERE a = 9 and b = 9 and c = 'B';
+DELETE FROM t3 WHERE a = 7;
+DELETE FROM t3 WHERE a = 8 and b = 8;
+DELETE FROM t3 WHERE a = 9 and b = 9 and c = 'B';
+DELETE FROM t4 WHERE a = 7;
+DELETE FROM t4 WHERE a = 8 and b = 8;
+DELETE FROM t4 WHERE a = 9 and b = 9 and c = 'B';
+DELETE FROM t5 WHERE a = 7;
+DELETE FROM t5 WHERE a = 8 and b = 8;
+DELETE FROM t5 WHERE a = 9 and b = 9 and c = 'B';
+INSERT INTO t2 SELECT * FROM t1;
+INSERT INTO t3 SELECT * FROM t1;
+INSERT INTO t4 SELECT * FROM t1;
+INSERT INTO t5 SELECT * FROM t1;
+UPDATE t1 SET d = 2 WHERE a = 1;
+UPDATE t1 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t1 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+UPDATE t1 SET d = 5 WHERE c = 'A';
+UPDATE t2 SET d = 2 WHERE a = 1;
+UPDATE t2 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t2 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+UPDATE t3 SET d = 2 WHERE a = 1;
+UPDATE t3 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t3 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+UPDATE t4 SET d = 2 WHERE a = 1;
+UPDATE t4 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t4 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+UPDATE t5 SET d = 2 WHERE a = 1;
+UPDATE t5 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t5 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+SELECT * FROM t1 WHERE a = 1;
+a b c d
+1 1 A 5
+SELECT * FROM t1 WHERE a = 1 and b = 1;
+a b c d
+1 1 A 5
+SELECT * FROM t1 WHERE a = 1 and b = 1 and c = 'B';
+a b c d
+SELECT * FROM t1 WHERE c = 'A';
+a b c d
+1 1 A 5
+SELECT * FROM t2 WHERE a = 1;
+a b c d
+1 1 A 3
+SELECT * FROM t2 WHERE a = 1 and b = 1;
+a b c d
+1 1 A 3
+SELECT * FROM t2 WHERE a = 1 and b = 1 and c = 'B';
+a b c d
+SELECT * FROM t3 WHERE a = 1;
+a b c d
+1 1 A 3
+SELECT * FROM t3 WHERE a = 1 and b = 1;
+a b c d
+1 1 A 3
+SELECT * FROM t3 WHERE a = 1 and b = 1 and c = 'B';
+a b c d
+SELECT * FROM t4 WHERE a = 1;
+a b c d
+1 1 A 3
+SELECT * FROM t4 WHERE a = 1 and b = 1;
+a b c d
+1 1 A 3
+SELECT * FROM t4 WHERE a = 1 and b = 1 and c = 'B';
+a b c d
+SELECT * FROM t5 WHERE a = 1;
+a b c d
+1 1 A 3
+SELECT * FROM t5 WHERE a = 1 and b = 1;
+a b c d
+1 1 A 3
+SELECT * FROM t5 WHERE a = 1 and b = 1 and c = 'B';
+a b c d
+CREATE TABLE t6 SELECT * FROM t1;
+
+*** Verifying if servers are consistent as expected ***
+*** Checking replicas a, c and d ***
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t4;
+COUNT(*)
+3
+SELECT COUNT(*) FROM t5;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t6;
+COUNT(*)
+1
+
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t4;
+COUNT(*)
+3
+SELECT COUNT(*) FROM t5;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t6;
+COUNT(*)
+1
+
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t4;
+COUNT(*)
+3
+SELECT COUNT(*) FROM t5;
+COUNT(*)
+2
+SELECT COUNT(*) FROM t6;
+COUNT(*)
+1
+
+*** Checking replica b ***
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+0
+SELECT COUNT(*) FROM t3;
+COUNT(*)
+0
+SELECT COUNT(*) FROM t4;
+COUNT(*)
+0
+SELECT COUNT(*) FROM t5;
+COUNT(*)
+0
+SELECT COUNT(*) FROM t6;
+COUNT(*)
+0
+
+*** Cleaning up ***
+DROP TABLE t1, t2, t3, t4, t5, t6;
+STOP SLAVE;
+RESET SLAVE;
+STOP SLAVE;
+RESET SLAVE;
+STOP SLAVE;
+RESET SLAVE;
=== added file 'mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.cnf'
--- a/mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.cnf 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.cnf 2008-10-07 13:42:38 +0000
@@ -0,0 +1,25 @@
+!include ../my.cnf
+
+[mysqld.1]
+log-slave-updates
+default-storage_engine = innodb
+
+[mysqld.2]
+log-slave-updates
+default-storage_engine = blackhole
+
+[mysqld.3]
+log-slave-updates
+default-storage_engine = innodb
+
+[mysqld.4]
+log-slave-updates
+default-storage_engine = innodb
+
+[ENV]
+SLAVE_MYPORT1= @mysqld.3.port
+SLAVE_MYSOCK1= @mysqld.3.socket
+
+SLAVE_MYPORT2= @mysqld.4.port
+SLAVE_MYSOCK2= @mysqld.4.socket
+
=== added file 'mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.test'
--- a/mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_hierarchical_for_4_hosts.test 2008-10-07 13:42:38 +0000
@@ -0,0 +1,290 @@
+#############################################################
+# Purpose: Verifying that replication works with an
+# intermediate slave using BLACKHOLE (BUG#38360) storage
+# engine, using the replication topology of the following
+# diagram:
+# A (innodb)
+# |
+# B (blackhole)
+# / \
+# C D (innodb)
+#
+# This test consists in the following steps:
+# - Ensuring that the server has innodb, blackhole and binlog.
+# - Configuring the environment.
+# - Creating the tables and relying on the default engine
+# specified in the configuration file.
+# - Changing database engine in server b.
+# - Executing several statements with the following filters:
+# (1) a multi-column primary key where only some of the columns are used;
+# (2) a multi-column primary key where only some of the columns are used,
+# and also some columns that are not in the key;
+# (3) use a key that is not primary;
+# (4) like (1) and (2), but a key that is not primary;
+# (5) like (3) and (4), but where more than one row match the key
+# - Verifying that server a, c and d have the same data.
+# - However, b does not have data but its bin log
+# was updated.
+#
+#############################################################
+--source include/have_innodb.inc
+--source include/have_blackhole.inc
+--source include/have_log_bin.inc
+
+# Set up a hierarchical replication
+--echo *** Setting up hierarchical replication with four nodes ***
+--source include/hierarchical_rpl_for_4_hosts_init.inc
+
+# Create database schema with the following tables:
+# t1 - no index
+# t2 - non-unique, multi-column index
+# t3 - non-unique, single-column index
+# t4 - multi-column primary key
+# t5 - single-column primary key
+--echo *** Creating database schema ***
+--connection master_a
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL);
+CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL, INDEX (a,b));
+CREATE TABLE t3 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL, INDEX (a));
+CREATE TABLE t4 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL, PRIMARY KEY(a,b));
+CREATE TABLE t5 (a INT NOT NULL, b INT NOT NULL, c VARCHAR(100), d INT NOT NULL, PRIMARY KEY(a));
+--source include/hierarchical_rpl_for_4_hosts_sync.inc
+--echo
+
+# Checking database schema.
+--echo *** Verifying database schema ***
+--echo *** Checking database schema for replica a, c and d ***
+--connection master_a
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+# Debuggin SHOW VARIABLES LIKE "BINLOG_FORMAT";
+--echo
+
+--connection master_c
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+# Debugging SHOW VARIABLES LIKE "BINLOG_FORMAT";
+--echo
+
+--connection master_d
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+# Debugging SHOW VARIABLES LIKE "BINLOG_FORMAT";
+--echo
+
+--echo *** Checking database schema for replica b ***
+--connection master_b
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+# Debugging SHOW VARIABLES LIKE "BINLOG_FORMAT";
+--echo
+
+# Test the hiearchical replication by means of the following operations:
+# insert, delete, insert/select, update, select and create...select.
+# And execute stataments (select, update and delete) with the following
+# characteristics:
+#
+# (1) a multi-column primary key where only some of the columns are used;
+#
+# (2) a multi-column primary key where only some of the columns are used,
+# and also some columns that are not in the key;
+#
+# (3) use a key that is not primary;
+#
+# (4) like (1) and (2), but use a key that is not primary;
+#
+# (5) like (3) and (4), but make sure that more than one row match the key
+
+--echo *** Testing the hiearchical replication ***
+--connection master_a
+
+# Populating tables through inserts with rows
+# that will not be deleted.
+INSERT INTO t1(a,b,c,d) VALUES(1,1,'A',1);
+
+INSERT INTO t2(a,b,c,d) VALUES(2,2,'A',1);
+
+INSERT INTO t3(a,b,c,d) VALUES(3,3,'A',1);
+
+INSERT INTO t4(a,b,c,d) VALUES(4,4,'A',1);
+
+INSERT INTO t4(a,b,c,d) VALUES(5,5,'A',1);
+
+INSERT INTO t5(a,b,c,d) VALUES(6,6,'A',1);
+
+# Populating tables through inserts with rows
+# that will be deleted.
+INSERT INTO t1(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t1(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t1(a,b,c,d) VALUES(9,9,'B',1);
+INSERT INTO t1(a,b,c,d) VALUES(10,10,'B',1);
+INSERT INTO t1(a,b,c,d) VALUES(11,11,'B',1);
+
+INSERT INTO t2(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t2(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t2(a,b,c,d) VALUES(9,9,'B',1);
+
+INSERT INTO t3(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t3(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t3(a,b,c,d) VALUES(9,9,'B',1);
+
+INSERT INTO t4(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t4(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t4(a,b,c,d) VALUES(9,9,'B',1);
+
+INSERT INTO t5(a,b,c,d) VALUES(7,7,'B',1);
+INSERT INTO t5(a,b,c,d) VALUES(8,8,'B',1);
+INSERT INTO t5(a,b,c,d) VALUES(9,9,'B',1);
+
+# Deleting rows and testing the following possibilities:
+# non-index, partial index, full index, full index + non-index.
+#
+# Note that there are redudant operations however doing the test
+# in such way make it easier to understand and maintain.
+DELETE FROM t1 WHERE a = 7;
+DELETE FROM t1 WHERE a = 8 and b = 8;
+DELETE FROM t1 WHERE a = 9 and b = 9 and c = 'B';
+DELETE FROM t1 WHERE c = 'B';
+
+DELETE FROM t2 WHERE a = 7;
+DELETE FROM t2 WHERE a = 8 and b = 8;
+DELETE FROM t2 WHERE a = 9 and b = 9 and c = 'B';
+
+DELETE FROM t3 WHERE a = 7;
+DELETE FROM t3 WHERE a = 8 and b = 8;
+DELETE FROM t3 WHERE a = 9 and b = 9 and c = 'B';
+
+DELETE FROM t4 WHERE a = 7;
+DELETE FROM t4 WHERE a = 8 and b = 8;
+DELETE FROM t4 WHERE a = 9 and b = 9 and c = 'B';
+
+DELETE FROM t5 WHERE a = 7;
+DELETE FROM t5 WHERE a = 8 and b = 8;
+DELETE FROM t5 WHERE a = 9 and b = 9 and c = 'B';
+
+# Populating tables through insert/select.
+# This should be placed before the updates
+# in order to avoid trying to update rows that do not exist.
+#
+INSERT INTO t2 SELECT * FROM t1;
+
+INSERT INTO t3 SELECT * FROM t1;
+
+INSERT INTO t4 SELECT * FROM t1;
+
+INSERT INTO t5 SELECT * FROM t1;
+# Updating rows and testing the following possibilities:
+# non-index, partial index, full index, full index + non-index.
+#
+# Note that there are redudant operations however doing the test
+# in such way make it easier to understand and maintain.
+UPDATE t1 SET d = 2 WHERE a = 1;
+UPDATE t1 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t1 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+UPDATE t1 SET d = 5 WHERE c = 'A';
+
+UPDATE t2 SET d = 2 WHERE a = 1;
+UPDATE t2 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t2 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+
+UPDATE t3 SET d = 2 WHERE a = 1;
+UPDATE t3 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t3 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+
+UPDATE t4 SET d = 2 WHERE a = 1;
+UPDATE t4 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t4 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+
+UPDATE t5 SET d = 2 WHERE a = 1;
+UPDATE t5 SET d = 3 WHERE a = 1 and b = 1;
+UPDATE t5 SET d = 4 WHERE a = 1 and b = 1 and c = 'B';
+
+# Selecting rows and testing the following possibilities:
+# non-index, partial index, full index, full index + non-index.
+#
+# Note that there are redudant operations however doing the test
+# in such way make it easier to understand and maintain.
+SELECT * FROM t1 WHERE a = 1;
+SELECT * FROM t1 WHERE a = 1 and b = 1;
+SELECT * FROM t1 WHERE a = 1 and b = 1 and c = 'B';
+SELECT * FROM t1 WHERE c = 'A';
+
+SELECT * FROM t2 WHERE a = 1;
+SELECT * FROM t2 WHERE a = 1 and b = 1;
+SELECT * FROM t2 WHERE a = 1 and b = 1 and c = 'B';
+
+SELECT * FROM t3 WHERE a = 1;
+SELECT * FROM t3 WHERE a = 1 and b = 1;
+SELECT * FROM t3 WHERE a = 1 and b = 1 and c = 'B';
+
+SELECT * FROM t4 WHERE a = 1;
+SELECT * FROM t4 WHERE a = 1 and b = 1;
+SELECT * FROM t4 WHERE a = 1 and b = 1 and c = 'B';
+
+SELECT * FROM t5 WHERE a = 1;
+SELECT * FROM t5 WHERE a = 1 and b = 1;
+SELECT * FROM t5 WHERE a = 1 and b = 1 and c = 'B';
+
+# Creating table from a select:
+CREATE TABLE t6 SELECT * FROM t1;
+
+--source include/hierarchical_rpl_for_4_hosts_sync.inc
+--echo
+
+# Verify if servers are consistent as expected
+# This should be replaced by source include/diff_tables.inc
+# after making some changes in this include.
+# Currently, it only allows two connections' identifiers: master and slave.
+--echo *** Verifying if servers are consistent as expected ***
+--echo *** Checking replicas a, c and d ***
+--connection master_a
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t2;
+SELECT COUNT(*) FROM t3;
+SELECT COUNT(*) FROM t4;
+SELECT COUNT(*) FROM t5;
+SELECT COUNT(*) FROM t6;
+--echo
+
+--connection master_c
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t2;
+SELECT COUNT(*) FROM t3;
+SELECT COUNT(*) FROM t4;
+SELECT COUNT(*) FROM t5;
+SELECT COUNT(*) FROM t6;
+--echo
+
+--connection master_d
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t2;
+SELECT COUNT(*) FROM t3;
+SELECT COUNT(*) FROM t4;
+SELECT COUNT(*) FROM t5;
+SELECT COUNT(*) FROM t6;
+--echo
+
+--echo *** Checking replica b ***
+--connection master_b
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t2;
+SELECT COUNT(*) FROM t3;
+SELECT COUNT(*) FROM t4;
+SELECT COUNT(*) FROM t5;
+SELECT COUNT(*) FROM t6;
+--echo
+
+# Clean up
+--echo *** Cleaning up ***
+--connection master_a
+DROP TABLE t1, t2, t3, t4, t5, t6;
+--source include/hierarchical_rpl_for_4_hosts_sync.inc
+--connection master_b
+STOP SLAVE;
+RESET SLAVE;
+--connection master_c
+STOP SLAVE;
+RESET SLAVE;
+--connection master_d
+STOP SLAVE;
+RESET SLAVE;
