Debian Codegraph @

mysql-test/suite/innodb/t/bug33788578_online_ddl.test

raw
1 --source include/have_debug.inc
2 --source include/have_debug_sync.inc
3 --source include/have_lowercase0.inc
4 --source include/have_innodb_16k.inc
5
6 --echo # Online DDL test on ALTER .. ADD PRIMRAY KEY with UPDATE SET on all possible row combinations
7 --echo #
8 --echo # What this test is doing:
9 --echo #
10 --echo # Upgrade server to latest version
11 --echo # Keep a zipped copy of upgraded datadir
12 --echo #
13 --echo # For the primary key columns
14 --echo # --let $pk_col = c0 {
15 --echo #
16 --echo # For all row formats
17 --echo # --let $row_format = [ta1b1, ta1b2, ta1b3, ta1b4, ta2b2, ta2b4, ta3b2, ta3b3, ta3b4] {
18 --echo # --source suite/innodb/include/bug33788578_online_ddl.inc
19 --echo #
20 --echo # For all tables
21 --echo # --let $table_name = [ta1b1, ta1b2, ta1b3, ta1b4, ta2b2, ta2b4, ta3b2, ta3b3, ta3b4]_$row_format {
22 --echo # Do the online DDL
23 --echo # --let $upd_col = c2/c6/c10 depending on table
24 --echo # --source suite/innodb/include/bug33788578.inc
25 --echo # }
26 --echo # }
27 --echo # }
28 --echo # Check comments near end of file to see what a1b1, a2b4, etc., means
29
30 --echo # This column will be made as the primary key in online DDL. TODO: extend test to include all possible primary key columns
31 --echo # TODO: extend test to update other columns : maybe instant added ones too;
32 --let $pk_col = c0
33
34 --echo # Upgrade previous tables to current
35 --source include/shutdown_mysqld.inc
36 --copy_file $MYSQLTEST_VARDIR/std_data/instant_ddl/bug33788578_online_ddl.zip $MYSQL_TMP_DIR/bug33788578_online_ddl.zip
37 --file_exists $MYSQL_TMP_DIR/bug33788578_online_ddl.zip
38 --exec unzip -qo $MYSQL_TMP_DIR/bug33788578_online_ddl.zip -d $MYSQL_TMP_DIR
39 let $DATADIR = $MYSQL_TMP_DIR/data;
40
41 --echo # Restart the server
42 --replace_result $DATADIR DATADIR
43 --let $restart_parameters = restart: --datadir=$DATADIR
44 --let $wait_counter=3000
45 --source include/start_mysqld.inc
46
47 SHOW TABLES;
48
49 --echo # For all REDUNDANT tables:
50 --let $row_format = redundant
51 --echo # Prepare the table
52 --source suite/innodb/include/bug33788578_online_ddl_prepare.inc
53 --echo # For each table, do online ddl
54 --echo # Run online ddl for ta1b1
55 --let $table_name = ta1b1_$row_format
56 --let $upd_col = c2
57 --source suite/innodb/include/bug33788578.inc
58
59 --echo # Run online ddl for ta1b2
60 --let $table_name = ta1b2_$row_format
61 --let $upd_col = c2
62 --source suite/innodb/include/bug33788578.inc
63
64 --echo # Run online ddl for ta1b3
65 --let $table_name = ta1b3_$row_format
66 --let $upd_col = c2
67 --source suite/innodb/include/bug33788578.inc
68
69 --echo # Run online ddl for ta1b4
70 --let $table_name = ta1b4_$row_format
71 --let $upd_col = c2
72 --source suite/innodb/include/bug33788578.inc
73
74 --echo # Run online ddl for ta2b2
75 --let $table_name = ta2b2_$row_format
76 --let $upd_col = c2
77 --source suite/innodb/include/bug33788578.inc
78
79 --echo # Run online ddl for ta2b4
80 --let $table_name = ta2b4_$row_format
81 --let $upd_col = c2
82 --source suite/innodb/include/bug33788578.inc
83
84 --echo # Run online ddl for ta3b2
85 --let $table_name = ta3b2_$row_format
86 --let $upd_col = c2
87 --source suite/innodb/include/bug33788578.inc
88
89 --echo # Run online ddl for ta3b3
90 --let $table_name = ta3b3_$row_format
91 --let $upd_col = c2
92 --source suite/innodb/include/bug33788578.inc
93
94 --echo # Run online ddl for ta3b4
95 --let $table_name = ta3b4_$row_format
96 --let $upd_col = c2
97 --source suite/innodb/include/bug33788578.inc
98
99 --echo # For all COMPACT tables:
100 --let $row_format = compact
101 --echo # Prepare the table
102 --source suite/innodb/include/bug33788578_online_ddl_prepare.inc
103 --echo # For each table, do online ddl
104 --echo # Run online ddl for ta1b1
105 --let $table_name = ta1b1_$row_format
106 --let $upd_col = c2
107 --source suite/innodb/include/bug33788578.inc
108
109 --echo # Run online ddl for ta1b2
110 --let $table_name = ta1b2_$row_format
111 --let $upd_col = c2
112 --source suite/innodb/include/bug33788578.inc
113
114 --echo # Run online ddl for ta1b3
115 --let $table_name = ta1b3_$row_format
116 --let $upd_col = c2
117 --source suite/innodb/include/bug33788578.inc
118
119 --echo # Run online ddl for ta1b4
120 --let $table_name = ta1b4_$row_format
121 --let $upd_col = c2
122 --source suite/innodb/include/bug33788578.inc
123
124 --echo # Run online ddl for ta2b2
125 --let $table_name = ta2b2_$row_format
126 --let $upd_col = c2
127 --source suite/innodb/include/bug33788578.inc
128
129 --echo # Run online ddl for ta2b4
130 --let $table_name = ta2b4_$row_format
131 --let $upd_col = c2
132 --source suite/innodb/include/bug33788578.inc
133
134 --echo # Run online ddl for ta3b2
135 --let $table_name = ta3b2_$row_format
136 --let $upd_col = c2
137 --source suite/innodb/include/bug33788578.inc
138
139 --echo # Run online ddl for ta3b3
140 --let $table_name = ta3b3_$row_format
141 --let $upd_col = c2
142 --source suite/innodb/include/bug33788578.inc
143
144 --echo # Run online ddl for ta3b4
145 --let $table_name = ta3b4_$row_format
146 --let $upd_col = c2
147 --source suite/innodb/include/bug33788578.inc
148
149 --echo # For all DYNAMIC tables:
150 --let $row_format = dynamic
151 --echo # Prepare the table
152 --source suite/innodb/include/bug33788578_online_ddl_prepare.inc
153 --echo # For each table, do online ddl
154 --echo # Run online ddl for ta1b1
155 --let $table_name = ta1b1_$row_format
156 --let $upd_col = c2
157 --source suite/innodb/include/bug33788578.inc
158
159 --echo # Run online ddl for ta1b2
160 --let $table_name = ta1b2_$row_format
161 --let $upd_col = c2
162 --source suite/innodb/include/bug33788578.inc
163
164 --echo # Run online ddl for ta1b3
165 --let $table_name = ta1b3_$row_format
166 --let $upd_col = c2
167 --source suite/innodb/include/bug33788578.inc
168
169 --echo # Run online ddl for ta1b4
170 --let $table_name = ta1b4_$row_format
171 --let $upd_col = c2
172 --source suite/innodb/include/bug33788578.inc
173
174 --echo # Run online ddl for ta2b2
175 --let $table_name = ta2b2_$row_format
176 --let $upd_col = c2
177 --source suite/innodb/include/bug33788578.inc
178
179 --echo # Run online ddl for ta2b4
180 --let $table_name = ta2b4_$row_format
181 --let $upd_col = c2
182 --source suite/innodb/include/bug33788578.inc
183
184 --echo # Run online ddl for ta3b2
185 --let $table_name = ta3b2_$row_format
186 --let $upd_col = c2
187 --source suite/innodb/include/bug33788578.inc
188
189 --echo # Run online ddl for ta3b3
190 --let $table_name = ta3b3_$row_format
191 --let $upd_col = c2
192 --source suite/innodb/include/bug33788578.inc
193
194 --echo # Run online ddl for ta3b4
195 --let $table_name = ta3b4_$row_format
196 --let $upd_col = c2
197 --source suite/innodb/include/bug33788578.inc
198
199 --echo # Cleanup
200 --echo # Shutdown server
201 --source include/shutdown_mysqld.inc
202
203 --echo # Remove temp files used
204 --file_exists $MYSQL_TMP_DIR/bug33788578_online_ddl.zip
205 --force-rmdir $MYSQL_TMP_DIR/data
206 --remove_file $MYSQL_TMP_DIR/bug33788578_online_ddl.zip
207
208 --echo # Restart server to restore server state
209 let $restart_parameters =;
210 --source include/start_mysqld.inc
211
212 # Table create in 8.0.28:
213 #
214 # ======
215 # 8.0.28
216 # ======
217 #
218 # DROP DATABASE IF EXISTS test;
219 # CREATE DATABASE IF NOT EXISTS test;
220 # USE test;
221 # -- --
222 # -- REDUNDANT --
223 # -- --
224 #
225 # -- 1. ta1b1_redundant: --
226 # -- [8.0.28] --
227 # CREATE TABLE ta1b1_redundant (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
228 #
229 # -- [8.0.28] --
230 # INSERT INTO ta1b1_redundant VALUES (1, "row1", 2, 3, 4, 5);
231 # INSERT INTO ta1b1_redundant (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
232 #
233 # -- UPGRADE --
234 #
235 # -- 2. ta1b2_redundant: --
236 # -- [8.0.28] --
237 # CREATE TABLE ta1b2_redundant (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
238 #
239 # -- [8.0.28] --
240 # INSERT INTO ta1b2_redundant VALUES (1, "row1", 2, 3, 4, 5);
241 # INSERT INTO ta1b2_redundant (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
242 #
243 # -- [8.0.28] --
244 # ALTER TABLE ta1b2_redundant ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
245 # ALTER TABLE ta1b2_redundant ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
246 #
247 # -- UPGRADE --
248 #
249 # -- 3. ta1b3_redundant: --
250 # -- [8.0.28] --
251 # CREATE TABLE ta1b3_redundant (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
252 #
253 # -- [8.0.28] --
254 # INSERT INTO ta1b3_redundant VALUES (1, "row1", 2, 3, 4, 5);
255 # INSERT INTO ta1b3_redundant (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
256 #
257 # -- UPGRADE --
258 #
259 # -- 4. ta1b4_redundant: --
260 # -- [8.0.28] --
261 # CREATE TABLE ta1b4_redundant (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
262 #
263 # -- [8.0.28] --
264 # INSERT INTO ta1b4_redundant VALUES (1, "row1", 2, 3, 4, 5);
265 # INSERT INTO ta1b4_redundant (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
266 #
267 # -- [8.0.28] --
268 # ALTER TABLE ta1b4_redundant ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
269 # ALTER TABLE ta1b4_redundant ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
270 #
271 # -- UPGRADE --
272 #
273 # -- 5. ta2b2_redundant: --
274 # -- [8.0.28] --
275 # CREATE TABLE ta2b2_redundant (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
276 #
277 # -- [8.0.28] --
278 # ALTER TABLE ta2b2_redundant ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
279 # ALTER TABLE ta2b2_redundant ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
280 #
281 # -- [8.0.28] --
282 # INSERT INTO ta2b2_redundant VALUES (1, "row1", 2, 3, 4, 5, 6, 7, 8, 9);
283 # INSERT INTO ta2b2_redundant (c0, c1, c3, c5, c7, c9) VALUES (2, "row2", 33, 55, 77, 99);
284 #
285 # -- UPGRADE --
286 #
287 # -- 6. ta2b4_redundant: --
288 # -- [8.0.28] --
289 # CREATE TABLE ta2b4_redundant (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
290 #
291 # -- [8.0.28] --
292 # ALTER TABLE ta2b4_redundant ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
293 # ALTER TABLE ta2b4_redundant ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
294 #
295 # -- [8.0.28] --
296 # INSERT INTO ta2b4_redundant VALUES (1, "row1", 2, 3, 4, 5, 6, 7, 8, 9);
297 # INSERT INTO ta2b4_redundant (c0, c1, c3, c5, c7, c9) VALUES (2, "row2", 33, 55, 77, 99);
298 #
299 # -- UPGRADE --
300 #
301 # -- 7. ta3b2_redundant: --
302 # -- [8.0.28] --
303 # CREATE TABLE ta3b2_redundant (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
304 #
305 # -- [8.0.28] --
306 # ALTER TABLE ta3b2_redundant ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
307 # ALTER TABLE ta3b2_redundant ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
308 #
309 # -- UPGRADE --
310 #
311 # -- 8. ta3b3_redundant: --
312 # -- [8.0.28] --
313 # CREATE TABLE ta3b3_redundant (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
314 #
315 # -- UPGRADE --
316 #
317 # -- 9. ta3b4_redundant: --
318 # -- [8.0.28] --
319 # CREATE TABLE ta3b4_redundant (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
320 #
321 # -- [8.0.28] --
322 # ALTER TABLE ta3b4_redundant ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
323 # ALTER TABLE ta3b4_redundant ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
324 #
325 # -- UPGRADE --
326 #
327 # -- --
328 # -- COMPACT --
329 # -- --
330 #
331 # -- 1. ta1b1_compact: --
332 # -- [8.0.28] --
333 # CREATE TABLE ta1b1_compact (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=COMPACT, ENGINE=INNODB;
334 #
335 # -- [8.0.28] --
336 # INSERT INTO ta1b1_compact VALUES (1, "row1", 2, 3, 4, 5);
337 # INSERT INTO ta1b1_compact (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
338 #
339 # -- UPGRADE --
340 #
341 # -- 2. ta1b2_compact: --
342 # -- [8.0.28] --
343 # CREATE TABLE ta1b2_compact (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=COMPACT, ENGINE=INNODB;
344 #
345 # -- [8.0.28] --
346 # INSERT INTO ta1b2_compact VALUES (1, "row1", 2, 3, 4, 5);
347 # INSERT INTO ta1b2_compact (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
348 #
349 # -- [8.0.28] --
350 # ALTER TABLE ta1b2_compact ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
351 # ALTER TABLE ta1b2_compact ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
352 #
353 # -- UPGRADE --
354 #
355 # -- 3. ta1b3_compact: --
356 # -- [8.0.28] --
357 # CREATE TABLE ta1b3_compact (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=COMPACT, ENGINE=INNODB;
358 #
359 # -- [8.0.28] --
360 # INSERT INTO ta1b3_compact VALUES (1, "row1", 2, 3, 4, 5);
361 # INSERT INTO ta1b3_compact (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
362 #
363 # -- UPGRADE --
364 #
365 # -- 4. ta1b4_compact: --
366 # -- [8.0.28] --
367 # CREATE TABLE ta1b4_compact (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=COMPACT, ENGINE=INNODB;
368 #
369 # -- [8.0.28] --
370 # INSERT INTO ta1b4_compact VALUES (1, "row1", 2, 3, 4, 5);
371 # INSERT INTO ta1b4_compact (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
372 #
373 # -- [8.0.28] --
374 # ALTER TABLE ta1b4_compact ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
375 # ALTER TABLE ta1b4_compact ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
376 #
377 # -- UPGRADE --
378 #
379 # -- 5. ta2b2_compact: --
380 # -- [8.0.28] --
381 # CREATE TABLE ta2b2_compact (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=COMPACT, ENGINE=INNODB;
382 #
383 # -- [8.0.28] --
384 # ALTER TABLE ta2b2_compact ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
385 # ALTER TABLE ta2b2_compact ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
386 #
387 # -- [8.0.28] --
388 # INSERT INTO ta2b2_compact VALUES (1, "row1", 2, 3, 4, 5, 6, 7, 8, 9);
389 # INSERT INTO ta2b2_compact (c0, c1, c3, c5, c7, c9) VALUES (2, "row2", 33, 55, 77, 99);
390 #
391 # -- UPGRADE --
392 #
393 # -- 6. ta2b4_compact: --
394 # -- [8.0.28] --
395 # CREATE TABLE ta2b4_compact (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=COMPACT, ENGINE=INNODB;
396 #
397 # -- [8.0.28] --
398 # ALTER TABLE ta2b4_compact ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
399 # ALTER TABLE ta2b4_compact ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
400 #
401 # -- [8.0.28] --
402 # INSERT INTO ta2b4_compact VALUES (1, "row1", 2, 3, 4, 5, 6, 7, 8, 9);
403 # INSERT INTO ta2b4_compact (c0, c1, c3, c5, c7, c9) VALUES (2, "row2", 33, 55, 77, 99);
404 #
405 # -- UPGRADE --
406 #
407 # -- 7. ta3b2_compact: --
408 # -- [8.0.28] --
409 # CREATE TABLE ta3b2_compact (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=COMPACT, ENGINE=INNODB;
410 #
411 # -- [8.0.28] --
412 # ALTER TABLE ta3b2_compact ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
413 # ALTER TABLE ta3b2_compact ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
414 #
415 # -- UPGRADE --
416 #
417 # -- 8. ta3b3_compact: --
418 # -- [8.0.28] --
419 # CREATE TABLE ta3b3_compact (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=COMPACT, ENGINE=INNODB;
420 #
421 # -- UPGRADE --
422 #
423 # -- 9. ta3b4_compact: --
424 # -- [8.0.28] --
425 # CREATE TABLE ta3b4_compact (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=COMPACT, ENGINE=INNODB;
426 #
427 # -- [8.0.28] --
428 # ALTER TABLE ta3b4_compact ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
429 # ALTER TABLE ta3b4_compact ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
430 #
431 # -- UPGRADE --
432 #
433 # -- --
434 # -- DYNAMIC --
435 # -- --
436 #
437 # -- 1. ta1b1_dynamic: --
438 # -- [8.0.28] --
439 # CREATE TABLE ta1b1_dynamic (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=DYNAMIC, ENGINE=INNODB;
440 #
441 # -- [8.0.28] --
442 # INSERT INTO ta1b1_dynamic VALUES (1, "row1", 2, 3, 4, 5);
443 # INSERT INTO ta1b1_dynamic (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
444 #
445 # -- UPGRADE --
446 #
447 # -- 2. ta1b2_dynamic: --
448 # -- [8.0.28] --
449 # CREATE TABLE ta1b2_dynamic (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=DYNAMIC, ENGINE=INNODB;
450 #
451 # -- [8.0.28] --
452 # INSERT INTO ta1b2_dynamic VALUES (1, "row1", 2, 3, 4, 5);
453 # INSERT INTO ta1b2_dynamic (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
454 #
455 # -- [8.0.28] --
456 # ALTER TABLE ta1b2_dynamic ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
457 # ALTER TABLE ta1b2_dynamic ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
458 #
459 # -- UPGRADE --
460 #
461 # -- 3. ta1b3_dynamic: --
462 # -- [8.0.28] --
463 # CREATE TABLE ta1b3_dynamic (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=DYNAMIC, ENGINE=INNODB;
464 #
465 # -- [8.0.28] --
466 # INSERT INTO ta1b3_dynamic VALUES (1, "row1", 2, 3, 4, 5);
467 # INSERT INTO ta1b3_dynamic (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
468 #
469 # -- UPGRADE --
470 #
471 # -- 4. ta1b4_dynamic: --
472 # -- [8.0.28] --
473 # CREATE TABLE ta1b4_dynamic (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=DYNAMIC, ENGINE=INNODB;
474 #
475 # -- [8.0.28] --
476 # INSERT INTO ta1b4_dynamic VALUES (1, "row1", 2, 3, 4, 5);
477 # INSERT INTO ta1b4_dynamic (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
478 #
479 # -- [8.0.28] --
480 # ALTER TABLE ta1b4_dynamic ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
481 # ALTER TABLE ta1b4_dynamic ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
482 #
483 # -- UPGRADE --
484 #
485 # -- 5. ta2b2_dynamic: --
486 # -- [8.0.28] --
487 # CREATE TABLE ta2b2_dynamic (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=DYNAMIC, ENGINE=INNODB;
488 #
489 # -- [8.0.28] --
490 # ALTER TABLE ta2b2_dynamic ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
491 # ALTER TABLE ta2b2_dynamic ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
492 #
493 # -- [8.0.28] --
494 # INSERT INTO ta2b2_dynamic VALUES (1, "row1", 2, 3, 4, 5, 6, 7, 8, 9);
495 # INSERT INTO ta2b2_dynamic (c0, c1, c3, c5, c7, c9) VALUES (2, "row2", 33, 55, 77, 99);
496 #
497 # -- UPGRADE --
498 #
499 # -- 6. ta2b4_dynamic: --
500 # -- [8.0.28] --
501 # CREATE TABLE ta2b4_dynamic (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=DYNAMIC, ENGINE=INNODB;
502 #
503 # -- [8.0.28] --
504 # ALTER TABLE ta2b4_dynamic ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
505 # ALTER TABLE ta2b4_dynamic ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
506 #
507 # -- [8.0.28] --
508 # INSERT INTO ta2b4_dynamic VALUES (1, "row1", 2, 3, 4, 5, 6, 7, 8, 9);
509 # INSERT INTO ta2b4_dynamic (c0, c1, c3, c5, c7, c9) VALUES (2, "row2", 33, 55, 77, 99);
510 #
511 # -- UPGRADE --
512 #
513 # -- 7. ta3b2_dynamic: --
514 # -- [8.0.28] --
515 # CREATE TABLE ta3b2_dynamic (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=DYNAMIC, ENGINE=INNODB;
516 #
517 # -- [8.0.28] --
518 # ALTER TABLE ta3b2_dynamic ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
519 # ALTER TABLE ta3b2_dynamic ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
520 #
521 # -- UPGRADE --
522 #
523 # -- 8. ta3b3_dynamic: --
524 # -- [8.0.28] --
525 # CREATE TABLE ta3b3_dynamic (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=DYNAMIC, ENGINE=INNODB;
526 #
527 # -- UPGRADE --
528 #
529 # -- 9. ta3b4_dynamic: --
530 # -- [8.0.28] --
531 # CREATE TABLE ta3b4_dynamic (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ROW_FORMAT=DYNAMIC, ENGINE=INNODB;
532 #
533 # -- [8.0.28] --
534 # ALTER TABLE ta3b4_dynamic ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
535 # ALTER TABLE ta3b4_dynamic ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
536 #
537 # -- UPGRADE --
538 #
539 # What does an, bn mean: see also: (https://confluence.oraclecorp.com/confluence/display/[email protected]/ROW+LOG+with+Online++DDL)
540 # ===============================================================================================
541 # Index(b) None(b1) Instant(b2) Version(b3) Instant&Version(b4)
542 # Row(a)
543 # ===============================================================================================
544 # None(a1) T[n]000 T[n]000 V[0]100 V[0]100
545 # Instant(a2) xxxxxxx T[n]100 xxxxxxx V[0]100
546 # Version(a3) xxxxxxx T[n]110 V[n]110 V[n]110
547 # ===============================================================================================
548 #
549 # ta1b1:
550 # [8.0.28]
551 # CREATE TABLE ta1b1 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
552 #
553 # [8.0.28]
554 # INSERT INTO ta1b1 VALUES (1, "row1", 2, 3, 4, 5);
555 # INSERT INTO ta1b1 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
556 #
557 # -- UPGRADE --
558 #
559 # [trunk]
560 # -- Do online DDL --
561 #
562 # ta3b4:
563 # [8.0.28]
564 # CREATE TABLE ta3b4 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
565 #
566 # [8.0.28]
567 # ALTER TABLE ta3b4 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
568 # ALTER TABLE ta3b4 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
569 #
570 # -- UPGRADE --
571 #
572 # [trunk]
573 # ALTER TABLE ta3b4 ADD COLUMN c10 INT, ADD COLUMN c11 INT NOT NULL, ALGORITHM=INSTANT;
574 # ALTER TABLE ta3b4 ADD COLUMN c12 INT DEFAULT 120, ADD COLUMN c13 INT NOT NULL DEFAULT 130, ALGORITHM=INSTANT;
575 #
576 # [trunk]
577 # INSERT INTO ta3b4 VALUES (1, "row1", 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13);
578 # INSERT INTO ta3b4 (c0, c1, c3, c5, c7, c9, c11, c13) VALUES (2, "row2", 33, 55, 77, 99, 1111, 1313);
579 #
580 # [trunk]
581 # -- Do online DDL --
582 #
583 # End