Debian Codegraph @

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

raw
1 # Create a table with 4 columns. [c1, c2, c3, c4]
2 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=redundant PARTITION BY RANGE(c1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE);
3 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
4 SELECT * from t1;
5 c1 c2 c3 c4
6 1 2 3 abcdefg
7 100 200 300 qwerty
8 200 300 400 asdfg
9 # Add a new column at the end. [c1, c2, c3, c4, +c5]
10 ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT;
11 INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ;
12 SELECT * FROM t1 ORDER BY c1;
13 c1 c2 c3 c4 c5
14 1 2 3 abcdefg c5_def
15 100 200 300 qwerty c5_def
16 200 300 400 asdfg c5_def
17 300 400 500 xxxxxx r4c5
18 # Add a new column in between. [c1, +c6, c2, c3, c4, c5]
19 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
20 INSERT INTO t1 VALUES(400, "r5c6", 500, 600, "xxxxxx", "r5c5") ;
21 SELECT * FROM t1 ORDER BY c1;
22 c1 c6 c2 c3 c4 c5
23 1 NULL 2 3 abcdefg c5_def
24 100 NULL 200 300 qwerty c5_def
25 200 NULL 300 400 asdfg c5_def
26 300 NULL 400 500 xxxxxx r4c5
27 400 r5c6 500 600 xxxxxx r5c5
28 # Metadata from INFORMATION_SCHEMA.TABLES
29 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
30 test/t1#p#p0 9 0 2
31 # Metadata from INFORMATION_SCHEMA.COLUMNS
32 NAME POS MTYPE PRTYPE HAS_DEFAULT
33 c1 0 6 1027 0
34 c6 1 13 16711934 1
35 c2 2 6 1027 0
36 c3 3 6 1027 0
37 c4 4 5 16711932 0
38 c5 5 13 16711934 1
39 # Metadata from INFORMATION_SCHEMA.TABLES
40 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
41 test/t1#p#p1 9 0 2
42 # Metadata from INFORMATION_SCHEMA.COLUMNS
43 NAME POS MTYPE PRTYPE HAS_DEFAULT
44 c1 0 6 1027 0
45 c6 1 13 16711934 1
46 c2 2 6 1027 0
47 c3 3 6 1027 0
48 c4 4 5 16711932 0
49 c5 5 13 16711934 1
50 # Metadata from INFORMATION_SCHEMA.TABLES
51 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
52 test/t1#p#p2 9 0 2
53 # Metadata from INFORMATION_SCHEMA.COLUMNS
54 NAME POS MTYPE PRTYPE HAS_DEFAULT
55 c1 0 6 1027 0
56 c6 1 13 16711934 1
57 c2 2 6 1027 0
58 c3 3 6 1027 0
59 c4 4 5 16711932 0
60 c5 5 13 16711934 1
61 # Metadata from INFORMATION_SCHEMA.TABLES
62 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
63 test/t1#p#p3 9 0 2
64 # Metadata from INFORMATION_SCHEMA.COLUMNS
65 NAME POS MTYPE PRTYPE HAS_DEFAULT
66 c1 0 6 1027 0
67 c6 1 13 16711934 1
68 c2 2 6 1027 0
69 c3 3 6 1027 0
70 c4 4 5 16711932 0
71 c5 5 13 16711934 1
72 # ------------------------------------------------------------
73 # Scenario 1.1 : Same table with tablespace discarded
74 # ------------------------------------------------------------
75 # EXPORT
76 FLUSH TABLE t1 FOR EXPORT;
77 backup: t1#p#p0
78 backup: t1#p#p1
79 backup: t1#p#p2
80 backup: t1#p#p3
81 UNLOCK TABLES;
82 # IMPORT
83 ALTER TABLE t1 discard tablespace;
84 restore: t1#p#p0 .ibd and .cfg files
85 restore: t1#p#p1 .ibd and .cfg files
86 restore: t1#p#p2 .ibd and .cfg files
87 restore: t1#p#p3 .ibd and .cfg files
88 ALTER TABLE t1 IMPORT TABLESPACE;
89 SELECT * FROM t1 ORDER BY c1;
90 c1 c6 c2 c3 c4 c5
91 1 NULL 2 3 abcdefg c5_def
92 100 NULL 200 300 qwerty c5_def
93 200 NULL 300 400 asdfg c5_def
94 300 NULL 400 500 xxxxxx r4c5
95 400 r5c6 500 600 xxxxxx r5c5
96 # Metadata from INFORMATION_SCHEMA.TABLES
97 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
98 test/t1#p#p0 9 0 2
99 # Metadata from INFORMATION_SCHEMA.COLUMNS
100 NAME POS MTYPE PRTYPE HAS_DEFAULT
101 c1 0 6 1027 0
102 c6 1 13 16711934 1
103 c2 2 6 1027 0
104 c3 3 6 1027 0
105 c4 4 5 16711932 0
106 c5 5 13 16711934 1
107 # Metadata from INFORMATION_SCHEMA.TABLES
108 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
109 test/t1#p#p1 9 0 2
110 # Metadata from INFORMATION_SCHEMA.COLUMNS
111 NAME POS MTYPE PRTYPE HAS_DEFAULT
112 c1 0 6 1027 0
113 c6 1 13 16711934 1
114 c2 2 6 1027 0
115 c3 3 6 1027 0
116 c4 4 5 16711932 0
117 c5 5 13 16711934 1
118 # Metadata from INFORMATION_SCHEMA.TABLES
119 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
120 test/t1#p#p2 9 0 2
121 # Metadata from INFORMATION_SCHEMA.COLUMNS
122 NAME POS MTYPE PRTYPE HAS_DEFAULT
123 c1 0 6 1027 0
124 c6 1 13 16711934 1
125 c2 2 6 1027 0
126 c3 3 6 1027 0
127 c4 4 5 16711932 0
128 c5 5 13 16711934 1
129 # Metadata from INFORMATION_SCHEMA.TABLES
130 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
131 test/t1#p#p3 9 0 2
132 # Metadata from INFORMATION_SCHEMA.COLUMNS
133 NAME POS MTYPE PRTYPE HAS_DEFAULT
134 c1 0 6 1027 0
135 c6 1 13 16711934 1
136 c2 2 6 1027 0
137 c3 3 6 1027 0
138 c4 4 5 16711932 0
139 c5 5 13 16711934 1
140 # ------------------------------------------------------------
141 # Scenario 1.2 : New table created with like
142 # ------------------------------------------------------------
143 # EXPORT
144 FLUSH TABLE t1 FOR EXPORT;
145 backup: t1#p#p0
146 backup: t1#p#p1
147 backup: t1#p#p2
148 backup: t1#p#p3
149 UNLOCK TABLES;
150 CREATE TABLE t2 like t1;
151 SHOW CREATE TABLE t2;
152 Table Create Table
153 t2 CREATE TABLE `t2` (
154 `c1` int DEFAULT NULL,
155 `c6` char(20) DEFAULT NULL,
156 `c2` int DEFAULT NULL,
157 `c3` int DEFAULT NULL,
158 `c4` text,
159 `c5` char(20) DEFAULT 'c5_def'
160 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
161 /*!50100 PARTITION BY RANGE ((`c1` * 2))
162 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
163 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
164 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
165 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
166 DROP TABLE t1;
167 ALTER TABLE t2 RENAME as t1;
168 # IMPORT
169 ALTER TABLE t1 discard tablespace;
170 restore: t1#p#p0 .ibd and .cfg files
171 restore: t1#p#p1 .ibd and .cfg files
172 restore: t1#p#p2 .ibd and .cfg files
173 restore: t1#p#p3 .ibd and .cfg files
174 ALTER TABLE t1 IMPORT TABLESPACE;
175 SELECT * FROM t1 ORDER BY c1;
176 c1 c6 c2 c3 c4 c5
177 1 NULL 2 3 abcdefg c5_def
178 100 NULL 200 300 qwerty c5_def
179 200 NULL 300 400 asdfg c5_def
180 300 NULL 400 500 xxxxxx r4c5
181 400 r5c6 500 600 xxxxxx r5c5
182 # Metadata from INFORMATION_SCHEMA.TABLES
183 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
184 test/t1#p#p0 9 0 2
185 # Metadata from INFORMATION_SCHEMA.COLUMNS
186 NAME POS MTYPE PRTYPE HAS_DEFAULT
187 c1 0 6 1027 0
188 c6 1 13 16711934 1
189 c2 2 6 1027 0
190 c3 3 6 1027 0
191 c4 4 5 16711932 0
192 c5 5 13 16711934 1
193 # Metadata from INFORMATION_SCHEMA.TABLES
194 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
195 test/t1#p#p1 9 0 2
196 # Metadata from INFORMATION_SCHEMA.COLUMNS
197 NAME POS MTYPE PRTYPE HAS_DEFAULT
198 c1 0 6 1027 0
199 c6 1 13 16711934 1
200 c2 2 6 1027 0
201 c3 3 6 1027 0
202 c4 4 5 16711932 0
203 c5 5 13 16711934 1
204 # Metadata from INFORMATION_SCHEMA.TABLES
205 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
206 test/t1#p#p2 9 0 2
207 # Metadata from INFORMATION_SCHEMA.COLUMNS
208 NAME POS MTYPE PRTYPE HAS_DEFAULT
209 c1 0 6 1027 0
210 c6 1 13 16711934 1
211 c2 2 6 1027 0
212 c3 3 6 1027 0
213 c4 4 5 16711932 0
214 c5 5 13 16711934 1
215 # Metadata from INFORMATION_SCHEMA.TABLES
216 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
217 test/t1#p#p3 9 0 2
218 # Metadata from INFORMATION_SCHEMA.COLUMNS
219 NAME POS MTYPE PRTYPE HAS_DEFAULT
220 c1 0 6 1027 0
221 c6 1 13 16711934 1
222 c2 2 6 1027 0
223 c3 3 6 1027 0
224 c4 4 5 16711932 0
225 c5 5 13 16711934 1
226 # ------------------------------------------------------------
227 # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5]
228 # ------------------------------------------------------------
229 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
230 # Metadata from INFORMATION_SCHEMA.TABLES
231 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
232 test/t1#p#p0 8 0 3
233 # Metadata from INFORMATION_SCHEMA.COLUMNS
234 NAME POS MTYPE PRTYPE HAS_DEFAULT
235 c1 0 6 1027 0
236 c6 1 13 16711934 1
237 c3 2 6 1027 0
238 c4 3 5 16711932 0
239 c5 4 13 16711934 1
240 # Metadata from INFORMATION_SCHEMA.TABLES
241 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
242 test/t1#p#p1 8 0 3
243 # Metadata from INFORMATION_SCHEMA.COLUMNS
244 NAME POS MTYPE PRTYPE HAS_DEFAULT
245 c1 0 6 1027 0
246 c6 1 13 16711934 1
247 c3 2 6 1027 0
248 c4 3 5 16711932 0
249 c5 4 13 16711934 1
250 # Metadata from INFORMATION_SCHEMA.TABLES
251 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
252 test/t1#p#p2 8 0 3
253 # Metadata from INFORMATION_SCHEMA.COLUMNS
254 NAME POS MTYPE PRTYPE HAS_DEFAULT
255 c1 0 6 1027 0
256 c6 1 13 16711934 1
257 c3 2 6 1027 0
258 c4 3 5 16711932 0
259 c5 4 13 16711934 1
260 # Metadata from INFORMATION_SCHEMA.TABLES
261 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
262 test/t1#p#p3 8 0 3
263 # Metadata from INFORMATION_SCHEMA.COLUMNS
264 NAME POS MTYPE PRTYPE HAS_DEFAULT
265 c1 0 6 1027 0
266 c6 1 13 16711934 1
267 c3 2 6 1027 0
268 c4 3 5 16711932 0
269 c5 4 13 16711934 1
270 SELECT * FROM t1 ORDER BY c1;
271 c1 c6 c3 c4 c5
272 1 NULL 3 abcdefg c5_def
273 100 NULL 300 qwerty c5_def
274 200 NULL 400 asdfg c5_def
275 300 NULL 500 xxxxxx r4c5
276 400 r5c6 600 xxxxxx r5c5
277 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ;
278 SELECT * FROM t1 ORDER BY c1;
279 c1 c6 c3 c4 c5
280 1 NULL 3 abcdefg c5_def
281 100 NULL 300 qwerty c5_def
282 200 NULL 400 asdfg c5_def
283 300 NULL 500 xxxxxx r4c5
284 400 r5c6 600 xxxxxx r5c5
285 500 r6c6 700 xxxxxx r6c5
286 # ------------------------------------------------------------
287 # Scenario 2.1 : Same table with tablespace discarded
288 # ------------------------------------------------------------
289 # EXPORT
290 FLUSH TABLE t1 FOR EXPORT;
291 backup: t1#p#p0
292 backup: t1#p#p1
293 backup: t1#p#p2
294 backup: t1#p#p3
295 UNLOCK TABLES;
296 # IMPORT
297 ALTER TABLE t1 discard tablespace;
298 restore: t1#p#p0 .ibd and .cfg files
299 restore: t1#p#p1 .ibd and .cfg files
300 restore: t1#p#p2 .ibd and .cfg files
301 restore: t1#p#p3 .ibd and .cfg files
302 ALTER TABLE t1 IMPORT TABLESPACE;
303 SELECT * FROM t1 ORDER BY c1;
304 c1 c6 c3 c4 c5
305 1 NULL 3 abcdefg c5_def
306 100 NULL 300 qwerty c5_def
307 200 NULL 400 asdfg c5_def
308 300 NULL 500 xxxxxx r4c5
309 400 r5c6 600 xxxxxx r5c5
310 500 r6c6 700 xxxxxx r6c5
311 # Metadata from INFORMATION_SCHEMA.TABLES
312 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
313 test/t1#p#p0 8 0 3
314 # Metadata from INFORMATION_SCHEMA.COLUMNS
315 NAME POS MTYPE PRTYPE HAS_DEFAULT
316 c1 0 6 1027 0
317 c6 1 13 16711934 1
318 c3 2 6 1027 0
319 c4 3 5 16711932 0
320 c5 4 13 16711934 1
321 # Metadata from INFORMATION_SCHEMA.TABLES
322 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
323 test/t1#p#p1 8 0 3
324 # Metadata from INFORMATION_SCHEMA.COLUMNS
325 NAME POS MTYPE PRTYPE HAS_DEFAULT
326 c1 0 6 1027 0
327 c6 1 13 16711934 1
328 c3 2 6 1027 0
329 c4 3 5 16711932 0
330 c5 4 13 16711934 1
331 # Metadata from INFORMATION_SCHEMA.TABLES
332 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
333 test/t1#p#p2 8 0 3
334 # Metadata from INFORMATION_SCHEMA.COLUMNS
335 NAME POS MTYPE PRTYPE HAS_DEFAULT
336 c1 0 6 1027 0
337 c6 1 13 16711934 1
338 c3 2 6 1027 0
339 c4 3 5 16711932 0
340 c5 4 13 16711934 1
341 # Metadata from INFORMATION_SCHEMA.TABLES
342 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
343 test/t1#p#p3 8 0 3
344 # Metadata from INFORMATION_SCHEMA.COLUMNS
345 NAME POS MTYPE PRTYPE HAS_DEFAULT
346 c1 0 6 1027 0
347 c6 1 13 16711934 1
348 c3 2 6 1027 0
349 c4 3 5 16711932 0
350 c5 4 13 16711934 1
351 # ------------------------------------------------------------
352 # Scenario 2.2 : New table created with like
353 # ------------------------------------------------------------
354 # EXPORT
355 FLUSH TABLE t1 FOR EXPORT;
356 backup: t1#p#p0
357 backup: t1#p#p1
358 backup: t1#p#p2
359 backup: t1#p#p3
360 UNLOCK TABLES;
361 CREATE TABLE t2 like t1;
362 SHOW CREATE TABLE t2;
363 Table Create Table
364 t2 CREATE TABLE `t2` (
365 `c1` int DEFAULT NULL,
366 `c6` char(20) DEFAULT NULL,
367 `c3` int DEFAULT NULL,
368 `c4` text,
369 `c5` char(20) DEFAULT 'c5_def'
370 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
371 /*!50100 PARTITION BY RANGE ((`c1` * 2))
372 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
373 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
374 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
375 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
376 DROP TABLE t1;
377 ALTER TABLE t2 RENAME as t1;
378 # IMPORT
379 ALTER TABLE t1 discard tablespace;
380 restore: t1#p#p0 .ibd and .cfg files
381 restore: t1#p#p1 .ibd and .cfg files
382 restore: t1#p#p2 .ibd and .cfg files
383 restore: t1#p#p3 .ibd and .cfg files
384 ALTER TABLE t1 IMPORT TABLESPACE;
385 SELECT * FROM t1 ORDER BY c1;
386 c1 c6 c3 c4 c5
387 1 NULL 3 abcdefg c5_def
388 100 NULL 300 qwerty c5_def
389 200 NULL 400 asdfg c5_def
390 300 NULL 500 xxxxxx r4c5
391 400 r5c6 600 xxxxxx r5c5
392 500 r6c6 700 xxxxxx r6c5
393 # Metadata from INFORMATION_SCHEMA.TABLES
394 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
395 test/t1#p#p0 8 0 3
396 # Metadata from INFORMATION_SCHEMA.COLUMNS
397 NAME POS MTYPE PRTYPE HAS_DEFAULT
398 c1 0 6 1027 0
399 c6 1 13 16711934 1
400 c3 2 6 1027 0
401 c4 3 5 16711932 0
402 c5 4 13 16711934 1
403 # Metadata from INFORMATION_SCHEMA.TABLES
404 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
405 test/t1#p#p1 8 0 3
406 # Metadata from INFORMATION_SCHEMA.COLUMNS
407 NAME POS MTYPE PRTYPE HAS_DEFAULT
408 c1 0 6 1027 0
409 c6 1 13 16711934 1
410 c3 2 6 1027 0
411 c4 3 5 16711932 0
412 c5 4 13 16711934 1
413 # Metadata from INFORMATION_SCHEMA.TABLES
414 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
415 test/t1#p#p2 8 0 3
416 # Metadata from INFORMATION_SCHEMA.COLUMNS
417 NAME POS MTYPE PRTYPE HAS_DEFAULT
418 c1 0 6 1027 0
419 c6 1 13 16711934 1
420 c3 2 6 1027 0
421 c4 3 5 16711932 0
422 c5 4 13 16711934 1
423 # Metadata from INFORMATION_SCHEMA.TABLES
424 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
425 test/t1#p#p3 8 0 3
426 # Metadata from INFORMATION_SCHEMA.COLUMNS
427 NAME POS MTYPE PRTYPE HAS_DEFAULT
428 c1 0 6 1027 0
429 c6 1 13 16711934 1
430 c3 2 6 1027 0
431 c4 3 5 16711932 0
432 c5 4 13 16711934 1
433 DROP TABLE t1;
434 # ------------------------------------------------------------
435 # A Partitioned table having multiple INSTANT ADD/DROP columns
436 # and REORGNIZED partitions.
437 # ------------------------------------------------------------
438 CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50), job VARCHAR(50), dept INT, phoneno INT) ROW_FORMAT=redundant PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (10), PARTITION P1 VALUES LESS THAN (20), PARTITION P2 VALUES LESS THAN (30), PARTITION P3 VALUES LESS THAN (40), PARTITION P4 VALUES LESS THAN (50));
439 INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111);
440 INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222);
441 INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333);
442 INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444);
443 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
444 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
445 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
446 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
447 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
448 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
449 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
450 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
451 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
452 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
453 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
454 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
455 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
456 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
457 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
458 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
459 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
460 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
461 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
462 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
463 ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50));
464 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
465 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
466 SELECT * FROM t1 ORDER BY id;
467 id name job dept sal
468 10 aaa xyz1 1 NULL
469 20 bbb xyz2 2 NULL
470 30 ccc xyz3 3 NULL
471 40 ddd xyz4 4 NULL
472 # ------------------------------------------------------------
473 # Scenario 3.1 : Same table with tablespace discarded
474 # ------------------------------------------------------------
475 # EXPORT
476 FLUSH TABLE t1 FOR EXPORT;
477 backup: t1#p#p0
478 backup: t1#p#p1
479 backup: t1#p#p2
480 backup: t1#p#p3
481 UNLOCK TABLES;
482 # IMPORT
483 ALTER TABLE t1 discard tablespace;
484 restore: t1#p#p0 .ibd and .cfg files
485 restore: t1#p#p1 .ibd and .cfg files
486 restore: t1#p#p2 .ibd and .cfg files
487 restore: t1#p#p3 .ibd and .cfg files
488 ALTER TABLE t1 IMPORT TABLESPACE;
489 SELECT * FROM t1 ORDER BY id;
490 id name job dept sal
491 10 aaa xyz1 1 NULL
492 20 bbb xyz2 2 NULL
493 30 ccc xyz3 3 NULL
494 40 ddd xyz4 4 NULL
495 # Metadata from INFORMATION_SCHEMA.TABLES
496 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
497 test/t1#p#p0 8 0 22
498 # Metadata from INFORMATION_SCHEMA.COLUMNS
499 NAME POS MTYPE PRTYPE HAS_DEFAULT
500 id 0 6 1283 0
501 name 1 12 16711695 0
502 job 2 12 16711695 0
503 dept 3 6 1027 0
504 sal 4 6 1027 1
505 # Metadata from INFORMATION_SCHEMA.TABLES
506 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
507 test/t1#p#p1 8 0 22
508 # Metadata from INFORMATION_SCHEMA.COLUMNS
509 NAME POS MTYPE PRTYPE HAS_DEFAULT
510 id 0 6 1283 0
511 name 1 12 16711695 0
512 job 2 12 16711695 0
513 dept 3 6 1027 0
514 sal 4 6 1027 1
515 # Metadata from INFORMATION_SCHEMA.TABLES
516 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
517 test/t1#p#p2 8 0 22
518 # Metadata from INFORMATION_SCHEMA.COLUMNS
519 NAME POS MTYPE PRTYPE HAS_DEFAULT
520 id 0 6 1283 0
521 name 1 12 16711695 0
522 job 2 12 16711695 0
523 dept 3 6 1027 0
524 sal 4 6 1027 1
525 # Metadata from INFORMATION_SCHEMA.TABLES
526 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
527 test/t1#p#p3 8 0 22
528 # Metadata from INFORMATION_SCHEMA.COLUMNS
529 NAME POS MTYPE PRTYPE HAS_DEFAULT
530 id 0 6 1283 0
531 name 1 12 16711695 0
532 job 2 12 16711695 0
533 dept 3 6 1027 0
534 sal 4 6 1027 1
535 # ------------------------------------------------------------
536 # Scenario 3.2 : New table created with like
537 # ------------------------------------------------------------
538 # EXPORT
539 FLUSH TABLE t1 FOR EXPORT;
540 backup: t1#p#p0
541 backup: t1#p#p1
542 backup: t1#p#p2
543 backup: t1#p#p3
544 UNLOCK TABLES;
545 CREATE TABLE t2 like t1;
546 SHOW CREATE TABLE t2;
547 Table Create Table
548 t2 CREATE TABLE `t2` (
549 `id` int NOT NULL,
550 `name` varchar(50) DEFAULT NULL,
551 `job` varchar(50) DEFAULT NULL,
552 `dept` int DEFAULT NULL,
553 `sal` int DEFAULT NULL,
554 PRIMARY KEY (`id`)
555 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
556 /*!50100 PARTITION BY RANGE (`id`)
557 (PARTITION P0 VALUES LESS THAN (10) ENGINE = InnoDB,
558 PARTITION P1 VALUES LESS THAN (20) ENGINE = InnoDB,
559 PARTITION P2 VALUES LESS THAN (30) ENGINE = InnoDB,
560 PARTITION P3 VALUES LESS THAN (50) ENGINE = InnoDB) */
561 DROP TABLE t1;
562 ALTER TABLE t2 RENAME as t1;
563 # IMPORT
564 ALTER TABLE t1 discard tablespace;
565 restore: t1#p#p0 .ibd and .cfg files
566 restore: t1#p#p1 .ibd and .cfg files
567 restore: t1#p#p2 .ibd and .cfg files
568 restore: t1#p#p3 .ibd and .cfg files
569 ALTER TABLE t1 IMPORT TABLESPACE;
570 SELECT * FROM t1 ORDER BY id;
571 id name job dept sal
572 10 aaa xyz1 1 NULL
573 20 bbb xyz2 2 NULL
574 30 ccc xyz3 3 NULL
575 40 ddd xyz4 4 NULL
576 # Metadata from INFORMATION_SCHEMA.TABLES
577 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
578 test/t1#p#p0 8 0 22
579 # Metadata from INFORMATION_SCHEMA.COLUMNS
580 NAME POS MTYPE PRTYPE HAS_DEFAULT
581 id 0 6 1283 0
582 name 1 12 16711695 0
583 job 2 12 16711695 0
584 dept 3 6 1027 0
585 sal 4 6 1027 1
586 # Metadata from INFORMATION_SCHEMA.TABLES
587 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
588 test/t1#p#p1 8 0 22
589 # Metadata from INFORMATION_SCHEMA.COLUMNS
590 NAME POS MTYPE PRTYPE HAS_DEFAULT
591 id 0 6 1283 0
592 name 1 12 16711695 0
593 job 2 12 16711695 0
594 dept 3 6 1027 0
595 sal 4 6 1027 1
596 # Metadata from INFORMATION_SCHEMA.TABLES
597 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
598 test/t1#p#p2 8 0 22
599 # Metadata from INFORMATION_SCHEMA.COLUMNS
600 NAME POS MTYPE PRTYPE HAS_DEFAULT
601 id 0 6 1283 0
602 name 1 12 16711695 0
603 job 2 12 16711695 0
604 dept 3 6 1027 0
605 sal 4 6 1027 1
606 # Metadata from INFORMATION_SCHEMA.TABLES
607 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
608 test/t1#p#p3 8 0 22
609 # Metadata from INFORMATION_SCHEMA.COLUMNS
610 NAME POS MTYPE PRTYPE HAS_DEFAULT
611 id 0 6 1283 0
612 name 1 12 16711695 0
613 job 2 12 16711695 0
614 dept 3 6 1027 0
615 sal 4 6 1027 1
616 DROP TABLE t1;
617 ###########
618 # CLEANUP #
619 ###########
620 # Create a table with 4 columns. [c1, c2, c3, c4]
621 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=dynamic PARTITION BY RANGE(c1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE);
622 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
623 SELECT * from t1;
624 c1 c2 c3 c4
625 1 2 3 abcdefg
626 100 200 300 qwerty
627 200 300 400 asdfg
628 # Add a new column at the end. [c1, c2, c3, c4, +c5]
629 ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT;
630 INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ;
631 SELECT * FROM t1 ORDER BY c1;
632 c1 c2 c3 c4 c5
633 1 2 3 abcdefg c5_def
634 100 200 300 qwerty c5_def
635 200 300 400 asdfg c5_def
636 300 400 500 xxxxxx r4c5
637 # Add a new column in between. [c1, +c6, c2, c3, c4, c5]
638 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
639 INSERT INTO t1 VALUES(400, "r5c6", 500, 600, "xxxxxx", "r5c5") ;
640 SELECT * FROM t1 ORDER BY c1;
641 c1 c6 c2 c3 c4 c5
642 1 NULL 2 3 abcdefg c5_def
643 100 NULL 200 300 qwerty c5_def
644 200 NULL 300 400 asdfg c5_def
645 300 NULL 400 500 xxxxxx r4c5
646 400 r5c6 500 600 xxxxxx r5c5
647 # Metadata from INFORMATION_SCHEMA.TABLES
648 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
649 test/t1#p#p0 9 0 2
650 # Metadata from INFORMATION_SCHEMA.COLUMNS
651 NAME POS MTYPE PRTYPE HAS_DEFAULT
652 c1 0 6 1027 0
653 c6 1 13 16711934 1
654 c2 2 6 1027 0
655 c3 3 6 1027 0
656 c4 4 5 16711932 0
657 c5 5 13 16711934 1
658 # Metadata from INFORMATION_SCHEMA.TABLES
659 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
660 test/t1#p#p1 9 0 2
661 # Metadata from INFORMATION_SCHEMA.COLUMNS
662 NAME POS MTYPE PRTYPE HAS_DEFAULT
663 c1 0 6 1027 0
664 c6 1 13 16711934 1
665 c2 2 6 1027 0
666 c3 3 6 1027 0
667 c4 4 5 16711932 0
668 c5 5 13 16711934 1
669 # Metadata from INFORMATION_SCHEMA.TABLES
670 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
671 test/t1#p#p2 9 0 2
672 # Metadata from INFORMATION_SCHEMA.COLUMNS
673 NAME POS MTYPE PRTYPE HAS_DEFAULT
674 c1 0 6 1027 0
675 c6 1 13 16711934 1
676 c2 2 6 1027 0
677 c3 3 6 1027 0
678 c4 4 5 16711932 0
679 c5 5 13 16711934 1
680 # Metadata from INFORMATION_SCHEMA.TABLES
681 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
682 test/t1#p#p3 9 0 2
683 # Metadata from INFORMATION_SCHEMA.COLUMNS
684 NAME POS MTYPE PRTYPE HAS_DEFAULT
685 c1 0 6 1027 0
686 c6 1 13 16711934 1
687 c2 2 6 1027 0
688 c3 3 6 1027 0
689 c4 4 5 16711932 0
690 c5 5 13 16711934 1
691 # ------------------------------------------------------------
692 # Scenario 1.1 : Same table with tablespace discarded
693 # ------------------------------------------------------------
694 # EXPORT
695 FLUSH TABLE t1 FOR EXPORT;
696 backup: t1#p#p0
697 backup: t1#p#p1
698 backup: t1#p#p2
699 backup: t1#p#p3
700 UNLOCK TABLES;
701 # IMPORT
702 ALTER TABLE t1 discard tablespace;
703 restore: t1#p#p0 .ibd and .cfg files
704 restore: t1#p#p1 .ibd and .cfg files
705 restore: t1#p#p2 .ibd and .cfg files
706 restore: t1#p#p3 .ibd and .cfg files
707 ALTER TABLE t1 IMPORT TABLESPACE;
708 SELECT * FROM t1 ORDER BY c1;
709 c1 c6 c2 c3 c4 c5
710 1 NULL 2 3 abcdefg c5_def
711 100 NULL 200 300 qwerty c5_def
712 200 NULL 300 400 asdfg c5_def
713 300 NULL 400 500 xxxxxx r4c5
714 400 r5c6 500 600 xxxxxx r5c5
715 # Metadata from INFORMATION_SCHEMA.TABLES
716 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
717 test/t1#p#p0 9 0 2
718 # Metadata from INFORMATION_SCHEMA.COLUMNS
719 NAME POS MTYPE PRTYPE HAS_DEFAULT
720 c1 0 6 1027 0
721 c6 1 13 16711934 1
722 c2 2 6 1027 0
723 c3 3 6 1027 0
724 c4 4 5 16711932 0
725 c5 5 13 16711934 1
726 # Metadata from INFORMATION_SCHEMA.TABLES
727 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
728 test/t1#p#p1 9 0 2
729 # Metadata from INFORMATION_SCHEMA.COLUMNS
730 NAME POS MTYPE PRTYPE HAS_DEFAULT
731 c1 0 6 1027 0
732 c6 1 13 16711934 1
733 c2 2 6 1027 0
734 c3 3 6 1027 0
735 c4 4 5 16711932 0
736 c5 5 13 16711934 1
737 # Metadata from INFORMATION_SCHEMA.TABLES
738 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
739 test/t1#p#p2 9 0 2
740 # Metadata from INFORMATION_SCHEMA.COLUMNS
741 NAME POS MTYPE PRTYPE HAS_DEFAULT
742 c1 0 6 1027 0
743 c6 1 13 16711934 1
744 c2 2 6 1027 0
745 c3 3 6 1027 0
746 c4 4 5 16711932 0
747 c5 5 13 16711934 1
748 # Metadata from INFORMATION_SCHEMA.TABLES
749 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
750 test/t1#p#p3 9 0 2
751 # Metadata from INFORMATION_SCHEMA.COLUMNS
752 NAME POS MTYPE PRTYPE HAS_DEFAULT
753 c1 0 6 1027 0
754 c6 1 13 16711934 1
755 c2 2 6 1027 0
756 c3 3 6 1027 0
757 c4 4 5 16711932 0
758 c5 5 13 16711934 1
759 # ------------------------------------------------------------
760 # Scenario 1.2 : New table created with like
761 # ------------------------------------------------------------
762 # EXPORT
763 FLUSH TABLE t1 FOR EXPORT;
764 backup: t1#p#p0
765 backup: t1#p#p1
766 backup: t1#p#p2
767 backup: t1#p#p3
768 UNLOCK TABLES;
769 CREATE TABLE t2 like t1;
770 SHOW CREATE TABLE t2;
771 Table Create Table
772 t2 CREATE TABLE `t2` (
773 `c1` int DEFAULT NULL,
774 `c6` char(20) DEFAULT NULL,
775 `c2` int DEFAULT NULL,
776 `c3` int DEFAULT NULL,
777 `c4` text,
778 `c5` char(20) DEFAULT 'c5_def'
779 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
780 /*!50100 PARTITION BY RANGE ((`c1` * 2))
781 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
782 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
783 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
784 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
785 DROP TABLE t1;
786 ALTER TABLE t2 RENAME as t1;
787 # IMPORT
788 ALTER TABLE t1 discard tablespace;
789 restore: t1#p#p0 .ibd and .cfg files
790 restore: t1#p#p1 .ibd and .cfg files
791 restore: t1#p#p2 .ibd and .cfg files
792 restore: t1#p#p3 .ibd and .cfg files
793 ALTER TABLE t1 IMPORT TABLESPACE;
794 SELECT * FROM t1 ORDER BY c1;
795 c1 c6 c2 c3 c4 c5
796 1 NULL 2 3 abcdefg c5_def
797 100 NULL 200 300 qwerty c5_def
798 200 NULL 300 400 asdfg c5_def
799 300 NULL 400 500 xxxxxx r4c5
800 400 r5c6 500 600 xxxxxx r5c5
801 # Metadata from INFORMATION_SCHEMA.TABLES
802 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
803 test/t1#p#p0 9 0 2
804 # Metadata from INFORMATION_SCHEMA.COLUMNS
805 NAME POS MTYPE PRTYPE HAS_DEFAULT
806 c1 0 6 1027 0
807 c6 1 13 16711934 1
808 c2 2 6 1027 0
809 c3 3 6 1027 0
810 c4 4 5 16711932 0
811 c5 5 13 16711934 1
812 # Metadata from INFORMATION_SCHEMA.TABLES
813 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
814 test/t1#p#p1 9 0 2
815 # Metadata from INFORMATION_SCHEMA.COLUMNS
816 NAME POS MTYPE PRTYPE HAS_DEFAULT
817 c1 0 6 1027 0
818 c6 1 13 16711934 1
819 c2 2 6 1027 0
820 c3 3 6 1027 0
821 c4 4 5 16711932 0
822 c5 5 13 16711934 1
823 # Metadata from INFORMATION_SCHEMA.TABLES
824 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
825 test/t1#p#p2 9 0 2
826 # Metadata from INFORMATION_SCHEMA.COLUMNS
827 NAME POS MTYPE PRTYPE HAS_DEFAULT
828 c1 0 6 1027 0
829 c6 1 13 16711934 1
830 c2 2 6 1027 0
831 c3 3 6 1027 0
832 c4 4 5 16711932 0
833 c5 5 13 16711934 1
834 # Metadata from INFORMATION_SCHEMA.TABLES
835 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
836 test/t1#p#p3 9 0 2
837 # Metadata from INFORMATION_SCHEMA.COLUMNS
838 NAME POS MTYPE PRTYPE HAS_DEFAULT
839 c1 0 6 1027 0
840 c6 1 13 16711934 1
841 c2 2 6 1027 0
842 c3 3 6 1027 0
843 c4 4 5 16711932 0
844 c5 5 13 16711934 1
845 # ------------------------------------------------------------
846 # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5]
847 # ------------------------------------------------------------
848 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
849 # Metadata from INFORMATION_SCHEMA.TABLES
850 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
851 test/t1#p#p0 8 0 3
852 # Metadata from INFORMATION_SCHEMA.COLUMNS
853 NAME POS MTYPE PRTYPE HAS_DEFAULT
854 c1 0 6 1027 0
855 c6 1 13 16711934 1
856 c3 2 6 1027 0
857 c4 3 5 16711932 0
858 c5 4 13 16711934 1
859 # Metadata from INFORMATION_SCHEMA.TABLES
860 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
861 test/t1#p#p1 8 0 3
862 # Metadata from INFORMATION_SCHEMA.COLUMNS
863 NAME POS MTYPE PRTYPE HAS_DEFAULT
864 c1 0 6 1027 0
865 c6 1 13 16711934 1
866 c3 2 6 1027 0
867 c4 3 5 16711932 0
868 c5 4 13 16711934 1
869 # Metadata from INFORMATION_SCHEMA.TABLES
870 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
871 test/t1#p#p2 8 0 3
872 # Metadata from INFORMATION_SCHEMA.COLUMNS
873 NAME POS MTYPE PRTYPE HAS_DEFAULT
874 c1 0 6 1027 0
875 c6 1 13 16711934 1
876 c3 2 6 1027 0
877 c4 3 5 16711932 0
878 c5 4 13 16711934 1
879 # Metadata from INFORMATION_SCHEMA.TABLES
880 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
881 test/t1#p#p3 8 0 3
882 # Metadata from INFORMATION_SCHEMA.COLUMNS
883 NAME POS MTYPE PRTYPE HAS_DEFAULT
884 c1 0 6 1027 0
885 c6 1 13 16711934 1
886 c3 2 6 1027 0
887 c4 3 5 16711932 0
888 c5 4 13 16711934 1
889 SELECT * FROM t1 ORDER BY c1;
890 c1 c6 c3 c4 c5
891 1 NULL 3 abcdefg c5_def
892 100 NULL 300 qwerty c5_def
893 200 NULL 400 asdfg c5_def
894 300 NULL 500 xxxxxx r4c5
895 400 r5c6 600 xxxxxx r5c5
896 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ;
897 SELECT * FROM t1 ORDER BY c1;
898 c1 c6 c3 c4 c5
899 1 NULL 3 abcdefg c5_def
900 100 NULL 300 qwerty c5_def
901 200 NULL 400 asdfg c5_def
902 300 NULL 500 xxxxxx r4c5
903 400 r5c6 600 xxxxxx r5c5
904 500 r6c6 700 xxxxxx r6c5
905 # ------------------------------------------------------------
906 # Scenario 2.1 : Same table with tablespace discarded
907 # ------------------------------------------------------------
908 # EXPORT
909 FLUSH TABLE t1 FOR EXPORT;
910 backup: t1#p#p0
911 backup: t1#p#p1
912 backup: t1#p#p2
913 backup: t1#p#p3
914 UNLOCK TABLES;
915 # IMPORT
916 ALTER TABLE t1 discard tablespace;
917 restore: t1#p#p0 .ibd and .cfg files
918 restore: t1#p#p1 .ibd and .cfg files
919 restore: t1#p#p2 .ibd and .cfg files
920 restore: t1#p#p3 .ibd and .cfg files
921 ALTER TABLE t1 IMPORT TABLESPACE;
922 SELECT * FROM t1 ORDER BY c1;
923 c1 c6 c3 c4 c5
924 1 NULL 3 abcdefg c5_def
925 100 NULL 300 qwerty c5_def
926 200 NULL 400 asdfg c5_def
927 300 NULL 500 xxxxxx r4c5
928 400 r5c6 600 xxxxxx r5c5
929 500 r6c6 700 xxxxxx r6c5
930 # Metadata from INFORMATION_SCHEMA.TABLES
931 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
932 test/t1#p#p0 8 0 3
933 # Metadata from INFORMATION_SCHEMA.COLUMNS
934 NAME POS MTYPE PRTYPE HAS_DEFAULT
935 c1 0 6 1027 0
936 c6 1 13 16711934 1
937 c3 2 6 1027 0
938 c4 3 5 16711932 0
939 c5 4 13 16711934 1
940 # Metadata from INFORMATION_SCHEMA.TABLES
941 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
942 test/t1#p#p1 8 0 3
943 # Metadata from INFORMATION_SCHEMA.COLUMNS
944 NAME POS MTYPE PRTYPE HAS_DEFAULT
945 c1 0 6 1027 0
946 c6 1 13 16711934 1
947 c3 2 6 1027 0
948 c4 3 5 16711932 0
949 c5 4 13 16711934 1
950 # Metadata from INFORMATION_SCHEMA.TABLES
951 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
952 test/t1#p#p2 8 0 3
953 # Metadata from INFORMATION_SCHEMA.COLUMNS
954 NAME POS MTYPE PRTYPE HAS_DEFAULT
955 c1 0 6 1027 0
956 c6 1 13 16711934 1
957 c3 2 6 1027 0
958 c4 3 5 16711932 0
959 c5 4 13 16711934 1
960 # Metadata from INFORMATION_SCHEMA.TABLES
961 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
962 test/t1#p#p3 8 0 3
963 # Metadata from INFORMATION_SCHEMA.COLUMNS
964 NAME POS MTYPE PRTYPE HAS_DEFAULT
965 c1 0 6 1027 0
966 c6 1 13 16711934 1
967 c3 2 6 1027 0
968 c4 3 5 16711932 0
969 c5 4 13 16711934 1
970 # ------------------------------------------------------------
971 # Scenario 2.2 : New table created with like
972 # ------------------------------------------------------------
973 # EXPORT
974 FLUSH TABLE t1 FOR EXPORT;
975 backup: t1#p#p0
976 backup: t1#p#p1
977 backup: t1#p#p2
978 backup: t1#p#p3
979 UNLOCK TABLES;
980 CREATE TABLE t2 like t1;
981 SHOW CREATE TABLE t2;
982 Table Create Table
983 t2 CREATE TABLE `t2` (
984 `c1` int DEFAULT NULL,
985 `c6` char(20) DEFAULT NULL,
986 `c3` int DEFAULT NULL,
987 `c4` text,
988 `c5` char(20) DEFAULT 'c5_def'
989 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
990 /*!50100 PARTITION BY RANGE ((`c1` * 2))
991 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
992 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
993 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
994 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
995 DROP TABLE t1;
996 ALTER TABLE t2 RENAME as t1;
997 # IMPORT
998 ALTER TABLE t1 discard tablespace;
999 restore: t1#p#p0 .ibd and .cfg files
1000 restore: t1#p#p1 .ibd and .cfg files
1001 restore: t1#p#p2 .ibd and .cfg files
1002 restore: t1#p#p3 .ibd and .cfg files
1003 ALTER TABLE t1 IMPORT TABLESPACE;
1004 SELECT * FROM t1 ORDER BY c1;
1005 c1 c6 c3 c4 c5
1006 1 NULL 3 abcdefg c5_def
1007 100 NULL 300 qwerty c5_def
1008 200 NULL 400 asdfg c5_def
1009 300 NULL 500 xxxxxx r4c5
1010 400 r5c6 600 xxxxxx r5c5
1011 500 r6c6 700 xxxxxx r6c5
1012 # Metadata from INFORMATION_SCHEMA.TABLES
1013 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1014 test/t1#p#p0 8 0 3
1015 # Metadata from INFORMATION_SCHEMA.COLUMNS
1016 NAME POS MTYPE PRTYPE HAS_DEFAULT
1017 c1 0 6 1027 0
1018 c6 1 13 16711934 1
1019 c3 2 6 1027 0
1020 c4 3 5 16711932 0
1021 c5 4 13 16711934 1
1022 # Metadata from INFORMATION_SCHEMA.TABLES
1023 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1024 test/t1#p#p1 8 0 3
1025 # Metadata from INFORMATION_SCHEMA.COLUMNS
1026 NAME POS MTYPE PRTYPE HAS_DEFAULT
1027 c1 0 6 1027 0
1028 c6 1 13 16711934 1
1029 c3 2 6 1027 0
1030 c4 3 5 16711932 0
1031 c5 4 13 16711934 1
1032 # Metadata from INFORMATION_SCHEMA.TABLES
1033 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1034 test/t1#p#p2 8 0 3
1035 # Metadata from INFORMATION_SCHEMA.COLUMNS
1036 NAME POS MTYPE PRTYPE HAS_DEFAULT
1037 c1 0 6 1027 0
1038 c6 1 13 16711934 1
1039 c3 2 6 1027 0
1040 c4 3 5 16711932 0
1041 c5 4 13 16711934 1
1042 # Metadata from INFORMATION_SCHEMA.TABLES
1043 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1044 test/t1#p#p3 8 0 3
1045 # Metadata from INFORMATION_SCHEMA.COLUMNS
1046 NAME POS MTYPE PRTYPE HAS_DEFAULT
1047 c1 0 6 1027 0
1048 c6 1 13 16711934 1
1049 c3 2 6 1027 0
1050 c4 3 5 16711932 0
1051 c5 4 13 16711934 1
1052 DROP TABLE t1;
1053 # ------------------------------------------------------------
1054 # A Partitioned table having multiple INSTANT ADD/DROP columns
1055 # and REORGNIZED partitions.
1056 # ------------------------------------------------------------
1057 CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50), job VARCHAR(50), dept INT, phoneno INT) ROW_FORMAT=dynamic PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (10), PARTITION P1 VALUES LESS THAN (20), PARTITION P2 VALUES LESS THAN (30), PARTITION P3 VALUES LESS THAN (40), PARTITION P4 VALUES LESS THAN (50));
1058 INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111);
1059 INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222);
1060 INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333);
1061 INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444);
1062 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1063 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1064 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1065 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1066 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1067 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1068 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1069 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1070 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1071 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1072 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1073 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1074 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1075 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1076 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1077 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1078 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1079 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1080 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1081 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1082 ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50));
1083 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1084 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1085 SELECT * FROM t1 ORDER BY id;
1086 id name job dept sal
1087 10 aaa xyz1 1 NULL
1088 20 bbb xyz2 2 NULL
1089 30 ccc xyz3 3 NULL
1090 40 ddd xyz4 4 NULL
1091 # ------------------------------------------------------------
1092 # Scenario 3.1 : Same table with tablespace discarded
1093 # ------------------------------------------------------------
1094 # EXPORT
1095 FLUSH TABLE t1 FOR EXPORT;
1096 backup: t1#p#p0
1097 backup: t1#p#p1
1098 backup: t1#p#p2
1099 backup: t1#p#p3
1100 UNLOCK TABLES;
1101 # IMPORT
1102 ALTER TABLE t1 discard tablespace;
1103 restore: t1#p#p0 .ibd and .cfg files
1104 restore: t1#p#p1 .ibd and .cfg files
1105 restore: t1#p#p2 .ibd and .cfg files
1106 restore: t1#p#p3 .ibd and .cfg files
1107 ALTER TABLE t1 IMPORT TABLESPACE;
1108 SELECT * FROM t1 ORDER BY id;
1109 id name job dept sal
1110 10 aaa xyz1 1 NULL
1111 20 bbb xyz2 2 NULL
1112 30 ccc xyz3 3 NULL
1113 40 ddd xyz4 4 NULL
1114 # Metadata from INFORMATION_SCHEMA.TABLES
1115 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1116 test/t1#p#p0 8 0 22
1117 # Metadata from INFORMATION_SCHEMA.COLUMNS
1118 NAME POS MTYPE PRTYPE HAS_DEFAULT
1119 id 0 6 1283 0
1120 name 1 12 16711695 0
1121 job 2 12 16711695 0
1122 dept 3 6 1027 0
1123 sal 4 6 1027 1
1124 # Metadata from INFORMATION_SCHEMA.TABLES
1125 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1126 test/t1#p#p1 8 0 22
1127 # Metadata from INFORMATION_SCHEMA.COLUMNS
1128 NAME POS MTYPE PRTYPE HAS_DEFAULT
1129 id 0 6 1283 0
1130 name 1 12 16711695 0
1131 job 2 12 16711695 0
1132 dept 3 6 1027 0
1133 sal 4 6 1027 1
1134 # Metadata from INFORMATION_SCHEMA.TABLES
1135 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1136 test/t1#p#p2 8 0 22
1137 # Metadata from INFORMATION_SCHEMA.COLUMNS
1138 NAME POS MTYPE PRTYPE HAS_DEFAULT
1139 id 0 6 1283 0
1140 name 1 12 16711695 0
1141 job 2 12 16711695 0
1142 dept 3 6 1027 0
1143 sal 4 6 1027 1
1144 # Metadata from INFORMATION_SCHEMA.TABLES
1145 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1146 test/t1#p#p3 8 0 22
1147 # Metadata from INFORMATION_SCHEMA.COLUMNS
1148 NAME POS MTYPE PRTYPE HAS_DEFAULT
1149 id 0 6 1283 0
1150 name 1 12 16711695 0
1151 job 2 12 16711695 0
1152 dept 3 6 1027 0
1153 sal 4 6 1027 1
1154 # ------------------------------------------------------------
1155 # Scenario 3.2 : New table created with like
1156 # ------------------------------------------------------------
1157 # EXPORT
1158 FLUSH TABLE t1 FOR EXPORT;
1159 backup: t1#p#p0
1160 backup: t1#p#p1
1161 backup: t1#p#p2
1162 backup: t1#p#p3
1163 UNLOCK TABLES;
1164 CREATE TABLE t2 like t1;
1165 SHOW CREATE TABLE t2;
1166 Table Create Table
1167 t2 CREATE TABLE `t2` (
1168 `id` int NOT NULL,
1169 `name` varchar(50) DEFAULT NULL,
1170 `job` varchar(50) DEFAULT NULL,
1171 `dept` int DEFAULT NULL,
1172 `sal` int DEFAULT NULL,
1173 PRIMARY KEY (`id`)
1174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1175 /*!50100 PARTITION BY RANGE (`id`)
1176 (PARTITION P0 VALUES LESS THAN (10) ENGINE = InnoDB,
1177 PARTITION P1 VALUES LESS THAN (20) ENGINE = InnoDB,
1178 PARTITION P2 VALUES LESS THAN (30) ENGINE = InnoDB,
1179 PARTITION P3 VALUES LESS THAN (50) ENGINE = InnoDB) */
1180 DROP TABLE t1;
1181 ALTER TABLE t2 RENAME as t1;
1182 # IMPORT
1183 ALTER TABLE t1 discard tablespace;
1184 restore: t1#p#p0 .ibd and .cfg files
1185 restore: t1#p#p1 .ibd and .cfg files
1186 restore: t1#p#p2 .ibd and .cfg files
1187 restore: t1#p#p3 .ibd and .cfg files
1188 ALTER TABLE t1 IMPORT TABLESPACE;
1189 SELECT * FROM t1 ORDER BY id;
1190 id name job dept sal
1191 10 aaa xyz1 1 NULL
1192 20 bbb xyz2 2 NULL
1193 30 ccc xyz3 3 NULL
1194 40 ddd xyz4 4 NULL
1195 # Metadata from INFORMATION_SCHEMA.TABLES
1196 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1197 test/t1#p#p0 8 0 22
1198 # Metadata from INFORMATION_SCHEMA.COLUMNS
1199 NAME POS MTYPE PRTYPE HAS_DEFAULT
1200 id 0 6 1283 0
1201 name 1 12 16711695 0
1202 job 2 12 16711695 0
1203 dept 3 6 1027 0
1204 sal 4 6 1027 1
1205 # Metadata from INFORMATION_SCHEMA.TABLES
1206 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1207 test/t1#p#p1 8 0 22
1208 # Metadata from INFORMATION_SCHEMA.COLUMNS
1209 NAME POS MTYPE PRTYPE HAS_DEFAULT
1210 id 0 6 1283 0
1211 name 1 12 16711695 0
1212 job 2 12 16711695 0
1213 dept 3 6 1027 0
1214 sal 4 6 1027 1
1215 # Metadata from INFORMATION_SCHEMA.TABLES
1216 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1217 test/t1#p#p2 8 0 22
1218 # Metadata from INFORMATION_SCHEMA.COLUMNS
1219 NAME POS MTYPE PRTYPE HAS_DEFAULT
1220 id 0 6 1283 0
1221 name 1 12 16711695 0
1222 job 2 12 16711695 0
1223 dept 3 6 1027 0
1224 sal 4 6 1027 1
1225 # Metadata from INFORMATION_SCHEMA.TABLES
1226 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1227 test/t1#p#p3 8 0 22
1228 # Metadata from INFORMATION_SCHEMA.COLUMNS
1229 NAME POS MTYPE PRTYPE HAS_DEFAULT
1230 id 0 6 1283 0
1231 name 1 12 16711695 0
1232 job 2 12 16711695 0
1233 dept 3 6 1027 0
1234 sal 4 6 1027 1
1235 DROP TABLE t1;
1236 ###########
1237 # CLEANUP #
1238 ###########
1239 ############################################
1240 # Test instant ADD/DROP COLUMN for COMPACT format
1241 ############################################
1242 # Create a table with 4 columns. [c1, c2, c3, c4]
1243 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=compact PARTITION BY RANGE(c1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE);
1244 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
1245 SELECT * from t1;
1246 c1 c2 c3 c4
1247 1 2 3 abcdefg
1248 100 200 300 qwerty
1249 200 300 400 asdfg
1250 # Add a new column at the end. [c1, c2, c3, c4, +c5]
1251 ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT;
1252 INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ;
1253 SELECT * FROM t1 ORDER BY c1;
1254 c1 c2 c3 c4 c5
1255 1 2 3 abcdefg c5_def
1256 100 200 300 qwerty c5_def
1257 200 300 400 asdfg c5_def
1258 300 400 500 xxxxxx r4c5
1259 # Add a new column in between. [c1, +c6, c2, c3, c4, c5]
1260 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
1261 INSERT INTO t1 VALUES(400, "r5c6", 500, 600, "xxxxxx", "r5c5") ;
1262 SELECT * FROM t1 ORDER BY c1;
1263 c1 c6 c2 c3 c4 c5
1264 1 NULL 2 3 abcdefg c5_def
1265 100 NULL 200 300 qwerty c5_def
1266 200 NULL 300 400 asdfg c5_def
1267 300 NULL 400 500 xxxxxx r4c5
1268 400 r5c6 500 600 xxxxxx r5c5
1269 # Metadata from INFORMATION_SCHEMA.TABLES
1270 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1271 test/t1#p#p0 9 0 2
1272 # Metadata from INFORMATION_SCHEMA.COLUMNS
1273 NAME POS MTYPE PRTYPE HAS_DEFAULT
1274 c1 0 6 1027 0
1275 c6 1 13 16711934 1
1276 c2 2 6 1027 0
1277 c3 3 6 1027 0
1278 c4 4 5 16711932 0
1279 c5 5 13 16711934 1
1280 # Metadata from INFORMATION_SCHEMA.TABLES
1281 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1282 test/t1#p#p1 9 0 2
1283 # Metadata from INFORMATION_SCHEMA.COLUMNS
1284 NAME POS MTYPE PRTYPE HAS_DEFAULT
1285 c1 0 6 1027 0
1286 c6 1 13 16711934 1
1287 c2 2 6 1027 0
1288 c3 3 6 1027 0
1289 c4 4 5 16711932 0
1290 c5 5 13 16711934 1
1291 # Metadata from INFORMATION_SCHEMA.TABLES
1292 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1293 test/t1#p#p2 9 0 2
1294 # Metadata from INFORMATION_SCHEMA.COLUMNS
1295 NAME POS MTYPE PRTYPE HAS_DEFAULT
1296 c1 0 6 1027 0
1297 c6 1 13 16711934 1
1298 c2 2 6 1027 0
1299 c3 3 6 1027 0
1300 c4 4 5 16711932 0
1301 c5 5 13 16711934 1
1302 # Metadata from INFORMATION_SCHEMA.TABLES
1303 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1304 test/t1#p#p3 9 0 2
1305 # Metadata from INFORMATION_SCHEMA.COLUMNS
1306 NAME POS MTYPE PRTYPE HAS_DEFAULT
1307 c1 0 6 1027 0
1308 c6 1 13 16711934 1
1309 c2 2 6 1027 0
1310 c3 3 6 1027 0
1311 c4 4 5 16711932 0
1312 c5 5 13 16711934 1
1313 # ------------------------------------------------------------
1314 # Scenario 1.1 : Same table with tablespace discarded
1315 # ------------------------------------------------------------
1316 # EXPORT
1317 FLUSH TABLE t1 FOR EXPORT;
1318 backup: t1#p#p0
1319 backup: t1#p#p1
1320 backup: t1#p#p2
1321 backup: t1#p#p3
1322 UNLOCK TABLES;
1323 # IMPORT
1324 ALTER TABLE t1 discard tablespace;
1325 restore: t1#p#p0 .ibd and .cfg files
1326 restore: t1#p#p1 .ibd and .cfg files
1327 restore: t1#p#p2 .ibd and .cfg files
1328 restore: t1#p#p3 .ibd and .cfg files
1329 ALTER TABLE t1 IMPORT TABLESPACE;
1330 SELECT * FROM t1 ORDER BY c1;
1331 c1 c6 c2 c3 c4 c5
1332 1 NULL 2 3 abcdefg c5_def
1333 100 NULL 200 300 qwerty c5_def
1334 200 NULL 300 400 asdfg c5_def
1335 300 NULL 400 500 xxxxxx r4c5
1336 400 r5c6 500 600 xxxxxx r5c5
1337 # Metadata from INFORMATION_SCHEMA.TABLES
1338 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1339 test/t1#p#p0 9 0 2
1340 # Metadata from INFORMATION_SCHEMA.COLUMNS
1341 NAME POS MTYPE PRTYPE HAS_DEFAULT
1342 c1 0 6 1027 0
1343 c6 1 13 16711934 1
1344 c2 2 6 1027 0
1345 c3 3 6 1027 0
1346 c4 4 5 16711932 0
1347 c5 5 13 16711934 1
1348 # Metadata from INFORMATION_SCHEMA.TABLES
1349 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1350 test/t1#p#p1 9 0 2
1351 # Metadata from INFORMATION_SCHEMA.COLUMNS
1352 NAME POS MTYPE PRTYPE HAS_DEFAULT
1353 c1 0 6 1027 0
1354 c6 1 13 16711934 1
1355 c2 2 6 1027 0
1356 c3 3 6 1027 0
1357 c4 4 5 16711932 0
1358 c5 5 13 16711934 1
1359 # Metadata from INFORMATION_SCHEMA.TABLES
1360 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1361 test/t1#p#p2 9 0 2
1362 # Metadata from INFORMATION_SCHEMA.COLUMNS
1363 NAME POS MTYPE PRTYPE HAS_DEFAULT
1364 c1 0 6 1027 0
1365 c6 1 13 16711934 1
1366 c2 2 6 1027 0
1367 c3 3 6 1027 0
1368 c4 4 5 16711932 0
1369 c5 5 13 16711934 1
1370 # Metadata from INFORMATION_SCHEMA.TABLES
1371 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1372 test/t1#p#p3 9 0 2
1373 # Metadata from INFORMATION_SCHEMA.COLUMNS
1374 NAME POS MTYPE PRTYPE HAS_DEFAULT
1375 c1 0 6 1027 0
1376 c6 1 13 16711934 1
1377 c2 2 6 1027 0
1378 c3 3 6 1027 0
1379 c4 4 5 16711932 0
1380 c5 5 13 16711934 1
1381 # ------------------------------------------------------------
1382 # Scenario 1.2 : New table created with like
1383 # ------------------------------------------------------------
1384 # EXPORT
1385 FLUSH TABLE t1 FOR EXPORT;
1386 backup: t1#p#p0
1387 backup: t1#p#p1
1388 backup: t1#p#p2
1389 backup: t1#p#p3
1390 UNLOCK TABLES;
1391 CREATE TABLE t2 like t1;
1392 SHOW CREATE TABLE t2;
1393 Table Create Table
1394 t2 CREATE TABLE `t2` (
1395 `c1` int DEFAULT NULL,
1396 `c6` char(20) DEFAULT NULL,
1397 `c2` int DEFAULT NULL,
1398 `c3` int DEFAULT NULL,
1399 `c4` text,
1400 `c5` char(20) DEFAULT 'c5_def'
1401 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
1402 /*!50100 PARTITION BY RANGE ((`c1` * 2))
1403 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
1404 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
1405 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
1406 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1407 DROP TABLE t1;
1408 ALTER TABLE t2 RENAME as t1;
1409 # IMPORT
1410 ALTER TABLE t1 discard tablespace;
1411 restore: t1#p#p0 .ibd and .cfg files
1412 restore: t1#p#p1 .ibd and .cfg files
1413 restore: t1#p#p2 .ibd and .cfg files
1414 restore: t1#p#p3 .ibd and .cfg files
1415 ALTER TABLE t1 IMPORT TABLESPACE;
1416 SELECT * FROM t1 ORDER BY c1;
1417 c1 c6 c2 c3 c4 c5
1418 1 NULL 2 3 abcdefg c5_def
1419 100 NULL 200 300 qwerty c5_def
1420 200 NULL 300 400 asdfg c5_def
1421 300 NULL 400 500 xxxxxx r4c5
1422 400 r5c6 500 600 xxxxxx r5c5
1423 # Metadata from INFORMATION_SCHEMA.TABLES
1424 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1425 test/t1#p#p0 9 0 2
1426 # Metadata from INFORMATION_SCHEMA.COLUMNS
1427 NAME POS MTYPE PRTYPE HAS_DEFAULT
1428 c1 0 6 1027 0
1429 c6 1 13 16711934 1
1430 c2 2 6 1027 0
1431 c3 3 6 1027 0
1432 c4 4 5 16711932 0
1433 c5 5 13 16711934 1
1434 # Metadata from INFORMATION_SCHEMA.TABLES
1435 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1436 test/t1#p#p1 9 0 2
1437 # Metadata from INFORMATION_SCHEMA.COLUMNS
1438 NAME POS MTYPE PRTYPE HAS_DEFAULT
1439 c1 0 6 1027 0
1440 c6 1 13 16711934 1
1441 c2 2 6 1027 0
1442 c3 3 6 1027 0
1443 c4 4 5 16711932 0
1444 c5 5 13 16711934 1
1445 # Metadata from INFORMATION_SCHEMA.TABLES
1446 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1447 test/t1#p#p2 9 0 2
1448 # Metadata from INFORMATION_SCHEMA.COLUMNS
1449 NAME POS MTYPE PRTYPE HAS_DEFAULT
1450 c1 0 6 1027 0
1451 c6 1 13 16711934 1
1452 c2 2 6 1027 0
1453 c3 3 6 1027 0
1454 c4 4 5 16711932 0
1455 c5 5 13 16711934 1
1456 # Metadata from INFORMATION_SCHEMA.TABLES
1457 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1458 test/t1#p#p3 9 0 2
1459 # Metadata from INFORMATION_SCHEMA.COLUMNS
1460 NAME POS MTYPE PRTYPE HAS_DEFAULT
1461 c1 0 6 1027 0
1462 c6 1 13 16711934 1
1463 c2 2 6 1027 0
1464 c3 3 6 1027 0
1465 c4 4 5 16711932 0
1466 c5 5 13 16711934 1
1467 # ------------------------------------------------------------
1468 # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5]
1469 # ------------------------------------------------------------
1470 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
1471 # Metadata from INFORMATION_SCHEMA.TABLES
1472 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1473 test/t1#p#p0 8 0 3
1474 # Metadata from INFORMATION_SCHEMA.COLUMNS
1475 NAME POS MTYPE PRTYPE HAS_DEFAULT
1476 c1 0 6 1027 0
1477 c6 1 13 16711934 1
1478 c3 2 6 1027 0
1479 c4 3 5 16711932 0
1480 c5 4 13 16711934 1
1481 # Metadata from INFORMATION_SCHEMA.TABLES
1482 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1483 test/t1#p#p1 8 0 3
1484 # Metadata from INFORMATION_SCHEMA.COLUMNS
1485 NAME POS MTYPE PRTYPE HAS_DEFAULT
1486 c1 0 6 1027 0
1487 c6 1 13 16711934 1
1488 c3 2 6 1027 0
1489 c4 3 5 16711932 0
1490 c5 4 13 16711934 1
1491 # Metadata from INFORMATION_SCHEMA.TABLES
1492 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1493 test/t1#p#p2 8 0 3
1494 # Metadata from INFORMATION_SCHEMA.COLUMNS
1495 NAME POS MTYPE PRTYPE HAS_DEFAULT
1496 c1 0 6 1027 0
1497 c6 1 13 16711934 1
1498 c3 2 6 1027 0
1499 c4 3 5 16711932 0
1500 c5 4 13 16711934 1
1501 # Metadata from INFORMATION_SCHEMA.TABLES
1502 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1503 test/t1#p#p3 8 0 3
1504 # Metadata from INFORMATION_SCHEMA.COLUMNS
1505 NAME POS MTYPE PRTYPE HAS_DEFAULT
1506 c1 0 6 1027 0
1507 c6 1 13 16711934 1
1508 c3 2 6 1027 0
1509 c4 3 5 16711932 0
1510 c5 4 13 16711934 1
1511 SELECT * FROM t1 ORDER BY c1;
1512 c1 c6 c3 c4 c5
1513 1 NULL 3 abcdefg c5_def
1514 100 NULL 300 qwerty c5_def
1515 200 NULL 400 asdfg c5_def
1516 300 NULL 500 xxxxxx r4c5
1517 400 r5c6 600 xxxxxx r5c5
1518 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ;
1519 SELECT * FROM t1 ORDER BY c1;
1520 c1 c6 c3 c4 c5
1521 1 NULL 3 abcdefg c5_def
1522 100 NULL 300 qwerty c5_def
1523 200 NULL 400 asdfg c5_def
1524 300 NULL 500 xxxxxx r4c5
1525 400 r5c6 600 xxxxxx r5c5
1526 500 r6c6 700 xxxxxx r6c5
1527 # ------------------------------------------------------------
1528 # Scenario 2.1 : Same table with tablespace discarded
1529 # ------------------------------------------------------------
1530 # EXPORT
1531 FLUSH TABLE t1 FOR EXPORT;
1532 backup: t1#p#p0
1533 backup: t1#p#p1
1534 backup: t1#p#p2
1535 backup: t1#p#p3
1536 UNLOCK TABLES;
1537 # IMPORT
1538 ALTER TABLE t1 discard tablespace;
1539 restore: t1#p#p0 .ibd and .cfg files
1540 restore: t1#p#p1 .ibd and .cfg files
1541 restore: t1#p#p2 .ibd and .cfg files
1542 restore: t1#p#p3 .ibd and .cfg files
1543 ALTER TABLE t1 IMPORT TABLESPACE;
1544 SELECT * FROM t1 ORDER BY c1;
1545 c1 c6 c3 c4 c5
1546 1 NULL 3 abcdefg c5_def
1547 100 NULL 300 qwerty c5_def
1548 200 NULL 400 asdfg c5_def
1549 300 NULL 500 xxxxxx r4c5
1550 400 r5c6 600 xxxxxx r5c5
1551 500 r6c6 700 xxxxxx r6c5
1552 # Metadata from INFORMATION_SCHEMA.TABLES
1553 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1554 test/t1#p#p0 8 0 3
1555 # Metadata from INFORMATION_SCHEMA.COLUMNS
1556 NAME POS MTYPE PRTYPE HAS_DEFAULT
1557 c1 0 6 1027 0
1558 c6 1 13 16711934 1
1559 c3 2 6 1027 0
1560 c4 3 5 16711932 0
1561 c5 4 13 16711934 1
1562 # Metadata from INFORMATION_SCHEMA.TABLES
1563 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1564 test/t1#p#p1 8 0 3
1565 # Metadata from INFORMATION_SCHEMA.COLUMNS
1566 NAME POS MTYPE PRTYPE HAS_DEFAULT
1567 c1 0 6 1027 0
1568 c6 1 13 16711934 1
1569 c3 2 6 1027 0
1570 c4 3 5 16711932 0
1571 c5 4 13 16711934 1
1572 # Metadata from INFORMATION_SCHEMA.TABLES
1573 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1574 test/t1#p#p2 8 0 3
1575 # Metadata from INFORMATION_SCHEMA.COLUMNS
1576 NAME POS MTYPE PRTYPE HAS_DEFAULT
1577 c1 0 6 1027 0
1578 c6 1 13 16711934 1
1579 c3 2 6 1027 0
1580 c4 3 5 16711932 0
1581 c5 4 13 16711934 1
1582 # Metadata from INFORMATION_SCHEMA.TABLES
1583 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1584 test/t1#p#p3 8 0 3
1585 # Metadata from INFORMATION_SCHEMA.COLUMNS
1586 NAME POS MTYPE PRTYPE HAS_DEFAULT
1587 c1 0 6 1027 0
1588 c6 1 13 16711934 1
1589 c3 2 6 1027 0
1590 c4 3 5 16711932 0
1591 c5 4 13 16711934 1
1592 # ------------------------------------------------------------
1593 # Scenario 2.2 : New table created with like
1594 # ------------------------------------------------------------
1595 # EXPORT
1596 FLUSH TABLE t1 FOR EXPORT;
1597 backup: t1#p#p0
1598 backup: t1#p#p1
1599 backup: t1#p#p2
1600 backup: t1#p#p3
1601 UNLOCK TABLES;
1602 CREATE TABLE t2 like t1;
1603 SHOW CREATE TABLE t2;
1604 Table Create Table
1605 t2 CREATE TABLE `t2` (
1606 `c1` int DEFAULT NULL,
1607 `c6` char(20) DEFAULT NULL,
1608 `c3` int DEFAULT NULL,
1609 `c4` text,
1610 `c5` char(20) DEFAULT 'c5_def'
1611 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
1612 /*!50100 PARTITION BY RANGE ((`c1` * 2))
1613 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
1614 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
1615 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
1616 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1617 DROP TABLE t1;
1618 ALTER TABLE t2 RENAME as t1;
1619 # IMPORT
1620 ALTER TABLE t1 discard tablespace;
1621 restore: t1#p#p0 .ibd and .cfg files
1622 restore: t1#p#p1 .ibd and .cfg files
1623 restore: t1#p#p2 .ibd and .cfg files
1624 restore: t1#p#p3 .ibd and .cfg files
1625 ALTER TABLE t1 IMPORT TABLESPACE;
1626 SELECT * FROM t1 ORDER BY c1;
1627 c1 c6 c3 c4 c5
1628 1 NULL 3 abcdefg c5_def
1629 100 NULL 300 qwerty c5_def
1630 200 NULL 400 asdfg c5_def
1631 300 NULL 500 xxxxxx r4c5
1632 400 r5c6 600 xxxxxx r5c5
1633 500 r6c6 700 xxxxxx r6c5
1634 # Metadata from INFORMATION_SCHEMA.TABLES
1635 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1636 test/t1#p#p0 8 0 3
1637 # Metadata from INFORMATION_SCHEMA.COLUMNS
1638 NAME POS MTYPE PRTYPE HAS_DEFAULT
1639 c1 0 6 1027 0
1640 c6 1 13 16711934 1
1641 c3 2 6 1027 0
1642 c4 3 5 16711932 0
1643 c5 4 13 16711934 1
1644 # Metadata from INFORMATION_SCHEMA.TABLES
1645 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1646 test/t1#p#p1 8 0 3
1647 # Metadata from INFORMATION_SCHEMA.COLUMNS
1648 NAME POS MTYPE PRTYPE HAS_DEFAULT
1649 c1 0 6 1027 0
1650 c6 1 13 16711934 1
1651 c3 2 6 1027 0
1652 c4 3 5 16711932 0
1653 c5 4 13 16711934 1
1654 # Metadata from INFORMATION_SCHEMA.TABLES
1655 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1656 test/t1#p#p2 8 0 3
1657 # Metadata from INFORMATION_SCHEMA.COLUMNS
1658 NAME POS MTYPE PRTYPE HAS_DEFAULT
1659 c1 0 6 1027 0
1660 c6 1 13 16711934 1
1661 c3 2 6 1027 0
1662 c4 3 5 16711932 0
1663 c5 4 13 16711934 1
1664 # Metadata from INFORMATION_SCHEMA.TABLES
1665 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1666 test/t1#p#p3 8 0 3
1667 # Metadata from INFORMATION_SCHEMA.COLUMNS
1668 NAME POS MTYPE PRTYPE HAS_DEFAULT
1669 c1 0 6 1027 0
1670 c6 1 13 16711934 1
1671 c3 2 6 1027 0
1672 c4 3 5 16711932 0
1673 c5 4 13 16711934 1
1674 DROP TABLE t1;
1675 # ------------------------------------------------------------
1676 # A Partitioned table having multiple INSTANT ADD/DROP columns
1677 # and REORGNIZED partitions.
1678 # ------------------------------------------------------------
1679 CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(50), job VARCHAR(50), dept INT, phoneno INT) ROW_FORMAT=compact PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (10), PARTITION P1 VALUES LESS THAN (20), PARTITION P2 VALUES LESS THAN (30), PARTITION P3 VALUES LESS THAN (40), PARTITION P4 VALUES LESS THAN (50));
1680 INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111);
1681 INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222);
1682 INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333);
1683 INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444);
1684 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1685 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1686 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1687 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1688 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1689 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1690 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1691 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1692 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1693 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1694 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1695 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1696 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1697 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1698 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1699 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1700 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1701 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1702 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1703 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1704 ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50));
1705 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1706 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1707 SELECT * FROM t1 ORDER BY id;
1708 id name job dept sal
1709 10 aaa xyz1 1 NULL
1710 20 bbb xyz2 2 NULL
1711 30 ccc xyz3 3 NULL
1712 40 ddd xyz4 4 NULL
1713 # ------------------------------------------------------------
1714 # Scenario 3.1 : Same table with tablespace discarded
1715 # ------------------------------------------------------------
1716 # EXPORT
1717 FLUSH TABLE t1 FOR EXPORT;
1718 backup: t1#p#p0
1719 backup: t1#p#p1
1720 backup: t1#p#p2
1721 backup: t1#p#p3
1722 UNLOCK TABLES;
1723 # IMPORT
1724 ALTER TABLE t1 discard tablespace;
1725 restore: t1#p#p0 .ibd and .cfg files
1726 restore: t1#p#p1 .ibd and .cfg files
1727 restore: t1#p#p2 .ibd and .cfg files
1728 restore: t1#p#p3 .ibd and .cfg files
1729 ALTER TABLE t1 IMPORT TABLESPACE;
1730 SELECT * FROM t1 ORDER BY id;
1731 id name job dept sal
1732 10 aaa xyz1 1 NULL
1733 20 bbb xyz2 2 NULL
1734 30 ccc xyz3 3 NULL
1735 40 ddd xyz4 4 NULL
1736 # Metadata from INFORMATION_SCHEMA.TABLES
1737 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1738 test/t1#p#p0 8 0 22
1739 # Metadata from INFORMATION_SCHEMA.COLUMNS
1740 NAME POS MTYPE PRTYPE HAS_DEFAULT
1741 id 0 6 1283 0
1742 name 1 12 16711695 0
1743 job 2 12 16711695 0
1744 dept 3 6 1027 0
1745 sal 4 6 1027 1
1746 # Metadata from INFORMATION_SCHEMA.TABLES
1747 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1748 test/t1#p#p1 8 0 22
1749 # Metadata from INFORMATION_SCHEMA.COLUMNS
1750 NAME POS MTYPE PRTYPE HAS_DEFAULT
1751 id 0 6 1283 0
1752 name 1 12 16711695 0
1753 job 2 12 16711695 0
1754 dept 3 6 1027 0
1755 sal 4 6 1027 1
1756 # Metadata from INFORMATION_SCHEMA.TABLES
1757 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1758 test/t1#p#p2 8 0 22
1759 # Metadata from INFORMATION_SCHEMA.COLUMNS
1760 NAME POS MTYPE PRTYPE HAS_DEFAULT
1761 id 0 6 1283 0
1762 name 1 12 16711695 0
1763 job 2 12 16711695 0
1764 dept 3 6 1027 0
1765 sal 4 6 1027 1
1766 # Metadata from INFORMATION_SCHEMA.TABLES
1767 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1768 test/t1#p#p3 8 0 22
1769 # Metadata from INFORMATION_SCHEMA.COLUMNS
1770 NAME POS MTYPE PRTYPE HAS_DEFAULT
1771 id 0 6 1283 0
1772 name 1 12 16711695 0
1773 job 2 12 16711695 0
1774 dept 3 6 1027 0
1775 sal 4 6 1027 1
1776 # ------------------------------------------------------------
1777 # Scenario 3.2 : New table created with like
1778 # ------------------------------------------------------------
1779 # EXPORT
1780 FLUSH TABLE t1 FOR EXPORT;
1781 backup: t1#p#p0
1782 backup: t1#p#p1
1783 backup: t1#p#p2
1784 backup: t1#p#p3
1785 UNLOCK TABLES;
1786 CREATE TABLE t2 like t1;
1787 SHOW CREATE TABLE t2;
1788 Table Create Table
1789 t2 CREATE TABLE `t2` (
1790 `id` int NOT NULL,
1791 `name` varchar(50) DEFAULT NULL,
1792 `job` varchar(50) DEFAULT NULL,
1793 `dept` int DEFAULT NULL,
1794 `sal` int DEFAULT NULL,
1795 PRIMARY KEY (`id`)
1796 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
1797 /*!50100 PARTITION BY RANGE (`id`)
1798 (PARTITION P0 VALUES LESS THAN (10) ENGINE = InnoDB,
1799 PARTITION P1 VALUES LESS THAN (20) ENGINE = InnoDB,
1800 PARTITION P2 VALUES LESS THAN (30) ENGINE = InnoDB,
1801 PARTITION P3 VALUES LESS THAN (50) ENGINE = InnoDB) */
1802 DROP TABLE t1;
1803 ALTER TABLE t2 RENAME as t1;
1804 # IMPORT
1805 ALTER TABLE t1 discard tablespace;
1806 restore: t1#p#p0 .ibd and .cfg files
1807 restore: t1#p#p1 .ibd and .cfg files
1808 restore: t1#p#p2 .ibd and .cfg files
1809 restore: t1#p#p3 .ibd and .cfg files
1810 ALTER TABLE t1 IMPORT TABLESPACE;
1811 SELECT * FROM t1 ORDER BY id;
1812 id name job dept sal
1813 10 aaa xyz1 1 NULL
1814 20 bbb xyz2 2 NULL
1815 30 ccc xyz3 3 NULL
1816 40 ddd xyz4 4 NULL
1817 # Metadata from INFORMATION_SCHEMA.TABLES
1818 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1819 test/t1#p#p0 8 0 22
1820 # Metadata from INFORMATION_SCHEMA.COLUMNS
1821 NAME POS MTYPE PRTYPE HAS_DEFAULT
1822 id 0 6 1283 0
1823 name 1 12 16711695 0
1824 job 2 12 16711695 0
1825 dept 3 6 1027 0
1826 sal 4 6 1027 1
1827 # Metadata from INFORMATION_SCHEMA.TABLES
1828 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1829 test/t1#p#p1 8 0 22
1830 # Metadata from INFORMATION_SCHEMA.COLUMNS
1831 NAME POS MTYPE PRTYPE HAS_DEFAULT
1832 id 0 6 1283 0
1833 name 1 12 16711695 0
1834 job 2 12 16711695 0
1835 dept 3 6 1027 0
1836 sal 4 6 1027 1
1837 # Metadata from INFORMATION_SCHEMA.TABLES
1838 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1839 test/t1#p#p2 8 0 22
1840 # Metadata from INFORMATION_SCHEMA.COLUMNS
1841 NAME POS MTYPE PRTYPE HAS_DEFAULT
1842 id 0 6 1283 0
1843 name 1 12 16711695 0
1844 job 2 12 16711695 0
1845 dept 3 6 1027 0
1846 sal 4 6 1027 1
1847 # Metadata from INFORMATION_SCHEMA.TABLES
1848 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
1849 test/t1#p#p3 8 0 22
1850 # Metadata from INFORMATION_SCHEMA.COLUMNS
1851 NAME POS MTYPE PRTYPE HAS_DEFAULT
1852 id 0 6 1283 0
1853 name 1 12 16711695 0
1854 job 2 12 16711695 0
1855 dept 3 6 1027 0
1856 sal 4 6 1027 1
1857 DROP TABLE t1;
1858 ###########
1859 # CLEANUP #
1860 ###########