From: Date: October 7 2008 3:42pm
Subject: bzr commit into mysql-5.1 branch (alfranio.correia:2669) Bug#38360
List-Archive: http://lists.mysql.com/commits/55592
X-Bug: 38360
Message-Id: <200810071342.m97DglKm025259@localhost.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#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;