Edit File: rpl_row_img_idx_min.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: '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 ### engines: MyISAM, MyISAM, MyISAM include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ### Master with no keys slaves with no keys as well CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with keys CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields (first slave with UK NOT NULL, second with PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, first slave with key on different field and second slave with key on yet another different field. CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields (first slave has UK NOT NULL) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, first slave with K on different field second slave with key on yet another different field CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL, first slave with UK NOT NULL on different field CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL and slaves with PK on different fields CREATE TABLE t (c1 int NOT NULL, c2 blob, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and no keys on the slaves CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and first slave with KEY only CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave (which has unique NOT NULL key instead of PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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 include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ### Master with no keys slaves with no keys as well CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with keys CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields (first slave with UK NOT NULL, second with PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, first slave with key on different field and second slave with key on yet another different field. CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields (first slave has UK NOT NULL) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, first slave with K on different field second slave with key on yet another different field CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL, first slave with UK NOT NULL on different field CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL and slaves with PK on different fields CREATE TABLE t (c1 int NOT NULL, c2 blob, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and no keys on the slaves CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and first slave with KEY only CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave (which has unique NOT NULL key instead of PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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 include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ### Master with no keys slaves with no keys as well CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with keys CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields (first slave with UK NOT NULL, second with PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, first slave with key on different field and second slave with key on yet another different field. CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields (first slave has UK NOT NULL) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, first slave with K on different field second slave with key on yet another different field CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL, first slave with UK NOT NULL on different field CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL and slaves with PK on different fields CREATE TABLE t (c1 int NOT NULL, c2 blob, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and no keys on the slaves CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and first slave with KEY only CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave (which has unique NOT NULL key instead of PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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 include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ### Master with no keys slaves with no keys as well CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with keys CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields (first slave with UK NOT NULL, second with PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, first slave with key on different field and second slave with key on yet another different field. CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields (first slave has UK NOT NULL) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, first slave with K on different field second slave with key on yet another different field CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL, first slave with UK NOT NULL on different field CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL and slaves with PK on different fields CREATE TABLE t (c1 int NOT NULL, c2 blob, c3 int, unique key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and no keys on the slaves CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and first slave with KEY only CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave (which has unique NOT NULL key instead of PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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 include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ### Master with no keys slaves with no keys as well CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with keys CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields (first slave with UK NOT NULL, second with PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, first slave with key on different field and second slave with key on yet another different field. CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields (first slave has UK NOT NULL) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, first slave with K on different field second slave with key on yet another different field CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL, first slave with UK NOT NULL on different field CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL and slaves with PK on different fields CREATE TABLE t (c1 int NOT NULL, c2 blob, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and no keys on the slaves CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and first slave with KEY only CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave (which has unique NOT NULL key instead of PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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 include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ### Master with no keys slaves with no keys as well CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with keys CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields (first slave with UK NOT NULL, second with PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, first slave with key on different field and second slave with key on yet another different field. CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields (first slave has UK NOT NULL) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, first slave with K on different field second slave with key on yet another different field CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL, first slave with UK NOT NULL on different field CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL and slaves with PK on different fields CREATE TABLE t (c1 int NOT NULL, c2 blob, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and no keys on the slaves CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and first slave with KEY only CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave (which has unique NOT NULL key instead of PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= MyISAM;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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 include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ### Master with no keys slaves with no keys as well CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with keys CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields (first slave with UK NOT NULL, second with PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, first slave with key on different field and second slave with key on yet another different field. CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields (first slave has UK NOT NULL) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, first slave with K on different field second slave with key on yet another different field CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL, first slave with UK NOT NULL on different field CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL and slaves with PK on different fields CREATE TABLE t (c1 int NOT NULL, c2 blob, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and no keys on the slaves CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and first slave with KEY only CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= MyISAM;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave (which has unique NOT NULL key instead of PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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 include/rpl_sync.inc SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; SET SQL_LOG_BIN=0; ### Master with no keys slaves with no keys as well CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with keys CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields (first slave with UK NOT NULL, second with PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with no key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, first slave with key on different field and second slave with key on yet another different field. CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields (first slave has UK NOT NULL) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with Key, slaves with PKs on different fields CREATE TABLE t (c1 int, c2 blob, c3 int, key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, slaves with no keys CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NULLABLE, first slave with K on different field second slave with key on yet another different field CREATE TABLE t (c1 int, c2 blob, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL, first slave with UK NOT NULL on different field CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with UK NOT NULL and slaves with PK on different fields CREATE TABLE t (c1 int NOT NULL, c2 blob, c3 int, unique key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and no keys on the slaves CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Master with PK and first slave with KEY only CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c3)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, key(c2(512))) engine= InnoDB;; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; SET SQL_LOG_BIN=1; INSERT INTO t VALUES (1, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave (which has unique NOT NULL key instead of PK) CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, unique key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob NOT NULL, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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; ### Different PKs on master and first slave CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c1)) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, primary key(c2(512))) engine= InnoDB;; CREATE TABLE t (c1 int, c2 blob, c3 int, 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, "a", 10); INSERT INTO t VALUES (2, "b", 20); INSERT INTO t VALUES (3, "c", 30); include/rpl_sync.inc UPDATE t SET c1=10 WHERE c2="a"; UPDATE t SET c1=20 WHERE c1=2; UPDATE t SET c1=30 WHERE c3=30; UPDATE t SET c3=40 WHERE c1=30; include/rpl_sync.inc include/diff_tables.inc [server_1:test.t, server_2:test.t, server_3:test.t] DELETE FROM t WHERE c2="a"; DELETE FROM t WHERE c1=20; 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 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