# # Basic tests of row-level logging # # # First we test tables with only an index. # eval CREATE TABLE t1 (C1 CHAR(1), C2 CHAR(1), INDEX (C1)$extra_index_t1) ENGINE = $type ; SELECT * FROM t1; sync_slave_with_master; SELECT * FROM t1; # Testing insert connection master; INSERT INTO t1 VALUES ('A','B'), ('X','Y'), ('X','X'); INSERT INTO t1 VALUES ('A','C'), ('X','Z'), ('A','A'); SELECT * FROM t1 ORDER BY C1,C2; sync_slave_with_master; SELECT * FROM t1 ORDER BY C1,C2; # Testing delete # Observe that are several rows having the value for the index but only one # should be deleted. connection master; DELETE FROM t1 WHERE C1 = C2; SELECT * FROM t1 ORDER BY C1,C2; sync_slave_with_master; SELECT * FROM t1 ORDER BY C1,C2; # # Testing update. # Note that we have a condition on a column that is not part of the index for # the table. The right row should be updated nevertheless. # connection master; UPDATE t1 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C'; SELECT * FROM t1 ORDER BY C1,C2; sync_slave_with_master; SELECT * FROM t1 ORDER BY C1,C2; # Testing update with a condition that does not match any rows, but # which has a match for the index. connection master; UPDATE t1 SET c2 = 'Q' WHERE c1 = 'A' AND c2 = 'N'; SELECT * FROM t1 ORDER BY c1,c2; sync_slave_with_master; SELECT * FROM t1 ORDER BY c1,c2; # # Testing table with primary key # connection master; eval CREATE TABLE t2 (c1 INT, c12 char(1), c2 INT, PRIMARY KEY (c1)) ENGINE = $type ; INSERT INTO t2 VALUES (1,'A',2), (2,'A',4), (3,'A',9), (4,'A',15), (5,'A',25), (6,'A',35), (7,'A',50), (8,'A',64), (9,'A',81); SELECT * FROM t2 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2; sync_slave_with_master; SELECT * FROM t2 ORDER BY c1,c2; SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2; connection master; UPDATE t2 SET c2 = c1*c1 WHERE c2 != c1*c1; SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2; sync_slave_with_master; SELECT * FROM t2 WHERE c2 = c1 * c1 ORDER BY c1,c2; # Testing update with a condition that does not match any rows, but # which has a match for the primary key. connection master; UPDATE t2 SET c12 = 'Q' WHERE c1 = 1 AND c2 = 999; SELECT * FROM t2 ORDER BY c1,c2; sync_slave_with_master; SELECT * FROM t2 ORDER BY c1,c2; connection master; DELETE FROM t2 WHERE c1 % 4 = 0; SELECT * FROM t2 ORDER BY c1,c2; sync_slave_with_master; SELECT * FROM t2 ORDER BY c1,c2; connection master; UPDATE t2 SET c12='X'; # # Testing table with a multi-column primary key. # connection master; eval CREATE TABLE t3 (C1 CHAR(1), C2 CHAR(1), pk1 INT, C3 CHAR(1), pk2 INT, PRIMARY KEY (pk1,pk2)) ENGINE = $type ; INSERT INTO t3 VALUES ('A','B',1,'B',1), ('X','Y',2,'B',1), ('X','X',3,'B',1); INSERT INTO t3 VALUES ('A','C',1,'B',2), ('X','Z',2,'B',2), ('A','A',3,'B',2); SELECT * FROM t3 ORDER BY C1,C2; sync_slave_with_master; SELECT * FROM t3 ORDER BY C1,C2; connection master; DELETE FROM t3 WHERE C1 = C2; SELECT * FROM t3 ORDER BY C1,C2; sync_slave_with_master; SELECT * FROM t3 ORDER BY C1,C2; connection master; UPDATE t3 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C'; SELECT * FROM t3 ORDER BY C1,C2; sync_slave_with_master; SELECT * FROM t3 ORDER BY C1,C2; # # Testing table without index or primary key # connection master; eval CREATE TABLE t6 (C1 CHAR(1), C2 CHAR(1), C3 INT$extra_index_t6) ENGINE = $type; # Testing insert INSERT INTO t6 VALUES ('A','B',1), ('X','Y',2), ('X','X',3); INSERT INTO t6 VALUES ('A','C',4), ('X','Z',5), ('A','A',6); SELECT * FROM t6 ORDER BY C3; sync_slave_with_master; SELECT * FROM t6 ORDER BY C3; # Testing delete # Observe that are several rows having the value for the index but only one # should be deleted. connection master; DELETE FROM t6 WHERE C1 = C2; SELECT * FROM t6 ORDER BY C3; sync_slave_with_master; SELECT * FROM t6 ORDER BY C3; # # Testing update. # Note that we have a condition on a column that is not part of the index for # the table. The right row should be updated nevertheless. # connection master; UPDATE t6 SET C2 = 'I' WHERE C1 = 'A' AND C2 = 'C'; SELECT * FROM t6 ORDER BY C3; sync_slave_with_master; SELECT * FROM t6 ORDER BY C3; # now mixing the 3 tables without begin/commit connection master; eval CREATE TABLE t5 (C1 CHAR(1), C2 CHAR(1), C3 INT PRIMARY KEY) ENGINE = $type ; INSERT INTO t5 VALUES ('A','B',1), ('X','Y',2), ('X','X',3); INSERT INTO t5 VALUES ('A','C',4), ('X','Z',5), ('A','A',6); UPDATE t5,t2,t3 SET t5.C2='Q', t2.c12='R', t3.C3 ='S' WHERE t5.C1 = t2.c12 AND t5.C1 = t3.C1; SELECT * FROM t5,t2,t3 WHERE t5.C2='Q' AND t2.c12='R' AND t3.C3 ='S' ORDER BY t5.C3,t2.c1,t3.pk1,t3.pk2; sync_slave_with_master; SELECT * FROM t5,t2,t3 WHERE t5.C2='Q' AND t2.c12='R' AND t3.C3 ='S' ORDER BY t5.C3,t2.c1,t3.pk1,t3.pk2; # # Testing special column types # connection master; eval CREATE TABLE t4 (C1 CHAR(1) PRIMARY KEY, B1 BIT(1), B2 BIT(1) NOT NULL DEFAULT 0, C2 CHAR(1) NOT NULL DEFAULT 'A') ENGINE = $type ; INSERT INTO t4 SET C1 = 1; SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1; sync_slave_with_master; SELECT C1,HEX(B1),HEX(B2) FROM t4 ORDER BY C1; # # Testing conflicting operations # connection master; eval CREATE TABLE t7 (C1 INT PRIMARY KEY, C2 INT) ENGINE = $type ; sync_slave_with_master; --echo --- on slave: original values --- INSERT INTO t7 VALUES (1,3), (2,6), (3,9); SELECT * FROM t7 ORDER BY C1; # since bug#31552/31609 idempotency is not default any longer. In order # the preceeding test INSERT INTO t7 to pass the mode is switched # temprorarily set @@global.slave_exec_mode= 'IDEMPOTENT'; connection master; --echo --- on master: new values inserted --- INSERT INTO t7 VALUES (1,2), (2,4), (3,6); SELECT * FROM t7 ORDER BY C1; sync_slave_with_master; set @@global.slave_exec_mode= default; --echo --- on slave: old values should be overwritten by replicated values --- SELECT * FROM t7 ORDER BY C1; # # A more complicated test where the table has several keys and we are # causing a conflict for a key that is not "last". # connection master; --echo --- on master --- eval CREATE TABLE t8 (a INT PRIMARY KEY, b INT UNIQUE, c INT UNIQUE) ENGINE = $type ; # First we make sure that the constraints are correctly set. INSERT INTO t8 VALUES (99,99,99); --error ER_DUP_ENTRY INSERT INTO t8 VALUES (99,22,33); --error ER_DUP_ENTRY INSERT INTO t8 VALUES (11,99,33); --error ER_DUP_ENTRY INSERT INTO t8 VALUES (11,22,99); SELECT * FROM t8 ORDER BY a; sync_slave_with_master; --echo --- on slave --- SELECT * FROM t8 ORDER BY a; INSERT INTO t8 VALUES (1,2,3), (2,4,6), (3,6,9); SELECT * FROM t8 ORDER BY a; # since bug#31552/31609 idempotency is not default any longer. In order # the preceeding test INSERT INTO t8 to pass the mode is switched # temprorarily set @@global.slave_exec_mode= 'IDEMPOTENT'; connection master; --echo --- on master --- # We insert a row that will cause conflict on the primary key but not # on the other keys. INSERT INTO t8 VALUES (2,4,8); sync_slave_with_master; set @@global.slave_exec_mode= default; --echo --- on slave --- SELECT * FROM t8 ORDER BY a; # BUG#31552: Replication breaks when deleting rows from out-of-sync # table without PK --echo **** Test for BUG#31552 **** --echo **** On Master **** # Clean up t1 so that we can use it. connection master; DELETE FROM t1; sync_slave_with_master; # Just to get a clean binary log source include/reset_master_and_slave.inc; --echo **** On Master **** connection master; INSERT INTO t1 VALUES ('K','K'), ('L','L'), ('M','M'); --echo **** On Master **** sync_slave_with_master; # since bug#31552/31609 idempotency is not default any longer. In order # the following test DELETE FROM t1 to pass the mode is switched # temprorarily set @@global.slave_exec_mode= 'IDEMPOTENT'; DELETE FROM t1 WHERE C1 = 'L'; connection master; DELETE FROM t1; query_vertical SELECT COUNT(*) FROM t1 ORDER BY c1,c2; sync_slave_with_master; set @@global.slave_exec_mode= default; let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); disable_query_log; eval SELECT "$last_error" AS Last_SQL_Error; enable_query_log; query_vertical SELECT COUNT(*) FROM t1 ORDER BY c1,c2; # BUG#37076: TIMESTAMP/DATETIME values are not replicated correctly # between machines with mixed endiannes # (regression test) --echo **** Test for BUG#37076 **** --echo **** On Master **** connection master; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIMESTAMP, b DATETIME, c DATE); INSERT INTO t1 VALUES( '2005-11-14 01:01:01', '2005-11-14 01:01:02', '2005-11-14'); --echo **** On Slave **** sync_slave_with_master slave; SELECT * FROM t1; # # cleanup # connection master; DROP TABLE IF EXISTS t1,t2,t3,t4,t5,t6,t7,t8; sync_slave_with_master; # # BUG#37426: RBR breaks for CHAR() UTF8 fields > 85 chars # # We have 4 combinations to test with respect to the field length # (i.e., the number of bytes) of the CHAR fields: # # 1. Replicating from CHAR<256 to CHAR<256 # 2. Replicating from CHAR<256 to CHAR>255 # 3. Replicating from CHAR>255 to CHAR<256 # 4. Replicating from CHAR>255 to CHAR>255 # We also make a special case of using the max size of a field on the # master, i.e. CHAR(255) in UTF-8, giving another three cases. # # 5. Replicating UTF-8 CHAR(255) to CHAR(<256) # 6. Replicating UTF-8 CHAR(255) to CHAR(>255) # 7. Replicating UTF-8 CHAR(255) to CHAR(255) UTF-8 connection master; CREATE TABLE t1 (i INT NOT NULL, c CHAR(16) CHARACTER SET utf8 NOT NULL, j INT NOT NULL); CREATE TABLE t2 (i INT NOT NULL, c CHAR(16) CHARACTER SET utf8 NOT NULL, j INT NOT NULL); sync_slave_with_master; ALTER TABLE t2 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL; connection master; CREATE TABLE t3 (i INT NOT NULL, c CHAR(128) CHARACTER SET utf8 NOT NULL, j INT NOT NULL); sync_slave_with_master; ALTER TABLE t3 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL; connection master; CREATE TABLE t4 (i INT NOT NULL, c CHAR(128) CHARACTER SET utf8 NOT NULL, j INT NOT NULL); CREATE TABLE t5 (i INT NOT NULL, c CHAR(255) CHARACTER SET utf8 NOT NULL, j INT NOT NULL); sync_slave_with_master; ALTER TABLE t5 MODIFY c CHAR(16) CHARACTER SET utf8 NOT NULL; connection master; CREATE TABLE t6 (i INT NOT NULL, c CHAR(255) CHARACTER SET utf8 NOT NULL, j INT NOT NULL); sync_slave_with_master; ALTER TABLE t6 MODIFY c CHAR(128) CHARACTER SET utf8 NOT NULL; connection master; CREATE TABLE t7 (i INT NOT NULL, c CHAR(255) CHARACTER SET utf8 NOT NULL, j INT NOT NULL); --echo [expecting slave to replicate correctly] connection master; INSERT INTO t1 VALUES (1, "", 1); INSERT INTO t1 VALUES (2, repeat(_utf8'a', 16), 2); sync_slave_with_master; let $diff_table_1=master:test.t1; let $diff_table_2=slave:test.t1; source include/diff_tables.inc; --echo [expecting slave to replicate correctly] connection master; INSERT INTO t2 VALUES (1, "", 1); INSERT INTO t2 VALUES (2, repeat(_utf8'a', 16), 2); sync_slave_with_master; let $diff_table_1=master:test.t2; let $diff_table_2=slave:test.t2; source include/diff_tables.inc; --echo [expecting slave to stop] connection master; INSERT INTO t3 VALUES (1, "", 1); INSERT INTO t3 VALUES (2, repeat(_utf8'a', 128), 2); connection slave; source include/wait_for_slave_sql_to_stop.inc; let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); disable_query_log; eval SELECT "$last_error" AS Last_SQL_Error; enable_query_log; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=8; START SLAVE; source include/wait_for_slave_to_start.inc; --echo [expecting slave to replicate correctly] connection master; INSERT INTO t4 VALUES (1, "", 1); INSERT INTO t4 VALUES (2, repeat(_utf8'a', 128), 2); sync_slave_with_master; let $diff_table_1=master:test.t4; let $diff_table_2=slave:test.t4; source include/diff_tables.inc; --echo [expecting slave to stop] connection master; INSERT INTO t5 VALUES (1, "", 1); INSERT INTO t5 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; source include/wait_for_slave_sql_to_stop.inc; let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); disable_query_log; eval SELECT "$last_error" AS Last_SQL_Error; enable_query_log; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=8; START SLAVE; source include/wait_for_slave_to_start.inc; --echo [expecting slave to stop] connection master; INSERT INTO t6 VALUES (1, "", 1); INSERT INTO t6 VALUES (2, repeat(_utf8'a', 255), 2); connection slave; source include/wait_for_slave_sql_to_stop.inc; let $last_error = query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); disable_query_log; eval SELECT "$last_error" AS Last_SQL_Error; enable_query_log; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=8; START SLAVE; source include/wait_for_slave_to_start.inc; --echo [expecting slave to replicate correctly] connection master; INSERT INTO t7 VALUES (1, "", 1); INSERT INTO t7 VALUES (2, repeat(_utf8'a', 255), 2); sync_slave_with_master; let $diff_table_1=master:test.t7; let $diff_table_2=slave:test.t7; source include/diff_tables.inc; connection master; drop table t1, t2, t3, t4, t5, t6, t7; sync_slave_with_master;