Debian Codegraph @

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

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