Debian Codegraph @

mysql-test/suite/innodb/include/instant_add_column_clear.inc

raw
1
2 --echo #
3 --echo # Scenario 1:
4 --echo # Create a normal table, rebuild and truncate will clear the instant
5 --echo # information
6 --echo #
7
8 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
9
10 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
11
12
13 let $new_cols = 1;
14 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
15
16 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
17
18 # An ALTER TABLE which requires rebuild
19 let $new_cols = 0;
20 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20, ADD KEY(c2);
21
22 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
23
24 # Should be empty now
25 SELECT count(*) FROM information_schema.innodb_tables WHERE instant_cols != 0;
26 SELECT count(*) FROM information_schema.innodb_columns WHERE has_default = 1;
27
28
29 INSERT INTO t1(a, b) VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
30
31 let $new_cols = 1;
32 let $instant_add_column = ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10;
33
34 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
35
36 # TRUNCATE TABLE should rebuild the table too
37 let $new_cols = 0;
38 let $instant_add_column = TRUNCATE TABLE t1;
39
40 --source ../mysql-test/suite/innodb/include/instant_add_column_exec_and_verify.inc
41
42 # Should be empty now
43 SELECT count(*) FROM information_schema.innodb_tables WHERE instant_cols != 0;
44 SELECT count(*) FROM information_schema.innodb_columns WHERE has_default = 1;
45
46
47 CHECK TABLE t1;
48
49 SHOW CREATE TABLE t1;
50
51 DROP TABLE t1;
52
53
54
55 --echo #
56 --echo # Scenario 2:
57 --echo # Create a partitioned table, rebuild and truncate will clear the instant
58 --echo # information
59 --echo #
60
61 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format PARTITION BY HASH(a) PARTITIONS 3;
62
63 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
64
65 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
66 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
67
68 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
69
70 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
71 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
72
73 # This will rebuild all partitions
74 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20, ALGORITHM = COPY;
75
76 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
77 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
78
79 INSERT INTO t1(b, c1, c2) SELECT b, c1, c2 FROM t1;
80
81 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 30;
82
83 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
84 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
85
86 # This will rebuild all partitions
87 TRUNCATE TABLE t1;
88
89 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
90 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
91
92 CHECK TABLE t1;
93
94 SHOW CREATE TABLE t1;
95
96 DROP TABLE t1;
97
98
99
100 --echo #
101 --echo # Scenario 3:
102 --echo # Create a partitioned table, ALTER TABLE ... PARTITION will clear the
103 --echo # instant information
104 --echo #
105
106 # Range partitioned table
107 --eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) ROW_FORMAT=$row_format PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));
108
109 INSERT INTO t1 VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
110
111 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
112 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
113
114 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
115
116 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
117 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
118
119 # The new partition should have instant column information
120 ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN(40));
121
122 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
123 SELECT count(*) AS `Expect 4` FROM information_schema.innodb_columns WHERE has_default = 1;
124 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p4%';
125
126 # The new partition should have instant column information
127 ALTER TABLE t1 REORGANIZE PARTITION p3 INTO (PARTITION p31 VALUES LESS THAN(25), PARTITION p32 VALUES LESS THAN(30));
128
129 SELECT * FROM t1 WHERE a > 20 AND a < 30;
130 SELECT * FROM t1 WHERE a > 10 AND a < 20;
131
132 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
133 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
134 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p3%';
135
136 # The truncated partition should not have instant information
137 ALTER TABLE t1 TRUNCATE PARTITION p1;
138
139 SELECT * FROM t1 WHERE a < 10;
140
141 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
142 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
143 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p1%';
144
145 # Truncate all partitions, the table instant information should be clean
146 ALTER TABLE t1 TRUNCATE PARTITION p1, p2, p31, p32, p4;
147
148 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
149 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
150
151
152 INSERT INTO t1(a, b) VALUES(1, 1), (2, 2), (11, 11), (12, 12), (21, 21), (22, 22), (26, 26), (27, 27);
153
154 SELECT c FROM t1 GROUP BY c;
155
156 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 100;
157
158 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
159 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
160
161 ALTER TABLE t1 TRUNCATE PARTITION p1;
162
163 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
164 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
165 SELECT instant_cols FROM information_schema.innodb_tables WHERE name LIKE '%p1%';
166
167
168 # Drop all partitions except p1, the table information should be clean,
169 # because p1 is a new partition
170 ALTER TABLE t1 DROP PARTITION p2, p4;
171
172 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
173 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
174
175 ALTER TABLE t1 DROP PARTITION p31, p32;
176
177 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
178 SELECT count(*) AS `Expect 1` FROM information_schema.innodb_columns WHERE has_default = 1;
179
180
181 CHECK TABLE t1;
182
183 SHOW CREATE TABLE t1;
184
185 DROP TABLE t1;
186
187
188 # HASH partitioned table
189 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format PARTITION BY HASH(a) PARTITIONS 3;
190
191 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
192
193 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
194 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_columns WHERE has_default = 1;
195
196 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10;
197
198 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
199 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_columns WHERE has_default = 1;
200
201 # ADD PARTITION will rebuild the whole table, and instant information should be
202 # clean for each partition
203 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
204
205 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
206 SELECT count(*) AS `Expect 5` FROM information_schema.innodb_columns WHERE has_default = 1;
207
208
209 ALTER TABLE t1 ADD COLUMN c2 INT DEFAULT 20;
210
211 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
212 SELECT count(*) AS `Expect 10` FROM information_schema.innodb_columns WHERE has_default = 1;
213
214 # COALESCE PARTITION will rebuild the whole table, and instant information
215 # should be clean for each partition
216 ALTER TABLE t1 COALESCE PARTITION 2;
217
218 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
219 SELECT count(*) AS `Expect 6` FROM information_schema.innodb_columns WHERE has_default = 1;
220
221
222 # Verify it once more
223
224 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 30;
225
226 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
227 SELECT count(*) AS `Expect 9` FROM information_schema.innodb_columns WHERE has_default = 1;
228
229 # ADD PARTITION will rebuild the whole table, and instant information should be
230 # clean for each partition
231 ALTER TABLE t1 ADD PARTITION PARTITIONS 2;
232
233 SELECT count(*) AS `Expect 0` FROM information_schema.innodb_tables WHERE instant_cols != 0;
234 SELECT count(*) AS `Expect 15` FROM information_schema.innodb_columns WHERE has_default = 1;
235
236
237 SELECT * FROM t1;
238
239
240 CHECK TABLE t1;
241
242 SHOW CREATE TABLE t1;
243
244 DROP TABLE t1;