Debian Codegraph @

mysql-test/suite/innodb/r/instant_ddl_upgrade_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
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 INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
48 test/t1_redundant 7 2 0 4 4 4
49 # Metadata from INFORMATION_SCHEMA.COLUMNS
50 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
51 c1 0 13 16711934 0 0 0
52 c2 1 13 16711934 0 0 0
53 c3 2 13 16711934 1 0 0
54 c4 3 13 16711934 1 0 0
55 # DD Metadata of table
56 NAME SE_PRIVATE_DATA
57 t1_redundant instant_col=2;
58 # DD Metadata of columns in table
59 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
60 c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID;
61 c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID;
62 c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020202020202020202020202020202020202020202020202020202020202020;table_id=TABLE_ID;
63 c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020202020202020202020202020202020202020202020202020202020202020;table_id=TABLE_ID;
64 DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID;
65 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID;
66 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID;
67 SELECT * FROM t1_redundant ORDER BY c1;
68 c1 c2 c3 c4
69 r1c1 r1c2 c3_def c4_def
70 r2c1 r2c2 r2c3 c4_def
71 r3c1 r3c2 r3c3 r3c4
72 # ------------------------------------------------------------
73 # Insert a row in upgraded table t1_redundant
74 # ------------------------------------------------------------
75 INSERT INTO t1_redundant values ("r4c1", "r4c2", "r4c3", "r4c4");
76 SELECT * FROM t1_redundant ORDER BY c1;
77 c1 c2 c3 c4
78 r1c1 r1c2 c3_def c4_def
79 r2c1 r2c2 r2c3 c4_def
80 r3c1 r3c2 r3c3 r3c4
81 r4c1 r4c2 r4c3 r4c4
82 # ------------------------------------------------------------
83 # UPDATE : IN PLACE for row with no version
84 # ------------------------------------------------------------
85 UPDATE t1_redundant SET c2="temp" where c1="r1c1";
86 SELECT * FROM t1_redundant ORDER BY c1;
87 c1 c2 c3 c4
88 r1c1 temp c3_def c4_def
89 r2c1 r2c2 r2c3 c4_def
90 r3c1 r3c2 r3c3 r3c4
91 r4c1 r4c2 r4c3 r4c4
92 # ------------------------------------------------------------
93 # UPDATE : IN PLACE for row with V1
94 # ------------------------------------------------------------
95 UPDATE t1_redundant SET c2="temp" where c1="r2c1";
96 SELECT * FROM t1_redundant ORDER BY c1;
97 c1 c2 c3 c4
98 r1c1 temp c3_def c4_def
99 r2c1 temp r2c3 c4_def
100 r3c1 r3c2 r3c3 r3c4
101 r4c1 r4c2 r4c3 r4c4
102 # ------------------------------------------------------------
103 # UPDATE : NOT IN PLACE for row with no version
104 # ------------------------------------------------------------
105 UPDATE t1_redundant SET c3="r1c3" where c1="r1c1";
106 SELECT * FROM t1_redundant ORDER BY c1;
107 c1 c2 c3 c4
108 r1c1 temp r1c3 c4_def
109 r2c1 temp r2c3 c4_def
110 r3c1 r3c2 r3c3 r3c4
111 r4c1 r4c2 r4c3 r4c4
112 # ------------------------------------------------------------
113 # UPDATE : NOT IN PLACE for row with V1
114 # ------------------------------------------------------------
115 UPDATE t1_redundant SET c4="r2c4" where c1="r2c1";
116 SELECT * FROM t1_redundant ORDER BY c1;
117 c1 c2 c3 c4
118 r1c1 temp r1c3 c4_def
119 r2c1 temp r2c3 r2c4
120 r3c1 r3c2 r3c3 r3c4
121 r4c1 r4c2 r4c3 r4c4
122 # ------------------------------------------------------------
123 # INSTANT DROP c2
124 # ------------------------------------------------------------
125 ALTER TABLE t1_redundant DROP COLUMN c2, ALGORITHM=INSTANT;
126 # Metadata from INFORMATION_SCHEMA.TABLES
127 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
128 test/t1_redundant 6 2 1 4 3 4
129 # Metadata from INFORMATION_SCHEMA.COLUMNS
130 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
131 c1 0 13 16711934 0 0 0
132 c3 1 13 16711934 1 0 0
133 c4 2 13 16711934 1 0 0
134 !hidden!_dropped_v1_p4_c2 6 13 16711934 0 0 1
135 # DD Metadata of table
136 NAME SE_PRIVATE_DATA
137 t1_redundant instant_col=2;
138 # DD Metadata of columns in table
139 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
140 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID;
141 c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;
142 c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;
143 DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
144 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
145 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
146 !hidden!_dropped_v1_p4_c2 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1;
147 SELECT * FROM t1_redundant ORDER BY c1;
148 c1 c3 c4
149 r1c1 r1c3 c4_def
150 r2c1 r2c3 r2c4
151 r3c1 r3c3 r3c4
152 r4c1 r4c3 r4c4
153 INSERT INTO t1_redundant values ("r5c1", "r5c3", "r5c4");
154 SELECT * FROM t1_redundant ORDER BY c1;
155 c1 c3 c4
156 r1c1 r1c3 c4_def
157 r2c1 r2c3 r2c4
158 r3c1 r3c3 r3c4
159 r4c1 r4c3 r4c4
160 r5c1 r5c3 r5c4
161 # ------------------------------------------------------------
162 # INSTANT ADD c5 at the end
163 # ------------------------------------------------------------
164 ALTER TABLE t1_redundant ADD COLUMN c5 char(10) default "c5_def", ALGORITHM=INSTANT;
165 # Metadata from INFORMATION_SCHEMA.TABLES
166 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
167 test/t1_redundant 7 2 2 4 4 5
168 # Metadata from INFORMATION_SCHEMA.COLUMNS
169 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
170 c1 0 13 16711934 0 0 0
171 c3 1 13 16711934 1 0 0
172 c4 2 13 16711934 1 0 0
173 c5 3 13 16711934 1 2 0
174 !hidden!_dropped_v1_p4_c2 7 13 16711934 0 0 1
175 # DD Metadata of table
176 NAME SE_PRIVATE_DATA
177 t1_redundant instant_col=2;
178 # DD Metadata of columns in table
179 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
180 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID;
181 c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;
182 c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;
183 c5 4 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=2;
184 DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
185 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
186 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
187 !hidden!_dropped_v1_p4_c2 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1;
188 SELECT * FROM t1_redundant ORDER BY c1;
189 c1 c3 c4 c5
190 r1c1 r1c3 c4_def c5_def
191 r2c1 r2c3 r2c4 c5_def
192 r3c1 r3c3 r3c4 c5_def
193 r4c1 r4c3 r4c4 c5_def
194 r5c1 r5c3 r5c4 c5_def
195 INSERT INTO t1_redundant values ("r6c1", "r6c3", "r6c4", "r6c5");
196 SELECT * FROM t1_redundant ORDER BY c1;
197 c1 c3 c4 c5
198 r1c1 r1c3 c4_def c5_def
199 r2c1 r2c3 r2c4 c5_def
200 r3c1 r3c3 r3c4 c5_def
201 r4c1 r4c3 r4c4 c5_def
202 r5c1 r5c3 r5c4 c5_def
203 r6c1 r6c3 r6c4 r6c5
204 # ------------------------------------------------------------
205 # INSTANT ADD c6 somewhere in between
206 # ------------------------------------------------------------
207 ALTER TABLE t1_redundant ADD COLUMN c6 char(10) default "c6_def" after c1, ALGORITHM=INSTANT;
208 # Metadata from INFORMATION_SCHEMA.TABLES
209 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
210 test/t1_redundant 8 2 3 4 5 6
211 # Metadata from INFORMATION_SCHEMA.COLUMNS
212 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
213 c1 0 13 16711934 0 0 0
214 c6 1 13 16711934 1 3 0
215 c3 2 13 16711934 1 0 0
216 c4 3 13 16711934 1 0 0
217 c5 4 13 16711934 1 2 0
218 !hidden!_dropped_v1_p4_c2 8 13 16711934 0 0 1
219 # DD Metadata of table
220 NAME SE_PRIVATE_DATA
221 t1_redundant instant_col=2;
222 # DD Metadata of columns in table
223 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
224 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID;
225 c6 2 MYSQL_TYPE_STRING 0 Visible default=63365f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=8;table_id=TABLE_ID;version_added=3;
226 c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=5;table_id=TABLE_ID;
227 c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=6;table_id=TABLE_ID;
228 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020202020202020202020202020202020202020202020202020202020202020;physical_pos=7;table_id=TABLE_ID;version_added=2;
229 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
230 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
231 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
232 !hidden!_dropped_v1_p4_c2 9 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1;
233 SELECT * FROM t1_redundant ORDER BY c1;
234 c1 c6 c3 c4 c5
235 r1c1 c6_def r1c3 c4_def c5_def
236 r2c1 c6_def r2c3 r2c4 c5_def
237 r3c1 c6_def r3c3 r3c4 c5_def
238 r4c1 c6_def r4c3 r4c4 c5_def
239 r5c1 c6_def r5c3 r5c4 c5_def
240 r6c1 c6_def r6c3 r6c4 r6c5
241 INSERT INTO t1_redundant values ("r7c1", "r7c6", "r7c3", "r7c4", "r7c5");
242 SELECT * FROM t1_redundant ORDER BY c1;
243 c1 c6 c3 c4 c5
244 r1c1 c6_def r1c3 c4_def c5_def
245 r2c1 c6_def r2c3 r2c4 c5_def
246 r3c1 c6_def r3c3 r3c4 c5_def
247 r4c1 c6_def r4c3 r4c4 c5_def
248 r5c1 c6_def r5c3 r5c4 c5_def
249 r6c1 c6_def r6c3 r6c4 r6c5
250 r7c1 r7c6 r7c3 r7c4 r7c5
251 DROP TABLE t1_redundant;
252 # ------------------------------------------------------------
253 # TEMP record with different possibilities
254 # ------------------------------------------------------------
255 show create table t2_redundant;
256 Table Create Table
257 t2_redundant CREATE TABLE `t2_redundant` (
258 `c1` char(10) DEFAULT NULL,
259 `c2` char(10) DEFAULT NULL,
260 `c3` char(10) DEFAULT 'c3_def'
261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
262 Select * from t2_redundant;
263 c1 c2 c3
264 r1c1 r1c2 c3_def
265 r2c1 r2c2 r2c3
266 Insert into t2_redundant values ("r3c1", "r3c2", "r3c3");
267 Select * from t2_redundant;
268 c1 c2 c3
269 r1c1 r1c2 c3_def
270 r2c1 r2c2 r2c3
271 r3c1 r3c2 r3c3
272 Alter table t2_redundant add column c4 char(10) default "c4_def" first, algorithm=INSTANT;
273 Insert into t2_redundant values ("r4c4", "r4c1", "r4c2", "r4c3");
274 Select * from t2_redundant;
275 c4 c1 c2 c3
276 c4_def r1c1 r1c2 c3_def
277 c4_def r2c1 r2c2 r2c3
278 c4_def r3c1 r3c2 r3c3
279 r4c4 r4c1 r4c2 r4c3
280 # Make alter table wait
281 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2';
282 # Rebuild the table
283 ALTER TABLE t2_redundant force, algorithm=inplace;;
284 # connection con1
285 SET DEBUG_SYNC = 'now WAIT_FOR s1';
286 # Update query
287 UPDATE t2_redundant SET c1="c1_upd";
288 # Let Alter table continue
289 SET DEBUG_SYNC = 'now SIGNAL s2';
290 # connection default
291 SELECT * FROM t2_redundant;
292 c4 c1 c2 c3
293 c4_def c1_upd r1c2 c3_def
294 c4_def c1_upd r2c2 r2c3
295 c4_def c1_upd r3c2 r3c3
296 r4c4 c1_upd r4c2 r4c3
297 DROP TABLE t2_redundant;
298 ############################################
299 # Test instant ADD/DROP COLUMN for DYNAMIC format
300 ############################################
301 # ------------------------------------------------------------
302 # Start server with old datadir with
303 # table t1_dynamic having
304 # c1, c2 normal columns
305 # c3 INSTANT ADD column with default def_c3.
306 # c4 INSTANT ADD column with default def_c4.
307 # +------+------+--------+--------+
308 # | c1 | c2 | c3 | c4 |
309 # +------+------+--------+--------+
310 # | r1c1 | r1c2 | c3_def | c4_def |
311 # | r2c1 | r2c2 | r2c3 | c4_def |
312 # | r3c1 | r3c2 | r3c3 | r3c4 |
313 # +------+------+--------+--------+
314 #
315 # table t2_dynamic having
316 # c1, c2 normal columns
317 # c3 INSTANT ADD column with default def_c3.
318 # +------+------+--------+
319 # | c1 | c2 | c3 |
320 # +------+------+--------+
321 # | r1c1 | r1c2 | c3_def |
322 # | r2c1 | r2c2 | r2c3 |
323 # +------+------+--------+
324 # ------------------------------------------------------------
325 SHOW CREATE TABLE t1_dynamic;
326 Table Create Table
327 t1_dynamic CREATE TABLE `t1_dynamic` (
328 `c1` char(10) DEFAULT NULL,
329 `c2` char(10) DEFAULT NULL,
330 `c3` char(10) DEFAULT 'c3_def',
331 `c4` char(10) DEFAULT 'c4_def'
332 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
333 # ------------------------------------------------------------
334 # Read rows from upgraded table t1_dynamic
335 # ------------------------------------------------------------
336 # Metadata from INFORMATION_SCHEMA.TABLES
337 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
338 test/t1_dynamic 7 2 0 4 4 4
339 # Metadata from INFORMATION_SCHEMA.COLUMNS
340 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
341 c1 0 13 16711934 0 0 0
342 c2 1 13 16711934 0 0 0
343 c3 2 13 16711934 1 0 0
344 c4 3 13 16711934 1 0 0
345 # DD Metadata of table
346 NAME SE_PRIVATE_DATA
347 t1_dynamic instant_col=2;
348 # DD Metadata of columns in table
349 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
350 c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID;
351 c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID;
352 c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;table_id=TABLE_ID;
353 c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;table_id=TABLE_ID;
354 DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID;
355 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID;
356 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID;
357 SELECT * FROM t1_dynamic ORDER BY c1;
358 c1 c2 c3 c4
359 r1c1 r1c2 c3_def c4_def
360 r2c1 r2c2 r2c3 c4_def
361 r3c1 r3c2 r3c3 r3c4
362 # ------------------------------------------------------------
363 # Insert a row in upgraded table t1_dynamic
364 # ------------------------------------------------------------
365 INSERT INTO t1_dynamic values ("r4c1", "r4c2", "r4c3", "r4c4");
366 SELECT * FROM t1_dynamic ORDER BY c1;
367 c1 c2 c3 c4
368 r1c1 r1c2 c3_def c4_def
369 r2c1 r2c2 r2c3 c4_def
370 r3c1 r3c2 r3c3 r3c4
371 r4c1 r4c2 r4c3 r4c4
372 # ------------------------------------------------------------
373 # UPDATE : IN PLACE for row with no version
374 # ------------------------------------------------------------
375 UPDATE t1_dynamic SET c2="temp" where c1="r1c1";
376 SELECT * FROM t1_dynamic ORDER BY c1;
377 c1 c2 c3 c4
378 r1c1 temp c3_def c4_def
379 r2c1 r2c2 r2c3 c4_def
380 r3c1 r3c2 r3c3 r3c4
381 r4c1 r4c2 r4c3 r4c4
382 # ------------------------------------------------------------
383 # UPDATE : IN PLACE for row with V1
384 # ------------------------------------------------------------
385 UPDATE t1_dynamic SET c2="temp" where c1="r2c1";
386 SELECT * FROM t1_dynamic ORDER BY c1;
387 c1 c2 c3 c4
388 r1c1 temp c3_def c4_def
389 r2c1 temp r2c3 c4_def
390 r3c1 r3c2 r3c3 r3c4
391 r4c1 r4c2 r4c3 r4c4
392 # ------------------------------------------------------------
393 # UPDATE : NOT IN PLACE for row with no version
394 # ------------------------------------------------------------
395 UPDATE t1_dynamic SET c3="r1c3" where c1="r1c1";
396 SELECT * FROM t1_dynamic ORDER BY c1;
397 c1 c2 c3 c4
398 r1c1 temp r1c3 c4_def
399 r2c1 temp r2c3 c4_def
400 r3c1 r3c2 r3c3 r3c4
401 r4c1 r4c2 r4c3 r4c4
402 # ------------------------------------------------------------
403 # UPDATE : NOT IN PLACE for row with V1
404 # ------------------------------------------------------------
405 UPDATE t1_dynamic SET c4="r2c4" where c1="r2c1";
406 SELECT * FROM t1_dynamic ORDER BY c1;
407 c1 c2 c3 c4
408 r1c1 temp r1c3 c4_def
409 r2c1 temp r2c3 r2c4
410 r3c1 r3c2 r3c3 r3c4
411 r4c1 r4c2 r4c3 r4c4
412 # ------------------------------------------------------------
413 # INSTANT DROP c2
414 # ------------------------------------------------------------
415 ALTER TABLE t1_dynamic DROP COLUMN c2, ALGORITHM=INSTANT;
416 # Metadata from INFORMATION_SCHEMA.TABLES
417 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
418 test/t1_dynamic 6 2 1 4 3 4
419 # Metadata from INFORMATION_SCHEMA.COLUMNS
420 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
421 c1 0 13 16711934 0 0 0
422 c3 1 13 16711934 1 0 0
423 c4 2 13 16711934 1 0 0
424 !hidden!_dropped_v1_p4_c2 6 13 16711934 0 0 1
425 # DD Metadata of table
426 NAME SE_PRIVATE_DATA
427 t1_dynamic instant_col=2;
428 # DD Metadata of columns in table
429 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
430 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID;
431 c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID;
432 c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID;
433 DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
434 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
435 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
436 !hidden!_dropped_v1_p4_c2 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1;
437 SELECT * FROM t1_dynamic ORDER BY c1;
438 c1 c3 c4
439 r1c1 r1c3 c4_def
440 r2c1 r2c3 r2c4
441 r3c1 r3c3 r3c4
442 r4c1 r4c3 r4c4
443 INSERT INTO t1_dynamic values ("r5c1", "r5c3", "r5c4");
444 SELECT * FROM t1_dynamic ORDER BY c1;
445 c1 c3 c4
446 r1c1 r1c3 c4_def
447 r2c1 r2c3 r2c4
448 r3c1 r3c3 r3c4
449 r4c1 r4c3 r4c4
450 r5c1 r5c3 r5c4
451 # ------------------------------------------------------------
452 # INSTANT ADD c5 at the end
453 # ------------------------------------------------------------
454 ALTER TABLE t1_dynamic ADD COLUMN c5 char(10) default "c5_def", ALGORITHM=INSTANT;
455 # Metadata from INFORMATION_SCHEMA.TABLES
456 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
457 test/t1_dynamic 7 2 2 4 4 5
458 # Metadata from INFORMATION_SCHEMA.COLUMNS
459 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
460 c1 0 13 16711934 0 0 0
461 c3 1 13 16711934 1 0 0
462 c4 2 13 16711934 1 0 0
463 c5 3 13 16711934 1 2 0
464 !hidden!_dropped_v1_p4_c2 7 13 16711934 0 0 1
465 # DD Metadata of table
466 NAME SE_PRIVATE_DATA
467 t1_dynamic instant_col=2;
468 # DD Metadata of columns in table
469 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
470 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID;
471 c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID;
472 c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID;
473 c5 4 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020;physical_pos=7;table_id=TABLE_ID;version_added=2;
474 DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
475 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
476 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
477 !hidden!_dropped_v1_p4_c2 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1;
478 SELECT * FROM t1_dynamic ORDER BY c1;
479 c1 c3 c4 c5
480 r1c1 r1c3 c4_def c5_def
481 r2c1 r2c3 r2c4 c5_def
482 r3c1 r3c3 r3c4 c5_def
483 r4c1 r4c3 r4c4 c5_def
484 r5c1 r5c3 r5c4 c5_def
485 INSERT INTO t1_dynamic values ("r6c1", "r6c3", "r6c4", "r6c5");
486 SELECT * FROM t1_dynamic ORDER BY c1;
487 c1 c3 c4 c5
488 r1c1 r1c3 c4_def c5_def
489 r2c1 r2c3 r2c4 c5_def
490 r3c1 r3c3 r3c4 c5_def
491 r4c1 r4c3 r4c4 c5_def
492 r5c1 r5c3 r5c4 c5_def
493 r6c1 r6c3 r6c4 r6c5
494 # ------------------------------------------------------------
495 # INSTANT ADD c6 somewhere in between
496 # ------------------------------------------------------------
497 ALTER TABLE t1_dynamic ADD COLUMN c6 char(10) default "c6_def" after c1, ALGORITHM=INSTANT;
498 # Metadata from INFORMATION_SCHEMA.TABLES
499 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
500 test/t1_dynamic 8 2 3 4 5 6
501 # Metadata from INFORMATION_SCHEMA.COLUMNS
502 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
503 c1 0 13 16711934 0 0 0
504 c6 1 13 16711934 1 3 0
505 c3 2 13 16711934 1 0 0
506 c4 3 13 16711934 1 0 0
507 c5 4 13 16711934 1 2 0
508 !hidden!_dropped_v1_p4_c2 8 13 16711934 0 0 1
509 # DD Metadata of table
510 NAME SE_PRIVATE_DATA
511 t1_dynamic instant_col=2;
512 # DD Metadata of columns in table
513 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
514 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID;
515 c6 2 MYSQL_TYPE_STRING 0 Visible default=63365f64656620202020;physical_pos=8;table_id=TABLE_ID;version_added=3;
516 c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID;
517 c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID;
518 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020;physical_pos=7;table_id=TABLE_ID;version_added=2;
519 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
520 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
521 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
522 !hidden!_dropped_v1_p4_c2 9 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1;
523 SELECT * FROM t1_dynamic ORDER BY c1;
524 c1 c6 c3 c4 c5
525 r1c1 c6_def r1c3 c4_def c5_def
526 r2c1 c6_def r2c3 r2c4 c5_def
527 r3c1 c6_def r3c3 r3c4 c5_def
528 r4c1 c6_def r4c3 r4c4 c5_def
529 r5c1 c6_def r5c3 r5c4 c5_def
530 r6c1 c6_def r6c3 r6c4 r6c5
531 INSERT INTO t1_dynamic values ("r7c1", "r7c6", "r7c3", "r7c4", "r7c5");
532 SELECT * FROM t1_dynamic ORDER BY c1;
533 c1 c6 c3 c4 c5
534 r1c1 c6_def r1c3 c4_def c5_def
535 r2c1 c6_def r2c3 r2c4 c5_def
536 r3c1 c6_def r3c3 r3c4 c5_def
537 r4c1 c6_def r4c3 r4c4 c5_def
538 r5c1 c6_def r5c3 r5c4 c5_def
539 r6c1 c6_def r6c3 r6c4 r6c5
540 r7c1 r7c6 r7c3 r7c4 r7c5
541 DROP TABLE t1_dynamic;
542 # ------------------------------------------------------------
543 # TEMP record with different possibilities
544 # ------------------------------------------------------------
545 show create table t2_dynamic;
546 Table Create Table
547 t2_dynamic CREATE TABLE `t2_dynamic` (
548 `c1` char(10) DEFAULT NULL,
549 `c2` char(10) DEFAULT NULL,
550 `c3` char(10) DEFAULT 'c3_def'
551 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
552 Select * from t2_dynamic;
553 c1 c2 c3
554 r1c1 r1c2 c3_def
555 r2c1 r2c2 r2c3
556 Insert into t2_dynamic values ("r3c1", "r3c2", "r3c3");
557 Select * from t2_dynamic;
558 c1 c2 c3
559 r1c1 r1c2 c3_def
560 r2c1 r2c2 r2c3
561 r3c1 r3c2 r3c3
562 Alter table t2_dynamic add column c4 char(10) default "c4_def" first, algorithm=INSTANT;
563 Insert into t2_dynamic values ("r4c4", "r4c1", "r4c2", "r4c3");
564 Select * from t2_dynamic;
565 c4 c1 c2 c3
566 c4_def r1c1 r1c2 c3_def
567 c4_def r2c1 r2c2 r2c3
568 c4_def r3c1 r3c2 r3c3
569 r4c4 r4c1 r4c2 r4c3
570 # Make alter table wait
571 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2';
572 # Rebuild the table
573 ALTER TABLE t2_dynamic force, algorithm=inplace;;
574 # connection con1
575 SET DEBUG_SYNC = 'now WAIT_FOR s1';
576 # Update query
577 UPDATE t2_dynamic SET c1="c1_upd";
578 # Let Alter table continue
579 SET DEBUG_SYNC = 'now SIGNAL s2';
580 # connection default
581 SELECT * FROM t2_dynamic;
582 c4 c1 c2 c3
583 c4_def c1_upd r1c2 c3_def
584 c4_def c1_upd r2c2 r2c3
585 c4_def c1_upd r3c2 r3c3
586 r4c4 c1_upd r4c2 r4c3
587 DROP TABLE t2_dynamic;
588 ############################################
589 # Test instant ADD/DROP COLUMN for COMPACT format
590 ############################################
591 # ------------------------------------------------------------
592 # Start server with old datadir with
593 # table t1_compact having
594 # c1, c2 normal columns
595 # c3 INSTANT ADD column with default def_c3.
596 # c4 INSTANT ADD column with default def_c4.
597 # +------+------+--------+--------+
598 # | c1 | c2 | c3 | c4 |
599 # +------+------+--------+--------+
600 # | r1c1 | r1c2 | c3_def | c4_def |
601 # | r2c1 | r2c2 | r2c3 | c4_def |
602 # | r3c1 | r3c2 | r3c3 | r3c4 |
603 # +------+------+--------+--------+
604 #
605 # table t2_compact having
606 # c1, c2 normal columns
607 # c3 INSTANT ADD column with default def_c3.
608 # +------+------+--------+
609 # | c1 | c2 | c3 |
610 # +------+------+--------+
611 # | r1c1 | r1c2 | c3_def |
612 # | r2c1 | r2c2 | r2c3 |
613 # +------+------+--------+
614 # ------------------------------------------------------------
615 SHOW CREATE TABLE t1_compact;
616 Table Create Table
617 t1_compact CREATE TABLE `t1_compact` (
618 `c1` char(10) DEFAULT NULL,
619 `c2` char(10) DEFAULT NULL,
620 `c3` char(10) DEFAULT 'c3_def',
621 `c4` char(10) DEFAULT 'c4_def'
622 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
623 # ------------------------------------------------------------
624 # Read rows from upgraded table t1_compact
625 # ------------------------------------------------------------
626 # Metadata from INFORMATION_SCHEMA.TABLES
627 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
628 test/t1_compact 7 2 0 4 4 4
629 # Metadata from INFORMATION_SCHEMA.COLUMNS
630 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
631 c1 0 13 16711934 0 0 0
632 c2 1 13 16711934 0 0 0
633 c3 2 13 16711934 1 0 0
634 c4 3 13 16711934 1 0 0
635 # DD Metadata of table
636 NAME SE_PRIVATE_DATA
637 t1_compact instant_col=2;
638 # DD Metadata of columns in table
639 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
640 c1 1 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID;
641 c2 2 MYSQL_TYPE_STRING 0 Visible table_id=TABLE_ID;
642 c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;table_id=TABLE_ID;
643 c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;table_id=TABLE_ID;
644 DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID;
645 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE table_id=TABLE_ID;
646 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE table_id=TABLE_ID;
647 SELECT * FROM t1_compact ORDER BY c1;
648 c1 c2 c3 c4
649 r1c1 r1c2 c3_def c4_def
650 r2c1 r2c2 r2c3 c4_def
651 r3c1 r3c2 r3c3 r3c4
652 # ------------------------------------------------------------
653 # Insert a row in upgraded table t1_compact
654 # ------------------------------------------------------------
655 INSERT INTO t1_compact values ("r4c1", "r4c2", "r4c3", "r4c4");
656 SELECT * FROM t1_compact ORDER BY c1;
657 c1 c2 c3 c4
658 r1c1 r1c2 c3_def c4_def
659 r2c1 r2c2 r2c3 c4_def
660 r3c1 r3c2 r3c3 r3c4
661 r4c1 r4c2 r4c3 r4c4
662 # ------------------------------------------------------------
663 # UPDATE : IN PLACE for row with no version
664 # ------------------------------------------------------------
665 UPDATE t1_compact SET c2="temp" where c1="r1c1";
666 SELECT * FROM t1_compact ORDER BY c1;
667 c1 c2 c3 c4
668 r1c1 temp c3_def c4_def
669 r2c1 r2c2 r2c3 c4_def
670 r3c1 r3c2 r3c3 r3c4
671 r4c1 r4c2 r4c3 r4c4
672 # ------------------------------------------------------------
673 # UPDATE : IN PLACE for row with V1
674 # ------------------------------------------------------------
675 UPDATE t1_compact SET c2="temp" where c1="r2c1";
676 SELECT * FROM t1_compact ORDER BY c1;
677 c1 c2 c3 c4
678 r1c1 temp c3_def c4_def
679 r2c1 temp r2c3 c4_def
680 r3c1 r3c2 r3c3 r3c4
681 r4c1 r4c2 r4c3 r4c4
682 # ------------------------------------------------------------
683 # UPDATE : NOT IN PLACE for row with no version
684 # ------------------------------------------------------------
685 UPDATE t1_compact SET c3="r1c3" where c1="r1c1";
686 SELECT * FROM t1_compact ORDER BY c1;
687 c1 c2 c3 c4
688 r1c1 temp r1c3 c4_def
689 r2c1 temp r2c3 c4_def
690 r3c1 r3c2 r3c3 r3c4
691 r4c1 r4c2 r4c3 r4c4
692 # ------------------------------------------------------------
693 # UPDATE : NOT IN PLACE for row with V1
694 # ------------------------------------------------------------
695 UPDATE t1_compact SET c4="r2c4" where c1="r2c1";
696 SELECT * FROM t1_compact ORDER BY c1;
697 c1 c2 c3 c4
698 r1c1 temp r1c3 c4_def
699 r2c1 temp r2c3 r2c4
700 r3c1 r3c2 r3c3 r3c4
701 r4c1 r4c2 r4c3 r4c4
702 # ------------------------------------------------------------
703 # INSTANT DROP c2
704 # ------------------------------------------------------------
705 ALTER TABLE t1_compact DROP COLUMN c2, ALGORITHM=INSTANT;
706 # Metadata from INFORMATION_SCHEMA.TABLES
707 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
708 test/t1_compact 6 2 1 4 3 4
709 # Metadata from INFORMATION_SCHEMA.COLUMNS
710 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
711 c1 0 13 16711934 0 0 0
712 c3 1 13 16711934 1 0 0
713 c4 2 13 16711934 1 0 0
714 !hidden!_dropped_v1_p4_c2 6 13 16711934 0 0 1
715 # DD Metadata of table
716 NAME SE_PRIVATE_DATA
717 t1_compact instant_col=2;
718 # DD Metadata of columns in table
719 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
720 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID;
721 c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID;
722 c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID;
723 DB_ROW_ID 4 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
724 DB_TRX_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
725 DB_ROLL_PTR 6 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
726 !hidden!_dropped_v1_p4_c2 7 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1;
727 SELECT * FROM t1_compact ORDER BY c1;
728 c1 c3 c4
729 r1c1 r1c3 c4_def
730 r2c1 r2c3 r2c4
731 r3c1 r3c3 r3c4
732 r4c1 r4c3 r4c4
733 INSERT INTO t1_compact values ("r5c1", "r5c3", "r5c4");
734 SELECT * FROM t1_compact ORDER BY c1;
735 c1 c3 c4
736 r1c1 r1c3 c4_def
737 r2c1 r2c3 r2c4
738 r3c1 r3c3 r3c4
739 r4c1 r4c3 r4c4
740 r5c1 r5c3 r5c4
741 # ------------------------------------------------------------
742 # INSTANT ADD c5 at the end
743 # ------------------------------------------------------------
744 ALTER TABLE t1_compact ADD COLUMN c5 char(10) default "c5_def", ALGORITHM=INSTANT;
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_compact 7 2 2 4 4 5
748 # Metadata from INFORMATION_SCHEMA.COLUMNS
749 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
750 c1 0 13 16711934 0 0 0
751 c3 1 13 16711934 1 0 0
752 c4 2 13 16711934 1 0 0
753 c5 3 13 16711934 1 2 0
754 !hidden!_dropped_v1_p4_c2 7 13 16711934 0 0 1
755 # DD Metadata of table
756 NAME SE_PRIVATE_DATA
757 t1_compact instant_col=2;
758 # DD Metadata of columns in table
759 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
760 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID;
761 c3 2 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID;
762 c4 3 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID;
763 c5 4 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020;physical_pos=7;table_id=TABLE_ID;version_added=2;
764 DB_ROW_ID 5 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
765 DB_TRX_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
766 DB_ROLL_PTR 7 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
767 !hidden!_dropped_v1_p4_c2 8 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1;
768 SELECT * FROM t1_compact ORDER BY c1;
769 c1 c3 c4 c5
770 r1c1 r1c3 c4_def c5_def
771 r2c1 r2c3 r2c4 c5_def
772 r3c1 r3c3 r3c4 c5_def
773 r4c1 r4c3 r4c4 c5_def
774 r5c1 r5c3 r5c4 c5_def
775 INSERT INTO t1_compact values ("r6c1", "r6c3", "r6c4", "r6c5");
776 SELECT * FROM t1_compact ORDER BY c1;
777 c1 c3 c4 c5
778 r1c1 r1c3 c4_def c5_def
779 r2c1 r2c3 r2c4 c5_def
780 r3c1 r3c3 r3c4 c5_def
781 r4c1 r4c3 r4c4 c5_def
782 r5c1 r5c3 r5c4 c5_def
783 r6c1 r6c3 r6c4 r6c5
784 # ------------------------------------------------------------
785 # INSTANT ADD c6 somewhere in between
786 # ------------------------------------------------------------
787 ALTER TABLE t1_compact ADD COLUMN c6 char(10) default "c6_def" after c1, ALGORITHM=INSTANT;
788 # Metadata from INFORMATION_SCHEMA.TABLES
789 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS INITIAL_COLUMN_COUNTS CURRENT_COLUMN_COUNTS TOTAL_COLUMN_COUNTS
790 test/t1_compact 8 2 3 4 5 6
791 # Metadata from INFORMATION_SCHEMA.COLUMNS
792 NAME POS MTYPE PRTYPE HAS_DEFAULT VERSION_ADDED VERSION_DROPPED
793 c1 0 13 16711934 0 0 0
794 c6 1 13 16711934 1 3 0
795 c3 2 13 16711934 1 0 0
796 c4 3 13 16711934 1 0 0
797 c5 4 13 16711934 1 2 0
798 !hidden!_dropped_v1_p4_c2 8 13 16711934 0 0 1
799 # DD Metadata of table
800 NAME SE_PRIVATE_DATA
801 t1_compact instant_col=2;
802 # DD Metadata of columns in table
803 NAME ORDINAL_POSITION TYPE HAS_NO_DEFAULT HIDDEN SE_PRIVATE_DATA
804 c1 1 MYSQL_TYPE_STRING 0 Visible physical_pos=3;table_id=TABLE_ID;
805 c6 2 MYSQL_TYPE_STRING 0 Visible default=63365f64656620202020;physical_pos=8;table_id=TABLE_ID;version_added=3;
806 c3 3 MYSQL_TYPE_STRING 0 Visible default=63335f64656620202020;physical_pos=5;table_id=TABLE_ID;
807 c4 4 MYSQL_TYPE_STRING 0 Visible default=63345f64656620202020;physical_pos=6;table_id=TABLE_ID;
808 c5 5 MYSQL_TYPE_STRING 0 Visible default=63355f64656620202020;physical_pos=7;table_id=TABLE_ID;version_added=2;
809 DB_ROW_ID 6 MYSQL_TYPE_INT24 0 SE physical_pos=0;table_id=TABLE_ID;
810 DB_TRX_ID 7 MYSQL_TYPE_INT24 0 SE physical_pos=1;table_id=TABLE_ID;
811 DB_ROLL_PTR 8 MYSQL_TYPE_LONGLONG 0 SE physical_pos=2;table_id=TABLE_ID;
812 !hidden!_dropped_v1_p4_c2 9 MYSQL_TYPE_STRING 0 SE physical_pos=4;version_dropped=1;
813 SELECT * FROM t1_compact ORDER BY c1;
814 c1 c6 c3 c4 c5
815 r1c1 c6_def r1c3 c4_def c5_def
816 r2c1 c6_def r2c3 r2c4 c5_def
817 r3c1 c6_def r3c3 r3c4 c5_def
818 r4c1 c6_def r4c3 r4c4 c5_def
819 r5c1 c6_def r5c3 r5c4 c5_def
820 r6c1 c6_def r6c3 r6c4 r6c5
821 INSERT INTO t1_compact values ("r7c1", "r7c6", "r7c3", "r7c4", "r7c5");
822 SELECT * FROM t1_compact ORDER BY c1;
823 c1 c6 c3 c4 c5
824 r1c1 c6_def r1c3 c4_def c5_def
825 r2c1 c6_def r2c3 r2c4 c5_def
826 r3c1 c6_def r3c3 r3c4 c5_def
827 r4c1 c6_def r4c3 r4c4 c5_def
828 r5c1 c6_def r5c3 r5c4 c5_def
829 r6c1 c6_def r6c3 r6c4 r6c5
830 r7c1 r7c6 r7c3 r7c4 r7c5
831 DROP TABLE t1_compact;
832 # ------------------------------------------------------------
833 # TEMP record with different possibilities
834 # ------------------------------------------------------------
835 show create table t2_compact;
836 Table Create Table
837 t2_compact CREATE TABLE `t2_compact` (
838 `c1` char(10) DEFAULT NULL,
839 `c2` char(10) DEFAULT NULL,
840 `c3` char(10) DEFAULT 'c3_def'
841 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
842 Select * from t2_compact;
843 c1 c2 c3
844 r1c1 r1c2 c3_def
845 r2c1 r2c2 r2c3
846 Insert into t2_compact values ("r3c1", "r3c2", "r3c3");
847 Select * from t2_compact;
848 c1 c2 c3
849 r1c1 r1c2 c3_def
850 r2c1 r2c2 r2c3
851 r3c1 r3c2 r3c3
852 Alter table t2_compact add column c4 char(10) default "c4_def" first, algorithm=INSTANT;
853 Insert into t2_compact values ("r4c4", "r4c1", "r4c2", "r4c3");
854 Select * from t2_compact;
855 c4 c1 c2 c3
856 c4_def r1c1 r1c2 c3_def
857 c4_def r2c1 r2c2 r2c3
858 c4_def r3c1 r3c2 r3c3
859 r4c4 r4c1 r4c2 r4c3
860 # Make alter table wait
861 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2';
862 # Rebuild the table
863 ALTER TABLE t2_compact force, algorithm=inplace;;
864 # connection con1
865 SET DEBUG_SYNC = 'now WAIT_FOR s1';
866 # Update query
867 UPDATE t2_compact SET c1="c1_upd";
868 # Let Alter table continue
869 SET DEBUG_SYNC = 'now SIGNAL s2';
870 # connection default
871 SELECT * FROM t2_compact;
872 c4 c1 c2 c3
873 c4_def c1_upd r1c2 c3_def
874 c4_def c1_upd r2c2 r2c3
875 c4_def c1_upd r3c2 r3c3
876 r4c4 c1_upd r4c2 r4c3
877 DROP TABLE t2_compact;
878 ###########
879 # CLEANUP #
880 ###########
881 # Shutdown server
882 # Remove copied files
883 # Restarting server to restore server state
884 # restart