MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sven Sandberg Date:October 28 2008 3:08pm
Subject:Re: bzr commit into mysql-5.1 branch (alfranio.correia:2669) Bug#38360
View as plain text  
Hi Alfranio,

Thanks for this big work, and sorry for the late review. It's mostly
very good, I can see that you put a lot of effort into this. I have some
remarks below. It's mostly polishing, but would be nice to have a very
good test case for this.

/Sven


Alfranio Correia wrote:
> #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 

Just for clarity, you could say in words that "B repliates from A, C
replicates from B, and D replicates from B". So that nobody reads the
diagram upside-down :-)

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

A isn't a replica. I suggest using "server" instead of "replica" (and
user "server" instead of "master" too) in the text below.

> +# 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,);

C isn't a master. I'd suggest to use either
master/blackhole/slave_a/slave_b, or server_a/server_b/server_c/server_d.

> +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

I suggest using "source include/start_slave.inc", it's an alias for
"start slave ; wait_for_slave_to_start"...

> +--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

... same here...

> +--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

... and here.

> +--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.

The above is a bit confusing. You're not changing engine in the test,
right? I think you can compress the text above a little, and just say
that the .cnf file ensures the default engine is as in the diagram. The
important thing is to document what setup you use and how you perform
the test.

> +# - Executing several statements with the following filters: 

I'm not sure "filter" is the correct technical term here. Perhaps you
can say that these are the characteristics of the tables you are using?

Please also say which types of queries you are using (INSERT, UPDATE, etc).

> +#   (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 *** 

... and b

> +--connection master_a
> +SHOW CREATE TABLE t1;
> +SHOW CREATE TABLE t2;
> +# Debuggin SHOW VARIABLES LIKE "BINLOG_FORMAT";

Please remove any unused code!

> +--echo
> +
> +--connection master_c
> +SHOW CREATE TABLE t1;
> +SHOW CREATE TABLE t2;
> +# Debugging SHOW VARIABLES LIKE "BINLOG_FORMAT";

... same here...

> +--echo
> +
> +--connection master_d
> +SHOW CREATE TABLE t1;
> +SHOW CREATE TABLE t2;
> +# Debugging SHOW VARIABLES LIKE "BINLOG_FORMAT";

.... and here...

> +--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";

... and here.

> +--echo
> +
> +# Test the hiearchical replication by means of the following operations:

hieRarchical

> +# insert, delete, insert/select, update, select and create...select. 
> +# And execute stataments (select, update and delete) with the following

statEments

> +# 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 ***

hieRarchical

> +--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);

Hint: you can abbreviate that a bit:
INSERT INTO t1 VALUES (7,7,'B',1),
                      (8,8,'B',1),
                      (9,9,'B',1),
                      (10,10,'B',1),
                      (11,11,'B',1);
But it's just style, do whatever you prefer.

> +
> +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. 

Why not just move UPDATE so that it's before DELETE? Then you don't have
to re-insert things here.

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

reduNdant

> +# in such way make it easier to understand and maintain.

What is redundant?

> +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.

There is no need to select things, since selects aren't replicated. But
please change it to INSERT ... SELECT. Also, please make a similar test
for CREATE...SELECT.

> +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.

Good news, mysqltest is now updated in 5.1-rpl so that "connection" can
take $variables! So you can rewrite diff_tables.inc and use that here.

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

For "normal" replication tests, we have a master-slave-enc.inc that is
called at the end, which does this. Perhaps you can factor out the code
after DROP TABLE to a separate file?

-- 
Sven Sandberg, Software Engineer
MySQL AB, www.mysql.com
Thread
bzr commit into mysql-5.1 branch (alfranio.correia:2669) Bug#38360Alfranio Correia7 Oct
  • Re: bzr commit into mysql-5.1 branch (alfranio.correia:2669) Bug#38360Sven Sandberg28 Oct