Debian Codegraph @

mysql-test/suite/rpl/include/rpl_not_null.test

raw
1 #################################################################################
2 # This test checks if the replication between "null" fields to either "null"
3 # fields or "not null" fields works properly. In the first case, the execution
4 # should work fine. In the second case, it may fail according to the sql_mode
5 # being used.
6 #
7 # The test is devided in three main parts:
8 #
9 # 1 - NULL --> NULL (no failures)
10 # 2 - NULL --> NOT NULL ( sql-mode = STRICT and failures)
11 # 3 - NULL --> NOT NULL ( sql-mode != STRICT and no failures)
12 #
13 #################################################################################
14 connection master;
15
16 SET SQL_LOG_BIN= 0;
17 eval CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
18 `c` INT DEFAULT NULL,
19 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
20
21 eval CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
22 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
23
24 eval CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL,
25 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
26
27 eval CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL,
28 `c` INT DEFAULT NULL,
29 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
30 SET SQL_LOG_BIN= 1;
31
32 connection slave;
33
34 eval CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
35 `c` INT DEFAULT NULL,
36 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
37
38 eval CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
39 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
40
41 eval CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00',
42 `c` INT DEFAULT 500,
43 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
44
45 eval CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00',
46 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
47
48 --echo ************* EXECUTION WITH INSERTS *************
49 connection master;
50 INSERT INTO t1(a,b,c) VALUES (1, null, 1);
51 INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
52 INSERT INTO t1(a,b) VALUES (3, null);
53 INSERT INTO t1(a,c) VALUES (4, 4);
54 INSERT INTO t1(a) VALUES (5);
55
56 INSERT INTO t2(a,b) VALUES (1, null);
57 INSERT INTO t2(a,b) VALUES (2,'1111-11-11');
58 INSERT INTO t2(a) VALUES (3);
59
60 INSERT INTO t3(a,b) VALUES (1, null);
61 INSERT INTO t3(a,b) VALUES (2,'1111-11-11');
62 INSERT INTO t3(a) VALUES (3);
63
64 INSERT INTO t4(a,b,c) VALUES (1, null, 1);
65 INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2);
66 INSERT INTO t4(a,b) VALUES (3, null);
67 INSERT INTO t4(a,c) VALUES (4, 4);
68 INSERT INTO t4(a) VALUES (5);
69
70 --echo ************* SHOWING THE RESULT SETS WITH INSERTS *************
71 sync_slave_with_master;
72
73 --echo TABLES t1 and t2 must be equal otherwise an error will be thrown.
74 let $diff_tables= master:t1, slave:t1;
75 source include/diff_tables.inc;
76
77 let $diff_tables= master:t2, slave:t2;
78 source include/diff_tables.inc;
79
80 --echo TABLES t2 and t3 must be different.
81 connection master;
82 SELECT * FROM t3 ORDER BY a;
83 connection slave;
84 SELECT * FROM t3 ORDER BY a;
85 connection master;
86 SELECT * FROM t4 ORDER BY a;
87 connection slave;
88 SELECT * FROM t4 ORDER BY a;
89
90 --echo ************* EXECUTION WITH UPDATES and REPLACES *************
91 connection master;
92 DELETE FROM t1;
93 INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1);
94 REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
95 UPDATE t1 set b= NULL, c= 300 where a= 1;
96 REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300);
97
98 --echo ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
99 sync_slave_with_master;
100
101 --echo TABLES t1 and t2 must be equal otherwise an error will be thrown.
102 let $diff_tables= master:t1, slave:t1;
103 source include/diff_tables.inc;
104
105 --echo ************* CLEANING *************
106 connection master;
107
108 DROP TABLE t1;
109 DROP TABLE t2;
110 DROP TABLE t3;
111 DROP TABLE t4;
112
113 sync_slave_with_master;
114
115 connection master;
116
117 SET SQL_LOG_BIN= 0;
118 eval CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL,
119 PRIMARY KEY (`a`)) ENGINE= $engine;
120 SET SQL_LOG_BIN= 1;
121
122 connection slave;
123
124 eval CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL,
125 PRIMARY KEY (`a`)) ENGINE= $engine;
126
127 --echo ************* EXECUTION WITH INSERTS *************
128 connection master;
129 INSERT INTO t1(a,b,c) VALUES (1, null, b'01');
130 INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01');
131 INSERT INTO t1(a,b) VALUES (3, null);
132 INSERT INTO t1(a,c) VALUES (4, b'01');
133 INSERT INTO t1(a) VALUES (5);
134
135 --echo ************* SHOWING THE RESULT SETS WITH INSERTS *************
136 --echo TABLES t1 and t2 must be different.
137 sync_slave_with_master;
138 connection master;
139 SELECT a,b+0,c+0 FROM t1 ORDER BY a;
140 connection slave;
141 SELECT a,b+0,c+0 FROM t1 ORDER BY a;
142
143 --echo ************* EXECUTION WITH UPDATES and REPLACES *************
144 connection master;
145 DELETE FROM t1;
146 INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01');
147 REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01');
148 UPDATE t1 set b= NULL, c= b'00' where a= 1;
149 REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00');
150
151 --echo ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
152 --echo TABLES t1 and t2 must be equal otherwise an error will be thrown.
153 sync_slave_with_master;
154 let $diff_tables= master:t1, slave:t1;
155 source include/diff_tables.inc;
156
157 connection master;
158
159 DROP TABLE t1;
160
161 sync_slave_with_master;
162
163 --echo ################################################################################
164 --echo # NULL ---> NOT NULL (STRICT MODE)
165 --echo # UNCOMMENT THIS AFTER FIXING BUG#43992
166 --echo ################################################################################
167 #connection slave;
168 #SET GLOBAL sql_mode="TRADITIONAL";
169 #
170 #STOP SLAVE;
171 #--source include/wait_for_slave_to_stop.inc
172 #START SLAVE;
173 #--source include/wait_for_slave_to_start.inc
174 #
175 #let $y=0;
176 #while ($y < 6)
177 #{
178 # connection master;
179 #
180 # SET SQL_LOG_BIN= 0;
181 # eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT,
182 # PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
183 # eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT,
184 # PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
185 # eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT,
186 # PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
187 # SET SQL_LOG_BIN= 1;
188 #
189 # connection slave;
190 #
191 # eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL,
192 # `c` INT NOT NULL,
193 # PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
194 # eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
195 # `c` INT,
196 # PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
197 # eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL,
198 # `c` INT DEFAULT 500,
199 # PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
200 #
201 # if ($y==0)
202 # {
203 # --echo ************* EXECUTION WITH INSERTS *************
204 # connection master;
205 # INSERT INTO t1(a) VALUES (1);
206 # }
207 #
208 # if ($y==1)
209 # {
210 # --echo ************* EXECUTION WITH INSERTS *************
211 # connection master;
212 # INSERT INTO t1(a, b) VALUES (1, NULL);
213 # }
214 #
215 # if ($y==2)
216 # {
217 # --echo ************* EXECUTION WITH UPDATES *************
218 # connection master;
219 # INSERT INTO t3(a, b) VALUES (1, 1);
220 # INSERT INTO t3(a, b) VALUES (2, 1);
221 # UPDATE t3 SET b = NULL where a= 1;
222 # }
223 #
224 # if ($y==3)
225 # {
226 # --echo ************* EXECUTION WITH INSERTS/REPLACES *************
227 # connection master;
228 # REPLACE INTO t3(a, b) VALUES (1, null);
229 # }
230 #
231 # if ($y==4)
232 # {
233 # --echo ************* EXECUTION WITH UPDATES/REPLACES *************
234 # connection master;
235 # INSERT INTO t3(a, b) VALUES (1, 1);
236 # REPLACE INTO t3(a, b) VALUES (1, null);
237 # }
238 #
239 # if ($y==5)
240 # {
241 # --echo ************* EXECUTION WITH MULTI-ROW INSERTS *************
242 # connection master;
243 #
244 # SET SQL_LOG_BIN= 0;
245 # INSERT INTO t2(a, b) VALUES (1, 1);
246 # INSERT INTO t2(a, b) VALUES (2, 1);
247 # INSERT INTO t2(a, b) VALUES (3, null);
248 # INSERT INTO t2(a, b) VALUES (4, 1);
249 # INSERT INTO t2(a, b) VALUES (5, 1);
250 # SET SQL_LOG_BIN= 1;
251 #
252 # INSERT INTO t2 SELECT a + 10, b from t2;
253 # --echo The statement below is just executed to stop processing
254 # INSERT INTO t1(a) VALUES (1);
255 # }
256 #
257 # --echo ************* SHOWING THE RESULT SETS *************
258 # connection slave;
259 # --source include/wait_for_slave_sql_to_stop.inc
260 # connection master;
261 # SELECT * FROM t1 ORDER BY a;
262 # connection slave;
263 # SELECT * FROM t1 ORDER BY a;
264 # connection master;
265 # SELECT * FROM t2 ORDER BY a;
266 # connection slave;
267 # SELECT * FROM t2 ORDER BY a;
268 # connection master;
269 # SELECT * FROM t3 ORDER BY a;
270 # connection slave;
271 # SELECT * FROM t3 ORDER BY a;
272 # --source include/rpl_reset.inc
273 #
274 # connection master;
275 #
276 # DROP TABLE t1;
277 # DROP TABLE t2;
278 # DROP TABLE t3;
279 #
280 # sync_slave_with_master;
281 #
282 # inc $y;
283 #}
284 #connection slave;
285 #SET GLOBAL sql_mode="";
286 #
287 #STOP SLAVE;
288 #source include/wait_for_slave_to_stop.inc;
289 #START SLAVE;
290 #--source include/wait_for_slave_to_start.inc
291
292 --echo ################################################################################
293 --echo # NULL ---> NOT NULL (NON-STRICT MODE)
294 --echo ################################################################################
295 connection master;
296
297 SET SQL_LOG_BIN= 0;
298 eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT,
299 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
300 eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT,
301 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
302 eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT,
303 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
304 SET SQL_LOG_BIN= 1;
305
306 connection slave;
307
308 eval CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL,
309 `c` INT NOT NULL,
310 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
311 eval CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
312 `c` INT,
313 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
314 eval CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL,
315 `c` INT DEFAULT 500,
316 PRIMARY KEY(`a`)) ENGINE=$engine DEFAULT CHARSET=LATIN1;
317
318 --echo ************* EXECUTION WITH INSERTS *************
319 connection master;
320 INSERT INTO t1(a) VALUES (1);
321 INSERT INTO t1(a, b) VALUES (2, NULL);
322 INSERT INTO t1(a, b) VALUES (3, 1);
323
324 INSERT INTO t2(a) VALUES (1);
325 INSERT INTO t2(a, b) VALUES (2, NULL);
326 INSERT INTO t2(a, b) VALUES (3, 1);
327
328 INSERT INTO t3(a) VALUES (1);
329 INSERT INTO t3(a, b) VALUES (2, NULL);
330 INSERT INTO t3(a, b) VALUES (3, 1);
331 INSERT INTO t3(a, b) VALUES (4, 1);
332 REPLACE INTO t3(a, b) VALUES (5, null);
333
334 REPLACE INTO t3(a, b) VALUES (3, null);
335 UPDATE t3 SET b = NULL where a = 4;
336
337 --echo ************* SHOWING THE RESULT SETS *************
338 connection master;
339 sync_slave_with_master;
340
341 connection master;
342 SELECT * FROM t1 ORDER BY a;
343 connection slave;
344 SELECT * FROM t1 ORDER BY a;
345 connection master;
346 SELECT * FROM t2 ORDER BY a;
347 connection slave;
348 SELECT * FROM t2 ORDER BY a;
349 connection master;
350 SELECT * FROM t3 ORDER BY a;
351 connection slave;
352 SELECT * FROM t3 ORDER BY a;
353
354 connection master;
355
356 DROP TABLE t1;
357 DROP TABLE t2;
358 DROP TABLE t3;
359
360 sync_slave_with_master;