Debian Codegraph @

mysql-test/suite/innodb/r/instant_ddl_import_partition_debug.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 # DD Metadata of columns in table
29 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
30 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
31 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
32 c2 3 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID;
33 c3 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
34 c4 5 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
35 c5 6 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
36 DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
37 DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
38 DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
39 # DD Metadata of partitions in table
40 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
41 p0 128 NULL
42 p1 256 NULL
43 p2 384 NULL
44 p3 MAXVALUE NULL
45 # Metadata from INFORMATION_SCHEMA.TABLES
46 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
47 test/t1#p#p0 9 0 2 4 6 6
48 # Metadata from INFORMATION_SCHEMA.COLUMNS
49 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
50 c1 0 6 1027 0 0 0
51 c6 1 13 16711934 1 2 0
52 c2 2 6 1027 0 0 0
53 c3 3 6 1027 0 0 0
54 c4 4 5 16711932 0 0 0
55 c5 5 13 16711934 1 1 0
56 # Metadata from INFORMATION_SCHEMA.TABLES
57 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
58 test/t1#p#p1 9 0 2 4 6 6
59 # Metadata from INFORMATION_SCHEMA.COLUMNS
60 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
61 c1 0 6 1027 0 0 0
62 c6 1 13 16711934 1 2 0
63 c2 2 6 1027 0 0 0
64 c3 3 6 1027 0 0 0
65 c4 4 5 16711932 0 0 0
66 c5 5 13 16711934 1 1 0
67 # Metadata from INFORMATION_SCHEMA.TABLES
68 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
69 test/t1#p#p2 9 0 2 4 6 6
70 # Metadata from INFORMATION_SCHEMA.COLUMNS
71 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
72 c1 0 6 1027 0 0 0
73 c6 1 13 16711934 1 2 0
74 c2 2 6 1027 0 0 0
75 c3 3 6 1027 0 0 0
76 c4 4 5 16711932 0 0 0
77 c5 5 13 16711934 1 1 0
78 # Metadata from INFORMATION_SCHEMA.TABLES
79 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
80 test/t1#p#p3 9 0 2 4 6 6
81 # Metadata from INFORMATION_SCHEMA.COLUMNS
82 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
83 c1 0 6 1027 0 0 0
84 c6 1 13 16711934 1 2 0
85 c2 2 6 1027 0 0 0
86 c3 3 6 1027 0 0 0
87 c4 4 5 16711932 0 0 0
88 c5 5 13 16711934 1 1 0
89 # ------------------------------------------------------------
90 # Scenario 1.1 : Same table with tablespace discarded
91 # ------------------------------------------------------------
92 # EXPORT
93 FLUSH TABLE t1 FOR EXPORT;
94 backup: t1#p#p0
95 backup: t1#p#p1
96 backup: t1#p#p2
97 backup: t1#p#p3
98 UNLOCK TABLES;
99 # IMPORT
100 ALTER TABLE t1 discard tablespace;
101 restore: t1#p#p0 .ibd and .cfg files
102 restore: t1#p#p1 .ibd and .cfg files
103 restore: t1#p#p2 .ibd and .cfg files
104 restore: t1#p#p3 .ibd and .cfg files
105 ALTER TABLE t1 IMPORT TABLESPACE;
106 SELECT * FROM t1 ORDER BY c1;
107 c1 c6 c2 c3 c4 c5
108 1 NULL 2 3 abcdefg c5_def
109 100 NULL 200 300 qwerty c5_def
110 200 NULL 300 400 asdfg c5_def
111 300 NULL 400 500 xxxxxx r4c5
112 400 r5c6 500 600 xxxxxx r5c5
113 # DD Metadata of columns in table
114 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
115 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
116 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
117 c2 3 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID;
118 c3 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
119 c4 5 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
120 c5 6 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
121 DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
122 DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
123 DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
124 # DD Metadata of partitions in table
125 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
126 p0 128 discard=0;
127 p1 256 discard=0;
128 p2 384 discard=0;
129 p3 MAXVALUE discard=0;
130 # Metadata from INFORMATION_SCHEMA.TABLES
131 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
132 test/t1#p#p0 9 0 2 4 6 6
133 # Metadata from INFORMATION_SCHEMA.COLUMNS
134 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
135 c1 0 6 1027 0 0 0
136 c6 1 13 16711934 1 2 0
137 c2 2 6 1027 0 0 0
138 c3 3 6 1027 0 0 0
139 c4 4 5 16711932 0 0 0
140 c5 5 13 16711934 1 1 0
141 # Metadata from INFORMATION_SCHEMA.TABLES
142 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
143 test/t1#p#p1 9 0 2 4 6 6
144 # Metadata from INFORMATION_SCHEMA.COLUMNS
145 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
146 c1 0 6 1027 0 0 0
147 c6 1 13 16711934 1 2 0
148 c2 2 6 1027 0 0 0
149 c3 3 6 1027 0 0 0
150 c4 4 5 16711932 0 0 0
151 c5 5 13 16711934 1 1 0
152 # Metadata from INFORMATION_SCHEMA.TABLES
153 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
154 test/t1#p#p2 9 0 2 4 6 6
155 # Metadata from INFORMATION_SCHEMA.COLUMNS
156 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
157 c1 0 6 1027 0 0 0
158 c6 1 13 16711934 1 2 0
159 c2 2 6 1027 0 0 0
160 c3 3 6 1027 0 0 0
161 c4 4 5 16711932 0 0 0
162 c5 5 13 16711934 1 1 0
163 # Metadata from INFORMATION_SCHEMA.TABLES
164 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
165 test/t1#p#p3 9 0 2 4 6 6
166 # Metadata from INFORMATION_SCHEMA.COLUMNS
167 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
168 c1 0 6 1027 0 0 0
169 c6 1 13 16711934 1 2 0
170 c2 2 6 1027 0 0 0
171 c3 3 6 1027 0 0 0
172 c4 4 5 16711932 0 0 0
173 c5 5 13 16711934 1 1 0
174 # ------------------------------------------------------------
175 # Scenario 1.2 : New table created with like
176 # ------------------------------------------------------------
177 # EXPORT
178 FLUSH TABLE t1 FOR EXPORT;
179 backup: t1#p#p0
180 backup: t1#p#p1
181 backup: t1#p#p2
182 backup: t1#p#p3
183 UNLOCK TABLES;
184 CREATE TABLE t2 like t1;
185 SHOW CREATE TABLE t2;
186 Table Create Table
187 t2 CREATE TABLE `t2` (
188 `c1` int DEFAULT NULL,
189 `c6` char(20) DEFAULT NULL,
190 `c2` int DEFAULT NULL,
191 `c3` int DEFAULT NULL,
192 `c4` text,
193 `c5` char(20) DEFAULT 'c5_def'
194 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
195 /*!50100 PARTITION BY RANGE ((`c1` * 2))
196 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
197 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
198 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
199 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
200 DROP TABLE t1;
201 ALTER TABLE t2 RENAME as t1;
202 # IMPORT
203 ALTER TABLE t1 discard tablespace;
204 restore: t1#p#p0 .ibd and .cfg files
205 restore: t1#p#p1 .ibd and .cfg files
206 restore: t1#p#p2 .ibd and .cfg files
207 restore: t1#p#p3 .ibd and .cfg files
208 ALTER TABLE t1 IMPORT TABLESPACE;
209 SELECT * FROM t1 ORDER BY c1;
210 c1 c6 c2 c3 c4 c5
211 1 NULL 2 3 abcdefg c5_def
212 100 NULL 200 300 qwerty c5_def
213 200 NULL 300 400 asdfg c5_def
214 300 NULL 400 500 xxxxxx r4c5
215 400 r5c6 500 600 xxxxxx r5c5
216 # DD Metadata of columns in table
217 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
218 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
219 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
220 c2 3 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID;
221 c3 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
222 c4 5 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
223 c5 6 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
224 DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
225 DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
226 DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
227 # DD Metadata of partitions in table
228 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
229 p0 128 discard=0;
230 p1 256 discard=0;
231 p2 384 discard=0;
232 p3 MAXVALUE discard=0;
233 # Metadata from INFORMATION_SCHEMA.TABLES
234 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
235 test/t1#p#p0 9 0 2 4 6 6
236 # Metadata from INFORMATION_SCHEMA.COLUMNS
237 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
238 c1 0 6 1027 0 0 0
239 c6 1 13 16711934 1 2 0
240 c2 2 6 1027 0 0 0
241 c3 3 6 1027 0 0 0
242 c4 4 5 16711932 0 0 0
243 c5 5 13 16711934 1 1 0
244 # Metadata from INFORMATION_SCHEMA.TABLES
245 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
246 test/t1#p#p1 9 0 2 4 6 6
247 # Metadata from INFORMATION_SCHEMA.COLUMNS
248 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
249 c1 0 6 1027 0 0 0
250 c6 1 13 16711934 1 2 0
251 c2 2 6 1027 0 0 0
252 c3 3 6 1027 0 0 0
253 c4 4 5 16711932 0 0 0
254 c5 5 13 16711934 1 1 0
255 # Metadata from INFORMATION_SCHEMA.TABLES
256 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
257 test/t1#p#p2 9 0 2 4 6 6
258 # Metadata from INFORMATION_SCHEMA.COLUMNS
259 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
260 c1 0 6 1027 0 0 0
261 c6 1 13 16711934 1 2 0
262 c2 2 6 1027 0 0 0
263 c3 3 6 1027 0 0 0
264 c4 4 5 16711932 0 0 0
265 c5 5 13 16711934 1 1 0
266 # Metadata from INFORMATION_SCHEMA.TABLES
267 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
268 test/t1#p#p3 9 0 2 4 6 6
269 # Metadata from INFORMATION_SCHEMA.COLUMNS
270 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
271 c1 0 6 1027 0 0 0
272 c6 1 13 16711934 1 2 0
273 c2 2 6 1027 0 0 0
274 c3 3 6 1027 0 0 0
275 c4 4 5 16711932 0 0 0
276 c5 5 13 16711934 1 1 0
277 # ------------------------------------------------------------
278 # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5]
279 # ------------------------------------------------------------
280 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
281 # DD Metadata of columns in table
282 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
283 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
284 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
285 c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
286 c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
287 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
288 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
289 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
290 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
291 !hidden!_dropped_v3_p4_c2 9 MYSQL_TYPE_LONG 0 SE physical_pos=4;version_dropped=3;
292 # DD Metadata of partitions in table
293 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
294 p0 128 discard=0;
295 p1 256 discard=0;
296 p2 384 discard=0;
297 p3 MAXVALUE discard=0;
298 # Metadata from INFORMATION_SCHEMA.TABLES
299 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
300 test/t1#p#p0 8 0 3 4 5 6
301 # Metadata from INFORMATION_SCHEMA.COLUMNS
302 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
303 c1 0 6 1027 0 0 0
304 c6 1 13 16711934 1 2 0
305 c3 2 6 1027 0 0 0
306 c4 3 5 16711932 0 0 0
307 c5 4 13 16711934 1 1 0
308 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
309 # Metadata from INFORMATION_SCHEMA.TABLES
310 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
311 test/t1#p#p1 8 0 3 4 5 6
312 # Metadata from INFORMATION_SCHEMA.COLUMNS
313 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
314 c1 0 6 1027 0 0 0
315 c6 1 13 16711934 1 2 0
316 c3 2 6 1027 0 0 0
317 c4 3 5 16711932 0 0 0
318 c5 4 13 16711934 1 1 0
319 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
320 # Metadata from INFORMATION_SCHEMA.TABLES
321 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
322 test/t1#p#p2 8 0 3 4 5 6
323 # Metadata from INFORMATION_SCHEMA.COLUMNS
324 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
325 c1 0 6 1027 0 0 0
326 c6 1 13 16711934 1 2 0
327 c3 2 6 1027 0 0 0
328 c4 3 5 16711932 0 0 0
329 c5 4 13 16711934 1 1 0
330 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
331 # Metadata from INFORMATION_SCHEMA.TABLES
332 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
333 test/t1#p#p3 8 0 3 4 5 6
334 # Metadata from INFORMATION_SCHEMA.COLUMNS
335 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
336 c1 0 6 1027 0 0 0
337 c6 1 13 16711934 1 2 0
338 c3 2 6 1027 0 0 0
339 c4 3 5 16711932 0 0 0
340 c5 4 13 16711934 1 1 0
341 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
342 SELECT * FROM t1 ORDER BY c1;
343 c1 c6 c3 c4 c5
344 1 NULL 3 abcdefg c5_def
345 100 NULL 300 qwerty c5_def
346 200 NULL 400 asdfg c5_def
347 300 NULL 500 xxxxxx r4c5
348 400 r5c6 600 xxxxxx r5c5
349 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ;
350 SELECT * FROM t1 ORDER BY c1;
351 c1 c6 c3 c4 c5
352 1 NULL 3 abcdefg c5_def
353 100 NULL 300 qwerty c5_def
354 200 NULL 400 asdfg c5_def
355 300 NULL 500 xxxxxx r4c5
356 400 r5c6 600 xxxxxx r5c5
357 500 r6c6 700 xxxxxx r6c5
358 # ------------------------------------------------------------
359 # Scenario 2.1 : Same table with tablespace discarded
360 # ------------------------------------------------------------
361 # EXPORT
362 FLUSH TABLE t1 FOR EXPORT;
363 backup: t1#p#p0
364 backup: t1#p#p1
365 backup: t1#p#p2
366 backup: t1#p#p3
367 UNLOCK TABLES;
368 # IMPORT
369 ALTER TABLE t1 discard tablespace;
370 restore: t1#p#p0 .ibd and .cfg files
371 restore: t1#p#p1 .ibd and .cfg files
372 restore: t1#p#p2 .ibd and .cfg files
373 restore: t1#p#p3 .ibd and .cfg files
374 ALTER TABLE t1 IMPORT TABLESPACE;
375 SELECT * FROM t1 ORDER BY c1;
376 c1 c6 c3 c4 c5
377 1 NULL 3 abcdefg c5_def
378 100 NULL 300 qwerty c5_def
379 200 NULL 400 asdfg c5_def
380 300 NULL 500 xxxxxx r4c5
381 400 r5c6 600 xxxxxx r5c5
382 500 r6c6 700 xxxxxx r6c5
383 # DD Metadata of columns in table
384 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
385 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
386 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
387 c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
388 c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
389 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
390 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
391 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
392 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
393 !hidden!_dropped_v3_p4_c2 9 MYSQL_TYPE_LONG 0 SE physical_pos=4;table_id=TABLE_ID;version_dropped=3;
394 # DD Metadata of partitions in table
395 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
396 p0 128 discard=0;
397 p1 256 discard=0;
398 p2 384 discard=0;
399 p3 MAXVALUE discard=0;
400 # Metadata from INFORMATION_SCHEMA.TABLES
401 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
402 test/t1#p#p0 8 0 3 4 5 6
403 # Metadata from INFORMATION_SCHEMA.COLUMNS
404 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
405 c1 0 6 1027 0 0 0
406 c6 1 13 16711934 1 2 0
407 c3 2 6 1027 0 0 0
408 c4 3 5 16711932 0 0 0
409 c5 4 13 16711934 1 1 0
410 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
411 # Metadata from INFORMATION_SCHEMA.TABLES
412 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
413 test/t1#p#p1 8 0 3 4 5 6
414 # Metadata from INFORMATION_SCHEMA.COLUMNS
415 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
416 c1 0 6 1027 0 0 0
417 c6 1 13 16711934 1 2 0
418 c3 2 6 1027 0 0 0
419 c4 3 5 16711932 0 0 0
420 c5 4 13 16711934 1 1 0
421 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
422 # Metadata from INFORMATION_SCHEMA.TABLES
423 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
424 test/t1#p#p2 8 0 3 4 5 6
425 # Metadata from INFORMATION_SCHEMA.COLUMNS
426 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
427 c1 0 6 1027 0 0 0
428 c6 1 13 16711934 1 2 0
429 c3 2 6 1027 0 0 0
430 c4 3 5 16711932 0 0 0
431 c5 4 13 16711934 1 1 0
432 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
433 # Metadata from INFORMATION_SCHEMA.TABLES
434 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
435 test/t1#p#p3 8 0 3 4 5 6
436 # Metadata from INFORMATION_SCHEMA.COLUMNS
437 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
438 c1 0 6 1027 0 0 0
439 c6 1 13 16711934 1 2 0
440 c3 2 6 1027 0 0 0
441 c4 3 5 16711932 0 0 0
442 c5 4 13 16711934 1 1 0
443 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
444 # ------------------------------------------------------------
445 # Scenario 2.2 : New table created with like
446 # ------------------------------------------------------------
447 # EXPORT
448 FLUSH TABLE t1 FOR EXPORT;
449 backup: t1#p#p0
450 backup: t1#p#p1
451 backup: t1#p#p2
452 backup: t1#p#p3
453 UNLOCK TABLES;
454 CREATE TABLE t2 like t1;
455 SHOW CREATE TABLE t2;
456 Table Create Table
457 t2 CREATE TABLE `t2` (
458 `c1` int DEFAULT NULL,
459 `c6` char(20) DEFAULT NULL,
460 `c3` int DEFAULT NULL,
461 `c4` text,
462 `c5` char(20) DEFAULT 'c5_def'
463 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
464 /*!50100 PARTITION BY RANGE ((`c1` * 2))
465 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
466 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
467 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
468 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
469 DROP TABLE t1;
470 ALTER TABLE t2 RENAME as t1;
471 # IMPORT
472 ALTER TABLE t1 discard tablespace;
473 restore: t1#p#p0 .ibd and .cfg files
474 restore: t1#p#p1 .ibd and .cfg files
475 restore: t1#p#p2 .ibd and .cfg files
476 restore: t1#p#p3 .ibd and .cfg files
477 ALTER TABLE t1 IMPORT TABLESPACE;
478 SELECT * FROM t1 ORDER BY c1;
479 c1 c6 c3 c4 c5
480 1 NULL 3 abcdefg c5_def
481 100 NULL 300 qwerty c5_def
482 200 NULL 400 asdfg c5_def
483 300 NULL 500 xxxxxx r4c5
484 400 r5c6 600 xxxxxx r5c5
485 500 r6c6 700 xxxxxx r6c5
486 # DD Metadata of columns in table
487 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
488 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
489 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
490 c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
491 c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
492 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
493 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
494 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
495 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
496 !hidden!_dropped_v3_p4_c2 9 MYSQL_TYPE_LONG 0 SE physical_pos=4;table_id=TABLE_ID;version_dropped=3;
497 # DD Metadata of partitions in table
498 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
499 p0 128 discard=0;
500 p1 256 discard=0;
501 p2 384 discard=0;
502 p3 MAXVALUE discard=0;
503 # Metadata from INFORMATION_SCHEMA.TABLES
504 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
505 test/t1#p#p0 8 0 3 4 5 6
506 # Metadata from INFORMATION_SCHEMA.COLUMNS
507 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
508 c1 0 6 1027 0 0 0
509 c6 1 13 16711934 1 2 0
510 c3 2 6 1027 0 0 0
511 c4 3 5 16711932 0 0 0
512 c5 4 13 16711934 1 1 0
513 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
514 # Metadata from INFORMATION_SCHEMA.TABLES
515 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
516 test/t1#p#p1 8 0 3 4 5 6
517 # Metadata from INFORMATION_SCHEMA.COLUMNS
518 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
519 c1 0 6 1027 0 0 0
520 c6 1 13 16711934 1 2 0
521 c3 2 6 1027 0 0 0
522 c4 3 5 16711932 0 0 0
523 c5 4 13 16711934 1 1 0
524 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
525 # Metadata from INFORMATION_SCHEMA.TABLES
526 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
527 test/t1#p#p2 8 0 3 4 5 6
528 # Metadata from INFORMATION_SCHEMA.COLUMNS
529 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
530 c1 0 6 1027 0 0 0
531 c6 1 13 16711934 1 2 0
532 c3 2 6 1027 0 0 0
533 c4 3 5 16711932 0 0 0
534 c5 4 13 16711934 1 1 0
535 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
536 # Metadata from INFORMATION_SCHEMA.TABLES
537 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
538 test/t1#p#p3 8 0 3 4 5 6
539 # Metadata from INFORMATION_SCHEMA.COLUMNS
540 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
541 c1 0 6 1027 0 0 0
542 c6 1 13 16711934 1 2 0
543 c3 2 6 1027 0 0 0
544 c4 3 5 16711932 0 0 0
545 c5 4 13 16711934 1 1 0
546 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
547 DROP TABLE t1;
548 # ------------------------------------------------------------
549 # A Partitioned table having multiple INSTANT ADD/DROP columns
550 # and REORGNIZED partitions.
551 # ------------------------------------------------------------
552 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));
553 INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111);
554 INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222);
555 INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333);
556 INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444);
557 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
558 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
559 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
560 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
561 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
562 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
563 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
564 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
565 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
566 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
567 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
568 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
569 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
570 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
571 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
572 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
573 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
574 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
575 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
576 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
577 ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50));
578 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
579 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
580 SELECT * FROM t1 ORDER BY id;
581 id name job dept sal
582 10 aaa xyz1 1 NULL
583 20 bbb xyz2 2 NULL
584 30 ccc xyz3 3 NULL
585 40 ddd xyz4 4 NULL
586 # ------------------------------------------------------------
587 # Scenario 3.1 : Same table with tablespace discarded
588 # ------------------------------------------------------------
589 # EXPORT
590 FLUSH TABLE t1 FOR EXPORT;
591 backup: t1#p#p0
592 backup: t1#p#p1
593 backup: t1#p#p2
594 backup: t1#p#p3
595 UNLOCK TABLES;
596 # IMPORT
597 ALTER TABLE t1 discard tablespace;
598 restore: t1#p#p0 .ibd and .cfg files
599 restore: t1#p#p1 .ibd and .cfg files
600 restore: t1#p#p2 .ibd and .cfg files
601 restore: t1#p#p3 .ibd and .cfg files
602 ALTER TABLE t1 IMPORT TABLESPACE;
603 SELECT * FROM t1 ORDER BY id;
604 id name job dept sal
605 10 aaa xyz1 1 NULL
606 20 bbb xyz2 2 NULL
607 30 ccc xyz3 3 NULL
608 40 ddd xyz4 4 NULL
609 # DD Metadata of columns in table
610 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
611 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
612 name 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
613 job 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
614 dept 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
615 sal 5 MYSQL_TYPE_LONG 0 Visible default_null=1;physical_pos=17;table_id=TABLE_ID;version_added=21;
616 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
617 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
618 !hidden!_dropped_v2_p6_phoneno 8 MYSQL_TYPE_LONG 0 SE physical_pos=6;table_id=TABLE_ID;version_dropped=2;
619 !hidden!_dropped_v4_p7_sal 9 MYSQL_TYPE_LONG 0 SE physical_pos=7;table_id=TABLE_ID;version_added=1;version_dropped=4;
620 !hidden!_dropped_v6_p8_phoneno 10 MYSQL_TYPE_LONG 0 SE physical_pos=8;table_id=TABLE_ID;version_added=3;version_dropped=6;
621 !hidden!_dropped_v8_p9_sal 11 MYSQL_TYPE_LONG 0 SE physical_pos=9;table_id=TABLE_ID;version_added=5;version_dropped=8;
622 !hidden!_dropped_v10_p10_phoneno 12 MYSQL_TYPE_LONG 0 SE physical_pos=10;table_id=TABLE_ID;version_added=7;version_dropped=10;
623 !hidden!_dropped_v12_p11_sal 13 MYSQL_TYPE_LONG 0 SE physical_pos=11;table_id=TABLE_ID;version_added=9;version_dropped=12;
624 !hidden!_dropped_v14_p12_phoneno 14 MYSQL_TYPE_LONG 0 SE physical_pos=12;table_id=TABLE_ID;version_added=11;version_dropped=14;
625 !hidden!_dropped_v16_p13_sal 15 MYSQL_TYPE_LONG 0 SE physical_pos=13;table_id=TABLE_ID;version_added=13;version_dropped=16;
626 !hidden!_dropped_v18_p14_phoneno 16 MYSQL_TYPE_LONG 0 SE physical_pos=14;table_id=TABLE_ID;version_added=15;version_dropped=18;
627 !hidden!_dropped_v20_p15_sal 17 MYSQL_TYPE_LONG 0 SE physical_pos=15;table_id=TABLE_ID;version_added=17;version_dropped=20;
628 !hidden!_dropped_v22_p16_phoneno 18 MYSQL_TYPE_LONG 0 SE physical_pos=16;table_id=TABLE_ID;version_added=19;version_dropped=22;
629 # DD Metadata of partitions in table
630 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
631 P0 10 discard=0;
632 P1 20 discard=0;
633 P2 30 discard=0;
634 P3 50 discard=0;
635 # Metadata from INFORMATION_SCHEMA.TABLES
636 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
637 test/t1#p#p0 8 0 22 5 5 16
638 # Metadata from INFORMATION_SCHEMA.COLUMNS
639 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
640 id 0 6 1283 0 0 0
641 name 1 12 16711695 0 0 0
642 job 2 12 16711695 0 0 0
643 dept 3 6 1027 0 0 0
644 sal 4 6 1027 1 21 0
645 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
646 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
647 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
648 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
649 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
650 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
651 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
652 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
653 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
654 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
655 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
656 # Metadata from INFORMATION_SCHEMA.TABLES
657 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
658 test/t1#p#p1 8 0 22 5 5 16
659 # Metadata from INFORMATION_SCHEMA.COLUMNS
660 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
661 id 0 6 1283 0 0 0
662 name 1 12 16711695 0 0 0
663 job 2 12 16711695 0 0 0
664 dept 3 6 1027 0 0 0
665 sal 4 6 1027 1 21 0
666 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
667 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
668 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
669 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
670 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
671 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
672 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
673 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
674 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
675 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
676 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
677 # Metadata from INFORMATION_SCHEMA.TABLES
678 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
679 test/t1#p#p2 8 0 22 5 5 16
680 # Metadata from INFORMATION_SCHEMA.COLUMNS
681 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
682 id 0 6 1283 0 0 0
683 name 1 12 16711695 0 0 0
684 job 2 12 16711695 0 0 0
685 dept 3 6 1027 0 0 0
686 sal 4 6 1027 1 21 0
687 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
688 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
689 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
690 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
691 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
692 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
693 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
694 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
695 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
696 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
697 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
698 # Metadata from INFORMATION_SCHEMA.TABLES
699 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
700 test/t1#p#p3 8 0 22 5 5 16
701 # Metadata from INFORMATION_SCHEMA.COLUMNS
702 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
703 id 0 6 1283 0 0 0
704 name 1 12 16711695 0 0 0
705 job 2 12 16711695 0 0 0
706 dept 3 6 1027 0 0 0
707 sal 4 6 1027 1 21 0
708 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
709 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
710 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
711 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
712 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
713 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
714 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
715 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
716 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
717 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
718 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
719 # ------------------------------------------------------------
720 # Scenario 3.2 : New table created with like
721 # ------------------------------------------------------------
722 # EXPORT
723 FLUSH TABLE t1 FOR EXPORT;
724 backup: t1#p#p0
725 backup: t1#p#p1
726 backup: t1#p#p2
727 backup: t1#p#p3
728 UNLOCK TABLES;
729 CREATE TABLE t2 like t1;
730 SHOW CREATE TABLE t2;
731 Table Create Table
732 t2 CREATE TABLE `t2` (
733 `id` int NOT NULL,
734 `name` varchar(50) DEFAULT NULL,
735 `job` varchar(50) DEFAULT NULL,
736 `dept` int DEFAULT NULL,
737 `sal` int DEFAULT NULL,
738 PRIMARY KEY (`id`)
739 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
740 /*!50100 PARTITION BY RANGE (`id`)
741 (PARTITION P0 VALUES LESS THAN (10) ENGINE = InnoDB,
742 PARTITION P1 VALUES LESS THAN (20) ENGINE = InnoDB,
743 PARTITION P2 VALUES LESS THAN (30) ENGINE = InnoDB,
744 PARTITION P3 VALUES LESS THAN (50) ENGINE = InnoDB) */
745 DROP TABLE t1;
746 ALTER TABLE t2 RENAME as t1;
747 # IMPORT
748 ALTER TABLE t1 discard tablespace;
749 restore: t1#p#p0 .ibd and .cfg files
750 restore: t1#p#p1 .ibd and .cfg files
751 restore: t1#p#p2 .ibd and .cfg files
752 restore: t1#p#p3 .ibd and .cfg files
753 ALTER TABLE t1 IMPORT TABLESPACE;
754 SELECT * FROM t1 ORDER BY id;
755 id name job dept sal
756 10 aaa xyz1 1 NULL
757 20 bbb xyz2 2 NULL
758 30 ccc xyz3 3 NULL
759 40 ddd xyz4 4 NULL
760 # DD Metadata of columns in table
761 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
762 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
763 name 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
764 job 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
765 dept 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
766 sal 5 MYSQL_TYPE_LONG 0 Visible default_null=1;physical_pos=17;table_id=TABLE_ID;version_added=21;
767 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
768 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
769 !hidden!_dropped_v2_p6_phoneno 8 MYSQL_TYPE_LONG 0 SE physical_pos=6;table_id=TABLE_ID;version_dropped=2;
770 !hidden!_dropped_v4_p7_sal 9 MYSQL_TYPE_LONG 0 SE physical_pos=7;table_id=TABLE_ID;version_added=1;version_dropped=4;
771 !hidden!_dropped_v6_p8_phoneno 10 MYSQL_TYPE_LONG 0 SE physical_pos=8;table_id=TABLE_ID;version_added=3;version_dropped=6;
772 !hidden!_dropped_v8_p9_sal 11 MYSQL_TYPE_LONG 0 SE physical_pos=9;table_id=TABLE_ID;version_added=5;version_dropped=8;
773 !hidden!_dropped_v10_p10_phoneno 12 MYSQL_TYPE_LONG 0 SE physical_pos=10;table_id=TABLE_ID;version_added=7;version_dropped=10;
774 !hidden!_dropped_v12_p11_sal 13 MYSQL_TYPE_LONG 0 SE physical_pos=11;table_id=TABLE_ID;version_added=9;version_dropped=12;
775 !hidden!_dropped_v14_p12_phoneno 14 MYSQL_TYPE_LONG 0 SE physical_pos=12;table_id=TABLE_ID;version_added=11;version_dropped=14;
776 !hidden!_dropped_v16_p13_sal 15 MYSQL_TYPE_LONG 0 SE physical_pos=13;table_id=TABLE_ID;version_added=13;version_dropped=16;
777 !hidden!_dropped_v18_p14_phoneno 16 MYSQL_TYPE_LONG 0 SE physical_pos=14;table_id=TABLE_ID;version_added=15;version_dropped=18;
778 !hidden!_dropped_v20_p15_sal 17 MYSQL_TYPE_LONG 0 SE physical_pos=15;table_id=TABLE_ID;version_added=17;version_dropped=20;
779 !hidden!_dropped_v22_p16_phoneno 18 MYSQL_TYPE_LONG 0 SE physical_pos=16;table_id=TABLE_ID;version_added=19;version_dropped=22;
780 # DD Metadata of partitions in table
781 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
782 P0 10 discard=0;
783 P1 20 discard=0;
784 P2 30 discard=0;
785 P3 50 discard=0;
786 # Metadata from INFORMATION_SCHEMA.TABLES
787 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
788 test/t1#p#p0 8 0 22 5 5 16
789 # Metadata from INFORMATION_SCHEMA.COLUMNS
790 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
791 id 0 6 1283 0 0 0
792 name 1 12 16711695 0 0 0
793 job 2 12 16711695 0 0 0
794 dept 3 6 1027 0 0 0
795 sal 4 6 1027 1 21 0
796 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
797 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
798 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
799 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
800 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
801 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
802 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
803 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
804 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
805 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
806 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
807 # Metadata from INFORMATION_SCHEMA.TABLES
808 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
809 test/t1#p#p1 8 0 22 5 5 16
810 # Metadata from INFORMATION_SCHEMA.COLUMNS
811 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
812 id 0 6 1283 0 0 0
813 name 1 12 16711695 0 0 0
814 job 2 12 16711695 0 0 0
815 dept 3 6 1027 0 0 0
816 sal 4 6 1027 1 21 0
817 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
818 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
819 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
820 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
821 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
822 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
823 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
824 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
825 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
826 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
827 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
828 # Metadata from INFORMATION_SCHEMA.TABLES
829 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
830 test/t1#p#p2 8 0 22 5 5 16
831 # Metadata from INFORMATION_SCHEMA.COLUMNS
832 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
833 id 0 6 1283 0 0 0
834 name 1 12 16711695 0 0 0
835 job 2 12 16711695 0 0 0
836 dept 3 6 1027 0 0 0
837 sal 4 6 1027 1 21 0
838 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
839 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
840 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
841 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
842 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
843 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
844 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
845 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
846 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
847 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
848 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
849 # Metadata from INFORMATION_SCHEMA.TABLES
850 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
851 test/t1#p#p3 8 0 22 5 5 16
852 # Metadata from INFORMATION_SCHEMA.COLUMNS
853 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
854 id 0 6 1283 0 0 0
855 name 1 12 16711695 0 0 0
856 job 2 12 16711695 0 0 0
857 dept 3 6 1027 0 0 0
858 sal 4 6 1027 1 21 0
859 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
860 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
861 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
862 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
863 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
864 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
865 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
866 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
867 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
868 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
869 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
870 DROP TABLE t1;
871 ###########
872 # CLEANUP #
873 ###########
874 # Create a table with 4 columns. [c1, c2, c3, c4]
875 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);
876 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
877 SELECT * from t1;
878 c1 c2 c3 c4
879 1 2 3 abcdefg
880 100 200 300 qwerty
881 200 300 400 asdfg
882 # Add a new column at the end. [c1, c2, c3, c4, +c5]
883 ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT;
884 INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ;
885 SELECT * FROM t1 ORDER BY c1;
886 c1 c2 c3 c4 c5
887 1 2 3 abcdefg c5_def
888 100 200 300 qwerty c5_def
889 200 300 400 asdfg c5_def
890 300 400 500 xxxxxx r4c5
891 # Add a new column in between. [c1, +c6, c2, c3, c4, c5]
892 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
893 INSERT INTO t1 VALUES(400, "r5c6", 500, 600, "xxxxxx", "r5c5") ;
894 SELECT * FROM t1 ORDER BY c1;
895 c1 c6 c2 c3 c4 c5
896 1 NULL 2 3 abcdefg c5_def
897 100 NULL 200 300 qwerty c5_def
898 200 NULL 300 400 asdfg c5_def
899 300 NULL 400 500 xxxxxx r4c5
900 400 r5c6 500 600 xxxxxx r5c5
901 # DD Metadata of columns in table
902 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
903 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
904 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
905 c2 3 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID;
906 c3 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
907 c4 5 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
908 c5 6 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
909 DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
910 DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
911 DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
912 # DD Metadata of partitions in table
913 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
914 p0 128 NULL
915 p1 256 NULL
916 p2 384 NULL
917 p3 MAXVALUE NULL
918 # Metadata from INFORMATION_SCHEMA.TABLES
919 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
920 test/t1#p#p0 9 0 2 4 6 6
921 # Metadata from INFORMATION_SCHEMA.COLUMNS
922 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
923 c1 0 6 1027 0 0 0
924 c6 1 13 16711934 1 2 0
925 c2 2 6 1027 0 0 0
926 c3 3 6 1027 0 0 0
927 c4 4 5 16711932 0 0 0
928 c5 5 13 16711934 1 1 0
929 # Metadata from INFORMATION_SCHEMA.TABLES
930 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
931 test/t1#p#p1 9 0 2 4 6 6
932 # Metadata from INFORMATION_SCHEMA.COLUMNS
933 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
934 c1 0 6 1027 0 0 0
935 c6 1 13 16711934 1 2 0
936 c2 2 6 1027 0 0 0
937 c3 3 6 1027 0 0 0
938 c4 4 5 16711932 0 0 0
939 c5 5 13 16711934 1 1 0
940 # Metadata from INFORMATION_SCHEMA.TABLES
941 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
942 test/t1#p#p2 9 0 2 4 6 6
943 # Metadata from INFORMATION_SCHEMA.COLUMNS
944 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
945 c1 0 6 1027 0 0 0
946 c6 1 13 16711934 1 2 0
947 c2 2 6 1027 0 0 0
948 c3 3 6 1027 0 0 0
949 c4 4 5 16711932 0 0 0
950 c5 5 13 16711934 1 1 0
951 # Metadata from INFORMATION_SCHEMA.TABLES
952 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
953 test/t1#p#p3 9 0 2 4 6 6
954 # Metadata from INFORMATION_SCHEMA.COLUMNS
955 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
956 c1 0 6 1027 0 0 0
957 c6 1 13 16711934 1 2 0
958 c2 2 6 1027 0 0 0
959 c3 3 6 1027 0 0 0
960 c4 4 5 16711932 0 0 0
961 c5 5 13 16711934 1 1 0
962 # ------------------------------------------------------------
963 # Scenario 1.1 : Same table with tablespace discarded
964 # ------------------------------------------------------------
965 # EXPORT
966 FLUSH TABLE t1 FOR EXPORT;
967 backup: t1#p#p0
968 backup: t1#p#p1
969 backup: t1#p#p2
970 backup: t1#p#p3
971 UNLOCK TABLES;
972 # IMPORT
973 ALTER TABLE t1 discard tablespace;
974 restore: t1#p#p0 .ibd and .cfg files
975 restore: t1#p#p1 .ibd and .cfg files
976 restore: t1#p#p2 .ibd and .cfg files
977 restore: t1#p#p3 .ibd and .cfg files
978 ALTER TABLE t1 IMPORT TABLESPACE;
979 SELECT * FROM t1 ORDER BY c1;
980 c1 c6 c2 c3 c4 c5
981 1 NULL 2 3 abcdefg c5_def
982 100 NULL 200 300 qwerty c5_def
983 200 NULL 300 400 asdfg c5_def
984 300 NULL 400 500 xxxxxx r4c5
985 400 r5c6 500 600 xxxxxx r5c5
986 # DD Metadata of columns in table
987 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
988 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
989 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
990 c2 3 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID;
991 c3 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
992 c4 5 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
993 c5 6 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
994 DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
995 DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
996 DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
997 # DD Metadata of partitions in table
998 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
999 p0 128 discard=0;
1000 p1 256 discard=0;
1001 p2 384 discard=0;
1002 p3 MAXVALUE discard=0;
1003 # Metadata from INFORMATION_SCHEMA.TABLES
1004 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1005 test/t1#p#p0 9 0 2 4 6 6
1006 # Metadata from INFORMATION_SCHEMA.COLUMNS
1007 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1008 c1 0 6 1027 0 0 0
1009 c6 1 13 16711934 1 2 0
1010 c2 2 6 1027 0 0 0
1011 c3 3 6 1027 0 0 0
1012 c4 4 5 16711932 0 0 0
1013 c5 5 13 16711934 1 1 0
1014 # Metadata from INFORMATION_SCHEMA.TABLES
1015 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1016 test/t1#p#p1 9 0 2 4 6 6
1017 # Metadata from INFORMATION_SCHEMA.COLUMNS
1018 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1019 c1 0 6 1027 0 0 0
1020 c6 1 13 16711934 1 2 0
1021 c2 2 6 1027 0 0 0
1022 c3 3 6 1027 0 0 0
1023 c4 4 5 16711932 0 0 0
1024 c5 5 13 16711934 1 1 0
1025 # Metadata from INFORMATION_SCHEMA.TABLES
1026 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1027 test/t1#p#p2 9 0 2 4 6 6
1028 # Metadata from INFORMATION_SCHEMA.COLUMNS
1029 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1030 c1 0 6 1027 0 0 0
1031 c6 1 13 16711934 1 2 0
1032 c2 2 6 1027 0 0 0
1033 c3 3 6 1027 0 0 0
1034 c4 4 5 16711932 0 0 0
1035 c5 5 13 16711934 1 1 0
1036 # Metadata from INFORMATION_SCHEMA.TABLES
1037 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1038 test/t1#p#p3 9 0 2 4 6 6
1039 # Metadata from INFORMATION_SCHEMA.COLUMNS
1040 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1041 c1 0 6 1027 0 0 0
1042 c6 1 13 16711934 1 2 0
1043 c2 2 6 1027 0 0 0
1044 c3 3 6 1027 0 0 0
1045 c4 4 5 16711932 0 0 0
1046 c5 5 13 16711934 1 1 0
1047 # ------------------------------------------------------------
1048 # Scenario 1.2 : New table created with like
1049 # ------------------------------------------------------------
1050 # EXPORT
1051 FLUSH TABLE t1 FOR EXPORT;
1052 backup: t1#p#p0
1053 backup: t1#p#p1
1054 backup: t1#p#p2
1055 backup: t1#p#p3
1056 UNLOCK TABLES;
1057 CREATE TABLE t2 like t1;
1058 SHOW CREATE TABLE t2;
1059 Table Create Table
1060 t2 CREATE TABLE `t2` (
1061 `c1` int DEFAULT NULL,
1062 `c6` char(20) DEFAULT NULL,
1063 `c2` int DEFAULT NULL,
1064 `c3` int DEFAULT NULL,
1065 `c4` text,
1066 `c5` char(20) DEFAULT 'c5_def'
1067 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1068 /*!50100 PARTITION BY RANGE ((`c1` * 2))
1069 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
1070 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
1071 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
1072 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1073 DROP TABLE t1;
1074 ALTER TABLE t2 RENAME as t1;
1075 # IMPORT
1076 ALTER TABLE t1 discard tablespace;
1077 restore: t1#p#p0 .ibd and .cfg files
1078 restore: t1#p#p1 .ibd and .cfg files
1079 restore: t1#p#p2 .ibd and .cfg files
1080 restore: t1#p#p3 .ibd and .cfg files
1081 ALTER TABLE t1 IMPORT TABLESPACE;
1082 SELECT * FROM t1 ORDER BY c1;
1083 c1 c6 c2 c3 c4 c5
1084 1 NULL 2 3 abcdefg c5_def
1085 100 NULL 200 300 qwerty c5_def
1086 200 NULL 300 400 asdfg c5_def
1087 300 NULL 400 500 xxxxxx r4c5
1088 400 r5c6 500 600 xxxxxx r5c5
1089 # DD Metadata of columns in table
1090 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
1091 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
1092 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
1093 c2 3 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID;
1094 c3 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
1095 c4 5 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
1096 c5 6 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
1097 DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
1098 DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
1099 DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
1100 # DD Metadata of partitions in table
1101 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
1102 p0 128 discard=0;
1103 p1 256 discard=0;
1104 p2 384 discard=0;
1105 p3 MAXVALUE discard=0;
1106 # Metadata from INFORMATION_SCHEMA.TABLES
1107 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1108 test/t1#p#p0 9 0 2 4 6 6
1109 # Metadata from INFORMATION_SCHEMA.COLUMNS
1110 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1111 c1 0 6 1027 0 0 0
1112 c6 1 13 16711934 1 2 0
1113 c2 2 6 1027 0 0 0
1114 c3 3 6 1027 0 0 0
1115 c4 4 5 16711932 0 0 0
1116 c5 5 13 16711934 1 1 0
1117 # Metadata from INFORMATION_SCHEMA.TABLES
1118 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1119 test/t1#p#p1 9 0 2 4 6 6
1120 # Metadata from INFORMATION_SCHEMA.COLUMNS
1121 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1122 c1 0 6 1027 0 0 0
1123 c6 1 13 16711934 1 2 0
1124 c2 2 6 1027 0 0 0
1125 c3 3 6 1027 0 0 0
1126 c4 4 5 16711932 0 0 0
1127 c5 5 13 16711934 1 1 0
1128 # Metadata from INFORMATION_SCHEMA.TABLES
1129 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1130 test/t1#p#p2 9 0 2 4 6 6
1131 # Metadata from INFORMATION_SCHEMA.COLUMNS
1132 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1133 c1 0 6 1027 0 0 0
1134 c6 1 13 16711934 1 2 0
1135 c2 2 6 1027 0 0 0
1136 c3 3 6 1027 0 0 0
1137 c4 4 5 16711932 0 0 0
1138 c5 5 13 16711934 1 1 0
1139 # Metadata from INFORMATION_SCHEMA.TABLES
1140 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1141 test/t1#p#p3 9 0 2 4 6 6
1142 # Metadata from INFORMATION_SCHEMA.COLUMNS
1143 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1144 c1 0 6 1027 0 0 0
1145 c6 1 13 16711934 1 2 0
1146 c2 2 6 1027 0 0 0
1147 c3 3 6 1027 0 0 0
1148 c4 4 5 16711932 0 0 0
1149 c5 5 13 16711934 1 1 0
1150 # ------------------------------------------------------------
1151 # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5]
1152 # ------------------------------------------------------------
1153 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
1154 # DD Metadata of columns in table
1155 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
1156 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
1157 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
1158 c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
1159 c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
1160 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
1161 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
1162 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
1163 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
1164 !hidden!_dropped_v3_p4_c2 9 MYSQL_TYPE_LONG 0 SE physical_pos=4;version_dropped=3;
1165 # DD Metadata of partitions in table
1166 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
1167 p0 128 discard=0;
1168 p1 256 discard=0;
1169 p2 384 discard=0;
1170 p3 MAXVALUE discard=0;
1171 # Metadata from INFORMATION_SCHEMA.TABLES
1172 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1173 test/t1#p#p0 8 0 3 4 5 6
1174 # Metadata from INFORMATION_SCHEMA.COLUMNS
1175 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1176 c1 0 6 1027 0 0 0
1177 c6 1 13 16711934 1 2 0
1178 c3 2 6 1027 0 0 0
1179 c4 3 5 16711932 0 0 0
1180 c5 4 13 16711934 1 1 0
1181 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1182 # Metadata from INFORMATION_SCHEMA.TABLES
1183 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1184 test/t1#p#p1 8 0 3 4 5 6
1185 # Metadata from INFORMATION_SCHEMA.COLUMNS
1186 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1187 c1 0 6 1027 0 0 0
1188 c6 1 13 16711934 1 2 0
1189 c3 2 6 1027 0 0 0
1190 c4 3 5 16711932 0 0 0
1191 c5 4 13 16711934 1 1 0
1192 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1193 # Metadata from INFORMATION_SCHEMA.TABLES
1194 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1195 test/t1#p#p2 8 0 3 4 5 6
1196 # Metadata from INFORMATION_SCHEMA.COLUMNS
1197 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1198 c1 0 6 1027 0 0 0
1199 c6 1 13 16711934 1 2 0
1200 c3 2 6 1027 0 0 0
1201 c4 3 5 16711932 0 0 0
1202 c5 4 13 16711934 1 1 0
1203 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1204 # Metadata from INFORMATION_SCHEMA.TABLES
1205 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1206 test/t1#p#p3 8 0 3 4 5 6
1207 # Metadata from INFORMATION_SCHEMA.COLUMNS
1208 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1209 c1 0 6 1027 0 0 0
1210 c6 1 13 16711934 1 2 0
1211 c3 2 6 1027 0 0 0
1212 c4 3 5 16711932 0 0 0
1213 c5 4 13 16711934 1 1 0
1214 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1215 SELECT * FROM t1 ORDER BY c1;
1216 c1 c6 c3 c4 c5
1217 1 NULL 3 abcdefg c5_def
1218 100 NULL 300 qwerty c5_def
1219 200 NULL 400 asdfg c5_def
1220 300 NULL 500 xxxxxx r4c5
1221 400 r5c6 600 xxxxxx r5c5
1222 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ;
1223 SELECT * FROM t1 ORDER BY c1;
1224 c1 c6 c3 c4 c5
1225 1 NULL 3 abcdefg c5_def
1226 100 NULL 300 qwerty c5_def
1227 200 NULL 400 asdfg c5_def
1228 300 NULL 500 xxxxxx r4c5
1229 400 r5c6 600 xxxxxx r5c5
1230 500 r6c6 700 xxxxxx r6c5
1231 # ------------------------------------------------------------
1232 # Scenario 2.1 : Same table with tablespace discarded
1233 # ------------------------------------------------------------
1234 # EXPORT
1235 FLUSH TABLE t1 FOR EXPORT;
1236 backup: t1#p#p0
1237 backup: t1#p#p1
1238 backup: t1#p#p2
1239 backup: t1#p#p3
1240 UNLOCK TABLES;
1241 # IMPORT
1242 ALTER TABLE t1 discard tablespace;
1243 restore: t1#p#p0 .ibd and .cfg files
1244 restore: t1#p#p1 .ibd and .cfg files
1245 restore: t1#p#p2 .ibd and .cfg files
1246 restore: t1#p#p3 .ibd and .cfg files
1247 ALTER TABLE t1 IMPORT TABLESPACE;
1248 SELECT * FROM t1 ORDER BY c1;
1249 c1 c6 c3 c4 c5
1250 1 NULL 3 abcdefg c5_def
1251 100 NULL 300 qwerty c5_def
1252 200 NULL 400 asdfg c5_def
1253 300 NULL 500 xxxxxx r4c5
1254 400 r5c6 600 xxxxxx r5c5
1255 500 r6c6 700 xxxxxx r6c5
1256 # DD Metadata of columns in table
1257 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
1258 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
1259 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
1260 c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
1261 c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
1262 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
1263 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
1264 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
1265 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
1266 !hidden!_dropped_v3_p4_c2 9 MYSQL_TYPE_LONG 0 SE physical_pos=4;table_id=TABLE_ID;version_dropped=3;
1267 # DD Metadata of partitions in table
1268 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
1269 p0 128 discard=0;
1270 p1 256 discard=0;
1271 p2 384 discard=0;
1272 p3 MAXVALUE discard=0;
1273 # Metadata from INFORMATION_SCHEMA.TABLES
1274 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1275 test/t1#p#p0 8 0 3 4 5 6
1276 # Metadata from INFORMATION_SCHEMA.COLUMNS
1277 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1278 c1 0 6 1027 0 0 0
1279 c6 1 13 16711934 1 2 0
1280 c3 2 6 1027 0 0 0
1281 c4 3 5 16711932 0 0 0
1282 c5 4 13 16711934 1 1 0
1283 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1284 # Metadata from INFORMATION_SCHEMA.TABLES
1285 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1286 test/t1#p#p1 8 0 3 4 5 6
1287 # Metadata from INFORMATION_SCHEMA.COLUMNS
1288 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1289 c1 0 6 1027 0 0 0
1290 c6 1 13 16711934 1 2 0
1291 c3 2 6 1027 0 0 0
1292 c4 3 5 16711932 0 0 0
1293 c5 4 13 16711934 1 1 0
1294 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1295 # Metadata from INFORMATION_SCHEMA.TABLES
1296 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1297 test/t1#p#p2 8 0 3 4 5 6
1298 # Metadata from INFORMATION_SCHEMA.COLUMNS
1299 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1300 c1 0 6 1027 0 0 0
1301 c6 1 13 16711934 1 2 0
1302 c3 2 6 1027 0 0 0
1303 c4 3 5 16711932 0 0 0
1304 c5 4 13 16711934 1 1 0
1305 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1306 # Metadata from INFORMATION_SCHEMA.TABLES
1307 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1308 test/t1#p#p3 8 0 3 4 5 6
1309 # Metadata from INFORMATION_SCHEMA.COLUMNS
1310 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1311 c1 0 6 1027 0 0 0
1312 c6 1 13 16711934 1 2 0
1313 c3 2 6 1027 0 0 0
1314 c4 3 5 16711932 0 0 0
1315 c5 4 13 16711934 1 1 0
1316 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1317 # ------------------------------------------------------------
1318 # Scenario 2.2 : New table created with like
1319 # ------------------------------------------------------------
1320 # EXPORT
1321 FLUSH TABLE t1 FOR EXPORT;
1322 backup: t1#p#p0
1323 backup: t1#p#p1
1324 backup: t1#p#p2
1325 backup: t1#p#p3
1326 UNLOCK TABLES;
1327 CREATE TABLE t2 like t1;
1328 SHOW CREATE TABLE t2;
1329 Table Create Table
1330 t2 CREATE TABLE `t2` (
1331 `c1` int DEFAULT NULL,
1332 `c6` char(20) DEFAULT NULL,
1333 `c3` int DEFAULT NULL,
1334 `c4` text,
1335 `c5` char(20) DEFAULT 'c5_def'
1336 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1337 /*!50100 PARTITION BY RANGE ((`c1` * 2))
1338 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
1339 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
1340 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
1341 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1342 DROP TABLE t1;
1343 ALTER TABLE t2 RENAME as t1;
1344 # IMPORT
1345 ALTER TABLE t1 discard tablespace;
1346 restore: t1#p#p0 .ibd and .cfg files
1347 restore: t1#p#p1 .ibd and .cfg files
1348 restore: t1#p#p2 .ibd and .cfg files
1349 restore: t1#p#p3 .ibd and .cfg files
1350 ALTER TABLE t1 IMPORT TABLESPACE;
1351 SELECT * FROM t1 ORDER BY c1;
1352 c1 c6 c3 c4 c5
1353 1 NULL 3 abcdefg c5_def
1354 100 NULL 300 qwerty c5_def
1355 200 NULL 400 asdfg c5_def
1356 300 NULL 500 xxxxxx r4c5
1357 400 r5c6 600 xxxxxx r5c5
1358 500 r6c6 700 xxxxxx r6c5
1359 # DD Metadata of columns in table
1360 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
1361 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
1362 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
1363 c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
1364 c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
1365 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
1366 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
1367 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
1368 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
1369 !hidden!_dropped_v3_p4_c2 9 MYSQL_TYPE_LONG 0 SE physical_pos=4;table_id=TABLE_ID;version_dropped=3;
1370 # DD Metadata of partitions in table
1371 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
1372 p0 128 discard=0;
1373 p1 256 discard=0;
1374 p2 384 discard=0;
1375 p3 MAXVALUE discard=0;
1376 # Metadata from INFORMATION_SCHEMA.TABLES
1377 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1378 test/t1#p#p0 8 0 3 4 5 6
1379 # Metadata from INFORMATION_SCHEMA.COLUMNS
1380 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1381 c1 0 6 1027 0 0 0
1382 c6 1 13 16711934 1 2 0
1383 c3 2 6 1027 0 0 0
1384 c4 3 5 16711932 0 0 0
1385 c5 4 13 16711934 1 1 0
1386 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1387 # Metadata from INFORMATION_SCHEMA.TABLES
1388 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1389 test/t1#p#p1 8 0 3 4 5 6
1390 # Metadata from INFORMATION_SCHEMA.COLUMNS
1391 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1392 c1 0 6 1027 0 0 0
1393 c6 1 13 16711934 1 2 0
1394 c3 2 6 1027 0 0 0
1395 c4 3 5 16711932 0 0 0
1396 c5 4 13 16711934 1 1 0
1397 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1398 # Metadata from INFORMATION_SCHEMA.TABLES
1399 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1400 test/t1#p#p2 8 0 3 4 5 6
1401 # Metadata from INFORMATION_SCHEMA.COLUMNS
1402 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1403 c1 0 6 1027 0 0 0
1404 c6 1 13 16711934 1 2 0
1405 c3 2 6 1027 0 0 0
1406 c4 3 5 16711932 0 0 0
1407 c5 4 13 16711934 1 1 0
1408 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1409 # Metadata from INFORMATION_SCHEMA.TABLES
1410 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1411 test/t1#p#p3 8 0 3 4 5 6
1412 # Metadata from INFORMATION_SCHEMA.COLUMNS
1413 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1414 c1 0 6 1027 0 0 0
1415 c6 1 13 16711934 1 2 0
1416 c3 2 6 1027 0 0 0
1417 c4 3 5 16711932 0 0 0
1418 c5 4 13 16711934 1 1 0
1419 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
1420 DROP TABLE t1;
1421 # ------------------------------------------------------------
1422 # A Partitioned table having multiple INSTANT ADD/DROP columns
1423 # and REORGNIZED partitions.
1424 # ------------------------------------------------------------
1425 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));
1426 INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111);
1427 INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222);
1428 INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333);
1429 INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444);
1430 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1431 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1432 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1433 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1434 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1435 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1436 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1437 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1438 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1439 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1440 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1441 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1442 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1443 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1444 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1445 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1446 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1447 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1448 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
1449 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
1450 ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50));
1451 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
1452 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
1453 SELECT * FROM t1 ORDER BY id;
1454 id name job dept sal
1455 10 aaa xyz1 1 NULL
1456 20 bbb xyz2 2 NULL
1457 30 ccc xyz3 3 NULL
1458 40 ddd xyz4 4 NULL
1459 # ------------------------------------------------------------
1460 # Scenario 3.1 : Same table with tablespace discarded
1461 # ------------------------------------------------------------
1462 # EXPORT
1463 FLUSH TABLE t1 FOR EXPORT;
1464 backup: t1#p#p0
1465 backup: t1#p#p1
1466 backup: t1#p#p2
1467 backup: t1#p#p3
1468 UNLOCK TABLES;
1469 # IMPORT
1470 ALTER TABLE t1 discard tablespace;
1471 restore: t1#p#p0 .ibd and .cfg files
1472 restore: t1#p#p1 .ibd and .cfg files
1473 restore: t1#p#p2 .ibd and .cfg files
1474 restore: t1#p#p3 .ibd and .cfg files
1475 ALTER TABLE t1 IMPORT TABLESPACE;
1476 SELECT * FROM t1 ORDER BY id;
1477 id name job dept sal
1478 10 aaa xyz1 1 NULL
1479 20 bbb xyz2 2 NULL
1480 30 ccc xyz3 3 NULL
1481 40 ddd xyz4 4 NULL
1482 # DD Metadata of columns in table
1483 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
1484 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
1485 name 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
1486 job 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
1487 dept 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
1488 sal 5 MYSQL_TYPE_LONG 0 Visible default_null=1;physical_pos=17;table_id=TABLE_ID;version_added=21;
1489 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
1490 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
1491 !hidden!_dropped_v2_p6_phoneno 8 MYSQL_TYPE_LONG 0 SE physical_pos=6;table_id=TABLE_ID;version_dropped=2;
1492 !hidden!_dropped_v4_p7_sal 9 MYSQL_TYPE_LONG 0 SE physical_pos=7;table_id=TABLE_ID;version_added=1;version_dropped=4;
1493 !hidden!_dropped_v6_p8_phoneno 10 MYSQL_TYPE_LONG 0 SE physical_pos=8;table_id=TABLE_ID;version_added=3;version_dropped=6;
1494 !hidden!_dropped_v8_p9_sal 11 MYSQL_TYPE_LONG 0 SE physical_pos=9;table_id=TABLE_ID;version_added=5;version_dropped=8;
1495 !hidden!_dropped_v10_p10_phoneno 12 MYSQL_TYPE_LONG 0 SE physical_pos=10;table_id=TABLE_ID;version_added=7;version_dropped=10;
1496 !hidden!_dropped_v12_p11_sal 13 MYSQL_TYPE_LONG 0 SE physical_pos=11;table_id=TABLE_ID;version_added=9;version_dropped=12;
1497 !hidden!_dropped_v14_p12_phoneno 14 MYSQL_TYPE_LONG 0 SE physical_pos=12;table_id=TABLE_ID;version_added=11;version_dropped=14;
1498 !hidden!_dropped_v16_p13_sal 15 MYSQL_TYPE_LONG 0 SE physical_pos=13;table_id=TABLE_ID;version_added=13;version_dropped=16;
1499 !hidden!_dropped_v18_p14_phoneno 16 MYSQL_TYPE_LONG 0 SE physical_pos=14;table_id=TABLE_ID;version_added=15;version_dropped=18;
1500 !hidden!_dropped_v20_p15_sal 17 MYSQL_TYPE_LONG 0 SE physical_pos=15;table_id=TABLE_ID;version_added=17;version_dropped=20;
1501 !hidden!_dropped_v22_p16_phoneno 18 MYSQL_TYPE_LONG 0 SE physical_pos=16;table_id=TABLE_ID;version_added=19;version_dropped=22;
1502 # DD Metadata of partitions in table
1503 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
1504 P0 10 discard=0;
1505 P1 20 discard=0;
1506 P2 30 discard=0;
1507 P3 50 discard=0;
1508 # Metadata from INFORMATION_SCHEMA.TABLES
1509 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1510 test/t1#p#p0 8 0 22 5 5 16
1511 # Metadata from INFORMATION_SCHEMA.COLUMNS
1512 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1513 id 0 6 1283 0 0 0
1514 name 1 12 16711695 0 0 0
1515 job 2 12 16711695 0 0 0
1516 dept 3 6 1027 0 0 0
1517 sal 4 6 1027 1 21 0
1518 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
1519 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
1520 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
1521 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
1522 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
1523 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
1524 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
1525 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
1526 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
1527 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
1528 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
1529 # Metadata from INFORMATION_SCHEMA.TABLES
1530 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1531 test/t1#p#p1 8 0 22 5 5 16
1532 # Metadata from INFORMATION_SCHEMA.COLUMNS
1533 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1534 id 0 6 1283 0 0 0
1535 name 1 12 16711695 0 0 0
1536 job 2 12 16711695 0 0 0
1537 dept 3 6 1027 0 0 0
1538 sal 4 6 1027 1 21 0
1539 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
1540 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
1541 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
1542 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
1543 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
1544 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
1545 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
1546 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
1547 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
1548 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
1549 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
1550 # Metadata from INFORMATION_SCHEMA.TABLES
1551 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1552 test/t1#p#p2 8 0 22 5 5 16
1553 # Metadata from INFORMATION_SCHEMA.COLUMNS
1554 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1555 id 0 6 1283 0 0 0
1556 name 1 12 16711695 0 0 0
1557 job 2 12 16711695 0 0 0
1558 dept 3 6 1027 0 0 0
1559 sal 4 6 1027 1 21 0
1560 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
1561 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
1562 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
1563 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
1564 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
1565 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
1566 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
1567 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
1568 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
1569 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
1570 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
1571 # Metadata from INFORMATION_SCHEMA.TABLES
1572 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1573 test/t1#p#p3 8 0 22 5 5 16
1574 # Metadata from INFORMATION_SCHEMA.COLUMNS
1575 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1576 id 0 6 1283 0 0 0
1577 name 1 12 16711695 0 0 0
1578 job 2 12 16711695 0 0 0
1579 dept 3 6 1027 0 0 0
1580 sal 4 6 1027 1 21 0
1581 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
1582 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
1583 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
1584 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
1585 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
1586 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
1587 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
1588 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
1589 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
1590 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
1591 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
1592 # ------------------------------------------------------------
1593 # Scenario 3.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 `id` int NOT NULL,
1607 `name` varchar(50) DEFAULT NULL,
1608 `job` varchar(50) DEFAULT NULL,
1609 `dept` int DEFAULT NULL,
1610 `sal` int DEFAULT NULL,
1611 PRIMARY KEY (`id`)
1612 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
1613 /*!50100 PARTITION BY RANGE (`id`)
1614 (PARTITION P0 VALUES LESS THAN (10) ENGINE = InnoDB,
1615 PARTITION P1 VALUES LESS THAN (20) ENGINE = InnoDB,
1616 PARTITION P2 VALUES LESS THAN (30) ENGINE = InnoDB,
1617 PARTITION P3 VALUES LESS THAN (50) ENGINE = InnoDB) */
1618 DROP TABLE t1;
1619 ALTER TABLE t2 RENAME as t1;
1620 # IMPORT
1621 ALTER TABLE t1 discard tablespace;
1622 restore: t1#p#p0 .ibd and .cfg files
1623 restore: t1#p#p1 .ibd and .cfg files
1624 restore: t1#p#p2 .ibd and .cfg files
1625 restore: t1#p#p3 .ibd and .cfg files
1626 ALTER TABLE t1 IMPORT TABLESPACE;
1627 SELECT * FROM t1 ORDER BY id;
1628 id name job dept sal
1629 10 aaa xyz1 1 NULL
1630 20 bbb xyz2 2 NULL
1631 30 ccc xyz3 3 NULL
1632 40 ddd xyz4 4 NULL
1633 # DD Metadata of columns in table
1634 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
1635 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
1636 name 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
1637 job 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
1638 dept 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
1639 sal 5 MYSQL_TYPE_LONG 0 Visible default_null=1;physical_pos=17;table_id=TABLE_ID;version_added=21;
1640 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
1641 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
1642 !hidden!_dropped_v2_p6_phoneno 8 MYSQL_TYPE_LONG 0 SE physical_pos=6;table_id=TABLE_ID;version_dropped=2;
1643 !hidden!_dropped_v4_p7_sal 9 MYSQL_TYPE_LONG 0 SE physical_pos=7;table_id=TABLE_ID;version_added=1;version_dropped=4;
1644 !hidden!_dropped_v6_p8_phoneno 10 MYSQL_TYPE_LONG 0 SE physical_pos=8;table_id=TABLE_ID;version_added=3;version_dropped=6;
1645 !hidden!_dropped_v8_p9_sal 11 MYSQL_TYPE_LONG 0 SE physical_pos=9;table_id=TABLE_ID;version_added=5;version_dropped=8;
1646 !hidden!_dropped_v10_p10_phoneno 12 MYSQL_TYPE_LONG 0 SE physical_pos=10;table_id=TABLE_ID;version_added=7;version_dropped=10;
1647 !hidden!_dropped_v12_p11_sal 13 MYSQL_TYPE_LONG 0 SE physical_pos=11;table_id=TABLE_ID;version_added=9;version_dropped=12;
1648 !hidden!_dropped_v14_p12_phoneno 14 MYSQL_TYPE_LONG 0 SE physical_pos=12;table_id=TABLE_ID;version_added=11;version_dropped=14;
1649 !hidden!_dropped_v16_p13_sal 15 MYSQL_TYPE_LONG 0 SE physical_pos=13;table_id=TABLE_ID;version_added=13;version_dropped=16;
1650 !hidden!_dropped_v18_p14_phoneno 16 MYSQL_TYPE_LONG 0 SE physical_pos=14;table_id=TABLE_ID;version_added=15;version_dropped=18;
1651 !hidden!_dropped_v20_p15_sal 17 MYSQL_TYPE_LONG 0 SE physical_pos=15;table_id=TABLE_ID;version_added=17;version_dropped=20;
1652 !hidden!_dropped_v22_p16_phoneno 18 MYSQL_TYPE_LONG 0 SE physical_pos=16;table_id=TABLE_ID;version_added=19;version_dropped=22;
1653 # DD Metadata of partitions in table
1654 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
1655 P0 10 discard=0;
1656 P1 20 discard=0;
1657 P2 30 discard=0;
1658 P3 50 discard=0;
1659 # Metadata from INFORMATION_SCHEMA.TABLES
1660 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1661 test/t1#p#p0 8 0 22 5 5 16
1662 # Metadata from INFORMATION_SCHEMA.COLUMNS
1663 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1664 id 0 6 1283 0 0 0
1665 name 1 12 16711695 0 0 0
1666 job 2 12 16711695 0 0 0
1667 dept 3 6 1027 0 0 0
1668 sal 4 6 1027 1 21 0
1669 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
1670 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
1671 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
1672 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
1673 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
1674 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
1675 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
1676 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
1677 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
1678 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
1679 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
1680 # Metadata from INFORMATION_SCHEMA.TABLES
1681 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1682 test/t1#p#p1 8 0 22 5 5 16
1683 # Metadata from INFORMATION_SCHEMA.COLUMNS
1684 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1685 id 0 6 1283 0 0 0
1686 name 1 12 16711695 0 0 0
1687 job 2 12 16711695 0 0 0
1688 dept 3 6 1027 0 0 0
1689 sal 4 6 1027 1 21 0
1690 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
1691 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
1692 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
1693 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
1694 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
1695 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
1696 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
1697 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
1698 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
1699 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
1700 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
1701 # Metadata from INFORMATION_SCHEMA.TABLES
1702 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1703 test/t1#p#p2 8 0 22 5 5 16
1704 # Metadata from INFORMATION_SCHEMA.COLUMNS
1705 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1706 id 0 6 1283 0 0 0
1707 name 1 12 16711695 0 0 0
1708 job 2 12 16711695 0 0 0
1709 dept 3 6 1027 0 0 0
1710 sal 4 6 1027 1 21 0
1711 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
1712 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
1713 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
1714 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
1715 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
1716 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
1717 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
1718 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
1719 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
1720 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
1721 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
1722 # Metadata from INFORMATION_SCHEMA.TABLES
1723 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1724 test/t1#p#p3 8 0 22 5 5 16
1725 # Metadata from INFORMATION_SCHEMA.COLUMNS
1726 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1727 id 0 6 1283 0 0 0
1728 name 1 12 16711695 0 0 0
1729 job 2 12 16711695 0 0 0
1730 dept 3 6 1027 0 0 0
1731 sal 4 6 1027 1 21 0
1732 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
1733 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
1734 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
1735 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
1736 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
1737 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
1738 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
1739 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
1740 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
1741 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
1742 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
1743 DROP TABLE t1;
1744 ###########
1745 # CLEANUP #
1746 ###########
1747 ############################################
1748 # Test instant ADD/DROP COLUMN for COMPACT format
1749 ############################################
1750 # Create a table with 4 columns. [c1, c2, c3, c4]
1751 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);
1752 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
1753 SELECT * from t1;
1754 c1 c2 c3 c4
1755 1 2 3 abcdefg
1756 100 200 300 qwerty
1757 200 300 400 asdfg
1758 # Add a new column at the end. [c1, c2, c3, c4, +c5]
1759 ALTER TABLE t1 ADD COLUMN c5 CHAR(20) default "c5_def", ALGORITHM=INSTANT;
1760 INSERT INTO t1 VALUES(300, 400, 500, "xxxxxx", "r4c5") ;
1761 SELECT * FROM t1 ORDER BY c1;
1762 c1 c2 c3 c4 c5
1763 1 2 3 abcdefg c5_def
1764 100 200 300 qwerty c5_def
1765 200 300 400 asdfg c5_def
1766 300 400 500 xxxxxx r4c5
1767 # Add a new column in between. [c1, +c6, c2, c3, c4, c5]
1768 ALTER TABLE t1 ADD COLUMN c6 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
1769 INSERT INTO t1 VALUES(400, "r5c6", 500, 600, "xxxxxx", "r5c5") ;
1770 SELECT * FROM t1 ORDER BY c1;
1771 c1 c6 c2 c3 c4 c5
1772 1 NULL 2 3 abcdefg c5_def
1773 100 NULL 200 300 qwerty c5_def
1774 200 NULL 300 400 asdfg c5_def
1775 300 NULL 400 500 xxxxxx r4c5
1776 400 r5c6 500 600 xxxxxx r5c5
1777 # DD Metadata of columns in table
1778 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
1779 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
1780 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
1781 c2 3 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID;
1782 c3 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
1783 c4 5 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
1784 c5 6 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
1785 DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
1786 DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
1787 DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
1788 # DD Metadata of partitions in table
1789 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
1790 p0 128 NULL
1791 p1 256 NULL
1792 p2 384 NULL
1793 p3 MAXVALUE NULL
1794 # Metadata from INFORMATION_SCHEMA.TABLES
1795 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1796 test/t1#p#p0 9 0 2 4 6 6
1797 # Metadata from INFORMATION_SCHEMA.COLUMNS
1798 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1799 c1 0 6 1027 0 0 0
1800 c6 1 13 16711934 1 2 0
1801 c2 2 6 1027 0 0 0
1802 c3 3 6 1027 0 0 0
1803 c4 4 5 16711932 0 0 0
1804 c5 5 13 16711934 1 1 0
1805 # Metadata from INFORMATION_SCHEMA.TABLES
1806 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1807 test/t1#p#p1 9 0 2 4 6 6
1808 # Metadata from INFORMATION_SCHEMA.COLUMNS
1809 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1810 c1 0 6 1027 0 0 0
1811 c6 1 13 16711934 1 2 0
1812 c2 2 6 1027 0 0 0
1813 c3 3 6 1027 0 0 0
1814 c4 4 5 16711932 0 0 0
1815 c5 5 13 16711934 1 1 0
1816 # Metadata from INFORMATION_SCHEMA.TABLES
1817 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1818 test/t1#p#p2 9 0 2 4 6 6
1819 # Metadata from INFORMATION_SCHEMA.COLUMNS
1820 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1821 c1 0 6 1027 0 0 0
1822 c6 1 13 16711934 1 2 0
1823 c2 2 6 1027 0 0 0
1824 c3 3 6 1027 0 0 0
1825 c4 4 5 16711932 0 0 0
1826 c5 5 13 16711934 1 1 0
1827 # Metadata from INFORMATION_SCHEMA.TABLES
1828 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1829 test/t1#p#p3 9 0 2 4 6 6
1830 # Metadata from INFORMATION_SCHEMA.COLUMNS
1831 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1832 c1 0 6 1027 0 0 0
1833 c6 1 13 16711934 1 2 0
1834 c2 2 6 1027 0 0 0
1835 c3 3 6 1027 0 0 0
1836 c4 4 5 16711932 0 0 0
1837 c5 5 13 16711934 1 1 0
1838 # ------------------------------------------------------------
1839 # Scenario 1.1 : Same table with tablespace discarded
1840 # ------------------------------------------------------------
1841 # EXPORT
1842 FLUSH TABLE t1 FOR EXPORT;
1843 backup: t1#p#p0
1844 backup: t1#p#p1
1845 backup: t1#p#p2
1846 backup: t1#p#p3
1847 UNLOCK TABLES;
1848 # IMPORT
1849 ALTER TABLE t1 discard tablespace;
1850 restore: t1#p#p0 .ibd and .cfg files
1851 restore: t1#p#p1 .ibd and .cfg files
1852 restore: t1#p#p2 .ibd and .cfg files
1853 restore: t1#p#p3 .ibd and .cfg files
1854 ALTER TABLE t1 IMPORT TABLESPACE;
1855 SELECT * FROM t1 ORDER BY c1;
1856 c1 c6 c2 c3 c4 c5
1857 1 NULL 2 3 abcdefg c5_def
1858 100 NULL 200 300 qwerty c5_def
1859 200 NULL 300 400 asdfg c5_def
1860 300 NULL 400 500 xxxxxx r4c5
1861 400 r5c6 500 600 xxxxxx r5c5
1862 # DD Metadata of columns in table
1863 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
1864 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
1865 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
1866 c2 3 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID;
1867 c3 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
1868 c4 5 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
1869 c5 6 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
1870 DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
1871 DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
1872 DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
1873 # DD Metadata of partitions in table
1874 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
1875 p0 128 discard=0;
1876 p1 256 discard=0;
1877 p2 384 discard=0;
1878 p3 MAXVALUE discard=0;
1879 # Metadata from INFORMATION_SCHEMA.TABLES
1880 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1881 test/t1#p#p0 9 0 2 4 6 6
1882 # Metadata from INFORMATION_SCHEMA.COLUMNS
1883 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1884 c1 0 6 1027 0 0 0
1885 c6 1 13 16711934 1 2 0
1886 c2 2 6 1027 0 0 0
1887 c3 3 6 1027 0 0 0
1888 c4 4 5 16711932 0 0 0
1889 c5 5 13 16711934 1 1 0
1890 # Metadata from INFORMATION_SCHEMA.TABLES
1891 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1892 test/t1#p#p1 9 0 2 4 6 6
1893 # Metadata from INFORMATION_SCHEMA.COLUMNS
1894 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1895 c1 0 6 1027 0 0 0
1896 c6 1 13 16711934 1 2 0
1897 c2 2 6 1027 0 0 0
1898 c3 3 6 1027 0 0 0
1899 c4 4 5 16711932 0 0 0
1900 c5 5 13 16711934 1 1 0
1901 # Metadata from INFORMATION_SCHEMA.TABLES
1902 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1903 test/t1#p#p2 9 0 2 4 6 6
1904 # Metadata from INFORMATION_SCHEMA.COLUMNS
1905 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1906 c1 0 6 1027 0 0 0
1907 c6 1 13 16711934 1 2 0
1908 c2 2 6 1027 0 0 0
1909 c3 3 6 1027 0 0 0
1910 c4 4 5 16711932 0 0 0
1911 c5 5 13 16711934 1 1 0
1912 # Metadata from INFORMATION_SCHEMA.TABLES
1913 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1914 test/t1#p#p3 9 0 2 4 6 6
1915 # Metadata from INFORMATION_SCHEMA.COLUMNS
1916 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1917 c1 0 6 1027 0 0 0
1918 c6 1 13 16711934 1 2 0
1919 c2 2 6 1027 0 0 0
1920 c3 3 6 1027 0 0 0
1921 c4 4 5 16711932 0 0 0
1922 c5 5 13 16711934 1 1 0
1923 # ------------------------------------------------------------
1924 # Scenario 1.2 : New table created with like
1925 # ------------------------------------------------------------
1926 # EXPORT
1927 FLUSH TABLE t1 FOR EXPORT;
1928 backup: t1#p#p0
1929 backup: t1#p#p1
1930 backup: t1#p#p2
1931 backup: t1#p#p3
1932 UNLOCK TABLES;
1933 CREATE TABLE t2 like t1;
1934 SHOW CREATE TABLE t2;
1935 Table Create Table
1936 t2 CREATE TABLE `t2` (
1937 `c1` int DEFAULT NULL,
1938 `c6` char(20) DEFAULT NULL,
1939 `c2` int DEFAULT NULL,
1940 `c3` int DEFAULT NULL,
1941 `c4` text,
1942 `c5` char(20) DEFAULT 'c5_def'
1943 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
1944 /*!50100 PARTITION BY RANGE ((`c1` * 2))
1945 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
1946 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
1947 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
1948 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
1949 DROP TABLE t1;
1950 ALTER TABLE t2 RENAME as t1;
1951 # IMPORT
1952 ALTER TABLE t1 discard tablespace;
1953 restore: t1#p#p0 .ibd and .cfg files
1954 restore: t1#p#p1 .ibd and .cfg files
1955 restore: t1#p#p2 .ibd and .cfg files
1956 restore: t1#p#p3 .ibd and .cfg files
1957 ALTER TABLE t1 IMPORT TABLESPACE;
1958 SELECT * FROM t1 ORDER BY c1;
1959 c1 c6 c2 c3 c4 c5
1960 1 NULL 2 3 abcdefg c5_def
1961 100 NULL 200 300 qwerty c5_def
1962 200 NULL 300 400 asdfg c5_def
1963 300 NULL 400 500 xxxxxx r4c5
1964 400 r5c6 500 600 xxxxxx r5c5
1965 # DD Metadata of columns in table
1966 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
1967 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
1968 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
1969 c2 3 MYSQL_TYPE_LONG 0 Visible physical_pos=4;table_id=TABLE_ID;
1970 c3 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
1971 c4 5 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
1972 c5 6 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
1973 DB_ROW_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
1974 DB_TRX_ID 8 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
1975 DB_ROLL_PTR 9 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
1976 # DD Metadata of partitions in table
1977 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
1978 p0 128 discard=0;
1979 p1 256 discard=0;
1980 p2 384 discard=0;
1981 p3 MAXVALUE discard=0;
1982 # Metadata from INFORMATION_SCHEMA.TABLES
1983 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1984 test/t1#p#p0 9 0 2 4 6 6
1985 # Metadata from INFORMATION_SCHEMA.COLUMNS
1986 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1987 c1 0 6 1027 0 0 0
1988 c6 1 13 16711934 1 2 0
1989 c2 2 6 1027 0 0 0
1990 c3 3 6 1027 0 0 0
1991 c4 4 5 16711932 0 0 0
1992 c5 5 13 16711934 1 1 0
1993 # Metadata from INFORMATION_SCHEMA.TABLES
1994 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
1995 test/t1#p#p1 9 0 2 4 6 6
1996 # Metadata from INFORMATION_SCHEMA.COLUMNS
1997 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
1998 c1 0 6 1027 0 0 0
1999 c6 1 13 16711934 1 2 0
2000 c2 2 6 1027 0 0 0
2001 c3 3 6 1027 0 0 0
2002 c4 4 5 16711932 0 0 0
2003 c5 5 13 16711934 1 1 0
2004 # Metadata from INFORMATION_SCHEMA.TABLES
2005 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2006 test/t1#p#p2 9 0 2 4 6 6
2007 # Metadata from INFORMATION_SCHEMA.COLUMNS
2008 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2009 c1 0 6 1027 0 0 0
2010 c6 1 13 16711934 1 2 0
2011 c2 2 6 1027 0 0 0
2012 c3 3 6 1027 0 0 0
2013 c4 4 5 16711932 0 0 0
2014 c5 5 13 16711934 1 1 0
2015 # Metadata from INFORMATION_SCHEMA.TABLES
2016 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2017 test/t1#p#p3 9 0 2 4 6 6
2018 # Metadata from INFORMATION_SCHEMA.COLUMNS
2019 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2020 c1 0 6 1027 0 0 0
2021 c6 1 13 16711934 1 2 0
2022 c2 2 6 1027 0 0 0
2023 c3 3 6 1027 0 0 0
2024 c4 4 5 16711932 0 0 0
2025 c5 5 13 16711934 1 1 0
2026 # ------------------------------------------------------------
2027 # Drop a column somewhere in between. [c1, c6, -c2, c3, c4, c5]
2028 # ------------------------------------------------------------
2029 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
2030 # DD Metadata of columns in table
2031 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
2032 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
2033 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
2034 c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
2035 c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
2036 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
2037 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
2038 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
2039 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
2040 !hidden!_dropped_v3_p4_c2 9 MYSQL_TYPE_LONG 0 SE physical_pos=4;version_dropped=3;
2041 # DD Metadata of partitions in table
2042 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
2043 p0 128 discard=0;
2044 p1 256 discard=0;
2045 p2 384 discard=0;
2046 p3 MAXVALUE discard=0;
2047 # Metadata from INFORMATION_SCHEMA.TABLES
2048 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2049 test/t1#p#p0 8 0 3 4 5 6
2050 # Metadata from INFORMATION_SCHEMA.COLUMNS
2051 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2052 c1 0 6 1027 0 0 0
2053 c6 1 13 16711934 1 2 0
2054 c3 2 6 1027 0 0 0
2055 c4 3 5 16711932 0 0 0
2056 c5 4 13 16711934 1 1 0
2057 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2058 # Metadata from INFORMATION_SCHEMA.TABLES
2059 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2060 test/t1#p#p1 8 0 3 4 5 6
2061 # Metadata from INFORMATION_SCHEMA.COLUMNS
2062 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2063 c1 0 6 1027 0 0 0
2064 c6 1 13 16711934 1 2 0
2065 c3 2 6 1027 0 0 0
2066 c4 3 5 16711932 0 0 0
2067 c5 4 13 16711934 1 1 0
2068 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2069 # Metadata from INFORMATION_SCHEMA.TABLES
2070 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2071 test/t1#p#p2 8 0 3 4 5 6
2072 # Metadata from INFORMATION_SCHEMA.COLUMNS
2073 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2074 c1 0 6 1027 0 0 0
2075 c6 1 13 16711934 1 2 0
2076 c3 2 6 1027 0 0 0
2077 c4 3 5 16711932 0 0 0
2078 c5 4 13 16711934 1 1 0
2079 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2080 # Metadata from INFORMATION_SCHEMA.TABLES
2081 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2082 test/t1#p#p3 8 0 3 4 5 6
2083 # Metadata from INFORMATION_SCHEMA.COLUMNS
2084 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2085 c1 0 6 1027 0 0 0
2086 c6 1 13 16711934 1 2 0
2087 c3 2 6 1027 0 0 0
2088 c4 3 5 16711932 0 0 0
2089 c5 4 13 16711934 1 1 0
2090 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2091 SELECT * FROM t1 ORDER BY c1;
2092 c1 c6 c3 c4 c5
2093 1 NULL 3 abcdefg c5_def
2094 100 NULL 300 qwerty c5_def
2095 200 NULL 400 asdfg c5_def
2096 300 NULL 500 xxxxxx r4c5
2097 400 r5c6 600 xxxxxx r5c5
2098 INSERT INTO t1 VALUES(500, "r6c6", 700, "xxxxxx", "r6c5") ;
2099 SELECT * FROM t1 ORDER BY c1;
2100 c1 c6 c3 c4 c5
2101 1 NULL 3 abcdefg c5_def
2102 100 NULL 300 qwerty c5_def
2103 200 NULL 400 asdfg c5_def
2104 300 NULL 500 xxxxxx r4c5
2105 400 r5c6 600 xxxxxx r5c5
2106 500 r6c6 700 xxxxxx r6c5
2107 # ------------------------------------------------------------
2108 # Scenario 2.1 : Same table with tablespace discarded
2109 # ------------------------------------------------------------
2110 # EXPORT
2111 FLUSH TABLE t1 FOR EXPORT;
2112 backup: t1#p#p0
2113 backup: t1#p#p1
2114 backup: t1#p#p2
2115 backup: t1#p#p3
2116 UNLOCK TABLES;
2117 # IMPORT
2118 ALTER TABLE t1 discard tablespace;
2119 restore: t1#p#p0 .ibd and .cfg files
2120 restore: t1#p#p1 .ibd and .cfg files
2121 restore: t1#p#p2 .ibd and .cfg files
2122 restore: t1#p#p3 .ibd and .cfg files
2123 ALTER TABLE t1 IMPORT TABLESPACE;
2124 SELECT * FROM t1 ORDER BY c1;
2125 c1 c6 c3 c4 c5
2126 1 NULL 3 abcdefg c5_def
2127 100 NULL 300 qwerty c5_def
2128 200 NULL 400 asdfg c5_def
2129 300 NULL 500 xxxxxx r4c5
2130 400 r5c6 600 xxxxxx r5c5
2131 500 r6c6 700 xxxxxx r6c5
2132 # DD Metadata of columns in table
2133 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
2134 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
2135 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
2136 c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
2137 c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
2138 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
2139 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
2140 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
2141 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
2142 !hidden!_dropped_v3_p4_c2 9 MYSQL_TYPE_LONG 0 SE physical_pos=4;table_id=TABLE_ID;version_dropped=3;
2143 # DD Metadata of partitions in table
2144 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
2145 p0 128 discard=0;
2146 p1 256 discard=0;
2147 p2 384 discard=0;
2148 p3 MAXVALUE discard=0;
2149 # Metadata from INFORMATION_SCHEMA.TABLES
2150 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2151 test/t1#p#p0 8 0 3 4 5 6
2152 # Metadata from INFORMATION_SCHEMA.COLUMNS
2153 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2154 c1 0 6 1027 0 0 0
2155 c6 1 13 16711934 1 2 0
2156 c3 2 6 1027 0 0 0
2157 c4 3 5 16711932 0 0 0
2158 c5 4 13 16711934 1 1 0
2159 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2160 # Metadata from INFORMATION_SCHEMA.TABLES
2161 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2162 test/t1#p#p1 8 0 3 4 5 6
2163 # Metadata from INFORMATION_SCHEMA.COLUMNS
2164 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2165 c1 0 6 1027 0 0 0
2166 c6 1 13 16711934 1 2 0
2167 c3 2 6 1027 0 0 0
2168 c4 3 5 16711932 0 0 0
2169 c5 4 13 16711934 1 1 0
2170 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2171 # Metadata from INFORMATION_SCHEMA.TABLES
2172 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2173 test/t1#p#p2 8 0 3 4 5 6
2174 # Metadata from INFORMATION_SCHEMA.COLUMNS
2175 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2176 c1 0 6 1027 0 0 0
2177 c6 1 13 16711934 1 2 0
2178 c3 2 6 1027 0 0 0
2179 c4 3 5 16711932 0 0 0
2180 c5 4 13 16711934 1 1 0
2181 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2182 # Metadata from INFORMATION_SCHEMA.TABLES
2183 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2184 test/t1#p#p3 8 0 3 4 5 6
2185 # Metadata from INFORMATION_SCHEMA.COLUMNS
2186 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2187 c1 0 6 1027 0 0 0
2188 c6 1 13 16711934 1 2 0
2189 c3 2 6 1027 0 0 0
2190 c4 3 5 16711932 0 0 0
2191 c5 4 13 16711934 1 1 0
2192 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2193 # ------------------------------------------------------------
2194 # Scenario 2.2 : New table created with like
2195 # ------------------------------------------------------------
2196 # EXPORT
2197 FLUSH TABLE t1 FOR EXPORT;
2198 backup: t1#p#p0
2199 backup: t1#p#p1
2200 backup: t1#p#p2
2201 backup: t1#p#p3
2202 UNLOCK TABLES;
2203 CREATE TABLE t2 like t1;
2204 SHOW CREATE TABLE t2;
2205 Table Create Table
2206 t2 CREATE TABLE `t2` (
2207 `c1` int DEFAULT NULL,
2208 `c6` char(20) DEFAULT NULL,
2209 `c3` int DEFAULT NULL,
2210 `c4` text,
2211 `c5` char(20) DEFAULT 'c5_def'
2212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
2213 /*!50100 PARTITION BY RANGE ((`c1` * 2))
2214 (PARTITION p0 VALUES LESS THAN (128) ENGINE = InnoDB,
2215 PARTITION p1 VALUES LESS THAN (256) ENGINE = InnoDB,
2216 PARTITION p2 VALUES LESS THAN (384) ENGINE = InnoDB,
2217 PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
2218 DROP TABLE t1;
2219 ALTER TABLE t2 RENAME as t1;
2220 # IMPORT
2221 ALTER TABLE t1 discard tablespace;
2222 restore: t1#p#p0 .ibd and .cfg files
2223 restore: t1#p#p1 .ibd and .cfg files
2224 restore: t1#p#p2 .ibd and .cfg files
2225 restore: t1#p#p3 .ibd and .cfg files
2226 ALTER TABLE t1 IMPORT TABLESPACE;
2227 SELECT * FROM t1 ORDER BY c1;
2228 c1 c6 c3 c4 c5
2229 1 NULL 3 abcdefg c5_def
2230 100 NULL 300 qwerty c5_def
2231 200 NULL 400 asdfg c5_def
2232 300 NULL 500 xxxxxx r4c5
2233 400 r5c6 600 xxxxxx r5c5
2234 500 r6c6 700 xxxxxx r6c5
2235 # DD Metadata of columns in table
2236 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
2237 c1 1 MYSQL_TYPE_LONG 0 Visible physical_pos=3;table_id=TABLE_ID;
2238 c6 2 MYSQL_TYPE_STRING 0 Visible default_null=1;physical_pos=8;table_id=TABLE_ID;version_added=2;
2239 c3 3 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
2240 c4 4 MYSQL_TYPE_BLOB 0 Visible physical_pos=6;table_id=TABLE_ID;
2241 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f6465662020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=1;
2242 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
2243 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
2244 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
2245 !hidden!_dropped_v3_p4_c2 9 MYSQL_TYPE_LONG 0 SE physical_pos=4;table_id=TABLE_ID;version_dropped=3;
2246 # DD Metadata of partitions in table
2247 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
2248 p0 128 discard=0;
2249 p1 256 discard=0;
2250 p2 384 discard=0;
2251 p3 MAXVALUE discard=0;
2252 # Metadata from INFORMATION_SCHEMA.TABLES
2253 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2254 test/t1#p#p0 8 0 3 4 5 6
2255 # Metadata from INFORMATION_SCHEMA.COLUMNS
2256 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2257 c1 0 6 1027 0 0 0
2258 c6 1 13 16711934 1 2 0
2259 c3 2 6 1027 0 0 0
2260 c4 3 5 16711932 0 0 0
2261 c5 4 13 16711934 1 1 0
2262 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2263 # Metadata from INFORMATION_SCHEMA.TABLES
2264 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2265 test/t1#p#p1 8 0 3 4 5 6
2266 # Metadata from INFORMATION_SCHEMA.COLUMNS
2267 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2268 c1 0 6 1027 0 0 0
2269 c6 1 13 16711934 1 2 0
2270 c3 2 6 1027 0 0 0
2271 c4 3 5 16711932 0 0 0
2272 c5 4 13 16711934 1 1 0
2273 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2274 # Metadata from INFORMATION_SCHEMA.TABLES
2275 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2276 test/t1#p#p2 8 0 3 4 5 6
2277 # Metadata from INFORMATION_SCHEMA.COLUMNS
2278 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2279 c1 0 6 1027 0 0 0
2280 c6 1 13 16711934 1 2 0
2281 c3 2 6 1027 0 0 0
2282 c4 3 5 16711932 0 0 0
2283 c5 4 13 16711934 1 1 0
2284 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2285 # Metadata from INFORMATION_SCHEMA.TABLES
2286 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2287 test/t1#p#p3 8 0 3 4 5 6
2288 # Metadata from INFORMATION_SCHEMA.COLUMNS
2289 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2290 c1 0 6 1027 0 0 0
2291 c6 1 13 16711934 1 2 0
2292 c3 2 6 1027 0 0 0
2293 c4 3 5 16711932 0 0 0
2294 c5 4 13 16711934 1 1 0
2295 !hidden!_dropped_v3_p4_c2 8 6 1027 0 0 3
2296 DROP TABLE t1;
2297 # ------------------------------------------------------------
2298 # A Partitioned table having multiple INSTANT ADD/DROP columns
2299 # and REORGNIZED partitions.
2300 # ------------------------------------------------------------
2301 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));
2302 INSERT INTO t1 VALUES(10,'aaa','xyz1',1,111);
2303 INSERT INTO t1 VALUES(20,'bbb','xyz2',2,222);
2304 INSERT INTO t1 VALUES(30,'ccc','xyz3',3,333);
2305 INSERT INTO t1 VALUES(40,'ddd','xyz4',4,444);
2306 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
2307 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
2308 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
2309 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
2310 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
2311 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
2312 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
2313 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
2314 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
2315 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
2316 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
2317 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
2318 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
2319 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
2320 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
2321 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
2322 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
2323 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
2324 ALTER TABLE t1 ADD COLUMN phoneno INT, ALGORITHM=INSTANT;
2325 ALTER TABLE t1 DROP COLUMN sal, ALGORITHM=INSTANT;
2326 ALTER TABLE t1 REORGANIZE PARTITION P3, P4 INTO (PARTITION P3 VALUES LESS THAN (50));
2327 ALTER TABLE t1 ADD COLUMN sal INT, ALGORITHM=INSTANT;
2328 ALTER TABLE t1 DROP COLUMN phoneno, ALGORITHM=INSTANT;
2329 SELECT * FROM t1 ORDER BY id;
2330 id name job dept sal
2331 10 aaa xyz1 1 NULL
2332 20 bbb xyz2 2 NULL
2333 30 ccc xyz3 3 NULL
2334 40 ddd xyz4 4 NULL
2335 # ------------------------------------------------------------
2336 # Scenario 3.1 : Same table with tablespace discarded
2337 # ------------------------------------------------------------
2338 # EXPORT
2339 FLUSH TABLE t1 FOR EXPORT;
2340 backup: t1#p#p0
2341 backup: t1#p#p1
2342 backup: t1#p#p2
2343 backup: t1#p#p3
2344 UNLOCK TABLES;
2345 # IMPORT
2346 ALTER TABLE t1 discard tablespace;
2347 restore: t1#p#p0 .ibd and .cfg files
2348 restore: t1#p#p1 .ibd and .cfg files
2349 restore: t1#p#p2 .ibd and .cfg files
2350 restore: t1#p#p3 .ibd and .cfg files
2351 ALTER TABLE t1 IMPORT TABLESPACE;
2352 SELECT * FROM t1 ORDER BY id;
2353 id name job dept sal
2354 10 aaa xyz1 1 NULL
2355 20 bbb xyz2 2 NULL
2356 30 ccc xyz3 3 NULL
2357 40 ddd xyz4 4 NULL
2358 # DD Metadata of columns in table
2359 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
2360 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
2361 name 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
2362 job 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
2363 dept 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
2364 sal 5 MYSQL_TYPE_LONG 0 Visible default_null=1;physical_pos=17;table_id=TABLE_ID;version_added=21;
2365 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
2366 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
2367 !hidden!_dropped_v2_p6_phoneno 8 MYSQL_TYPE_LONG 0 SE physical_pos=6;table_id=TABLE_ID;version_dropped=2;
2368 !hidden!_dropped_v4_p7_sal 9 MYSQL_TYPE_LONG 0 SE physical_pos=7;table_id=TABLE_ID;version_added=1;version_dropped=4;
2369 !hidden!_dropped_v6_p8_phoneno 10 MYSQL_TYPE_LONG 0 SE physical_pos=8;table_id=TABLE_ID;version_added=3;version_dropped=6;
2370 !hidden!_dropped_v8_p9_sal 11 MYSQL_TYPE_LONG 0 SE physical_pos=9;table_id=TABLE_ID;version_added=5;version_dropped=8;
2371 !hidden!_dropped_v10_p10_phoneno 12 MYSQL_TYPE_LONG 0 SE physical_pos=10;table_id=TABLE_ID;version_added=7;version_dropped=10;
2372 !hidden!_dropped_v12_p11_sal 13 MYSQL_TYPE_LONG 0 SE physical_pos=11;table_id=TABLE_ID;version_added=9;version_dropped=12;
2373 !hidden!_dropped_v14_p12_phoneno 14 MYSQL_TYPE_LONG 0 SE physical_pos=12;table_id=TABLE_ID;version_added=11;version_dropped=14;
2374 !hidden!_dropped_v16_p13_sal 15 MYSQL_TYPE_LONG 0 SE physical_pos=13;table_id=TABLE_ID;version_added=13;version_dropped=16;
2375 !hidden!_dropped_v18_p14_phoneno 16 MYSQL_TYPE_LONG 0 SE physical_pos=14;table_id=TABLE_ID;version_added=15;version_dropped=18;
2376 !hidden!_dropped_v20_p15_sal 17 MYSQL_TYPE_LONG 0 SE physical_pos=15;table_id=TABLE_ID;version_added=17;version_dropped=20;
2377 !hidden!_dropped_v22_p16_phoneno 18 MYSQL_TYPE_LONG 0 SE physical_pos=16;table_id=TABLE_ID;version_added=19;version_dropped=22;
2378 # DD Metadata of partitions in table
2379 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
2380 P0 10 discard=0;
2381 P1 20 discard=0;
2382 P2 30 discard=0;
2383 P3 50 discard=0;
2384 # Metadata from INFORMATION_SCHEMA.TABLES
2385 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2386 test/t1#p#p0 8 0 22 5 5 16
2387 # Metadata from INFORMATION_SCHEMA.COLUMNS
2388 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2389 id 0 6 1283 0 0 0
2390 name 1 12 16711695 0 0 0
2391 job 2 12 16711695 0 0 0
2392 dept 3 6 1027 0 0 0
2393 sal 4 6 1027 1 21 0
2394 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
2395 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
2396 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
2397 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
2398 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
2399 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
2400 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
2401 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
2402 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
2403 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
2404 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
2405 # Metadata from INFORMATION_SCHEMA.TABLES
2406 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2407 test/t1#p#p1 8 0 22 5 5 16
2408 # Metadata from INFORMATION_SCHEMA.COLUMNS
2409 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2410 id 0 6 1283 0 0 0
2411 name 1 12 16711695 0 0 0
2412 job 2 12 16711695 0 0 0
2413 dept 3 6 1027 0 0 0
2414 sal 4 6 1027 1 21 0
2415 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
2416 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
2417 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
2418 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
2419 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
2420 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
2421 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
2422 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
2423 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
2424 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
2425 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
2426 # Metadata from INFORMATION_SCHEMA.TABLES
2427 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2428 test/t1#p#p2 8 0 22 5 5 16
2429 # Metadata from INFORMATION_SCHEMA.COLUMNS
2430 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2431 id 0 6 1283 0 0 0
2432 name 1 12 16711695 0 0 0
2433 job 2 12 16711695 0 0 0
2434 dept 3 6 1027 0 0 0
2435 sal 4 6 1027 1 21 0
2436 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
2437 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
2438 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
2439 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
2440 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
2441 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
2442 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
2443 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
2444 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
2445 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
2446 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
2447 # Metadata from INFORMATION_SCHEMA.TABLES
2448 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2449 test/t1#p#p3 8 0 22 5 5 16
2450 # Metadata from INFORMATION_SCHEMA.COLUMNS
2451 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2452 id 0 6 1283 0 0 0
2453 name 1 12 16711695 0 0 0
2454 job 2 12 16711695 0 0 0
2455 dept 3 6 1027 0 0 0
2456 sal 4 6 1027 1 21 0
2457 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
2458 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
2459 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
2460 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
2461 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
2462 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
2463 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
2464 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
2465 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
2466 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
2467 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
2468 # ------------------------------------------------------------
2469 # Scenario 3.2 : New table created with like
2470 # ------------------------------------------------------------
2471 # EXPORT
2472 FLUSH TABLE t1 FOR EXPORT;
2473 backup: t1#p#p0
2474 backup: t1#p#p1
2475 backup: t1#p#p2
2476 backup: t1#p#p3
2477 UNLOCK TABLES;
2478 CREATE TABLE t2 like t1;
2479 SHOW CREATE TABLE t2;
2480 Table Create Table
2481 t2 CREATE TABLE `t2` (
2482 `id` int NOT NULL,
2483 `name` varchar(50) DEFAULT NULL,
2484 `job` varchar(50) DEFAULT NULL,
2485 `dept` int DEFAULT NULL,
2486 `sal` int DEFAULT NULL,
2487 PRIMARY KEY (`id`)
2488 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
2489 /*!50100 PARTITION BY RANGE (`id`)
2490 (PARTITION P0 VALUES LESS THAN (10) ENGINE = InnoDB,
2491 PARTITION P1 VALUES LESS THAN (20) ENGINE = InnoDB,
2492 PARTITION P2 VALUES LESS THAN (30) ENGINE = InnoDB,
2493 PARTITION P3 VALUES LESS THAN (50) ENGINE = InnoDB) */
2494 DROP TABLE t1;
2495 ALTER TABLE t2 RENAME as t1;
2496 # IMPORT
2497 ALTER TABLE t1 discard tablespace;
2498 restore: t1#p#p0 .ibd and .cfg files
2499 restore: t1#p#p1 .ibd and .cfg files
2500 restore: t1#p#p2 .ibd and .cfg files
2501 restore: t1#p#p3 .ibd and .cfg files
2502 ALTER TABLE t1 IMPORT TABLESPACE;
2503 SELECT * FROM t1 ORDER BY id;
2504 id name job dept sal
2505 10 aaa xyz1 1 NULL
2506 20 bbb xyz2 2 NULL
2507 30 ccc xyz3 3 NULL
2508 40 ddd xyz4 4 NULL
2509 # DD Metadata of columns in table
2510 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
2511 id 1 MYSQL_TYPE_LONG 1 Visible physical_pos=0;table_id=TABLE_ID;
2512 name 2 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=3;table_id=TABLE_ID;
2513 job 3 MYSQL_TYPE_VARCHAR 0 Visible physical_pos=4;table_id=TABLE_ID;
2514 dept 4 MYSQL_TYPE_LONG 0 Visible physical_pos=5;table_id=TABLE_ID;
2515 sal 5 MYSQL_TYPE_LONG 0 Visible default_null=1;physical_pos=17;table_id=TABLE_ID;version_added=21;
2516 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
2517 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
2518 !hidden!_dropped_v2_p6_phoneno 8 MYSQL_TYPE_LONG 0 SE physical_pos=6;table_id=TABLE_ID;version_dropped=2;
2519 !hidden!_dropped_v4_p7_sal 9 MYSQL_TYPE_LONG 0 SE physical_pos=7;table_id=TABLE_ID;version_added=1;version_dropped=4;
2520 !hidden!_dropped_v6_p8_phoneno 10 MYSQL_TYPE_LONG 0 SE physical_pos=8;table_id=TABLE_ID;version_added=3;version_dropped=6;
2521 !hidden!_dropped_v8_p9_sal 11 MYSQL_TYPE_LONG 0 SE physical_pos=9;table_id=TABLE_ID;version_added=5;version_dropped=8;
2522 !hidden!_dropped_v10_p10_phoneno 12 MYSQL_TYPE_LONG 0 SE physical_pos=10;table_id=TABLE_ID;version_added=7;version_dropped=10;
2523 !hidden!_dropped_v12_p11_sal 13 MYSQL_TYPE_LONG 0 SE physical_pos=11;table_id=TABLE_ID;version_added=9;version_dropped=12;
2524 !hidden!_dropped_v14_p12_phoneno 14 MYSQL_TYPE_LONG 0 SE physical_pos=12;table_id=TABLE_ID;version_added=11;version_dropped=14;
2525 !hidden!_dropped_v16_p13_sal 15 MYSQL_TYPE_LONG 0 SE physical_pos=13;table_id=TABLE_ID;version_added=13;version_dropped=16;
2526 !hidden!_dropped_v18_p14_phoneno 16 MYSQL_TYPE_LONG 0 SE physical_pos=14;table_id=TABLE_ID;version_added=15;version_dropped=18;
2527 !hidden!_dropped_v20_p15_sal 17 MYSQL_TYPE_LONG 0 SE physical_pos=15;table_id=TABLE_ID;version_added=17;version_dropped=20;
2528 !hidden!_dropped_v22_p16_phoneno 18 MYSQL_TYPE_LONG 0 SE physical_pos=16;table_id=TABLE_ID;version_added=19;version_dropped=22;
2529 # DD Metadata of partitions in table
2530 NAME DESCRIPTION_UTF8 SE_PRIVATE_DATA
2531 P0 10 discard=0;
2532 P1 20 discard=0;
2533 P2 30 discard=0;
2534 P3 50 discard=0;
2535 # Metadata from INFORMATION_SCHEMA.TABLES
2536 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2537 test/t1#p#p0 8 0 22 5 5 16
2538 # Metadata from INFORMATION_SCHEMA.COLUMNS
2539 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2540 id 0 6 1283 0 0 0
2541 name 1 12 16711695 0 0 0
2542 job 2 12 16711695 0 0 0
2543 dept 3 6 1027 0 0 0
2544 sal 4 6 1027 1 21 0
2545 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
2546 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
2547 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
2548 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
2549 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
2550 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
2551 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
2552 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
2553 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
2554 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
2555 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
2556 # Metadata from INFORMATION_SCHEMA.TABLES
2557 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2558 test/t1#p#p1 8 0 22 5 5 16
2559 # Metadata from INFORMATION_SCHEMA.COLUMNS
2560 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2561 id 0 6 1283 0 0 0
2562 name 1 12 16711695 0 0 0
2563 job 2 12 16711695 0 0 0
2564 dept 3 6 1027 0 0 0
2565 sal 4 6 1027 1 21 0
2566 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
2567 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
2568 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
2569 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
2570 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
2571 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
2572 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
2573 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
2574 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
2575 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
2576 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
2577 # Metadata from INFORMATION_SCHEMA.TABLES
2578 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2579 test/t1#p#p2 8 0 22 5 5 16
2580 # Metadata from INFORMATION_SCHEMA.COLUMNS
2581 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2582 id 0 6 1283 0 0 0
2583 name 1 12 16711695 0 0 0
2584 job 2 12 16711695 0 0 0
2585 dept 3 6 1027 0 0 0
2586 sal 4 6 1027 1 21 0
2587 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
2588 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
2589 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
2590 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
2591 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
2592 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
2593 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
2594 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
2595 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
2596 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
2597 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
2598 # Metadata from INFORMATION_SCHEMA.TABLES
2599 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
2600 test/t1#p#p3 8 0 22 5 5 16
2601 # Metadata from INFORMATION_SCHEMA.COLUMNS
2602 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
2603 id 0 6 1283 0 0 0
2604 name 1 12 16711695 0 0 0
2605 job 2 12 16711695 0 0 0
2606 dept 3 6 1027 0 0 0
2607 sal 4 6 1027 1 21 0
2608 !hidden!_dropped_v2_p6_phoneno 8 6 1027 0 0 2
2609 !hidden!_dropped_v4_p7_sal 9 6 1027 0 1 4
2610 !hidden!_dropped_v6_p8_phoneno 10 6 1027 0 3 6
2611 !hidden!_dropped_v8_p9_sal 11 6 1027 0 5 8
2612 !hidden!_dropped_v10_p10_phoneno 12 6 1027 0 7 10
2613 !hidden!_dropped_v12_p11_sal 13 6 1027 0 9 12
2614 !hidden!_dropped_v14_p12_phoneno 14 6 1027 0 11 14
2615 !hidden!_dropped_v16_p13_sal 15 6 1027 0 13 16
2616 !hidden!_dropped_v18_p14_phoneno 16 6 1027 0 15 18
2617 !hidden!_dropped_v20_p15_sal 17 6 1027 0 17 20
2618 !hidden!_dropped_v22_p16_phoneno 18 6 1027 0 19 22
2619 DROP TABLE t1;
2620 ###########
2621 # CLEANUP #
2622 ###########