Edit File: rpl_row_img_eng_full.result
include/rpl_init.inc [topology=1->2->3] Warnings: Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. Warnings: Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. CON: 'server_1', IMG: 'FULL', RESTART SLAVE: 'N' SET SESSION binlog_row_image= 'FULL'; SET GLOBAL binlog_row_image= 'FULL'; FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image FULL CON: 'server_2', IMG: 'FULL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'FULL'; SET GLOBAL binlog_row_image= 'FULL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image FULL CON: 'server_3', IMG: 'FULL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'FULL'; SET GLOBAL binlog_row_image= 'FULL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image FULL ### engines: MyISAM, MyISAM, MyISAM SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes in master, but NULLABLE on first slave CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Primary key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: One key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: No keys CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc ### engines: MyISAM, MyISAM, InnoDB SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes in master, but NULLABLE on first slave CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Primary key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: One key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: No keys CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc ### engines: MyISAM, InnoDB, MyISAM SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes in master, but NULLABLE on first slave CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Primary key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: One key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: No keys CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc ### engines: MyISAM, InnoDB, InnoDB SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes in master, but NULLABLE on first slave CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Primary key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: One key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: No keys CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc ### engines: InnoDB, MyISAM, MyISAM SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes in master, but NULLABLE on first slave CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Primary key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: One key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: No keys CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc ### engines: InnoDB, MyISAM, InnoDB SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes in master, but NULLABLE on first slave CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Primary key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: One key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: No keys CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc ### engines: InnoDB, InnoDB, MyISAM SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes in master, but NULLABLE on first slave CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Primary key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: One key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: No keys CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc ### engines: InnoDB, InnoDB, InnoDB SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes in master, but NULLABLE on first slave CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite NOT NULL Unique key with holes CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int NOT NULL, c2 char(1), c3 char(1) NOT NULL, c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key with holes CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c3)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Primary Key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Primary key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), primary key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Unique key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), unique key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ****** TEST: One Composite key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1,c2)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: One key CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1), key(c1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ******* TEST: No keys CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 char(1), c3 char(1), c4 char(1)) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; 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' ); include/rpl_sync.inc 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'; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c1 = 7; DELETE FROM t WHERE c1 = 8; DELETE FROM t; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DROP TABLE t; include/rpl_sync.inc ### Testing with MyISAM storage engine CON: 'server_1', IMG: 'MINIMAL', RESTART SLAVE: 'N' SET SESSION binlog_row_image= 'MINIMAL'; SET GLOBAL binlog_row_image= 'MINIMAL'; FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image MINIMAL CON: 'server_2', IMG: 'MINIMAL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'MINIMAL'; SET GLOBAL binlog_row_image= 'MINIMAL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image MINIMAL CON: 'server_3', IMG: 'MINIMAL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'MINIMAL'; SET GLOBAL binlog_row_image= 'MINIMAL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image MINIMAL #### case #1: AI: no values logged CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; INSERT INTO t1 VALUES (); SELECT * FROM t1; c1 100 include/rpl_sync.inc SELECT * FROM t1; c1 100 SELECT * FROM t1; c1 100 DROP TABLE t1; include/rpl_sync.inc #### case #2: AI: not empty but slave does not have usable data for its columns (INSERT) CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100, c2 int, primary key(c2)) Engine=ENGINE; SET SQL_LOG_BIN=1; INSERT INTO t1(c2) VALUES (1); SELECT * FROM t1; c1 c2 100 1 include/rpl_sync.inc SELECT * FROM t1; c1 100 SELECT * FROM t1; c1 100 DROP TABLE t1; include/rpl_sync.inc #### case #3: BI: usable columns on the slave, AI: no usable columns on the slave CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100, c2 int) Engine=ENGINE; SET SQL_LOG_BIN=1; INSERT INTO t1 VALUES (1,1); SELECT * FROM t1; c1 c2 1 1 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 UPDATE t1 SET c2=2 WHERE c1=1 AND c2=1; SELECT * FROM t1; c1 c2 1 2 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 DROP TABLE t1; include/rpl_sync.inc #### case #4: AI, BI: no usable columns on the slave (NOOP UPDATE). #### CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100, c2 int, c3 int, primary key(c2)) Engine=ENGINE; SET SQL_LOG_BIN=1; INSERT INTO t1 VALUES (1,1,1); SELECT * FROM t1; c1 c2 c3 1 1 1 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 UPDATE t1 SET c3=300 WHERE c2=1; SELECT * FROM t1; c1 c2 c3 1 1 300 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 DROP TABLE t1; include/rpl_sync.inc #### case #5: BI: no usable columns on the slave, AI: usable columns on the slave (slave must stop). #### CREATE TABLE t1 (c1 INT DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 INT DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 INT DEFAULT 100, c2 INT PRIMARY KEY) Engine=ENGINE; SET SQL_LOG_BIN=1; INSERT INTO t1 VALUES (1,1); SELECT * FROM t1; c1 c2 1 1 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 UPDATE t1 SET c1=300 WHERE c2=1; SELECT * FROM t1; c1 c2 300 1 SET SQL_LOG_BIN=0; call mtr.add_suppression("Slave: Can\'t find record in \'t1\' Error_code: 1032"); call mtr.add_suppression("Slave SQL: .*Could not execute Update_rows event on table test.t1; Can.t find record in .t1.* Error_code: 1032"); call mtr.add_suppression("The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state"); SET SQL_LOG_BIN=1; include/wait_for_slave_sql_error_and_skip.inc [errno=1032] DROP TABLE t1; include/rpl_sync.inc CON: 'server_1', IMG: 'FULL', RESTART SLAVE: 'N' SET SESSION binlog_row_image= 'FULL'; SET GLOBAL binlog_row_image= 'FULL'; FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image FULL CON: 'server_2', IMG: 'FULL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'FULL'; SET GLOBAL binlog_row_image= 'FULL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image FULL CON: 'server_3', IMG: 'FULL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'FULL'; SET GLOBAL binlog_row_image= 'FULL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image FULL ### Testing with InnoDB storage engine CON: 'server_1', IMG: 'MINIMAL', RESTART SLAVE: 'N' SET SESSION binlog_row_image= 'MINIMAL'; SET GLOBAL binlog_row_image= 'MINIMAL'; FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image MINIMAL CON: 'server_2', IMG: 'MINIMAL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'MINIMAL'; SET GLOBAL binlog_row_image= 'MINIMAL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image MINIMAL CON: 'server_3', IMG: 'MINIMAL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'MINIMAL'; SET GLOBAL binlog_row_image= 'MINIMAL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image MINIMAL #### case #1: AI: no values logged CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; INSERT INTO t1 VALUES (); SELECT * FROM t1; c1 100 include/rpl_sync.inc SELECT * FROM t1; c1 100 SELECT * FROM t1; c1 100 DROP TABLE t1; include/rpl_sync.inc #### case #2: AI: not empty but slave does not have usable data for its columns (INSERT) CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100, c2 int, primary key(c2)) Engine=ENGINE; SET SQL_LOG_BIN=1; INSERT INTO t1(c2) VALUES (1); SELECT * FROM t1; c1 c2 100 1 include/rpl_sync.inc SELECT * FROM t1; c1 100 SELECT * FROM t1; c1 100 DROP TABLE t1; include/rpl_sync.inc #### case #3: BI: usable columns on the slave, AI: no usable columns on the slave CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100, c2 int) Engine=ENGINE; SET SQL_LOG_BIN=1; INSERT INTO t1 VALUES (1,1); SELECT * FROM t1; c1 c2 1 1 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 UPDATE t1 SET c2=2 WHERE c1=1 AND c2=1; SELECT * FROM t1; c1 c2 1 2 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 DROP TABLE t1; include/rpl_sync.inc #### case #4: AI, BI: no usable columns on the slave (NOOP UPDATE). #### CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 int DEFAULT 100, c2 int, c3 int, primary key(c2)) Engine=ENGINE; SET SQL_LOG_BIN=1; INSERT INTO t1 VALUES (1,1,1); SELECT * FROM t1; c1 c2 c3 1 1 1 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 UPDATE t1 SET c3=300 WHERE c2=1; SELECT * FROM t1; c1 c2 c3 1 1 300 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 DROP TABLE t1; include/rpl_sync.inc #### case #5: BI: no usable columns on the slave, AI: usable columns on the slave (slave must stop). #### CREATE TABLE t1 (c1 INT DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 INT DEFAULT 100) Engine=ENGINE; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=0; CREATE TABLE t1 (c1 INT DEFAULT 100, c2 INT PRIMARY KEY) Engine=ENGINE; SET SQL_LOG_BIN=1; INSERT INTO t1 VALUES (1,1); SELECT * FROM t1; c1 c2 1 1 include/rpl_sync.inc SELECT * FROM t1; c1 1 SELECT * FROM t1; c1 1 UPDATE t1 SET c1=300 WHERE c2=1; SELECT * FROM t1; c1 c2 300 1 SET SQL_LOG_BIN=0; call mtr.add_suppression("Slave: Can\'t find record in \'t1\' Error_code: 1032"); call mtr.add_suppression("Slave SQL: .*Could not execute Update_rows event on table test.t1; Can.t find record in .t1.* Error_code: 1032"); call mtr.add_suppression("The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state"); SET SQL_LOG_BIN=1; include/wait_for_slave_sql_error_and_skip.inc [errno=1032] DROP TABLE t1; include/rpl_sync.inc CON: 'server_1', IMG: 'FULL', RESTART SLAVE: 'N' SET SESSION binlog_row_image= 'FULL'; SET GLOBAL binlog_row_image= 'FULL'; FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image FULL CON: 'server_2', IMG: 'FULL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'FULL'; SET GLOBAL binlog_row_image= 'FULL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image FULL CON: 'server_3', IMG: 'FULL', RESTART SLAVE: 'Y' SET SESSION binlog_row_image= 'FULL'; SET GLOBAL binlog_row_image= 'FULL'; include/stop_slave.inc include/start_slave.inc FLUSH TABLES; SHOW VARIABLES LIKE 'binlog_row_image'; Variable_name Value binlog_row_image FULL include/rpl_end.inc