Debian Codegraph @

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

raw
1 ##############################################
2 # Test instant ADD COLUMN for REDUNDANT format
3 ##############################################
4 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT) ROW_FORMAT=REDUNDANT;;
5 INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (6, 6, 6);
6 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 10;
7 SELECT * FROM t1;
8 a b c d
9 1 1 1 10
10 2 2 2 10
11 3 3 3 10
12 6 6 6 10
13 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done';
14 ALTER TABLE t1 DROP COLUMN c, algorithm=inplace;
15 SET DEBUG_SYNC = 'now WAIT_FOR altered';
16 INSERT INTO t1(a, b, c) VALUES(7, 7, 7);
17 UPDATE t1 SET a = 10 WHERE a = 1;
18 DELETE FROM t1 WHERE a = 2;
19 UPDATE t1 SET c = 10 WHERE a = 3;
20 UPDATE t1 SET d = 20 WHERE a = 5;
21 UPDATE t1 SET a = 8, d = 20 WHERE a = 6;
22 SET DEBUG_SYNC = 'now SIGNAL dmls_done';
23 SET DEBUG_SYNC = 'RESET';
24 CHECK TABLE t1;
25 Table Op Msg_type Msg_text
26 test.t1 check status OK
27 SHOW CREATE TABLE t1;
28 Table Create Table
29 t1 CREATE TABLE `t1` (
30 `a` int NOT NULL,
31 `b` int DEFAULT NULL,
32 `d` int DEFAULT '10',
33 PRIMARY KEY (`a`)
34 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
35 SELECT * FROM t1;
36 a b d
37 3 3 10
38 7 7 10
39 8 6 20
40 10 1 10
41 DROP TABLE t1;
42 CREATE TABLE t1 (a INT, b INT) row_format=REDUNDANT;
43 INSERT INTO t1 VALUES(0,0);
44 ALTER TABLE t1 ADD COLUMN c varchar(25) DEFAULT NULL, ALGORITHM=INSTANT;
45 UPDATE t1 SET c="" WHERE a=0;
46 SELECT * FROM t1;
47 a b c
48 0 0
49 DROP TABLE t1;
50 ############################################
51 # Test instant ADD COLUMN for DYNAMIC format
52 ############################################
53 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT) ROW_FORMAT=DYNAMIC;;
54 INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (6, 6, 6);
55 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 10;
56 SELECT * FROM t1;
57 a b c d
58 1 1 1 10
59 2 2 2 10
60 3 3 3 10
61 6 6 6 10
62 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done';
63 ALTER TABLE t1 DROP COLUMN c, algorithm=inplace;
64 SET DEBUG_SYNC = 'now WAIT_FOR altered';
65 INSERT INTO t1(a, b, c) VALUES(7, 7, 7);
66 UPDATE t1 SET a = 10 WHERE a = 1;
67 DELETE FROM t1 WHERE a = 2;
68 UPDATE t1 SET c = 10 WHERE a = 3;
69 UPDATE t1 SET d = 20 WHERE a = 5;
70 UPDATE t1 SET a = 8, d = 20 WHERE a = 6;
71 SET DEBUG_SYNC = 'now SIGNAL dmls_done';
72 SET DEBUG_SYNC = 'RESET';
73 CHECK TABLE t1;
74 Table Op Msg_type Msg_text
75 test.t1 check status OK
76 SHOW CREATE TABLE t1;
77 Table Create Table
78 t1 CREATE TABLE `t1` (
79 `a` int NOT NULL,
80 `b` int DEFAULT NULL,
81 `d` int DEFAULT '10',
82 PRIMARY KEY (`a`)
83 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
84 SELECT * FROM t1;
85 a b d
86 3 3 10
87 7 7 10
88 8 6 20
89 10 1 10
90 DROP TABLE t1;
91 CREATE TABLE t1 (a INT, b INT) row_format=DYNAMIC;
92 INSERT INTO t1 VALUES(0,0);
93 ALTER TABLE t1 ADD COLUMN c varchar(25) DEFAULT NULL, ALGORITHM=INSTANT;
94 UPDATE t1 SET c="" WHERE a=0;
95 SELECT * FROM t1;
96 a b c
97 0 0
98 DROP TABLE t1;
99 ############################################
100 # Test instant ADD COLUMN for COMPACT format
101 ############################################
102 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT) ROW_FORMAT=COMPACT;;
103 INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (6, 6, 6);
104 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 10;
105 SELECT * FROM t1;
106 a b c d
107 1 1 1 10
108 2 2 2 10
109 3 3 3 10
110 6 6 6 10
111 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done';
112 ALTER TABLE t1 DROP COLUMN c, algorithm=inplace;
113 SET DEBUG_SYNC = 'now WAIT_FOR altered';
114 INSERT INTO t1(a, b, c) VALUES(7, 7, 7);
115 UPDATE t1 SET a = 10 WHERE a = 1;
116 DELETE FROM t1 WHERE a = 2;
117 UPDATE t1 SET c = 10 WHERE a = 3;
118 UPDATE t1 SET d = 20 WHERE a = 5;
119 UPDATE t1 SET a = 8, d = 20 WHERE a = 6;
120 SET DEBUG_SYNC = 'now SIGNAL dmls_done';
121 SET DEBUG_SYNC = 'RESET';
122 CHECK TABLE t1;
123 Table Op Msg_type Msg_text
124 test.t1 check status OK
125 SHOW CREATE TABLE t1;
126 Table Create Table
127 t1 CREATE TABLE `t1` (
128 `a` int NOT NULL,
129 `b` int DEFAULT NULL,
130 `d` int DEFAULT '10',
131 PRIMARY KEY (`a`)
132 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
133 SELECT * FROM t1;
134 a b d
135 3 3 10
136 7 7 10
137 8 6 20
138 10 1 10
139 DROP TABLE t1;
140 CREATE TABLE t1 (a INT, b INT) row_format=COMPACT;
141 INSERT INTO t1 VALUES(0,0);
142 ALTER TABLE t1 ADD COLUMN c varchar(25) DEFAULT NULL, ALGORITHM=INSTANT;
143 UPDATE t1 SET c="" WHERE a=0;
144 SELECT * FROM t1;
145 a b c
146 0 0
147 DROP TABLE t1;