Debian Codegraph @

mysql-test/suite/rpl/r/rpl_not_null_innodb.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=Innodb DEFAULT CHARSET=LATIN1;
10 CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
11 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
12 CREATE TABLE t3(`a` INT, `b` DATE DEFAULT NULL,
13 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
14 CREATE TABLE t4(`a` INT, `b` DATE DEFAULT NULL,
15 `c` INT DEFAULT NULL,
16 PRIMARY KEY(`a`)) ENGINE=Innodb 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=Innodb DEFAULT CHARSET=LATIN1;
21 CREATE TABLE t2(`a` INT, `b` DATE DEFAULT NULL,
22 PRIMARY KEY(`a`)) ENGINE=Innodb 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=Innodb DEFAULT CHARSET=LATIN1;
27 CREATE TABLE t4(`a` INT, `b` DATE DEFAULT '0000-00-00',
28 PRIMARY KEY(`a`)) ENGINE=Innodb 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= Innodb;
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= Innodb;
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=Innodb DEFAULT CHARSET=LATIN1;
144 CREATE TABLE t2(`a` INT NOT NULL, `b` INT,
145 PRIMARY KEY(`a`)) ENGINE=Innodb DEFAULT CHARSET=LATIN1;
146 CREATE TABLE t3(`a` INT NOT NULL, `b` INT,
147 PRIMARY KEY(`a`)) ENGINE=Innodb 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=Innodb DEFAULT CHARSET=LATIN1;
152 CREATE TABLE t2(`a` INT NOT NULL, `b` INT NOT NULL,
153 `c` INT,
154 PRIMARY KEY(`a`)) ENGINE=Innodb 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=Innodb 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 1 0 0
182 2 0 0
183 3 1 0
184 SELECT * FROM t2 ORDER BY a;
185 a b
186 1 NULL
187 2 NULL
188 3 1
189 SELECT * FROM t2 ORDER BY a;
190 a b c
191 1 0 NULL
192 2 0 NULL
193 3 1 NULL
194 SELECT * FROM t3 ORDER BY a;
195 a b
196 1 NULL
197 2 NULL
198 3 NULL
199 4 NULL
200 5 NULL
201 SELECT * FROM t3 ORDER BY a;
202 a b c
203 1 0 500
204 2 0 500
205 3 0 500
206 4 0 500
207 5 0 500
208 DROP TABLE t1;
209 DROP TABLE t2;
210 DROP TABLE t3;
211 include/sync_slave_sql_with_master.inc
212 include/rpl_end.inc