Debian Codegraph @

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