1include/master-slave.inc 2[connection master] 3connection master; 4SET SQL_LOG_BIN= 0; 5CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, 6`c` INT DEFAULT NULL, 7PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 8CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, 9PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 10CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL, 11PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 12CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL, 13`c` INT DEFAULT NULL, 14PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 15SET SQL_LOG_BIN= 1; 16connection slave; 17CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL, 18`c` INT DEFAULT NULL, 19PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 20CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL, 21PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 22CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00', 23`c` INT DEFAULT 500, 24PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 25CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00', 26PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 27************* EXECUTION WITH INSERTS ************* 28connection master; 29INSERT INTO t1(a,b,c) VALUES (1, null, 1); 30INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); 31INSERT INTO t1(a,b) VALUES (3, null); 32INSERT INTO t1(a,c) VALUES (4, 4); 33INSERT INTO t1(a) VALUES (5); 34INSERT INTO t2(a,b) VALUES (1, null); 35INSERT INTO t2(a,b) VALUES (2,'1111-11-11'); 36INSERT INTO t2(a) VALUES (3); 37INSERT INTO t3(a,b) VALUES (1, null); 38INSERT INTO t3(a,b) VALUES (2,'1111-11-11'); 39INSERT INTO t3(a) VALUES (3); 40INSERT INTO t4(a,b,c) VALUES (1, null, 1); 41INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2); 42INSERT INTO t4(a,b) VALUES (3, null); 43INSERT INTO t4(a,c) VALUES (4, 4); 44INSERT INTO t4(a) VALUES (5); 45************* SHOWING THE RESULT SETS WITH INSERTS ************* 46connection slave; 47TABLES t1 and t2 must be equal otherwise an error will be thrown. 48include/diff_tables.inc [master:t1, slave:t1] 49include/diff_tables.inc [master:t2, slave:t2] 50TABLES t2 and t3 must be different. 51connection master; 52SELECT * FROM t3 ORDER BY a; 53a b 541 NULL 552 1111-11-11 563 NULL 57connection slave; 58SELECT * FROM t3 ORDER BY a; 59a b c 601 NULL 500 612 1111-11-11 500 623 NULL 500 63connection master; 64SELECT * FROM t4 ORDER BY a; 65a b c 661 NULL 1 672 1111-11-11 2 683 NULL NULL 694 NULL 4 705 NULL NULL 71connection slave; 72SELECT * FROM t4 ORDER BY a; 73a b 741 NULL 752 1111-11-11 763 NULL 774 NULL 785 NULL 79************* EXECUTION WITH UPDATES and REPLACES ************* 80connection master; 81DELETE FROM t1; 82INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1); 83REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2); 84UPDATE t1 set b= NULL, c= 300 where a= 1; 85REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300); 86************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* 87connection slave; 88TABLES t1 and t2 must be equal otherwise an error will be thrown. 89include/diff_tables.inc [master:t1, slave:t1] 90************* CLEANING ************* 91connection master; 92DROP TABLE t1; 93DROP TABLE t2; 94DROP TABLE t3; 95DROP TABLE t4; 96connection slave; 97connection master; 98SET SQL_LOG_BIN= 0; 99CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL, 100PRIMARY KEY (`a`)) ENGINE= Innodb; 101SET SQL_LOG_BIN= 1; 102connection slave; 103CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL, 104PRIMARY KEY (`a`)) ENGINE= Innodb; 105************* EXECUTION WITH INSERTS ************* 106connection master; 107INSERT INTO t1(a,b,c) VALUES (1, null, b'01'); 108INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01'); 109INSERT INTO t1(a,b) VALUES (3, null); 110INSERT INTO t1(a,c) VALUES (4, b'01'); 111INSERT INTO t1(a) VALUES (5); 112************* SHOWING THE RESULT SETS WITH INSERTS ************* 113TABLES t1 and t2 must be different. 114connection slave; 115connection master; 116SELECT a,b+0,c+0 FROM t1 ORDER BY a; 117a b+0 c+0 1181 NULL 1 1192 0 1 1203 NULL NULL 1214 NULL 1 1225 NULL NULL 123connection slave; 124SELECT a,b+0,c+0 FROM t1 ORDER BY a; 125a b+0 c+0 1261 NULL 1 1272 0 1 1283 NULL NULL 1294 NULL 1 1305 NULL NULL 131************* EXECUTION WITH UPDATES and REPLACES ************* 132connection master; 133DELETE FROM t1; 134INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01'); 135REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01'); 136UPDATE t1 set b= NULL, c= b'00' where a= 1; 137REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00'); 138************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES ************* 139TABLES t1 and t2 must be equal otherwise an error will be thrown. 140connection slave; 141include/diff_tables.inc [master:t1, slave:t1] 142connection master; 143DROP TABLE t1; 144connection slave; 145################################################################################ 146# NULL ---> NOT NULL (STRICT MODE) 147# UNCOMMENT THIS AFTER FIXING BUG#43992 148################################################################################ 149################################################################################ 150# NULL ---> NOT NULL (NON-STRICT MODE) 151################################################################################ 152connection master; 153SET SQL_LOG_BIN= 0; 154CREATE TABLE t1(`a` INT NOT NULL, `b` INT, 155PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 156CREATE TABLE t2(`a` INT NOT NULL, `b` INT, 157PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 158CREATE TABLE t3(`a` INT NOT NULL, `b` INT, 159PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 160SET SQL_LOG_BIN= 1; 161connection slave; 162CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL, 163`c` INT NOT NULL, 164PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 165CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL, 166`c` INT, 167PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 168CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL, 169`c` INT DEFAULT 500, 170PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1; 171************* EXECUTION WITH INSERTS ************* 172connection master; 173INSERT INTO t1(a) VALUES (1); 174INSERT INTO t1(a, b) VALUES (2, NULL); 175INSERT INTO t1(a, b) VALUES (3, 1); 176INSERT INTO t2(a) VALUES (1); 177INSERT INTO t2(a, b) VALUES (2, NULL); 178INSERT INTO t2(a, b) VALUES (3, 1); 179INSERT INTO t3(a) VALUES (1); 180INSERT INTO t3(a, b) VALUES (2, NULL); 181INSERT INTO t3(a, b) VALUES (3, 1); 182INSERT INTO t3(a, b) VALUES (4, 1); 183REPLACE INTO t3(a, b) VALUES (5, null); 184REPLACE INTO t3(a, b) VALUES (3, null); 185UPDATE t3 SET b = NULL where a = 4; 186************* SHOWING THE RESULT SETS ************* 187connection master; 188connection slave; 189connection master; 190SELECT * FROM t1 ORDER BY a; 191a b 1921 NULL 1932 NULL 1943 1 195connection slave; 196SELECT * FROM t1 ORDER BY a; 197a b c 1981 0 0 1992 0 0 2003 1 0 201connection master; 202SELECT * FROM t2 ORDER BY a; 203a b 2041 NULL 2052 NULL 2063 1 207connection slave; 208SELECT * FROM t2 ORDER BY a; 209a b c 2101 0 NULL 2112 0 NULL 2123 1 NULL 213connection master; 214SELECT * FROM t3 ORDER BY a; 215a b 2161 NULL 2172 NULL 2183 NULL 2194 NULL 2205 NULL 221connection slave; 222SELECT * FROM t3 ORDER BY a; 223a b c 2241 0 500 2252 0 500 2263 0 500 2274 0 500 2285 0 500 229connection master; 230DROP TABLE t1; 231DROP TABLE t2; 232DROP TABLE t3; 233connection slave; 234include/rpl_end.inc 235