Debian Codegraph @

mysql-test/suite/rpl/r/rpl_instant_add_column.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 # Scenario 1:
7 # Create a small table, add some columns instantly
8 #
9 [connection master]
10 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
11 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
12 include/sync_slave_sql_with_master.inc
13 [connection master]
14 ALTER TABLE t1 ADD COLUMN c1 INT, ALGORITHM=INSTANT;
15 Table id did not change
16 count(*) = 1
17 1
18 CHECK TABLE t1;
19 Table Op Msg_type Msg_text
20 test.t1 check status OK
21 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
22 name default_value
23 c1 NULL
24 INSERT INTO t1(a, c1) VALUES(6, 1);
25 SELECT count(*) = max(a) FROM t1 WHERE c1 IS NULL;
26 count(*) = max(a)
27 1
28 SELECT c1 FROM t1 WHERE c1 = 1;
29 c1
30 1
31 SHOW CREATE TABLE t1;
32 Table Create Table
33 t1 CREATE TABLE `t1` (
34 `a` int NOT NULL AUTO_INCREMENT,
35 `b` int DEFAULT NULL,
36 `c1` int DEFAULT NULL,
37 PRIMARY KEY (`a`)
38 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
39 include/sync_slave_sql_with_master.inc
40 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
41 name default_value
42 c1 NULL
43 SHOW CREATE TABLE t1;
44 Table Create Table
45 t1 CREATE TABLE `t1` (
46 `a` int NOT NULL AUTO_INCREMENT,
47 `b` int DEFAULT NULL,
48 `c1` int DEFAULT NULL,
49 PRIMARY KEY (`a`)
50 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
51 [connection master]
52 include/diff_tables.inc [master:test.t1,slave:test.t1]
53 # Table id not changed on slave
54 [connection master]
55 DROP TABLE t1;
56 include/sync_slave_sql_with_master.inc
57 # Scenario 2:
58 # Create a small table, add some virtual columns instantly
59 #
60 [connection master]
61 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT);
62 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
63 include/sync_slave_sql_with_master.inc
64 [connection master]
65 ALTER TABLE t1 ADD COLUMN c1 INT, ADD COLUMN c2 FLOAT GENERATED ALWAYS AS ((1.4 * 2.8)) VIRTUAL, ALGORITHM=INSTANT;
66 Table id did not change
67 count(*) = 1
68 1
69 CHECK TABLE t1;
70 Table Op Msg_type Msg_text
71 test.t1 check status OK
72 INSERT INTO t1(a, c1) VALUES(6, 1);
73 SELECT * FROM t1;
74 a b c1 c2
75 1 1 NULL 3.92
76 2 2 NULL 3.92
77 3 3 NULL 3.92
78 4 4 NULL 3.92
79 5 5 NULL 3.92
80 6 NULL 1 3.92
81 SHOW CREATE TABLE t1;
82 Table Create Table
83 t1 CREATE TABLE `t1` (
84 `a` int NOT NULL AUTO_INCREMENT,
85 `b` int DEFAULT NULL,
86 `c1` int DEFAULT NULL,
87 `c2` float GENERATED ALWAYS AS ((1.4 * 2.8)) VIRTUAL,
88 PRIMARY KEY (`a`)
89 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
90 include/sync_slave_sql_with_master.inc
91 SELECT name, default_value FROM information_schema.innodb_columns WHERE name = 'c1' AND has_default = 1;
92 name default_value
93 c1 NULL
94 SHOW CREATE TABLE t1;
95 Table Create Table
96 t1 CREATE TABLE `t1` (
97 `a` int NOT NULL AUTO_INCREMENT,
98 `b` int DEFAULT NULL,
99 `c1` int DEFAULT NULL,
100 `c2` float GENERATED ALWAYS AS ((1.4 * 2.8)) VIRTUAL,
101 PRIMARY KEY (`a`)
102 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
103 [connection master]
104 include/diff_tables.inc [master:test.t1,slave:test.t1]
105 # Table id not changed on slave
106 [connection master]
107 DROP TABLE t1;
108 include/sync_slave_sql_with_master.inc
109 # Scenario 3:
110 # Create a small table with RANGE and HASH partition,
111 # and add INSTANT column
112 #
113 [connection master]
114 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT)
115 PARTITION BY RANGE (a)
116 SUBPARTITION BY HASH (a)
117 SUBPARTITIONS 3 (
118 PARTITION p1 values less than (10),
119 PARTITION p2 values less than (20),
120 PARTITION p3 values less than maxvalue);
121 INSERT INTO t1 VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
122 include/sync_slave_sql_with_master.inc
123 [connection master]
124 INSERT INTO t1(a, b) VALUES(5, 5), (6, 6), (28, 28);
125 SELECT * FROM t1;
126 a b
127 6 6
128 1 1
129 2 2
130 5 5
131 12 12
132 11 11
133 21 21
134 27 27
135 22 22
136 28 28
137 26 26
138 SHOW CREATE TABLE t1;
139 Table Create Table
140 t1 CREATE TABLE `t1` (
141 `a` int NOT NULL,
142 `b` int DEFAULT NULL,
143 PRIMARY KEY (`a`)
144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
145 /*!50100 PARTITION BY RANGE (`a`)
146 SUBPARTITION BY HASH (`a`)
147 SUBPARTITIONS 3
148 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
149 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
150 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
151 include/sync_slave_sql_with_master.inc
152 SELECT * FROM t1;
153 a b
154 6 6
155 1 1
156 2 2
157 5 5
158 12 12
159 11 11
160 21 21
161 27 27
162 22 22
163 28 28
164 26 26
165 SHOW CREATE TABLE t1;
166 Table Create Table
167 t1 CREATE TABLE `t1` (
168 `a` int NOT NULL,
169 `b` int DEFAULT NULL,
170 PRIMARY KEY (`a`)
171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
172 /*!50100 PARTITION BY RANGE (`a`)
173 SUBPARTITION BY HASH (`a`)
174 SUBPARTITIONS 3
175 (PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB,
176 PARTITION p2 VALUES LESS THAN (20) ENGINE = InnoDB,
177 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
178 [connection master]
179 include/diff_tables.inc [master:test.t1,slave:test.t1]
180 # Table id not changed on slave
181 # Table id not changed on master
182 [connection master]
183 DROP TABLE t1;
184 include/sync_slave_sql_with_master.inc
185 # Scenario 4:
186 # Create a small table and add TIMESTAMP columns instantly
187 #
188 [connection master]
189 SET @start_session_value = @@session.explicit_defaults_for_timestamp;
190 SET @@session.explicit_defaults_for_timestamp=OFF;
191 Warnings:
192 Warning 1287 'explicit_defaults_for_timestamp' is deprecated and will be removed in a future release.
193 SELECT @@session.explicit_defaults_for_timestamp;
194 @@session.explicit_defaults_for_timestamp
195 0
196 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
197 INSERT INTO t1 VALUES(1);
198 include/sync_slave_sql_with_master.inc
199 [connection master]
200 ALTER TABLE t1 ADD COLUMN c1 TIMESTAMP, ADD COLUMN c2 TIMESTAMP NULL, ADD COLUMN c3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ALGORITHM=INSTANT;
201 Table id did not change
202 count(*) = 3
203 1
204 CHECK TABLE t1;
205 Table Op Msg_type Msg_text
206 test.t1 check status OK
207 INSERT INTO t1 VALUES(2,NULL,NULL,NULL);
208 SHOW CREATE TABLE t1;
209 Table Create Table
210 t1 CREATE TABLE `t1` (
211 `a` int NOT NULL AUTO_INCREMENT,
212 `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
213 `c2` timestamp NULL DEFAULT NULL,
214 `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
215 PRIMARY KEY (`a`)
216 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
217 SELECT * FROM t1;
218 a c1 c2 c3
219 1 # NULL #
220 2 # NULL #
221 include/sync_slave_sql_with_master.inc
222 SELECT * FROM t1;
223 a c1 c2 c3
224 1 # NULL #
225 2 # NULL #
226 SHOW CREATE TABLE t1;
227 Table Create Table
228 t1 CREATE TABLE `t1` (
229 `a` int NOT NULL AUTO_INCREMENT,
230 `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
231 `c2` timestamp NULL DEFAULT NULL,
232 `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
233 PRIMARY KEY (`a`)
234 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
235 [connection master]
236 include/diff_tables.inc [master:test.t1,slave:test.t1]
237 # Table id not changed on slave
238 DROP TABLE t1;
239 include/sync_slave_sql_with_master.inc
240 [connection master]
241 SET @@session.explicit_defaults_for_timestamp=ON;
242 SELECT @@session.explicit_defaults_for_timestamp;
243 @@session.explicit_defaults_for_timestamp
244 1
245 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
246 INSERT INTO t1 VALUES(1);
247 include/sync_slave_sql_with_master.inc
248 [connection master]
249 ALTER TABLE t1 ADD COLUMN c1 TIMESTAMP, ADD COLUMN c2 TIMESTAMP NOT NULL, ADD COLUMN c3 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ALGORITHM=INSTANT;
250 Table id did not change
251 count(*) = 3
252 1
253 CHECK TABLE t1;
254 Table Op Msg_type Msg_text
255 test.t1 check status OK
256 INSERT INTO t1 VALUES(2,NULL,'2001-01-01 00:00:00',NULL);
257 SHOW CREATE TABLE t1;
258 Table Create Table
259 t1 CREATE TABLE `t1` (
260 `a` int NOT NULL AUTO_INCREMENT,
261 `c1` timestamp NULL DEFAULT NULL,
262 `c2` timestamp NOT NULL,
263 `c3` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
264 PRIMARY KEY (`a`)
265 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
266 SELECT * FROM t1;
267 a c1 c2 c3
268 1 # 0000-00-00 00:00:00 #
269 2 # 2001-01-01 00:00:00 #
270 include/sync_slave_sql_with_master.inc
271 SELECT * FROM t1;
272 a c1 c2 c3
273 1 # 0000-00-00 00:00:00 #
274 2 # 2001-01-01 00:00:00 #
275 SHOW CREATE TABLE t1;
276 Table Create Table
277 t1 CREATE TABLE `t1` (
278 `a` int NOT NULL AUTO_INCREMENT,
279 `c1` timestamp NULL DEFAULT NULL,
280 `c2` timestamp NOT NULL,
281 `c3` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
282 PRIMARY KEY (`a`)
283 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
284 [connection master]
285 include/diff_tables.inc [master:test.t1,slave:test.t1]
286 # Table id not changed on slave
287 DROP TABLE t1;
288 include/sync_slave_sql_with_master.inc
289 [connection master]
290 SET @@session.explicit_defaults_for_timestamp = @start_session_value;
291 SELECT @@session.explicit_defaults_for_timestamp;
292 @@session.explicit_defaults_for_timestamp
293 1
294 include/rpl_end.inc