Hi Luís,
Summary: Good start with the test cases. Much of the code is structured
and easy to follow. I think the tests can be even better if we get rid
of some code duplication. Also, I think some test scenarios are missing.
Suggestions:
(S1) Please state for each test (1) what server property is tested; (2)
how is it tested (see comments inline)
(S2) I think a little refactoring of rpl_row_img_*.test would make the
test even more transparent (see comments inline)
(S3) I refuse to review rpl_row_sanity :-) Seriously, I'm impressed that
you could write something that looks so complex, but I think it is
possible to refactor it to make it more easy to decipher. See comments
inline.
(S4) I think some tests are missing. In particular, can you add tests
for the following:
(S4-1) In rpl_row_sanity, please make sure all cases listed in the
tables in WL#5072 are tested. Currently, it misses e.g., the autoinc case.
(S4-2) Test that a slave with missing columns does not write columns
that it does not have
(S4-3) Test that if master has binlog_row_image=minimal and slave has
binlog_row_image=noblob|full, then slave logs the correct columns.
(S4-4) Test that an error is generated when the slave does not have
the columns included in the AI.
(S4-5) Test that correct default values are inserted in extra slave
columns.
(S4-6) Test that extra master columns are correctly ignored by the
slave as long as there is at least one usable column on the slave. E.g.:
master> CREATE TABLE t1 (a INT NOT NULL, b INT, c INT
PRIMARY KEY (a,c), UNIQUE KEY (a));
slave> CREATE TABLE t1 (a INT NOT NULL, b INT,
UNIQUE KEY (a));
AIUI, the above should always replicate correctly even with
binlog_row_image=minimal?
(S4-7) Test that the default value for binlog_row_image is 'full'.
(S5) Instead of the connect/disconnect hack in
rpl_chained_3_hosts_diff_tables.inc , could you make
include/diff_tables.inc capable of diffing tables in arbitrary
connections? I think diff_tables.inc should also reset the original
connection at the end. (Ah, finally! I've been waiting for a chance to
scratch this itch ever since BUG#35701 was fixed :-)
(S6) Some tests seem redundant and/or it is unclear what is being
tested. See comments inline.
(S7) Possible typo, see comments inline.
(S8) Remove DELAYED from INSERT DELAYED (see comments inline)
(S9) It looks like a lot of code is duplicated between
rpl_record_find.test and rpl_row_img.test. Isn't rpl_record_find.test a
special case of rpl_row_img.test, only with one server less? If not,
please explain!
==== mysql-test/suite/rpl/t/rpl_row_img_*.test ====
> #
> # Test Description
> # ================
> #
> # See WL#5096.
> #
(S1) Please state: (1) the properties that we test; (2) how these
properties are tested. I think this should be in the test case, it's not
enough to refer to a WL because test cases and WLs may evolve
independently over time. When you use a .inc file, you could also put
the documentation in the .inc file and refer to the .inc file from the
.test file.
> -- source suite/rpl/include/rpl_chained_3_hosts.inc
> -- source include/have_binlog_format_row.inc
> -- connection mysqld_a
> -- source include/have_innodb.inc
> -- connection mysqld_b
> -- source include/have_innodb.inc
> -- connection mysqld_c
> -- source include/have_innodb.inc
> -- connection mysqld_a
>
> -- echo #### Chained replication: A -> B -> C ####
>
> -- let $row_image_param= 'FULL'
> -- source suite/rpl/include/rpl_row_img_set.inc
>
> --echo #################################################################
> --echo # CASE 1: InnoDB - A, MyISAM - B,C
> --echo #################################################################
(S2) There is a lot of code duplication here. Why not use loops:
--let $engine_type_a= 2
--let $mysqld_a_engine= MyISAM
while ($engine_type_a) {
--let $engine_type_b= 2
--let $mysqld_b_engine= MyISAM
while ($engine_type_b) {
--let $engine_type_c= 2
--let $mysqld_c_engine= MyISAM
while ($engine_type_c) {
...
--let $mysqld_c_engine= InnoDB
--dec $engine_type_c
}
--let $mysqld_b_engine= InnoDB
--dec $engine_type_b
}
--let $mysqld_a_engine= InnoDB
--dec $engine_type_a
}
Then you can also merge all the
rpl_row_img_[myisam|innodb][|_blobs|_indexes].test tests into one. And
adding another engine becomes easy.
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= MyISAM;
> let $mysqld_c_engine= MyISAM;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 2: InnoDB - A,B,C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 3: MyISAM - A, InnoDB - B,C
> --echo #################################################################
>
> let $mysqld_a_engine= MyISAM;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 4: MyISAM - A,C, InnoDB - B
> --echo #################################################################
>
> let $mysqld_a_engine= MyISAM;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= MyISAM;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 5: MyISAM - B, InnoDB - A,C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= MyISAM;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 6: InnoDB - C, MyISAM - A,B
> --echo #################################################################
>
> let $mysqld_a_engine= MyISAM;
> let $mysqld_b_engine= MyISAM;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 7: InnoDB - A,C MyISAM - C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= MyISAM;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> -- echo ############## NOBLOB
>
> -- let $row_image_param= 'NOBLOB'
> -- source suite/rpl/include/rpl_row_img_set.inc
>
> --echo #################################################################
> --echo # CASE 8: InnoDB - A, MyISAM - B,C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= MyISAM;
> let $mysqld_c_engine= MyISAM;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 9: InnoDB - A,B,C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 10: MyISAM - A, InnoDB - B,C
> --echo #################################################################
>
> let $mysqld_a_engine= MyISAM;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 11: MyISAM - A,C, InnoDB - B
> --echo #################################################################
>
> let $mysqld_a_engine= MyISAM;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= MyISAM;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 12: MyISAM - B, InnoDB - A,C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= MyISAM;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 13: InnoDB - C, MyISAM - A,B
> --echo #################################################################
>
> let $mysqld_a_engine= MyISAM;
> let $mysqld_b_engine= MyISAM;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 14: InnoDB - A,C MyISAM - C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= MyISAM;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> -- echo ############## MINIMAL
>
> -- let $row_image_param= 'MINIMAL'
> -- source suite/rpl/include/rpl_row_img_set.inc
>
> --echo #################################################################
> --echo # CASE 15: InnoDB - A, MyISAM - B,C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= MyISAM;
> let $mysqld_c_engine= MyISAM;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 16: InnoDB - A,B,C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 17: MyISAM - A, InnoDB - B,C
> --echo #################################################################
>
> let $mysqld_a_engine= MyISAM;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 18: MyISAM - A,C, InnoDB - B
> --echo #################################################################
>
> let $mysqld_a_engine= MyISAM;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= MyISAM;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 19: MyISAM - B, InnoDB - A,C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= MyISAM;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 20: InnoDB - C, MyISAM - A,B
> --echo #################################################################
>
> let $mysqld_a_engine= MyISAM;
> let $mysqld_b_engine= MyISAM;
> let $mysqld_c_engine= InnoDB;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> --echo #################################################################
> --echo # CASE 21: InnoDB - A,C MyISAM - C
> --echo #################################################################
>
> let $mysqld_a_engine= InnoDB;
> let $mysqld_b_engine= InnoDB;
> let $mysqld_c_engine= MyISAM;
>
> -- source extra/rpl_tests/rpl_row_img.test
>
> ## clean up
> -- let $row_image_param= 'FULL'
> -- source suite/rpl/include/rpl_row_img_set.inc
>
==== mysql-test/extra/rpl_tests/rpl_row_img.test ====
> # WL#5096
> #
> # Description
> # ===========
> #
> # This test case covers Requirements for different index structures
> # on both master and slave. This covers requirements R1. R2 is
> # covered in another test.
(S1) Please state the requirements explicitly in the test case!
> #
> # Usage
> # =====
> #
> # Before including this file the following variables should be set:
> # * $mysqld_a_engine
> # * $mysqld_b_engine
> # * $mysqld_c_engine
> #
> # Example:
> #
> # -- let $mysqld_a_engine= Falcon
> # -- let $mysqld_b_engine= MyISAM
> # -- let $mysqld_c_engine= InnoDB
> #
> # -- source extra/rpl_tests/rpl_row_img.test
> #
>
> -- connection mysqld_a
>
> -- disable_warnings
> DROP TABLE IF EXISTS t;
> -- enable_warnings
> -- source suite/rpl/include/rpl_chained_3_hosts_sync.inc
>
> -- connection mysqld_a
>
> let $i= 14;
> while($i)
> {
> let $step= "";
>
> -- connection mysqld_a
> SET SQL_LOG_BIN=0;
>
> -- connection mysqld_b
> SET SQL_LOG_BIN=0;
>
> -- connection mysqld_c
> SET SQL_LOG_BIN=0;
>
> if (`SELECT $i=1`) {
> let $step= No keys;
> --echo ******* TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine=
> $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine=
> $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine=
> $mysqld_c_engine;
>
> }
> if (`SELECT $i=2`)
> {
> let $step= One key;
> --echo ******* TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1))
> engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1))
> engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1))
> engine= $mysqld_c_engine;
>
> }
> if (`SELECT $i=3`)
> {
> let $step= One Composite key;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2))
> engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2))
> engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2))
> engine= $mysqld_c_engine;
> }
> if (`SELECT $i=4`)
> {
> let $step= One Unique key;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1)) engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1)) engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1)) engine= $mysqld_c_engine;
>
> }
> if (`SELECT $i=5`)
> {
> let $step= One Composite Unique key;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1,c2)) engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1,c2)) engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1,c2)) engine= $mysqld_c_engine;
>
(S6) For case 1, 2, 3, 4, 5, 8, 9, what is it you actually test? I
thought that keys and nullable UKs had no impact on what is logged? If
you want to test that it searches for the row in the correct way (e.g.,
according to the HLS of WL#5092), then there must be a way for the test
case to check how the keys are used on the slaves (that would probably
require some ifdebug code).
Case 11 looks like a duplicate of case 2 (only the index column differs).
> }
> if (`SELECT $i=6`)
> {
> let $step= One Primary key;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1)) engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1)) engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1)) engine= $mysqld_c_engine;
> }
> if (`SELECT $i=7`)
> {
> let $step= One Composite Primary Key;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1,c2)) engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1,c2)) engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1,c2)) engine= $mysqld_c_engine;
> }
> if (`SELECT $i=8`)
> {
> let $step= One Composite key with holes;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3))
> engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3))
> engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3))
> engine= $mysqld_c_engine;
> }
> if (`SELECT $i=9`)
> {
> let $step= One Composite Unique key with holes;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1,c3)) engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1,c3)) engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1,c3)) engine= $mysqld_c_engine;
> }
> if (`SELECT $i=10`)
> {
> let $step= One Composite Primary Key with holes;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1,c3)) engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1,c3)) engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1,c3)) engine= $mysqld_c_engine;
> }
> if (`SELECT $i=11`)
> {
> let $step= One key;
> --echo ******* TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c4))
> engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c4))
> engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c4))
> engine= $mysqld_c_engine;
> }
> if (`SELECT $i=12`)
> {
> let $step= One Composite NOT NULL Unique key with holes;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4
> char(1), unique key(c1,c3)) engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4
> char(1), unique key(c1,c3)) engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4
> char(1), unique key(c1,c3)) engine= $mysqld_c_engine;
> }
> if (`SELECT $i=13`)
> {
> let $step= One Composite NOT NULL Unique key with holes in master, but not on
> NULLABLE on first slave;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4
> char(1), unique key(c1,c3)) engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique
> key(c1,c3)) engine= $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4
> char(1), unique key(c1,c3)) engine= $mysqld_c_engine;
> }
> if (`SELECT $i=14`)
> {
> let $step= One Composite PK with holes in master, but no PK on first slave;
> --echo ****** TEST: $step
> -- connection mysqld_a
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary
> key(c1,c3)) engine= $mysqld_a_engine;
> -- connection mysqld_b
> --eval CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine=
> $mysqld_b_engine;
> -- connection mysqld_c
> --eval CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4
> char(1), primary key(c1,c3)) engine= $mysqld_c_engine;
(S7) Why use NOT NULL on the last slave? Is it a typo?
> }
>
> -- connection mysqld_a
> SET SQL_LOG_BIN=1;
>
> -- connection mysqld_b
> SET SQL_LOG_BIN=1;
>
> -- connection mysqld_c
> SET SQL_LOG_BIN=1;
>
> -- connection mysqld_a
> INSERT INTO t VALUES (1, '1', '1', '1' );
> INSERT INTO t VALUES (4, '4', '4', '4' );
> INSERT INTO t VALUES (7, '7', '7', '7' );
>
> INSERT INTO t VALUES (9, '9', '9', NULL );
>
> INSERT INTO t VALUES (2, '1', '2', '2' );
> INSERT INTO t VALUES (3, '1', '3', '2' );
>
> -- source suite/rpl/include/rpl_chained_3_hosts_sync.inc
>
> -- connection mysqld_a
> UPDATE t SET c4 = '7';
>
> UPDATE t SET c4 = '5' WHERE c1 = 1;
> UPDATE t SET c2 = '5' WHERE c1 = 1;
> UPDATE t SET c1 = '5' WHERE c1 = 1;
>
> UPDATE t SET c4 = '8' WHERE c2 = '4';
> UPDATE t SET c1 = '8' WHERE c2 = '4';
> UPDATE t SET c2 = '8' WHERE c2 = '4';
>
> UPDATE t SET c3 = '0' WHERE c4 = NULL;
> UPDATE t SET c2 = '0' WHERE c4 = '0';
>
> UPDATE t SET c2 = '2' WHERE c4 = '2';
Can you comment on the purpose of the inserts and updates above? Can
some insert/update be eliminated without removing any test scenario?
When the result file is as big as this, it helps to minimize things that
are done repeatedly...
>
> -- source suite/rpl/include/rpl_chained_3_hosts_sync.inc
>
> -- let $diff_table=test.t
> -- source suite/rpl/include/rpl_chained_3_hosts_diff_tables.inc
>
> -- connection mysqld_a
>
> DELETE FROM t WHERE c1 = 7;
> DELETE FROM t WHERE c1 = 8;
> DELETE FROM t;
>
> -- source suite/rpl/include/rpl_chained_3_hosts_sync.inc
>
> -- let $diff_table=test.t
> -- source suite/rpl/include/rpl_chained_3_hosts_diff_tables.inc
>
> -- connection mysqld_a
>
> DROP TABLE t;
>
> -- source suite/rpl/include/rpl_chained_3_hosts_sync.inc
>
> dec $i;
> }
==== rpl_row_img_sanity.test ====
> #
> # Description
> # ===========
> #
> # This test case checks whether binlog files contain
> # Before and After image values as expected.
> #
> # Configuration is done using the --binlog-row-image
> # option.
> #
> # How it works
> # ============
> #
> # The test case is implemented such that master and slave
> # create a table with different types of keys. Then, deletes
> # and updates are performed in the master. The resulting
> # output of mysqlbinlog -v issued on the binlogs generated
> # is then searched for the pattern expected wrt before and
> # after images.
> #
> # See also WL#5096.
(S3) I think this file is too big and unstructured! It is very hard to
check that all the perl re's are correct, and that all test scenarios
are covered.
I would suggest to create a subroutine (i.e., .inc file) that takes as
input the expected binlog contents in a human-readable form, and makes
the corresponding checks. For example:
UPDATE t1 SET c1=7, c4=7 WHERE c1=1;
# This means: the BI contains columns 1, 2, 4, and the AI contains
# the columns 1, 4. Since both BI and AI are specified, the event
# is an update event.
--let $expected_image= 'BI: 1 2 4 AI: 1 4'
--source extra/rpl_tests/rpl_row_image_test.inc
INSERT INTO t1 SET c1=7, c4=7;
# This means: there is no BI, and the AI contains the columns 1, 4.
# Since only AI is specified, the event is a write event.
--let $expected_image= 'AI: 1 4'
--source extra/rpl_tests/rpl_row_image_test.inc
If needed, you could also make the .inc file accept an
$expected_slave_image parameter to check what the slave logs etc.
The .inc file can keep track of binlog coordinates internally (i.e.,
save the end position of the binlog at the end, and start from that
position next time it starts).
>
>
> -- let $TMP_FILE= $MYSQLTEST_VARDIR/tmp/rpl_row_img_sanity.tmp
>
> -- source include/master-slave.inc
> -- source include/have_binlog_format_row.inc
> -- connection master
>
> -- echo ###################################
> -- echo # NO KEYS
> -- echo ###################################
>
> -- source include/master-slave-reset.inc
> -- connection master
>
> CREATE TABLE t (c1 int, c2 int, c3 int);
> INSERT INTO t VALUES (1,2,3);
> INSERT INTO t(c1,c2) VALUES (10,20);
> INSERT DELAYED INTO t(c1,c2) VALUES (100,200);
(S8) Why do you test INSERT DELAYED? I don't think there is anything
special about DELAYED w.r.t. WL#5096? If there is, please add a comment
about it.
> UPDATE t SET c1=2 WHERE c1=1;
> DELETE FROM t WHERE c2=2;
> DROP TABLE t;
>
> -- sync_slave_with_master
> -- connection master
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/master-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "master: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "master: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
>
> print "master: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- connection slave
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/slave-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "slave: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- echo ###################################
> -- echo # KEYS
> -- echo ###################################
>
> -- source include/master-slave-reset.inc
> -- connection master
>
> CREATE TABLE t (c1 int, c2 int, c3 int, key(c1));
> INSERT INTO t VALUES (1,2,3);
> INSERT INTO t(c1,c2) VALUES (10,20);
> INSERT DELAYED INTO t(c1,c2) VALUES (100,200);
> UPDATE t SET c1=2 WHERE c1=1;
> DELETE FROM t WHERE c2=2;
> DROP TABLE t;
>
> -- sync_slave_with_master
> -- connection master
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/master-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "master: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "master: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
> print "master: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- connection slave
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/slave-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "slave: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- echo ###################################
> -- echo # UNIQUE KEY NULLABLE
> -- echo ###################################
>
> -- source include/master-slave-reset.inc
> -- connection master
>
> CREATE TABLE t (c1 int, c2 int, c3 int, unique key(c1));
> INSERT INTO t VALUES (1,2,3);
> INSERT INTO t(c1,c2) VALUES (10,20);
> INSERT DELAYED INTO t(c1,c2) VALUES (100,200);
> UPDATE t SET c1=2 WHERE c1=1;
> DELETE FROM t WHERE c2=2;
> DROP TABLE t;
>
> -- sync_slave_with_master
> -- connection master
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/master-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "master: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "master: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
> print "master: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- connection slave
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/slave-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "slave: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- echo ###################################
> -- echo # PRIMARY KEY
> -- echo ###################################
>
> -- source include/master-slave-reset.inc
> -- connection master
>
> CREATE TABLE t (c1 int, c2 int, c3 int, primary key(c1));
> INSERT INTO t VALUES (1,2,3);
> INSERT INTO t(c1,c2) VALUES (10,20);
> INSERT DELAYED INTO t(c1,c2) VALUES (100,200);
> UPDATE t SET c1=2 WHERE c1=1;
> DELETE FROM t WHERE c2=2;
> DROP TABLE t;
>
> -- sync_slave_with_master
> -- connection master
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/master-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "master: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "master: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
> print "master: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- connection slave
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/slave-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "slave: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- echo ###################################
> -- echo # UNIQUE KEY NOT NULL
> -- echo ###################################
>
> -- source include/master-slave-reset.inc
> -- connection master
>
> CREATE TABLE t (c1 int NOT NULL, c2 int, c3 int, unique key(c1));
> INSERT INTO t VALUES (1,2,3);
> INSERT INTO t(c1,c2) VALUES (10,20);
> INSERT DELAYED INTO t(c1,c2) VALUES (100,200);
> UPDATE t SET c1=2 WHERE c1=1;
> DELETE FROM t WHERE c2=2;
> DROP TABLE t;
>
> -- sync_slave_with_master
> -- connection master
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/master-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "master: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "master: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
> print "master: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "master: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- connection slave
>
> -- let $MYSQLD_DATADIR= `select @@datadir;`
> -- exec $MYSQL_BINLOG -v $MYSQLD_DATADIR/slave-bin.000001 > $TMP_FILE
> -- let BINLOG_CONTENTS= $TMP_FILE
>
> perl;
> $binlog= $ENV{'BINLOG_CONTENTS'};
> open(FILE, "$binlog") or die("Unable to open $log_error: $!\n");
> my $contents = do { local $/; <FILE> };
> print "slave: Unexpected columns in binlog (write row event)\n" if not ($contents
> =~ /### INSERT INTO test\.t\n### SET\n### \@1=1\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (partial write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=10\n### \@2=20\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (delayed write row event)\n" if not
> ($contents =~ /### INSERT INTO test\.t\n### SET\n### \@1=100\n### \@2=200\n###
> \@3=NULL\n# at/m);
> print "slave: Unexpected columns in binlog (update row event)\n" if not ($contents
> =~ /### UPDATE test\.t\n### WHERE\n### \@1=1\n### \@2=2\n### \@3=3\n### SET\n###
> \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> print "slave: Unexpected columns in binlog (delete row event)\n" if not ($contents
> =~ /### DELETE FROM test\.t\n### WHERE\n### \@1=2\n### \@2=2\n### \@3=3\n# at/m);
> close(FILE);
> EOF
>
> -- remove_file $TMP_FILE
>
--
Sven Sandberg, Software Engineer
MySQL AB, www.mysql.com
