Debian Codegraph @

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