1
include/master-slave.inc
4
CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
6
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
7
CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
8
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
9
CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL,
10
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
11
CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL,
13
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
15
CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
17
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
18
CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
19
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
20
CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00',
22
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
23
CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00',
24
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
25
************* EXECUTION WITH INSERTS *************
26
INSERT INTO t1(a,b,c) VALUES (1, null, 1);
27
INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
28
INSERT INTO t1(a,b) VALUES (3, null);
29
INSERT INTO t1(a,c) VALUES (4, 4);
30
INSERT INTO t1(a) VALUES (5);
31
INSERT INTO t2(a,b) VALUES (1, null);
32
INSERT INTO t2(a,b) VALUES (2,'1111-11-11');
33
INSERT INTO t2(a) VALUES (3);
34
INSERT INTO t3(a,b) VALUES (1, null);
35
INSERT INTO t3(a,b) VALUES (2,'1111-11-11');
36
INSERT INTO t3(a) VALUES (3);
37
INSERT INTO t4(a,b,c) VALUES (1, null, 1);
38
INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2);
39
INSERT INTO t4(a,b) VALUES (3, null);
40
INSERT INTO t4(a,c) VALUES (4, 4);
41
INSERT INTO t4(a) VALUES (5);
42
************* SHOWING THE RESULT SETS WITH INSERTS *************
43
TABLES t1 and t2 must be equal otherwise an error will be thrown.
44
include/diff_tables.inc [master:t1, slave:t1]
45
include/diff_tables.inc [master:t2, slave:t2]
46
TABLES t2 and t3 must be different.
47
SELECT * FROM t3 ORDER BY a;
52
SELECT * FROM t3 ORDER BY a;
57
SELECT * FROM t4 ORDER BY a;
64
SELECT * FROM t4 ORDER BY a;
71
************* EXECUTION WITH UPDATES and REPLACES *************
73
INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1);
74
REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
75
UPDATE t1 set b= NULL, c= 300 where a= 1;
76
REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300);
77
************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
78
TABLES t1 and t2 must be equal otherwise an error will be thrown.
79
include/diff_tables.inc [master:t1, slave:t1]
80
************* CLEANING *************
86
CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL,
87
PRIMARY KEY (`a`)) ENGINE= MyISAM;
89
CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL,
90
PRIMARY KEY (`a`)) ENGINE= MyISAM;
91
************* EXECUTION WITH INSERTS *************
92
INSERT INTO t1(a,b,c) VALUES (1, null, b'01');
93
INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01');
94
INSERT INTO t1(a,b) VALUES (3, null);
95
INSERT INTO t1(a,c) VALUES (4, b'01');
96
INSERT INTO t1(a) VALUES (5);
97
************* SHOWING THE RESULT SETS WITH INSERTS *************
98
TABLES t1 and t2 must be different.
99
SELECT a,b+0,c+0 FROM t1 ORDER BY a;
106
SELECT a,b+0,c+0 FROM t1 ORDER BY a;
113
************* EXECUTION WITH UPDATES and REPLACES *************
115
INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01');
116
REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01');
117
UPDATE t1 set b= NULL, c= b'00' where a= 1;
118
REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00');
119
************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
120
TABLES t1 and t2 must be equal otherwise an error will be thrown.
121
include/diff_tables.inc [master:t1, slave:t1]
123
################################################################################
124
# NULL ---> NOT NULL (STRICT MODE)
125
# UNCOMMENT THIS AFTER FIXING BUG#43992
126
################################################################################
127
################################################################################
128
# NULL ---> NOT NULL (NON-STRICT MODE)
129
################################################################################
131
CREATE TABLE t1(`a` INT NOT NULL, `b` INT,
132
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
133
CREATE TABLE t2(`a` INT NOT NULL, `b` INT,
134
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
135
CREATE TABLE t3(`a` INT NOT NULL, `b` INT,
136
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
138
CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL,
140
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
141
CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
143
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
144
CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL,
146
PRIMARY KEY(`a`)) ENGINE=MyISAM DEFAULT CHARSET=LATIN1;
147
************* EXECUTION WITH INSERTS *************
148
INSERT INTO t1(a) VALUES (1);
149
INSERT INTO t1(a, b) VALUES (2, NULL);
150
INSERT INTO t1(a, b) VALUES (3, 1);
151
INSERT INTO t2(a) VALUES (1);
152
INSERT INTO t2(a, b) VALUES (2, NULL);
153
INSERT INTO t2(a, b) VALUES (3, 1);
154
INSERT INTO t3(a) VALUES (1);
155
INSERT INTO t3(a, b) VALUES (2, NULL);
156
INSERT INTO t3(a, b) VALUES (3, 1);
157
INSERT INTO t3(a, b) VALUES (4, 1);
158
REPLACE INTO t3(a, b) VALUES (5, null);
159
REPLACE INTO t3(a, b) VALUES (3, null);
160
UPDATE t3 SET b = NULL where a = 4;
161
************* SHOWING THE RESULT SETS *************
162
SELECT * FROM t1 ORDER BY a;
167
SELECT * FROM t1 ORDER BY a;
172
SELECT * FROM t2 ORDER BY a;
177
SELECT * FROM t2 ORDER BY a;
182
SELECT * FROM t3 ORDER BY a;
189
SELECT * FROM t3 ORDER BY a;