Debian Codegraph @

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

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