Debian Codegraph @

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

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