Debian Codegraph @

mysql-test/suite/rpl_ndb/r/rpl_ndb_not_null.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 SET SQL_LOG_BIN= 0;
7 CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
8 `c` INT DEFAULT NULL,
9 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
10 CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
11 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
12 CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL,
13 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
14 CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL,
15 `c` INT DEFAULT NULL,
16 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
17 SET SQL_LOG_BIN= 1;
18 CREATE TABLE t1(`a` INT, `b` DATE DEFAULT NULL,
19 `c` INT DEFAULT NULL,
20 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
21 CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
22 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
23 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
24 CREATE TABLE t3(`a` INT, `b` DATE DEFAULT '0000-00-00',
25 `c` INT DEFAULT 500,
26 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
27 CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00',
28 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
29 SET sql_mode = default;
30 ************* EXECUTION WITH INSERTS *************
31 INSERT INTO t1(a,b,c) VALUES (1, null, 1);
32 INSERT INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
33 INSERT INTO t1(a,b) VALUES (3, null);
34 INSERT INTO t1(a,c) VALUES (4, 4);
35 INSERT INTO t1(a) VALUES (5);
36 INSERT INTO t2(a,b) VALUES (1, null);
37 INSERT INTO t2(a,b) VALUES (2,'1111-11-11');
38 INSERT INTO t2(a) VALUES (3);
39 INSERT INTO t3(a,b) VALUES (1, null);
40 INSERT INTO t3(a,b) VALUES (2,'1111-11-11');
41 INSERT INTO t3(a) VALUES (3);
42 INSERT INTO t4(a,b,c) VALUES (1, null, 1);
43 INSERT INTO t4(a,b,c) VALUES (2,'1111-11-11', 2);
44 INSERT INTO t4(a,b) VALUES (3, null);
45 INSERT INTO t4(a,c) VALUES (4, 4);
46 INSERT INTO t4(a) VALUES (5);
47 ************* SHOWING THE RESULT SETS WITH INSERTS *************
48 include/sync_slave_sql_with_master.inc
49 TABLES t1 and t2 must be equal otherwise an error will be thrown.
50 include/diff_tables.inc [master:t1, slave:t1]
51 include/diff_tables.inc [master:t2, slave:t2]
52 TABLES t2 and t3 must be different.
53 SELECT * FROM t3 ORDER BY a;
54 a b
55 1 NULL
56 2 1111-11-11
57 3 NULL
58 SELECT * FROM t3 ORDER BY a;
59 a b c
60 1 NULL 500
61 2 1111-11-11 500
62 3 NULL 500
63 SELECT * FROM t4 ORDER BY a;
64 a b c
65 1 NULL 1
66 2 1111-11-11 2
67 3 NULL NULL
68 4 NULL 4
69 5 NULL NULL
70 SELECT * FROM t4 ORDER BY a;
71 a b
72 1 NULL
73 2 1111-11-11
74 3 NULL
75 4 NULL
76 5 NULL
77 ************* EXECUTION WITH UPDATES and REPLACES *************
78 DELETE FROM t1;
79 INSERT INTO t1(a,b,c) VALUES (1,'1111-11-11', 1);
80 REPLACE INTO t1(a,b,c) VALUES (2,'1111-11-11', 2);
81 UPDATE t1 set b= NULL, c= 300 where a= 1;
82 REPLACE INTO t1(a,b,c) VALUES (2, NULL, 300);
83 ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
84 include/sync_slave_sql_with_master.inc
85 TABLES t1 and t2 must be equal otherwise an error will be thrown.
86 include/diff_tables.inc [master:t1, slave:t1]
87 ************* CLEANING *************
88 DROP TABLE t1;
89 DROP TABLE t2;
90 DROP TABLE t3;
91 DROP TABLE t4;
92 include/sync_slave_sql_with_master.inc
93 SET SQL_LOG_BIN= 0;
94 CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT NULL, `c` BIT DEFAULT NULL,
95 PRIMARY KEY (`a`)) ENGINE= 'NDB';
96 SET SQL_LOG_BIN= 1;
97 CREATE TABLE t1 (`a` INT, `b` BIT DEFAULT b'01', `c` BIT DEFAULT NULL,
98 PRIMARY KEY (`a`)) ENGINE= 'NDB';
99 ************* EXECUTION WITH INSERTS *************
100 INSERT INTO t1(a,b,c) VALUES (1, null, b'01');
101 INSERT INTO t1(a,b,c) VALUES (2,b'00', b'01');
102 INSERT INTO t1(a,b) VALUES (3, null);
103 INSERT INTO t1(a,c) VALUES (4, b'01');
104 INSERT INTO t1(a) VALUES (5);
105 ************* SHOWING THE RESULT SETS WITH INSERTS *************
106 TABLES t1 and t2 must be different.
107 include/sync_slave_sql_with_master.inc
108 SELECT a,b+0,c+0 FROM t1 ORDER BY a;
109 a b+0 c+0
110 1 NULL 1
111 2 0 1
112 3 NULL NULL
113 4 NULL 1
114 5 NULL NULL
115 SELECT a,b+0,c+0 FROM t1 ORDER BY a;
116 a b+0 c+0
117 1 NULL 1
118 2 0 1
119 3 NULL NULL
120 4 NULL 1
121 5 NULL NULL
122 ************* EXECUTION WITH UPDATES and REPLACES *************
123 DELETE FROM t1;
124 INSERT INTO t1(a,b,c) VALUES (1,b'00', b'01');
125 REPLACE INTO t1(a,b,c) VALUES (2,b'00',b'01');
126 UPDATE t1 set b= NULL, c= b'00' where a= 1;
127 REPLACE INTO t1(a,b,c) VALUES (2, NULL, b'00');
128 ************* SHOWING THE RESULT SETS WITH UPDATES and REPLACES *************
129 TABLES t1 and t2 must be equal otherwise an error will be thrown.
130 include/sync_slave_sql_with_master.inc
131 include/diff_tables.inc [master:t1, slave:t1]
132 DROP TABLE t1;
133 include/sync_slave_sql_with_master.inc
134 ################################################################################
135 # NULL ---> NOT NULL (STRICT MODE)
136 # UNCOMMENT THIS AFTER FIXING BUG#43992
137 ################################################################################
138 ################################################################################
139 # NULL ---> NOT NULL (NON-STRICT MODE)
140 ################################################################################
141 SET SQL_LOG_BIN= 0;
142 CREATE TABLE t1(`a` INT NOT NULL, `b` INT,
143 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
144 CREATE TABLE t2(`a` INT NOT NULL, `b` INT,
145 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
146 CREATE TABLE t3(`a` INT NOT NULL, `b` INT,
147 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
148 SET SQL_LOG_BIN= 1;
149 CREATE TABLE t1(`a` INT NOT NULL, `b` INT NOT NULL,
150 `c` INT NOT NULL,
151 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
152 CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
153 `c` INT,
154 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
155 CREATE TABLE t3(`a` INT NOT NULL, `b` INT NOT NULL,
156 `c` INT DEFAULT 500,
157 PRIMARY KEY(`a`)) ENGINE='NDB' DEFAULT CHARSET=LATIN1;
158 ************* EXECUTION WITH INSERTS *************
159 INSERT INTO t1(a) VALUES (1);
160 INSERT INTO t1(a, b) VALUES (2, NULL);
161 INSERT INTO t1(a, b) VALUES (3, 1);
162 INSERT INTO t2(a) VALUES (1);
163 INSERT INTO t2(a, b) VALUES (2, NULL);
164 INSERT INTO t2(a, b) VALUES (3, 1);
165 INSERT INTO t3(a) VALUES (1);
166 INSERT INTO t3(a, b) VALUES (2, NULL);
167 INSERT INTO t3(a, b) VALUES (3, 1);
168 INSERT INTO t3(a, b) VALUES (4, 1);
169 REPLACE INTO t3(a, b) VALUES (5, null);
170 REPLACE INTO t3(a, b) VALUES (3, null);
171 UPDATE t3 SET b = NULL where a = 4;
172 ************* SHOWING THE RESULT SETS *************
173 include/sync_slave_sql_with_master.inc
174 SELECT * FROM t1 ORDER BY a;
175 a b
176 1 NULL
177 2 NULL
178 3 1
179 SELECT * FROM t1 ORDER BY a;
180 a b c
181 SELECT * FROM t2 ORDER BY a;
182 a b
183 1 NULL
184 2 NULL
185 3 1
186 SELECT * FROM t2 ORDER BY a;
187 a b c
188 1 0 NULL
189 2 0 NULL
190 3 1 NULL
191 SELECT * FROM t3 ORDER BY a;
192 a b
193 1 NULL
194 2 NULL
195 3 NULL
196 4 NULL
197 5 NULL
198 SELECT * FROM t3 ORDER BY a;
199 a b c
200 1 0 500
201 2 0 500
202 3 0 500
203 4 0 500
204 5 0 500
205 DROP TABLE t1;
206 DROP TABLE t2;
207 DROP TABLE t3;
208 include/sync_slave_sql_with_master.inc
209 include/rpl_end.inc