Debian Codegraph @

mysql-test/suite/innodb/r/instant_ddl_import_debug.result

raw
1 ##############################################
2 # Test instant ADD/DROP COLUMN for REDUNDANT format
3 ##############################################
4 # Create a table with 2 columns. [c1, c2]
5 CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=REDUNDANT;;
6 INSERT INTO t1 values ("row1_c1", "a");
7 SELECT * FROM t1 ORDER BY c1;
8 c1 c2
9 row1_c1 a
10 # Add a new column at the end. [c1, c2, +c3]
11 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
12 INSERT INTO t1 values ("row2_c1", "b", "row2_c3");
13 SELECT * FROM t1 ORDER BY c1;
14 c1 c2 c3
15 row1_c1 a default_c3
16 row2_c1 b row2_c3
17 # Add a new column in between. [c1, +c4, c2, c3]
18 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
19 INSERT INTO t1 values ("row3_c1", "row3_c4", "c", "row3_c3");
20 SELECT * FROM t1 ORDER BY c1;
21 c1 c4 c2 c3
22 row1_c1 NULL a default_c3
23 row2_c1 NULL b row2_c3
24 row3_c1 row3_c4 c row3_c3
25 # ------------------------------------------------------------
26 # Scenario 1.1 : Same table with tablespace discarded
27 # ------------------------------------------------------------
28 # EXPORT
29 FLUSH TABLE t1 FOR EXPORT;
30 # Copy cfg/ibd files into temp location
31 UNLOCK TABLES;
32 # IMPORT
33 ALTER TABLE t1 DISCARD TABLESPACE;
34 # Copy cfg/ibd files from temp location
35 ALTER TABLE t1 IMPORT TABLESPACE;
36 SELECT * FROM t1 ORDER BY c1;
37 c1 c4 c2 c3
38 row1_c1 NULL a default_c3
39 row2_c1 NULL b row2_c3
40 row3_c1 row3_c4 c row3_c3
41 # Metadata from INFORMATION_SCHEMA.TABLES
42 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
43 test/t1 7 0 2 2 4 4
44 # Metadata from INFORMATION_SCHEMA.COLUMNS
45 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
46 c1 0 13 16712190 0 0 0
47 c4 1 13 16711934 1 2 0
48 c2 2 6 766 0 0 0
49 c3 3 13 16711934 1 1 0
50 # DD Metadata of table
51 NAME SE_PRIVATE_DATA
52 t1 discard=0;
53 # DD Metadata of columns in table
54 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
55 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
56 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
57 c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID;
58 c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
59 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
60 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
61 # ------------------------------------------------------------
62 # Scenario 1.2 : New table created with like
63 # ------------------------------------------------------------
64 # EXPORT
65 FLUSH TABLE t1 FOR EXPORT;
66 # Copy cfg/ibd files into temp location
67 UNLOCK TABLES;
68 CREATE TABLE t2 like t1;
69 DROP TABLE t1;
70 ALTER TABLE t2 RENAME to t1;
71 SHOW CREATE TABLE t1;
72 Table Create Table
73 t1 CREATE TABLE `t1` (
74 `c1` char(20) NOT NULL,
75 `c4` char(20) DEFAULT NULL,
76 `c2` enum('a','b','c') DEFAULT NULL,
77 `c3` char(20) DEFAULT 'default_c3',
78 PRIMARY KEY (`c1`)
79 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
80 # IMPORT
81 ALTER TABLE t1 DISCARD TABLESPACE;
82 # Copy cfg/ibd files from temp location
83 ALTER TABLE t1 IMPORT TABLESPACE;
84 SELECT * FROM t1;
85 c1 c4 c2 c3
86 row1_c1 NULL a default_c3
87 row2_c1 NULL b row2_c3
88 row3_c1 row3_c4 c row3_c3
89 # Metadata from INFORMATION_SCHEMA.TABLES
90 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
91 test/t1 7 0 2 2 4 4
92 # Metadata from INFORMATION_SCHEMA.COLUMNS
93 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
94 c1 0 13 16712190 0 0 0
95 c4 1 13 16711934 1 2 0
96 c2 2 6 766 0 0 0
97 c3 3 13 16711934 1 1 0
98 # DD Metadata of table
99 NAME SE_PRIVATE_DATA
100 t1 discard=0;
101 # DD Metadata of columns in table
102 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
103 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
104 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
105 c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID;
106 c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
107 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
108 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
109 # ------------------------------------------------------------
110 # Drop a column somewhere in between. [c1, c4, -c2, c3]
111 # ------------------------------------------------------------
112 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
113 # Metadata from INFORMATION_SCHEMA.TABLES
114 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
115 test/t1 6 0 3 2 3 4
116 # Metadata from INFORMATION_SCHEMA.COLUMNS
117 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
118 c1 0 13 16712190 0 0 0
119 c4 1 13 16711934 1 2 0
120 c3 2 13 16711934 1 1 0
121 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3
122 # DD Metadata of table
123 NAME SE_PRIVATE_DATA
124 t1 discard=0;
125 # DD Metadata of columns in table
126 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
127 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
128 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
129 c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
130 DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
131 DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
132 !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;version_dropped=3;
133 SELECT * FROM t1 ORDER BY c1;
134 c1 c4 c3
135 row1_c1 NULL default_c3
136 row2_c1 NULL row2_c3
137 row3_c1 row3_c4 row3_c3
138 INSERT INTO t1 values ("row4_c1", "row4_c4", "row4_c3");
139 SELECT * FROM t1 ORDER BY c1;
140 c1 c4 c3
141 row1_c1 NULL default_c3
142 row2_c1 NULL row2_c3
143 row3_c1 row3_c4 row3_c3
144 row4_c1 row4_c4 row4_c3
145 # ------------------------------------------------------------
146 # Scenario 2.1 : Same table with tablespace discarded
147 # ------------------------------------------------------------
148 # EXPORT
149 FLUSH TABLE t1 FOR EXPORT;
150 # Copy cfg/ibd files into temp location
151 UNLOCK TABLES;
152 # IMPORT
153 ALTER TABLE t1 DISCARD TABLESPACE;
154 # Copy cfg/ibd files from temp location
155 ALTER TABLE t1 IMPORT TABLESPACE;
156 SELECT * FROM t1 ORDER BY c1;
157 c1 c4 c3
158 row1_c1 NULL default_c3
159 row2_c1 NULL row2_c3
160 row3_c1 row3_c4 row3_c3
161 row4_c1 row4_c4 row4_c3
162 # Metadata from INFORMATION_SCHEMA.TABLES
163 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
164 test/t1 6 0 3 2 3 4
165 # Metadata from INFORMATION_SCHEMA.COLUMNS
166 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
167 c1 0 13 16712190 0 0 0
168 c4 1 13 16711934 1 2 0
169 c3 2 13 16711934 1 1 0
170 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3
171 # DD Metadata of table
172 NAME SE_PRIVATE_DATA
173 t1 discard=0;
174 # DD Metadata of columns in table
175 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
176 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
177 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
178 c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
179 DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
180 DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
181 !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3;
182 # ------------------------------------------------------------
183 # Scenario 2.2 : New table created with like
184 # ------------------------------------------------------------
185 # EXPORT
186 FLUSH TABLE t1 FOR EXPORT;
187 # Copy cfg/ibd files into temp location
188 UNLOCK TABLES;
189 CREATE TABLE t2 like t1;
190 DROP TABLE t1;
191 ALTER TABLE t2 RENAME to t1;
192 SHOW CREATE TABLE t1;
193 Table Create Table
194 t1 CREATE TABLE `t1` (
195 `c1` char(20) NOT NULL,
196 `c4` char(20) DEFAULT NULL,
197 `c3` char(20) DEFAULT 'default_c3',
198 PRIMARY KEY (`c1`)
199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
200 # IMPORT
201 ALTER TABLE t1 DISCARD TABLESPACE;
202 # Copy cfg/ibd files from temp location
203 ALTER TABLE t1 IMPORT TABLESPACE;
204 SELECT * FROM t1;
205 c1 c4 c3
206 row1_c1 NULL default_c3
207 row2_c1 NULL row2_c3
208 row3_c1 row3_c4 row3_c3
209 row4_c1 row4_c4 row4_c3
210 # Metadata from INFORMATION_SCHEMA.TABLES
211 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
212 test/t1 6 0 3 2 3 4
213 # Metadata from INFORMATION_SCHEMA.COLUMNS
214 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
215 c1 0 13 16712190 0 0 0
216 c4 1 13 16711934 1 2 0
217 c3 2 13 16711934 1 1 0
218 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3
219 # DD Metadata of table
220 NAME SE_PRIVATE_DATA
221 t1 discard=0;
222 # DD Metadata of columns in table
223 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
224 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
225 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
226 c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
227 DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
228 DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
229 !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3;
230 DROP TABLE t1;
231 # ------------------------------------------------------------
232 # Scenarios 3 : Error scenarios
233 # ------------------------------------------------------------
234 # ------------------------------------------------------------
235 # Scenario 3.1 : Target table has INSTANT, source doesn't
236 # ------------------------------------------------------------
237 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;;
238 INSERT INTO t1 values ("row1_c1", "row1_c2");
239 SELECT * FROM t1 ORDER BY c1;
240 c1 c2
241 row1_c1 row1_c2
242 # EXPORT
243 FLUSH TABLE t1 FOR EXPORT;
244 # Copy cfg/ibd files into temp location
245 UNLOCK TABLES;
246 DROP TABLE t1;
247 CREATE TABLE t1 (c1 char(20) KEY) ROW_FORMAT=REDUNDANT;;
248 ALTER TABLE t1 ADD COLUMN c2 char(20) DEFAULT "def_c2", ALGORITHM=INSTANT;
249 # IMPORT
250 ALTER TABLE t1 DISCARD TABLESPACE;
251 # Copy cfg/ibd files from temp location
252 ALTER TABLE t1 IMPORT TABLESPACE;
253 ERROR HY000: Schema mismatch (The .cfg file indicates no INSTANT column in the source table whereas the metadata in data dictionary says there are instant columns in the target table)
254 DROP TABLE t1;
255 # ------------------------------------------------------------
256 # Scenario 3.2 : Both has INSTANT, but different instant version
257 # ------------------------------------------------------------
258 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;;
259 INSERT INTO t1 values ("row1_c1", "row1_c2");
260 SELECT * FROM t1 ORDER BY c1;
261 c1 c2
262 row1_c1 row1_c2
263 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
264 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
265 SELECT * FROM t1 ORDER BY c1;
266 c1 c2 c3
267 row1_c1 row1_c2 default_c3
268 row2_c1 row2_c2 row2_c3
269 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
270 INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3");
271 SELECT * FROM t1 ORDER BY c1;
272 c1 c4 c2 c3
273 row1_c1 NULL row1_c2 default_c3
274 row2_c1 NULL row2_c2 row2_c3
275 row3_c1 row3_c4 row3_c2 row3_c3
276 # EXPORT
277 FLUSH TABLE t1 FOR EXPORT;
278 # Copy cfg/ibd files into temp location
279 UNLOCK TABLES;
280 DROP TABLE t1;
281 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=REDUNDANT;;
282 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
283 # IMPORT
284 ALTER TABLE t1 DISCARD TABLESPACE;
285 # Copy cfg/ibd files from temp location
286 ALTER TABLE t1 IMPORT TABLESPACE;
287 ERROR HY000: Schema mismatch (Table has instant column but current row version didn't match.)
288 DROP TABLE t1;
289 # ------------------------------------------------------------
290 # Scenario 3.3 : Both has INSTANT, but different col count
291 # ------------------------------------------------------------
292 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;;
293 INSERT INTO t1 values ("row1_c1", "row1_c2");
294 SELECT * FROM t1 ORDER BY c1;
295 c1 c2
296 row1_c1 row1_c2
297 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
298 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
299 SELECT * FROM t1 ORDER BY c1;
300 c1 c2 c3
301 row1_c1 row1_c2 default_c3
302 row2_c1 row2_c2 row2_c3
303 # EXPORT
304 FLUSH TABLE t1 FOR EXPORT;
305 # Copy cfg/ibd files into temp location
306 UNLOCK TABLES;
307 DROP TABLE t1;
308 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=REDUNDANT;;
309 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
310 # IMPORT
311 ALTER TABLE t1 DISCARD TABLESPACE;
312 # Copy cfg/ibd files from temp location
313 ALTER TABLE t1 IMPORT TABLESPACE;
314 ERROR HY000: Schema mismatch (Number of columns don't match, table has 7 columns but the tablespace meta-data file has 6 columns)
315 DROP TABLE t1;
316 # ------------------------------------------------------------
317 # Scenario 3.4 : Both has INSTANT, but different col count
318 # ------------------------------------------------------------
319 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;;
320 INSERT INTO t1 values ("row1_c1", "row1_c2");
321 SELECT * FROM t1 ORDER BY c1;
322 c1 c2
323 row1_c1 row1_c2
324 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
325 INSERT INTO t1 values ("row2_c1", "row2_c4", "row2_c2", "row2_c3");
326 SELECT * FROM t1 ORDER BY c1;
327 c1 c4 c2 c3
328 row1_c1 NULL row1_c2 default_c3
329 row2_c1 row2_c4 row2_c2 row2_c3
330 # EXPORT
331 FLUSH TABLE t1 FOR EXPORT;
332 # Copy cfg/ibd files into temp location
333 UNLOCK TABLES;
334 DROP TABLE t1;
335 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=REDUNDANT;;
336 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
337 # IMPORT
338 ALTER TABLE t1 DISCARD TABLESPACE;
339 # Copy cfg/ibd files from temp location
340 ALTER TABLE t1 IMPORT TABLESPACE;
341 ERROR HY000: Schema mismatch (Table has instant column but column counts didn't match.)
342 DROP TABLE t1;
343 # ------------------------------------------------------------
344 # Scenario 3.5 : Both has INSTANT, but different default value
345 # ------------------------------------------------------------
346 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;;
347 INSERT INTO t1 values ("row1_c1", "row1_c2");
348 SELECT * FROM t1 ORDER BY c1;
349 c1 c2
350 row1_c1 row1_c2
351 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
352 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
353 SELECT * FROM t1 ORDER BY c1;
354 c1 c2 c3
355 row1_c1 row1_c2 default_c3
356 row2_c1 row2_c2 row2_c3
357 # EXPORT
358 FLUSH TABLE t1 FOR EXPORT;
359 # Copy cfg/ibd files into temp location
360 UNLOCK TABLES;
361 DROP TABLE t1;
362 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=REDUNDANT;;
363 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "newdefault_c3", ALGORITHM=INSTANT;
364 # IMPORT
365 ALTER TABLE t1 DISCARD TABLESPACE;
366 # Copy cfg/ibd files from temp location
367 ALTER TABLE t1 IMPORT TABLESPACE;
368 ERROR HY000: Schema mismatch (The metadata in the data dictionary and the .cfg file contain different default values for column c3!)
369 DROP TABLE t1;
370 ###########
371 # CLEANUP #
372 ###########
373 ############################################
374 # Test instant ADD/DROP COLUMN for DYNAMIC format
375 ############################################
376 # Create a table with 2 columns. [c1, c2]
377 CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=DYNAMIC;;
378 INSERT INTO t1 values ("row1_c1", "a");
379 SELECT * FROM t1 ORDER BY c1;
380 c1 c2
381 row1_c1 a
382 # Add a new column at the end. [c1, c2, +c3]
383 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
384 INSERT INTO t1 values ("row2_c1", "b", "row2_c3");
385 SELECT * FROM t1 ORDER BY c1;
386 c1 c2 c3
387 row1_c1 a default_c3
388 row2_c1 b row2_c3
389 # Add a new column in between. [c1, +c4, c2, c3]
390 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
391 INSERT INTO t1 values ("row3_c1", "row3_c4", "c", "row3_c3");
392 SELECT * FROM t1 ORDER BY c1;
393 c1 c4 c2 c3
394 row1_c1 NULL a default_c3
395 row2_c1 NULL b row2_c3
396 row3_c1 row3_c4 c row3_c3
397 # ------------------------------------------------------------
398 # Scenario 1.1 : Same table with tablespace discarded
399 # ------------------------------------------------------------
400 # EXPORT
401 FLUSH TABLE t1 FOR EXPORT;
402 # Copy cfg/ibd files into temp location
403 UNLOCK TABLES;
404 # IMPORT
405 ALTER TABLE t1 DISCARD TABLESPACE;
406 # Copy cfg/ibd files from temp location
407 ALTER TABLE t1 IMPORT TABLESPACE;
408 SELECT * FROM t1 ORDER BY c1;
409 c1 c4 c2 c3
410 row1_c1 NULL a default_c3
411 row2_c1 NULL b row2_c3
412 row3_c1 row3_c4 c row3_c3
413 # Metadata from INFORMATION_SCHEMA.TABLES
414 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
415 test/t1 7 0 2 2 4 4
416 # Metadata from INFORMATION_SCHEMA.COLUMNS
417 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
418 c1 0 13 16712190 0 0 0
419 c4 1 13 16711934 1 2 0
420 c2 2 6 766 0 0 0
421 c3 3 13 16711934 1 1 0
422 # DD Metadata of table
423 NAME SE_PRIVATE_DATA
424 t1 discard=0;
425 # DD Metadata of columns in table
426 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
427 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
428 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
429 c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID;
430 c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
431 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
432 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
433 # ------------------------------------------------------------
434 # Scenario 1.2 : New table created with like
435 # ------------------------------------------------------------
436 # EXPORT
437 FLUSH TABLE t1 FOR EXPORT;
438 # Copy cfg/ibd files into temp location
439 UNLOCK TABLES;
440 CREATE TABLE t2 like t1;
441 DROP TABLE t1;
442 ALTER TABLE t2 RENAME to t1;
443 SHOW CREATE TABLE t1;
444 Table Create Table
445 t1 CREATE TABLE `t1` (
446 `c1` char(20) NOT NULL,
447 `c4` char(20) DEFAULT NULL,
448 `c2` enum('a','b','c') DEFAULT NULL,
449 `c3` char(20) DEFAULT 'default_c3',
450 PRIMARY KEY (`c1`)
451 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
452 # IMPORT
453 ALTER TABLE t1 DISCARD TABLESPACE;
454 # Copy cfg/ibd files from temp location
455 ALTER TABLE t1 IMPORT TABLESPACE;
456 SELECT * FROM t1;
457 c1 c4 c2 c3
458 row1_c1 NULL a default_c3
459 row2_c1 NULL b row2_c3
460 row3_c1 row3_c4 c row3_c3
461 # Metadata from INFORMATION_SCHEMA.TABLES
462 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
463 test/t1 7 0 2 2 4 4
464 # Metadata from INFORMATION_SCHEMA.COLUMNS
465 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
466 c1 0 13 16712190 0 0 0
467 c4 1 13 16711934 1 2 0
468 c2 2 6 766 0 0 0
469 c3 3 13 16711934 1 1 0
470 # DD Metadata of table
471 NAME SE_PRIVATE_DATA
472 t1 discard=0;
473 # DD Metadata of columns in table
474 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
475 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
476 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
477 c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID;
478 c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
479 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
480 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
481 # ------------------------------------------------------------
482 # Drop a column somewhere in between. [c1, c4, -c2, c3]
483 # ------------------------------------------------------------
484 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
485 # Metadata from INFORMATION_SCHEMA.TABLES
486 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
487 test/t1 6 0 3 2 3 4
488 # Metadata from INFORMATION_SCHEMA.COLUMNS
489 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
490 c1 0 13 16712190 0 0 0
491 c4 1 13 16711934 1 2 0
492 c3 2 13 16711934 1 1 0
493 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3
494 # DD Metadata of table
495 NAME SE_PRIVATE_DATA
496 t1 discard=0;
497 # DD Metadata of columns in table
498 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
499 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
500 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
501 c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
502 DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
503 DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
504 !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;version_dropped=3;
505 SELECT * FROM t1 ORDER BY c1;
506 c1 c4 c3
507 row1_c1 NULL default_c3
508 row2_c1 NULL row2_c3
509 row3_c1 row3_c4 row3_c3
510 INSERT INTO t1 values ("row4_c1", "row4_c4", "row4_c3");
511 SELECT * FROM t1 ORDER BY c1;
512 c1 c4 c3
513 row1_c1 NULL default_c3
514 row2_c1 NULL row2_c3
515 row3_c1 row3_c4 row3_c3
516 row4_c1 row4_c4 row4_c3
517 # ------------------------------------------------------------
518 # Scenario 2.1 : Same table with tablespace discarded
519 # ------------------------------------------------------------
520 # EXPORT
521 FLUSH TABLE t1 FOR EXPORT;
522 # Copy cfg/ibd files into temp location
523 UNLOCK TABLES;
524 # IMPORT
525 ALTER TABLE t1 DISCARD TABLESPACE;
526 # Copy cfg/ibd files from temp location
527 ALTER TABLE t1 IMPORT TABLESPACE;
528 SELECT * FROM t1 ORDER BY c1;
529 c1 c4 c3
530 row1_c1 NULL default_c3
531 row2_c1 NULL row2_c3
532 row3_c1 row3_c4 row3_c3
533 row4_c1 row4_c4 row4_c3
534 # Metadata from INFORMATION_SCHEMA.TABLES
535 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
536 test/t1 6 0 3 2 3 4
537 # Metadata from INFORMATION_SCHEMA.COLUMNS
538 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
539 c1 0 13 16712190 0 0 0
540 c4 1 13 16711934 1 2 0
541 c3 2 13 16711934 1 1 0
542 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3
543 # DD Metadata of table
544 NAME SE_PRIVATE_DATA
545 t1 discard=0;
546 # DD Metadata of columns in table
547 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
548 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
549 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
550 c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
551 DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
552 DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
553 !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3;
554 # ------------------------------------------------------------
555 # Scenario 2.2 : New table created with like
556 # ------------------------------------------------------------
557 # EXPORT
558 FLUSH TABLE t1 FOR EXPORT;
559 # Copy cfg/ibd files into temp location
560 UNLOCK TABLES;
561 CREATE TABLE t2 like t1;
562 DROP TABLE t1;
563 ALTER TABLE t2 RENAME to t1;
564 SHOW CREATE TABLE t1;
565 Table Create Table
566 t1 CREATE TABLE `t1` (
567 `c1` char(20) NOT NULL,
568 `c4` char(20) DEFAULT NULL,
569 `c3` char(20) DEFAULT 'default_c3',
570 PRIMARY KEY (`c1`)
571 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
572 # IMPORT
573 ALTER TABLE t1 DISCARD TABLESPACE;
574 # Copy cfg/ibd files from temp location
575 ALTER TABLE t1 IMPORT TABLESPACE;
576 SELECT * FROM t1;
577 c1 c4 c3
578 row1_c1 NULL default_c3
579 row2_c1 NULL row2_c3
580 row3_c1 row3_c4 row3_c3
581 row4_c1 row4_c4 row4_c3
582 # Metadata from INFORMATION_SCHEMA.TABLES
583 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
584 test/t1 6 0 3 2 3 4
585 # Metadata from INFORMATION_SCHEMA.COLUMNS
586 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
587 c1 0 13 16712190 0 0 0
588 c4 1 13 16711934 1 2 0
589 c3 2 13 16711934 1 1 0
590 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3
591 # DD Metadata of table
592 NAME SE_PRIVATE_DATA
593 t1 discard=0;
594 # DD Metadata of columns in table
595 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
596 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
597 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
598 c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
599 DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
600 DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
601 !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3;
602 DROP TABLE t1;
603 # ------------------------------------------------------------
604 # Scenarios 3 : Error scenarios
605 # ------------------------------------------------------------
606 # ------------------------------------------------------------
607 # Scenario 3.1 : Target table has INSTANT, source doesn't
608 # ------------------------------------------------------------
609 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;;
610 INSERT INTO t1 values ("row1_c1", "row1_c2");
611 SELECT * FROM t1 ORDER BY c1;
612 c1 c2
613 row1_c1 row1_c2
614 # EXPORT
615 FLUSH TABLE t1 FOR EXPORT;
616 # Copy cfg/ibd files into temp location
617 UNLOCK TABLES;
618 DROP TABLE t1;
619 CREATE TABLE t1 (c1 char(20) KEY) ROW_FORMAT=DYNAMIC;;
620 ALTER TABLE t1 ADD COLUMN c2 char(20) DEFAULT "def_c2", ALGORITHM=INSTANT;
621 # IMPORT
622 ALTER TABLE t1 DISCARD TABLESPACE;
623 # Copy cfg/ibd files from temp location
624 ALTER TABLE t1 IMPORT TABLESPACE;
625 ERROR HY000: Schema mismatch (The .cfg file indicates no INSTANT column in the source table whereas the metadata in data dictionary says there are instant columns in the target table)
626 DROP TABLE t1;
627 # ------------------------------------------------------------
628 # Scenario 3.2 : Both has INSTANT, but different instant version
629 # ------------------------------------------------------------
630 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;;
631 INSERT INTO t1 values ("row1_c1", "row1_c2");
632 SELECT * FROM t1 ORDER BY c1;
633 c1 c2
634 row1_c1 row1_c2
635 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
636 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
637 SELECT * FROM t1 ORDER BY c1;
638 c1 c2 c3
639 row1_c1 row1_c2 default_c3
640 row2_c1 row2_c2 row2_c3
641 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
642 INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3");
643 SELECT * FROM t1 ORDER BY c1;
644 c1 c4 c2 c3
645 row1_c1 NULL row1_c2 default_c3
646 row2_c1 NULL row2_c2 row2_c3
647 row3_c1 row3_c4 row3_c2 row3_c3
648 # EXPORT
649 FLUSH TABLE t1 FOR EXPORT;
650 # Copy cfg/ibd files into temp location
651 UNLOCK TABLES;
652 DROP TABLE t1;
653 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=DYNAMIC;;
654 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
655 # IMPORT
656 ALTER TABLE t1 DISCARD TABLESPACE;
657 # Copy cfg/ibd files from temp location
658 ALTER TABLE t1 IMPORT TABLESPACE;
659 ERROR HY000: Schema mismatch (Table has instant column but current row version didn't match.)
660 DROP TABLE t1;
661 # ------------------------------------------------------------
662 # Scenario 3.3 : Both has INSTANT, but different col count
663 # ------------------------------------------------------------
664 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;;
665 INSERT INTO t1 values ("row1_c1", "row1_c2");
666 SELECT * FROM t1 ORDER BY c1;
667 c1 c2
668 row1_c1 row1_c2
669 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
670 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
671 SELECT * FROM t1 ORDER BY c1;
672 c1 c2 c3
673 row1_c1 row1_c2 default_c3
674 row2_c1 row2_c2 row2_c3
675 # EXPORT
676 FLUSH TABLE t1 FOR EXPORT;
677 # Copy cfg/ibd files into temp location
678 UNLOCK TABLES;
679 DROP TABLE t1;
680 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=DYNAMIC;;
681 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
682 # IMPORT
683 ALTER TABLE t1 DISCARD TABLESPACE;
684 # Copy cfg/ibd files from temp location
685 ALTER TABLE t1 IMPORT TABLESPACE;
686 ERROR HY000: Schema mismatch (Number of columns don't match, table has 7 columns but the tablespace meta-data file has 6 columns)
687 DROP TABLE t1;
688 # ------------------------------------------------------------
689 # Scenario 3.4 : Both has INSTANT, but different col count
690 # ------------------------------------------------------------
691 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;;
692 INSERT INTO t1 values ("row1_c1", "row1_c2");
693 SELECT * FROM t1 ORDER BY c1;
694 c1 c2
695 row1_c1 row1_c2
696 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
697 INSERT INTO t1 values ("row2_c1", "row2_c4", "row2_c2", "row2_c3");
698 SELECT * FROM t1 ORDER BY c1;
699 c1 c4 c2 c3
700 row1_c1 NULL row1_c2 default_c3
701 row2_c1 row2_c4 row2_c2 row2_c3
702 # EXPORT
703 FLUSH TABLE t1 FOR EXPORT;
704 # Copy cfg/ibd files into temp location
705 UNLOCK TABLES;
706 DROP TABLE t1;
707 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=DYNAMIC;;
708 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
709 # IMPORT
710 ALTER TABLE t1 DISCARD TABLESPACE;
711 # Copy cfg/ibd files from temp location
712 ALTER TABLE t1 IMPORT TABLESPACE;
713 ERROR HY000: Schema mismatch (Table has instant column but column counts didn't match.)
714 DROP TABLE t1;
715 # ------------------------------------------------------------
716 # Scenario 3.5 : Both has INSTANT, but different default value
717 # ------------------------------------------------------------
718 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;;
719 INSERT INTO t1 values ("row1_c1", "row1_c2");
720 SELECT * FROM t1 ORDER BY c1;
721 c1 c2
722 row1_c1 row1_c2
723 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
724 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
725 SELECT * FROM t1 ORDER BY c1;
726 c1 c2 c3
727 row1_c1 row1_c2 default_c3
728 row2_c1 row2_c2 row2_c3
729 # EXPORT
730 FLUSH TABLE t1 FOR EXPORT;
731 # Copy cfg/ibd files into temp location
732 UNLOCK TABLES;
733 DROP TABLE t1;
734 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=DYNAMIC;;
735 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "newdefault_c3", ALGORITHM=INSTANT;
736 # IMPORT
737 ALTER TABLE t1 DISCARD TABLESPACE;
738 # Copy cfg/ibd files from temp location
739 ALTER TABLE t1 IMPORT TABLESPACE;
740 ERROR HY000: Schema mismatch (The metadata in the data dictionary and the .cfg file contain different default values for column c3!)
741 DROP TABLE t1;
742 ###########
743 # CLEANUP #
744 ###########
745 ############################################
746 # Test instant ADD/DROP COLUMN for COMPACT format
747 ############################################
748 # Create a table with 2 columns. [c1, c2]
749 CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=COMPACT;;
750 INSERT INTO t1 values ("row1_c1", "a");
751 SELECT * FROM t1 ORDER BY c1;
752 c1 c2
753 row1_c1 a
754 # Add a new column at the end. [c1, c2, +c3]
755 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
756 INSERT INTO t1 values ("row2_c1", "b", "row2_c3");
757 SELECT * FROM t1 ORDER BY c1;
758 c1 c2 c3
759 row1_c1 a default_c3
760 row2_c1 b row2_c3
761 # Add a new column in between. [c1, +c4, c2, c3]
762 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
763 INSERT INTO t1 values ("row3_c1", "row3_c4", "c", "row3_c3");
764 SELECT * FROM t1 ORDER BY c1;
765 c1 c4 c2 c3
766 row1_c1 NULL a default_c3
767 row2_c1 NULL b row2_c3
768 row3_c1 row3_c4 c row3_c3
769 # ------------------------------------------------------------
770 # Scenario 1.1 : Same table with tablespace discarded
771 # ------------------------------------------------------------
772 # EXPORT
773 FLUSH TABLE t1 FOR EXPORT;
774 # Copy cfg/ibd files into temp location
775 UNLOCK TABLES;
776 # IMPORT
777 ALTER TABLE t1 DISCARD TABLESPACE;
778 # Copy cfg/ibd files from temp location
779 ALTER TABLE t1 IMPORT TABLESPACE;
780 SELECT * FROM t1 ORDER BY c1;
781 c1 c4 c2 c3
782 row1_c1 NULL a default_c3
783 row2_c1 NULL b row2_c3
784 row3_c1 row3_c4 c row3_c3
785 # Metadata from INFORMATION_SCHEMA.TABLES
786 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
787 test/t1 7 0 2 2 4 4
788 # Metadata from INFORMATION_SCHEMA.COLUMNS
789 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
790 c1 0 13 16712190 0 0 0
791 c4 1 13 16711934 1 2 0
792 c2 2 6 766 0 0 0
793 c3 3 13 16711934 1 1 0
794 # DD Metadata of table
795 NAME SE_PRIVATE_DATA
796 t1 discard=0;
797 # DD Metadata of columns in table
798 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
799 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
800 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
801 c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID;
802 c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
803 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
804 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
805 # ------------------------------------------------------------
806 # Scenario 1.2 : New table created with like
807 # ------------------------------------------------------------
808 # EXPORT
809 FLUSH TABLE t1 FOR EXPORT;
810 # Copy cfg/ibd files into temp location
811 UNLOCK TABLES;
812 CREATE TABLE t2 like t1;
813 DROP TABLE t1;
814 ALTER TABLE t2 RENAME to t1;
815 SHOW CREATE TABLE t1;
816 Table Create Table
817 t1 CREATE TABLE `t1` (
818 `c1` char(20) NOT NULL,
819 `c4` char(20) DEFAULT NULL,
820 `c2` enum('a','b','c') DEFAULT NULL,
821 `c3` char(20) DEFAULT 'default_c3',
822 PRIMARY KEY (`c1`)
823 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
824 # IMPORT
825 ALTER TABLE t1 DISCARD TABLESPACE;
826 # Copy cfg/ibd files from temp location
827 ALTER TABLE t1 IMPORT TABLESPACE;
828 SELECT * FROM t1;
829 c1 c4 c2 c3
830 row1_c1 NULL a default_c3
831 row2_c1 NULL b row2_c3
832 row3_c1 row3_c4 c row3_c3
833 # Metadata from INFORMATION_SCHEMA.TABLES
834 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
835 test/t1 7 0 2 2 4 4
836 # Metadata from INFORMATION_SCHEMA.COLUMNS
837 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
838 c1 0 13 16712190 0 0 0
839 c4 1 13 16711934 1 2 0
840 c2 2 6 766 0 0 0
841 c3 3 13 16711934 1 1 0
842 # DD Metadata of table
843 NAME SE_PRIVATE_DATA
844 t1 discard=0;
845 # DD Metadata of columns in table
846 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
847 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
848 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
849 c2 3 MYSQL_TYPE_ENUM 0 Visible physical_pos=3;table_id=TABLE_ID;
850 c3 4 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
851 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
852 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
853 # ------------------------------------------------------------
854 # Drop a column somewhere in between. [c1, c4, -c2, c3]
855 # ------------------------------------------------------------
856 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
857 # Metadata from INFORMATION_SCHEMA.TABLES
858 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
859 test/t1 6 0 3 2 3 4
860 # Metadata from INFORMATION_SCHEMA.COLUMNS
861 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
862 c1 0 13 16712190 0 0 0
863 c4 1 13 16711934 1 2 0
864 c3 2 13 16711934 1 1 0
865 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3
866 # DD Metadata of table
867 NAME SE_PRIVATE_DATA
868 t1 discard=0;
869 # DD Metadata of columns in table
870 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
871 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
872 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
873 c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
874 DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
875 DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
876 !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;version_dropped=3;
877 SELECT * FROM t1 ORDER BY c1;
878 c1 c4 c3
879 row1_c1 NULL default_c3
880 row2_c1 NULL row2_c3
881 row3_c1 row3_c4 row3_c3
882 INSERT INTO t1 values ("row4_c1", "row4_c4", "row4_c3");
883 SELECT * FROM t1 ORDER BY c1;
884 c1 c4 c3
885 row1_c1 NULL default_c3
886 row2_c1 NULL row2_c3
887 row3_c1 row3_c4 row3_c3
888 row4_c1 row4_c4 row4_c3
889 # ------------------------------------------------------------
890 # Scenario 2.1 : Same table with tablespace discarded
891 # ------------------------------------------------------------
892 # EXPORT
893 FLUSH TABLE t1 FOR EXPORT;
894 # Copy cfg/ibd files into temp location
895 UNLOCK TABLES;
896 # IMPORT
897 ALTER TABLE t1 DISCARD TABLESPACE;
898 # Copy cfg/ibd files from temp location
899 ALTER TABLE t1 IMPORT TABLESPACE;
900 SELECT * FROM t1 ORDER BY c1;
901 c1 c4 c3
902 row1_c1 NULL default_c3
903 row2_c1 NULL row2_c3
904 row3_c1 row3_c4 row3_c3
905 row4_c1 row4_c4 row4_c3
906 # Metadata from INFORMATION_SCHEMA.TABLES
907 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
908 test/t1 6 0 3 2 3 4
909 # Metadata from INFORMATION_SCHEMA.COLUMNS
910 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
911 c1 0 13 16712190 0 0 0
912 c4 1 13 16711934 1 2 0
913 c3 2 13 16711934 1 1 0
914 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3
915 # DD Metadata of table
916 NAME SE_PRIVATE_DATA
917 t1 discard=0;
918 # DD Metadata of columns in table
919 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
920 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
921 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
922 c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
923 DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
924 DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
925 !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3;
926 # ------------------------------------------------------------
927 # Scenario 2.2 : New table created with like
928 # ------------------------------------------------------------
929 # EXPORT
930 FLUSH TABLE t1 FOR EXPORT;
931 # Copy cfg/ibd files into temp location
932 UNLOCK TABLES;
933 CREATE TABLE t2 like t1;
934 DROP TABLE t1;
935 ALTER TABLE t2 RENAME to t1;
936 SHOW CREATE TABLE t1;
937 Table Create Table
938 t1 CREATE TABLE `t1` (
939 `c1` char(20) NOT NULL,
940 `c4` char(20) DEFAULT NULL,
941 `c3` char(20) DEFAULT 'default_c3',
942 PRIMARY KEY (`c1`)
943 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
944 # IMPORT
945 ALTER TABLE t1 DISCARD TABLESPACE;
946 # Copy cfg/ibd files from temp location
947 ALTER TABLE t1 IMPORT TABLESPACE;
948 SELECT * FROM t1;
949 c1 c4 c3
950 row1_c1 NULL default_c3
951 row2_c1 NULL row2_c3
952 row3_c1 row3_c4 row3_c3
953 row4_c1 row4_c4 row4_c3
954 # Metadata from INFORMATION_SCHEMA.TABLES
955 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
956 test/t1 6 0 3 2 3 4
957 # Metadata from INFORMATION_SCHEMA.COLUMNS
958 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
959 c1 0 13 16712190 0 0 0
960 c4 1 13 16711934 1 2 0
961 c3 2 13 16711934 1 1 0
962 !hidden!_dropped_v3_p3_c2 6 6 759 0 0 3
963 # DD Metadata of table
964 NAME SE_PRIVATE_DATA
965 t1 discard=0;
966 # DD Metadata of columns in table
967 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
968 c1 1 MYSQL_TYPE_STRING 1 Visible physical_pos=0;table_id=TABLE_ID;
969 c4 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=5;table_id=TABLE_ID;version_added=2;
970 c3 3 MYSQL_TYPE_STRING 0 Visible default=64656661756c745f633320202020202020202020;physical_pos=4;table_id=TABLE_ID;version_added=1;
971 DB_TRX_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
972 DB_ROLL_PTR 5 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
973 !hidden!_dropped_v3_p3_c2 6 MYSQL_TYPE_ENUM 0 SE physical_pos=3;table_id=TABLE_ID;version_dropped=3;
974 DROP TABLE t1;
975 # ------------------------------------------------------------
976 # Scenarios 3 : Error scenarios
977 # ------------------------------------------------------------
978 # ------------------------------------------------------------
979 # Scenario 3.1 : Target table has INSTANT, source doesn't
980 # ------------------------------------------------------------
981 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;;
982 INSERT INTO t1 values ("row1_c1", "row1_c2");
983 SELECT * FROM t1 ORDER BY c1;
984 c1 c2
985 row1_c1 row1_c2
986 # EXPORT
987 FLUSH TABLE t1 FOR EXPORT;
988 # Copy cfg/ibd files into temp location
989 UNLOCK TABLES;
990 DROP TABLE t1;
991 CREATE TABLE t1 (c1 char(20) KEY) ROW_FORMAT=COMPACT;;
992 ALTER TABLE t1 ADD COLUMN c2 char(20) DEFAULT "def_c2", ALGORITHM=INSTANT;
993 # IMPORT
994 ALTER TABLE t1 DISCARD TABLESPACE;
995 # Copy cfg/ibd files from temp location
996 ALTER TABLE t1 IMPORT TABLESPACE;
997 ERROR HY000: Schema mismatch (The .cfg file indicates no INSTANT column in the source table whereas the metadata in data dictionary says there are instant columns in the target table)
998 DROP TABLE t1;
999 # ------------------------------------------------------------
1000 # Scenario 3.2 : Both has INSTANT, but different instant version
1001 # ------------------------------------------------------------
1002 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;;
1003 INSERT INTO t1 values ("row1_c1", "row1_c2");
1004 SELECT * FROM t1 ORDER BY c1;
1005 c1 c2
1006 row1_c1 row1_c2
1007 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
1008 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
1009 SELECT * FROM t1 ORDER BY c1;
1010 c1 c2 c3
1011 row1_c1 row1_c2 default_c3
1012 row2_c1 row2_c2 row2_c3
1013 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
1014 INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3");
1015 SELECT * FROM t1 ORDER BY c1;
1016 c1 c4 c2 c3
1017 row1_c1 NULL row1_c2 default_c3
1018 row2_c1 NULL row2_c2 row2_c3
1019 row3_c1 row3_c4 row3_c2 row3_c3
1020 # EXPORT
1021 FLUSH TABLE t1 FOR EXPORT;
1022 # Copy cfg/ibd files into temp location
1023 UNLOCK TABLES;
1024 DROP TABLE t1;
1025 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=COMPACT;;
1026 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
1027 # IMPORT
1028 ALTER TABLE t1 DISCARD TABLESPACE;
1029 # Copy cfg/ibd files from temp location
1030 ALTER TABLE t1 IMPORT TABLESPACE;
1031 ERROR HY000: Schema mismatch (Table has instant column but current row version didn't match.)
1032 DROP TABLE t1;
1033 # ------------------------------------------------------------
1034 # Scenario 3.3 : Both has INSTANT, but different col count
1035 # ------------------------------------------------------------
1036 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;;
1037 INSERT INTO t1 values ("row1_c1", "row1_c2");
1038 SELECT * FROM t1 ORDER BY c1;
1039 c1 c2
1040 row1_c1 row1_c2
1041 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
1042 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
1043 SELECT * FROM t1 ORDER BY c1;
1044 c1 c2 c3
1045 row1_c1 row1_c2 default_c3
1046 row2_c1 row2_c2 row2_c3
1047 # EXPORT
1048 FLUSH TABLE t1 FOR EXPORT;
1049 # Copy cfg/ibd files into temp location
1050 UNLOCK TABLES;
1051 DROP TABLE t1;
1052 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=COMPACT;;
1053 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
1054 # IMPORT
1055 ALTER TABLE t1 DISCARD TABLESPACE;
1056 # Copy cfg/ibd files from temp location
1057 ALTER TABLE t1 IMPORT TABLESPACE;
1058 ERROR HY000: Schema mismatch (Number of columns don't match, table has 7 columns but the tablespace meta-data file has 6 columns)
1059 DROP TABLE t1;
1060 # ------------------------------------------------------------
1061 # Scenario 3.4 : Both has INSTANT, but different col count
1062 # ------------------------------------------------------------
1063 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;;
1064 INSERT INTO t1 values ("row1_c1", "row1_c2");
1065 SELECT * FROM t1 ORDER BY c1;
1066 c1 c2
1067 row1_c1 row1_c2
1068 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
1069 INSERT INTO t1 values ("row2_c1", "row2_c4", "row2_c2", "row2_c3");
1070 SELECT * FROM t1 ORDER BY c1;
1071 c1 c4 c2 c3
1072 row1_c1 NULL row1_c2 default_c3
1073 row2_c1 row2_c4 row2_c2 row2_c3
1074 # EXPORT
1075 FLUSH TABLE t1 FOR EXPORT;
1076 # Copy cfg/ibd files into temp location
1077 UNLOCK TABLES;
1078 DROP TABLE t1;
1079 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20), c3 char(20) DEFAULT "default_c3") ROW_FORMAT=COMPACT;;
1080 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
1081 # IMPORT
1082 ALTER TABLE t1 DISCARD TABLESPACE;
1083 # Copy cfg/ibd files from temp location
1084 ALTER TABLE t1 IMPORT TABLESPACE;
1085 ERROR HY000: Schema mismatch (Table has instant column but column counts didn't match.)
1086 DROP TABLE t1;
1087 # ------------------------------------------------------------
1088 # Scenario 3.5 : Both has INSTANT, but different default value
1089 # ------------------------------------------------------------
1090 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;;
1091 INSERT INTO t1 values ("row1_c1", "row1_c2");
1092 SELECT * FROM t1 ORDER BY c1;
1093 c1 c2
1094 row1_c1 row1_c2
1095 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
1096 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
1097 SELECT * FROM t1 ORDER BY c1;
1098 c1 c2 c3
1099 row1_c1 row1_c2 default_c3
1100 row2_c1 row2_c2 row2_c3
1101 # EXPORT
1102 FLUSH TABLE t1 FOR EXPORT;
1103 # Copy cfg/ibd files into temp location
1104 UNLOCK TABLES;
1105 DROP TABLE t1;
1106 CREATE TABLE t1 (c1 char(20) KEY, c2 char(20)) ROW_FORMAT=COMPACT;;
1107 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "newdefault_c3", ALGORITHM=INSTANT;
1108 # IMPORT
1109 ALTER TABLE t1 DISCARD TABLESPACE;
1110 # Copy cfg/ibd files from temp location
1111 ALTER TABLE t1 IMPORT TABLESPACE;
1112 ERROR HY000: Schema mismatch (The metadata in the data dictionary and the .cfg file contain different default values for column c3!)
1113 DROP TABLE t1;
1114 ###########
1115 # CLEANUP #
1116 ###########