Debian Codegraph @

mysql-test/suite/innodb/r/instant_ddl_upgrade.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
13 # table t1_redundant having
14 # c1, c2 normal columns
15 # c3 INSTANT ADD column with default def_c3.
16 # c4 INSTANT ADD column with default def_c4.
17 # +------+------+--------+--------+
18 # | c1 | c2 | c3 | c4 |
19 # +------+------+--------+--------+
20 # | r1c1 | r1c2 | c3_def | c4_def |
21 # | r2c1 | r2c2 | r2c3 | c4_def |
22 # | r3c1 | r3c2 | r3c3 | r3c4 |
23 # +------+------+--------+--------+
24 #
25 # table t2_redundant having
26 # c1, c2 normal columns
27 # c3 INSTANT ADD column with default def_c3.
28 # +------+------+--------+
29 # | c1 | c2 | c3 |
30 # +------+------+--------+
31 # | r1c1 | r1c2 | c3_def |
32 # | r2c1 | r2c2 | r2c3 |
33 # +------+------+--------+
34 # ------------------------------------------------------------
35 SHOW CREATE TABLE t1_redundant;
36 Table Create Table
37 t1_redundant CREATE TABLE `t1_redundant` (
38 `c1` char(10) DEFAULT NULL,
39 `c2` char(10) DEFAULT NULL,
40 `c3` char(10) DEFAULT 'c3_def',
41 `c4` char(10) DEFAULT 'c4_def'
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
43 # ------------------------------------------------------------
44 # Read rows from upgraded table t1_redundant
45 # ------------------------------------------------------------
46 # Metadata from INFORMATION_SCHEMA.TABLES
47 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
48 test/t1_redundant 7 2 0
49 # Metadata from INFORMATION_SCHEMA.COLUMNS
50 NAME POS MTYPE PRTYPE HAS_DEFAULT
51 c1 0 13 16711934 0
52 c2 1 13 16711934 0
53 c3 2 13 16711934 1
54 c4 3 13 16711934 1
55 SELECT * FROM t1_redundant ORDER BY c1;
56 c1 c2 c3 c4
57 r1c1 r1c2 c3_def c4_def
58 r2c1 r2c2 r2c3 c4_def
59 r3c1 r3c2 r3c3 r3c4
60 # ------------------------------------------------------------
61 # Insert a row in upgraded table t1_redundant
62 # ------------------------------------------------------------
63 INSERT INTO t1_redundant values ("r4c1", "r4c2", "r4c3", "r4c4");
64 SELECT * FROM t1_redundant ORDER BY c1;
65 c1 c2 c3 c4
66 r1c1 r1c2 c3_def c4_def
67 r2c1 r2c2 r2c3 c4_def
68 r3c1 r3c2 r3c3 r3c4
69 r4c1 r4c2 r4c3 r4c4
70 # ------------------------------------------------------------
71 # UPDATE : IN PLACE for row with no version
72 # ------------------------------------------------------------
73 UPDATE t1_redundant SET c2="temp" where c1="r1c1";
74 SELECT * FROM t1_redundant ORDER BY c1;
75 c1 c2 c3 c4
76 r1c1 temp c3_def c4_def
77 r2c1 r2c2 r2c3 c4_def
78 r3c1 r3c2 r3c3 r3c4
79 r4c1 r4c2 r4c3 r4c4
80 # ------------------------------------------------------------
81 # UPDATE : IN PLACE for row with V1
82 # ------------------------------------------------------------
83 UPDATE t1_redundant SET c2="temp" where c1="r2c1";
84 SELECT * FROM t1_redundant ORDER BY c1;
85 c1 c2 c3 c4
86 r1c1 temp c3_def c4_def
87 r2c1 temp r2c3 c4_def
88 r3c1 r3c2 r3c3 r3c4
89 r4c1 r4c2 r4c3 r4c4
90 # ------------------------------------------------------------
91 # UPDATE : NOT IN PLACE for row with no version
92 # ------------------------------------------------------------
93 UPDATE t1_redundant SET c3="r1c3" where c1="r1c1";
94 SELECT * FROM t1_redundant ORDER BY c1;
95 c1 c2 c3 c4
96 r1c1 temp r1c3 c4_def
97 r2c1 temp r2c3 c4_def
98 r3c1 r3c2 r3c3 r3c4
99 r4c1 r4c2 r4c3 r4c4
100 # ------------------------------------------------------------
101 # UPDATE : NOT IN PLACE for row with V1
102 # ------------------------------------------------------------
103 UPDATE t1_redundant SET c4="r2c4" where c1="r2c1";
104 SELECT * FROM t1_redundant ORDER BY c1;
105 c1 c2 c3 c4
106 r1c1 temp r1c3 c4_def
107 r2c1 temp r2c3 r2c4
108 r3c1 r3c2 r3c3 r3c4
109 r4c1 r4c2 r4c3 r4c4
110 # ------------------------------------------------------------
111 # INSTANT DROP c2
112 # ------------------------------------------------------------
113 ALTER TABLE t1_redundant DROP COLUMN c2, ALGORITHM=INSTANT;
114 # Metadata from INFORMATION_SCHEMA.TABLES
115 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
116 test/t1_redundant 6 2 1
117 # Metadata from INFORMATION_SCHEMA.COLUMNS
118 NAME POS MTYPE PRTYPE HAS_DEFAULT
119 c1 0 13 16711934 0
120 c3 1 13 16711934 1
121 c4 2 13 16711934 1
122 SELECT * FROM t1_redundant ORDER BY c1;
123 c1 c3 c4
124 r1c1 r1c3 c4_def
125 r2c1 r2c3 r2c4
126 r3c1 r3c3 r3c4
127 r4c1 r4c3 r4c4
128 INSERT INTO t1_redundant values ("r5c1", "r5c3", "r5c4");
129 SELECT * FROM t1_redundant ORDER BY c1;
130 c1 c3 c4
131 r1c1 r1c3 c4_def
132 r2c1 r2c3 r2c4
133 r3c1 r3c3 r3c4
134 r4c1 r4c3 r4c4
135 r5c1 r5c3 r5c4
136 # ------------------------------------------------------------
137 # INSTANT ADD c5 at the end
138 # ------------------------------------------------------------
139 ALTER TABLE t1_redundant ADD COLUMN c5 char(10) default "c5_def", ALGORITHM=INSTANT;
140 # Metadata from INFORMATION_SCHEMA.TABLES
141 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
142 test/t1_redundant 7 2 2
143 # Metadata from INFORMATION_SCHEMA.COLUMNS
144 NAME POS MTYPE PRTYPE HAS_DEFAULT
145 c1 0 13 16711934 0
146 c3 1 13 16711934 1
147 c4 2 13 16711934 1
148 c5 3 13 16711934 1
149 SELECT * FROM t1_redundant ORDER BY c1;
150 c1 c3 c4 c5
151 r1c1 r1c3 c4_def c5_def
152 r2c1 r2c3 r2c4 c5_def
153 r3c1 r3c3 r3c4 c5_def
154 r4c1 r4c3 r4c4 c5_def
155 r5c1 r5c3 r5c4 c5_def
156 INSERT INTO t1_redundant values ("r6c1", "r6c3", "r6c4", "r6c5");
157 SELECT * FROM t1_redundant ORDER BY c1;
158 c1 c3 c4 c5
159 r1c1 r1c3 c4_def c5_def
160 r2c1 r2c3 r2c4 c5_def
161 r3c1 r3c3 r3c4 c5_def
162 r4c1 r4c3 r4c4 c5_def
163 r5c1 r5c3 r5c4 c5_def
164 r6c1 r6c3 r6c4 r6c5
165 # ------------------------------------------------------------
166 # INSTANT ADD c6 somewhere in between
167 # ------------------------------------------------------------
168 ALTER TABLE t1_redundant ADD COLUMN c6 char(10) default "c6_def" after c1, ALGORITHM=INSTANT;
169 # Metadata from INFORMATION_SCHEMA.TABLES
170 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
171 test/t1_redundant 8 2 3
172 # Metadata from INFORMATION_SCHEMA.COLUMNS
173 NAME POS MTYPE PRTYPE HAS_DEFAULT
174 c1 0 13 16711934 0
175 c6 1 13 16711934 1
176 c3 2 13 16711934 1
177 c4 3 13 16711934 1
178 c5 4 13 16711934 1
179 SELECT * FROM t1_redundant ORDER BY c1;
180 c1 c6 c3 c4 c5
181 r1c1 c6_def r1c3 c4_def c5_def
182 r2c1 c6_def r2c3 r2c4 c5_def
183 r3c1 c6_def r3c3 r3c4 c5_def
184 r4c1 c6_def r4c3 r4c4 c5_def
185 r5c1 c6_def r5c3 r5c4 c5_def
186 r6c1 c6_def r6c3 r6c4 r6c5
187 INSERT INTO t1_redundant values ("r7c1", "r7c6", "r7c3", "r7c4", "r7c5");
188 SELECT * FROM t1_redundant ORDER BY c1;
189 c1 c6 c3 c4 c5
190 r1c1 c6_def r1c3 c4_def c5_def
191 r2c1 c6_def r2c3 r2c4 c5_def
192 r3c1 c6_def r3c3 r3c4 c5_def
193 r4c1 c6_def r4c3 r4c4 c5_def
194 r5c1 c6_def r5c3 r5c4 c5_def
195 r6c1 c6_def r6c3 r6c4 r6c5
196 r7c1 r7c6 r7c3 r7c4 r7c5
197 DROP TABLE t1_redundant;
198 ############################################
199 # Test instant ADD/DROP COLUMN for DYNAMIC format
200 ############################################
201 # ------------------------------------------------------------
202 # Start server with old datadir with
203 # table t1_dynamic having
204 # c1, c2 normal columns
205 # c3 INSTANT ADD column with default def_c3.
206 # c4 INSTANT ADD column with default def_c4.
207 # +------+------+--------+--------+
208 # | c1 | c2 | c3 | c4 |
209 # +------+------+--------+--------+
210 # | r1c1 | r1c2 | c3_def | c4_def |
211 # | r2c1 | r2c2 | r2c3 | c4_def |
212 # | r3c1 | r3c2 | r3c3 | r3c4 |
213 # +------+------+--------+--------+
214 #
215 # table t2_dynamic having
216 # c1, c2 normal columns
217 # c3 INSTANT ADD column with default def_c3.
218 # +------+------+--------+
219 # | c1 | c2 | c3 |
220 # +------+------+--------+
221 # | r1c1 | r1c2 | c3_def |
222 # | r2c1 | r2c2 | r2c3 |
223 # +------+------+--------+
224 # ------------------------------------------------------------
225 SHOW CREATE TABLE t1_dynamic;
226 Table Create Table
227 t1_dynamic CREATE TABLE `t1_dynamic` (
228 `c1` char(10) DEFAULT NULL,
229 `c2` char(10) DEFAULT NULL,
230 `c3` char(10) DEFAULT 'c3_def',
231 `c4` char(10) DEFAULT 'c4_def'
232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
233 # ------------------------------------------------------------
234 # Read rows from upgraded table t1_dynamic
235 # ------------------------------------------------------------
236 # Metadata from INFORMATION_SCHEMA.TABLES
237 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
238 test/t1_dynamic 7 2 0
239 # Metadata from INFORMATION_SCHEMA.COLUMNS
240 NAME POS MTYPE PRTYPE HAS_DEFAULT
241 c1 0 13 16711934 0
242 c2 1 13 16711934 0
243 c3 2 13 16711934 1
244 c4 3 13 16711934 1
245 SELECT * FROM t1_dynamic ORDER BY c1;
246 c1 c2 c3 c4
247 r1c1 r1c2 c3_def c4_def
248 r2c1 r2c2 r2c3 c4_def
249 r3c1 r3c2 r3c3 r3c4
250 # ------------------------------------------------------------
251 # Insert a row in upgraded table t1_dynamic
252 # ------------------------------------------------------------
253 INSERT INTO t1_dynamic values ("r4c1", "r4c2", "r4c3", "r4c4");
254 SELECT * FROM t1_dynamic ORDER BY c1;
255 c1 c2 c3 c4
256 r1c1 r1c2 c3_def c4_def
257 r2c1 r2c2 r2c3 c4_def
258 r3c1 r3c2 r3c3 r3c4
259 r4c1 r4c2 r4c3 r4c4
260 # ------------------------------------------------------------
261 # UPDATE : IN PLACE for row with no version
262 # ------------------------------------------------------------
263 UPDATE t1_dynamic SET c2="temp" where c1="r1c1";
264 SELECT * FROM t1_dynamic ORDER BY c1;
265 c1 c2 c3 c4
266 r1c1 temp c3_def c4_def
267 r2c1 r2c2 r2c3 c4_def
268 r3c1 r3c2 r3c3 r3c4
269 r4c1 r4c2 r4c3 r4c4
270 # ------------------------------------------------------------
271 # UPDATE : IN PLACE for row with V1
272 # ------------------------------------------------------------
273 UPDATE t1_dynamic SET c2="temp" where c1="r2c1";
274 SELECT * FROM t1_dynamic ORDER BY c1;
275 c1 c2 c3 c4
276 r1c1 temp c3_def c4_def
277 r2c1 temp r2c3 c4_def
278 r3c1 r3c2 r3c3 r3c4
279 r4c1 r4c2 r4c3 r4c4
280 # ------------------------------------------------------------
281 # UPDATE : NOT IN PLACE for row with no version
282 # ------------------------------------------------------------
283 UPDATE t1_dynamic SET c3="r1c3" where c1="r1c1";
284 SELECT * FROM t1_dynamic ORDER BY c1;
285 c1 c2 c3 c4
286 r1c1 temp r1c3 c4_def
287 r2c1 temp r2c3 c4_def
288 r3c1 r3c2 r3c3 r3c4
289 r4c1 r4c2 r4c3 r4c4
290 # ------------------------------------------------------------
291 # UPDATE : NOT IN PLACE for row with V1
292 # ------------------------------------------------------------
293 UPDATE t1_dynamic SET c4="r2c4" where c1="r2c1";
294 SELECT * FROM t1_dynamic ORDER BY c1;
295 c1 c2 c3 c4
296 r1c1 temp r1c3 c4_def
297 r2c1 temp r2c3 r2c4
298 r3c1 r3c2 r3c3 r3c4
299 r4c1 r4c2 r4c3 r4c4
300 # ------------------------------------------------------------
301 # INSTANT DROP c2
302 # ------------------------------------------------------------
303 ALTER TABLE t1_dynamic DROP COLUMN c2, ALGORITHM=INSTANT;
304 # Metadata from INFORMATION_SCHEMA.TABLES
305 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
306 test/t1_dynamic 6 2 1
307 # Metadata from INFORMATION_SCHEMA.COLUMNS
308 NAME POS MTYPE PRTYPE HAS_DEFAULT
309 c1 0 13 16711934 0
310 c3 1 13 16711934 1
311 c4 2 13 16711934 1
312 SELECT * FROM t1_dynamic ORDER BY c1;
313 c1 c3 c4
314 r1c1 r1c3 c4_def
315 r2c1 r2c3 r2c4
316 r3c1 r3c3 r3c4
317 r4c1 r4c3 r4c4
318 INSERT INTO t1_dynamic values ("r5c1", "r5c3", "r5c4");
319 SELECT * FROM t1_dynamic ORDER BY c1;
320 c1 c3 c4
321 r1c1 r1c3 c4_def
322 r2c1 r2c3 r2c4
323 r3c1 r3c3 r3c4
324 r4c1 r4c3 r4c4
325 r5c1 r5c3 r5c4
326 # ------------------------------------------------------------
327 # INSTANT ADD c5 at the end
328 # ------------------------------------------------------------
329 ALTER TABLE t1_dynamic ADD COLUMN c5 char(10) default "c5_def", ALGORITHM=INSTANT;
330 # Metadata from INFORMATION_SCHEMA.TABLES
331 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
332 test/t1_dynamic 7 2 2
333 # Metadata from INFORMATION_SCHEMA.COLUMNS
334 NAME POS MTYPE PRTYPE HAS_DEFAULT
335 c1 0 13 16711934 0
336 c3 1 13 16711934 1
337 c4 2 13 16711934 1
338 c5 3 13 16711934 1
339 SELECT * FROM t1_dynamic ORDER BY c1;
340 c1 c3 c4 c5
341 r1c1 r1c3 c4_def c5_def
342 r2c1 r2c3 r2c4 c5_def
343 r3c1 r3c3 r3c4 c5_def
344 r4c1 r4c3 r4c4 c5_def
345 r5c1 r5c3 r5c4 c5_def
346 INSERT INTO t1_dynamic values ("r6c1", "r6c3", "r6c4", "r6c5");
347 SELECT * FROM t1_dynamic ORDER BY c1;
348 c1 c3 c4 c5
349 r1c1 r1c3 c4_def c5_def
350 r2c1 r2c3 r2c4 c5_def
351 r3c1 r3c3 r3c4 c5_def
352 r4c1 r4c3 r4c4 c5_def
353 r5c1 r5c3 r5c4 c5_def
354 r6c1 r6c3 r6c4 r6c5
355 # ------------------------------------------------------------
356 # INSTANT ADD c6 somewhere in between
357 # ------------------------------------------------------------
358 ALTER TABLE t1_dynamic ADD COLUMN c6 char(10) default "c6_def" after c1, ALGORITHM=INSTANT;
359 # Metadata from INFORMATION_SCHEMA.TABLES
360 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
361 test/t1_dynamic 8 2 3
362 # Metadata from INFORMATION_SCHEMA.COLUMNS
363 NAME POS MTYPE PRTYPE HAS_DEFAULT
364 c1 0 13 16711934 0
365 c6 1 13 16711934 1
366 c3 2 13 16711934 1
367 c4 3 13 16711934 1
368 c5 4 13 16711934 1
369 SELECT * FROM t1_dynamic ORDER BY c1;
370 c1 c6 c3 c4 c5
371 r1c1 c6_def r1c3 c4_def c5_def
372 r2c1 c6_def r2c3 r2c4 c5_def
373 r3c1 c6_def r3c3 r3c4 c5_def
374 r4c1 c6_def r4c3 r4c4 c5_def
375 r5c1 c6_def r5c3 r5c4 c5_def
376 r6c1 c6_def r6c3 r6c4 r6c5
377 INSERT INTO t1_dynamic values ("r7c1", "r7c6", "r7c3", "r7c4", "r7c5");
378 SELECT * FROM t1_dynamic ORDER BY c1;
379 c1 c6 c3 c4 c5
380 r1c1 c6_def r1c3 c4_def c5_def
381 r2c1 c6_def r2c3 r2c4 c5_def
382 r3c1 c6_def r3c3 r3c4 c5_def
383 r4c1 c6_def r4c3 r4c4 c5_def
384 r5c1 c6_def r5c3 r5c4 c5_def
385 r6c1 c6_def r6c3 r6c4 r6c5
386 r7c1 r7c6 r7c3 r7c4 r7c5
387 DROP TABLE t1_dynamic;
388 ############################################
389 # Test instant ADD/DROP COLUMN for COMPACT format
390 ############################################
391 # ------------------------------------------------------------
392 # Start server with old datadir with
393 # table t1_compact having
394 # c1, c2 normal columns
395 # c3 INSTANT ADD column with default def_c3.
396 # c4 INSTANT ADD column with default def_c4.
397 # +------+------+--------+--------+
398 # | c1 | c2 | c3 | c4 |
399 # +------+------+--------+--------+
400 # | r1c1 | r1c2 | c3_def | c4_def |
401 # | r2c1 | r2c2 | r2c3 | c4_def |
402 # | r3c1 | r3c2 | r3c3 | r3c4 |
403 # +------+------+--------+--------+
404 #
405 # table t2_compact having
406 # c1, c2 normal columns
407 # c3 INSTANT ADD column with default def_c3.
408 # +------+------+--------+
409 # | c1 | c2 | c3 |
410 # +------+------+--------+
411 # | r1c1 | r1c2 | c3_def |
412 # | r2c1 | r2c2 | r2c3 |
413 # +------+------+--------+
414 # ------------------------------------------------------------
415 SHOW CREATE TABLE t1_compact;
416 Table Create Table
417 t1_compact CREATE TABLE `t1_compact` (
418 `c1` char(10) DEFAULT NULL,
419 `c2` char(10) DEFAULT NULL,
420 `c3` char(10) DEFAULT 'c3_def',
421 `c4` char(10) DEFAULT 'c4_def'
422 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
423 # ------------------------------------------------------------
424 # Read rows from upgraded table t1_compact
425 # ------------------------------------------------------------
426 # Metadata from INFORMATION_SCHEMA.TABLES
427 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
428 test/t1_compact 7 2 0
429 # Metadata from INFORMATION_SCHEMA.COLUMNS
430 NAME POS MTYPE PRTYPE HAS_DEFAULT
431 c1 0 13 16711934 0
432 c2 1 13 16711934 0
433 c3 2 13 16711934 1
434 c4 3 13 16711934 1
435 SELECT * FROM t1_compact ORDER BY c1;
436 c1 c2 c3 c4
437 r1c1 r1c2 c3_def c4_def
438 r2c1 r2c2 r2c3 c4_def
439 r3c1 r3c2 r3c3 r3c4
440 # ------------------------------------------------------------
441 # Insert a row in upgraded table t1_compact
442 # ------------------------------------------------------------
443 INSERT INTO t1_compact values ("r4c1", "r4c2", "r4c3", "r4c4");
444 SELECT * FROM t1_compact ORDER BY c1;
445 c1 c2 c3 c4
446 r1c1 r1c2 c3_def c4_def
447 r2c1 r2c2 r2c3 c4_def
448 r3c1 r3c2 r3c3 r3c4
449 r4c1 r4c2 r4c3 r4c4
450 # ------------------------------------------------------------
451 # UPDATE : IN PLACE for row with no version
452 # ------------------------------------------------------------
453 UPDATE t1_compact SET c2="temp" where c1="r1c1";
454 SELECT * FROM t1_compact ORDER BY c1;
455 c1 c2 c3 c4
456 r1c1 temp c3_def c4_def
457 r2c1 r2c2 r2c3 c4_def
458 r3c1 r3c2 r3c3 r3c4
459 r4c1 r4c2 r4c3 r4c4
460 # ------------------------------------------------------------
461 # UPDATE : IN PLACE for row with V1
462 # ------------------------------------------------------------
463 UPDATE t1_compact SET c2="temp" where c1="r2c1";
464 SELECT * FROM t1_compact ORDER BY c1;
465 c1 c2 c3 c4
466 r1c1 temp c3_def c4_def
467 r2c1 temp r2c3 c4_def
468 r3c1 r3c2 r3c3 r3c4
469 r4c1 r4c2 r4c3 r4c4
470 # ------------------------------------------------------------
471 # UPDATE : NOT IN PLACE for row with no version
472 # ------------------------------------------------------------
473 UPDATE t1_compact SET c3="r1c3" where c1="r1c1";
474 SELECT * FROM t1_compact ORDER BY c1;
475 c1 c2 c3 c4
476 r1c1 temp r1c3 c4_def
477 r2c1 temp r2c3 c4_def
478 r3c1 r3c2 r3c3 r3c4
479 r4c1 r4c2 r4c3 r4c4
480 # ------------------------------------------------------------
481 # UPDATE : NOT IN PLACE for row with V1
482 # ------------------------------------------------------------
483 UPDATE t1_compact SET c4="r2c4" where c1="r2c1";
484 SELECT * FROM t1_compact ORDER BY c1;
485 c1 c2 c3 c4
486 r1c1 temp r1c3 c4_def
487 r2c1 temp r2c3 r2c4
488 r3c1 r3c2 r3c3 r3c4
489 r4c1 r4c2 r4c3 r4c4
490 # ------------------------------------------------------------
491 # INSTANT DROP c2
492 # ------------------------------------------------------------
493 ALTER TABLE t1_compact DROP COLUMN c2, ALGORITHM=INSTANT;
494 # Metadata from INFORMATION_SCHEMA.TABLES
495 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
496 test/t1_compact 6 2 1
497 # Metadata from INFORMATION_SCHEMA.COLUMNS
498 NAME POS MTYPE PRTYPE HAS_DEFAULT
499 c1 0 13 16711934 0
500 c3 1 13 16711934 1
501 c4 2 13 16711934 1
502 SELECT * FROM t1_compact ORDER BY c1;
503 c1 c3 c4
504 r1c1 r1c3 c4_def
505 r2c1 r2c3 r2c4
506 r3c1 r3c3 r3c4
507 r4c1 r4c3 r4c4
508 INSERT INTO t1_compact values ("r5c1", "r5c3", "r5c4");
509 SELECT * FROM t1_compact ORDER BY c1;
510 c1 c3 c4
511 r1c1 r1c3 c4_def
512 r2c1 r2c3 r2c4
513 r3c1 r3c3 r3c4
514 r4c1 r4c3 r4c4
515 r5c1 r5c3 r5c4
516 # ------------------------------------------------------------
517 # INSTANT ADD c5 at the end
518 # ------------------------------------------------------------
519 ALTER TABLE t1_compact ADD COLUMN c5 char(10) default "c5_def", ALGORITHM=INSTANT;
520 # Metadata from INFORMATION_SCHEMA.TABLES
521 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
522 test/t1_compact 7 2 2
523 # Metadata from INFORMATION_SCHEMA.COLUMNS
524 NAME POS MTYPE PRTYPE HAS_DEFAULT
525 c1 0 13 16711934 0
526 c3 1 13 16711934 1
527 c4 2 13 16711934 1
528 c5 3 13 16711934 1
529 SELECT * FROM t1_compact ORDER BY c1;
530 c1 c3 c4 c5
531 r1c1 r1c3 c4_def c5_def
532 r2c1 r2c3 r2c4 c5_def
533 r3c1 r3c3 r3c4 c5_def
534 r4c1 r4c3 r4c4 c5_def
535 r5c1 r5c3 r5c4 c5_def
536 INSERT INTO t1_compact values ("r6c1", "r6c3", "r6c4", "r6c5");
537 SELECT * FROM t1_compact ORDER BY c1;
538 c1 c3 c4 c5
539 r1c1 r1c3 c4_def c5_def
540 r2c1 r2c3 r2c4 c5_def
541 r3c1 r3c3 r3c4 c5_def
542 r4c1 r4c3 r4c4 c5_def
543 r5c1 r5c3 r5c4 c5_def
544 r6c1 r6c3 r6c4 r6c5
545 # ------------------------------------------------------------
546 # INSTANT ADD c6 somewhere in between
547 # ------------------------------------------------------------
548 ALTER TABLE t1_compact ADD COLUMN c6 char(10) default "c6_def" after c1, ALGORITHM=INSTANT;
549 # Metadata from INFORMATION_SCHEMA.TABLES
550 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
551 test/t1_compact 8 2 3
552 # Metadata from INFORMATION_SCHEMA.COLUMNS
553 NAME POS MTYPE PRTYPE HAS_DEFAULT
554 c1 0 13 16711934 0
555 c6 1 13 16711934 1
556 c3 2 13 16711934 1
557 c4 3 13 16711934 1
558 c5 4 13 16711934 1
559 SELECT * FROM t1_compact ORDER BY c1;
560 c1 c6 c3 c4 c5
561 r1c1 c6_def r1c3 c4_def c5_def
562 r2c1 c6_def r2c3 r2c4 c5_def
563 r3c1 c6_def r3c3 r3c4 c5_def
564 r4c1 c6_def r4c3 r4c4 c5_def
565 r5c1 c6_def r5c3 r5c4 c5_def
566 r6c1 c6_def r6c3 r6c4 r6c5
567 INSERT INTO t1_compact values ("r7c1", "r7c6", "r7c3", "r7c4", "r7c5");
568 SELECT * FROM t1_compact ORDER BY c1;
569 c1 c6 c3 c4 c5
570 r1c1 c6_def r1c3 c4_def c5_def
571 r2c1 c6_def r2c3 r2c4 c5_def
572 r3c1 c6_def r3c3 r3c4 c5_def
573 r4c1 c6_def r4c3 r4c4 c5_def
574 r5c1 c6_def r5c3 r5c4 c5_def
575 r6c1 c6_def r6c3 r6c4 r6c5
576 r7c1 r7c6 r7c3 r7c4 r7c5
577 DROP TABLE t1_compact;
578 ###########
579 # CLEANUP #
580 ###########
581 # Shutdown server
582 # Remove copied files
583 # Restarting server to restore server state
584 # restart