Debian Codegraph @

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

raw
1 --source include/have_debug.inc
2 --source include/have_innodb_min_16k.inc
3
4 --echo #
5 --echo # Bug: #34378513 : Assertion failure: dict0mem.h:2482:pos < n_def thread 140243300361984
6 --echo #
7
8 --disable_query_log
9 # Procedure to create a table with given number of columns
10 DELIMITER |;
11
12 CREATE PROCEDURE create_table(table_name varchar(50), n_cols INT)
13 BEGIN
14 DECLARE i INT DEFAULT 1;
15 SET @sql_text = CONCAT('CREATE TABLE ', table_name, ' (');
16 WHILE (i <= n_cols) DO
17 SET @sql_text = CONCAT(@sql_text, CONCAT('col_', i), ' INTEGER DEFAULT NULL');
18 set i = i + 1;
19 IF (i <= n_cols) THEN
20 SET @sql_text = CONCAT(@sql_text, ', ');
21 END IF;
22 END WHILE;
23 SET @sql_text = CONCAT(@sql_text, ")");
24 PREPARE stmt FROM @sql_text;
25 EXECUTE stmt;
26 DEALLOCATE PREPARE stmt;
27 END|
28
29 DELIMITER ;|
30 --enable_query_log
31
32 --echo # Create table with 1017 columns
33 call create_table("tb1", 1017);
34
35 --echo # Generate row version
36 INSERT INTO tb1 (col_1017) VALUES (1);
37
38 --echo # Keep a copy for other scenarios
39 CREATE TABLE t1 AS SELECT * FROM tb1;
40 CREATE TABLE t2 AS SELECT * FROM tb1;
41 CREATE TABLE t3 AS SELECT * FROM tb1;
42 CREATE TABLE t4 AS SELECT * FROM tb1;
43
44 --echo
45 --echo # 1017 (user cols) + 3 (system cols) = 1020 (MAX_FIELDS_ALLOWED)
46 --echo
47
48 --echo ########################################
49 --echo # Scenario 1: ADD/DROP single column:
50 --echo ########################################
51
52 --echo # Current: n_def = (user columns + system columns + n_drop_cols)
53 --echo # Current: n_def = 1020 (1017 + 3 + 0)
54
55 --echo # -----------------------------------------------------------------------
56 --echo # DROP - ADD columns till (n_def + n_col_added < MAX_FIELDS_ALLOWED)
57 --echo # -----------------------------------------------------------------------
58
59 --echo # DROP COLUMN should pass
60 ALTER TABLE tb1 DROP COLUMN col_1017, ALGORITHM=INSTANT;
61
62 --echo # Current: n_def = 1020 (1016 + 3 + 1)
63 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
64 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/tb1";
65
66 --echo # ADD COLUMN should fail
67 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
68 ALTER TABLE tb1 ADD COLUMN col_1017 INT, ALGORITHM=INSTANT;
69
70 --echo # DROP COLUMN should still pass
71 ALTER TABLE tb1 DROP COLUMN col_1016, ALGORITHM=INSTANT;
72
73 --echo # Current: n_def = 1020 (1015 + 3 + 2)
74 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
75 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/tb1";
76
77 --echo # ADD COLUMN should still fail
78 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
79 ALTER TABLE tb1 ADD COLUMN col_1017 INT, ALGORITHM=INSTANT;
80
81 --echo # -----------------------------------------------------------------------
82 --echo # Verify that ADD is possible without specifying ALGORITHM
83 --echo # (should fallback to INPLACE)
84 --echo # -----------------------------------------------------------------------
85
86 --echo # Table is rebuilt when ALGORITHM=INPLACE is used (internally)
87 ALTER TABLE tb1 ADD COLUMN col_1017 INT;
88
89 --echo # Current: n_def = 1019 (1016 + 3)
90 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
91 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/tb1";
92
93 --echo # Cleanup
94 DROP TABLE tb1;
95
96 --echo ########################################
97 --echo # Scenario 2: ADD/DROP two columns:
98 --echo ########################################
99
100 --echo # Current: n_def = (user columns + system columns + n_drop_cols)
101 --echo # Current: n_def = 1020 (1017 + 3 + 0)
102
103 --echo # -----------------------------------------------------------------------
104 --echo # DROP - ADD columns till (n_def + n_col_added < MAX_FIELDS_ALLOWED)
105 --echo # -----------------------------------------------------------------------
106
107 --echo # DROP COLUMN for 2 columns should pass
108 ALTER TABLE t1
109 DROP COLUMN col_1017,
110 DROP COLUMN col_1016,
111 ALGORITHM=INSTANT;
112
113 --echo # Current: n_def = 1020 (1015 + 3 + 2)
114 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
115 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1";
116
117 --echo # ADD COLUMN should fail
118 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
119 ALTER TABLE t1
120 ADD COLUMN col_1017 INT,
121 ADD COLUMN col_1016 INT,
122 ALGORITHM=INSTANT;
123
124 --echo # DROP COLUMN for 2 columns should still pass
125 ALTER TABLE t1
126 DROP COLUMN col_1015,
127 DROP COLUMN col_1014,
128 ALGORITHM=INSTANT;
129
130 --echo # Current: n_def = 1020 (1013 + 3 + 4)
131 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
132 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1";
133
134 --echo # ADD COLUMN should still fail
135 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
136 ALTER TABLE t1
137 ADD COLUMN col_1017 INT,
138 ADD COLUMN col_1016 INT,
139 ALGORITHM=INSTANT;
140
141 --echo # -----------------------------------------------------------------------
142 --echo # Verify that ADD is possible without specifying ALGORITHM
143 --echo # (should fallback to INPLACE)
144 --echo # -----------------------------------------------------------------------
145
146 --echo # Table is rebuilt when ALGORITHM=INPLACE is used (internally)
147 ALTER TABLE t1
148 ADD COLUMN col_1017 INT,
149 ADD COLUMN col_1016 INT;
150
151 --echo # Current: n_def = 1018 (1015 + 3)
152 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
153 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1";
154
155 --echo # Cleanup
156 DROP TABLE t1;
157
158 --echo ########################################
159 --echo # Scenario 3: ADD/DROP multiple columns:
160 --echo ########################################
161
162 --echo # Current: n_def = (user columns + system columns + n_drop_cols)
163 --echo # Current: n_def = 1020 (1017 + 3 + 0)
164
165 --echo # -----------------------------------------------------------------------
166 --echo # ADD/DROP multiple columns and look for failures
167 --echo # -----------------------------------------------------------------------
168
169 --echo # DROP multiple columns should pass
170 ALTER TABLE t2
171 DROP COLUMN col_1016,
172 DROP COLUMN col_1015,
173 DROP COLUMN col_1014,
174 DROP COLUMN col_1013,
175 DROP COLUMN col_1012,
176 DROP COLUMN col_1011,
177 ALGORITHM=INSTANT;
178
179 --echo # Current: n_def = 1020 (1011 + 3 + 6)
180 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
181 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t2";
182
183 --echo # ADD COLUMN should fail
184 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
185 ALTER TABLE t2
186 ADD COLUMN col_1011 INT,
187 DROP COLUMN col_1010,
188 ADD COLUMN col_1012 INT,
189 ADD COLUMN col_1013 INT,
190 DROP COLUMN col_1009,
191 DROP COLUMN col_1008,
192 ADD COLUMN col_1014 INT,
193 ALGORITHM=INSTANT;
194
195 --echo # -----------------------------------------------------------------------
196 --echo # Fallback to inplace for failed query
197 --echo # -----------------------------------------------------------------------
198 ALTER TABLE t2
199 ADD COLUMN col_1011 INT,
200 DROP COLUMN col_1010,
201 ADD COLUMN col_1012 INT,
202 ADD COLUMN col_1013 INT,
203 DROP COLUMN col_1009,
204 DROP COLUMN col_1008,
205 ADD COLUMN col_1014 INT;
206
207 --echo # Current: n_def = 1018 (1015 + 3 + 0)
208 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
209 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t2";
210
211 --echo # Cleanup
212 DROP TABLE t2;
213
214 --echo ########################################
215 --echo # Scenario 4: With other ALTER operations:
216 --echo ########################################
217
218 --echo # Current: n_def = (user columns + system columns + n_drop_cols)
219 --echo # Current: n_def = 1020 (1017 + 3 + 0)
220
221 --echo # -----------------------------------------------------------------------
222 --echo # ADD/DROP with other ALTER operations
223 --echo # -----------------------------------------------------------------------
224
225 --echo # DROP COLUMN should pass
226 ALTER TABLE t3
227 DROP COLUMN col_1016,
228 DROP COLUMN col_1015,
229 DROP COLUMN col_1014,
230 DROP COLUMN col_1013,
231 DROP COLUMN col_1012,
232 DROP COLUMN col_1011,
233 DROP COLUMN col_1010,
234 DROP COLUMN col_1009,
235 ALGORITHM=INSTANT;
236
237 --echo Current: n_def = 1020 (1009 + 3 + 8)
238 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
239 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t3";
240
241 --echo # ADD COLUMN with other operations should fail
242 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
243 ALTER TABLE t3
244 ADD COLUMN col_1009 INT,
245 RENAME COLUMN col_1017 TO col_10177,
246 RENAME COLUMN col_1007 TO col_1007_new,
247 DROP COLUMN col_1008,
248 ALGORITHM=INSTANT;
249
250 --echo # ADD COLUMN should fail
251 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
252 ALTER TABLE t3 ADD COLUMN col_1010 INT, ALGORITHM=INSTANT;
253
254 --echo # Similarly single column add/drop will fail
255 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
256 ALTER TABLE t3 ADD COLUMN col_1010 INT, DROP COLUMN col_1008, ALGORITHM=INSTANT;
257
258 --echo # -----------------------------------------------------------------------
259 --echo # Fallback to inplace for failed query
260 --echo # -----------------------------------------------------------------------
261
262 --echo # ADD COLUMN should fallback to INPLACE and should pass
263 ALTER TABLE t3 ADD COLUMN col_1010 INT;
264
265 --echo Current: n_def = 1013 (1010 + 3 + 0)
266 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
267 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t3";
268
269 --echo # Cleanup
270 DROP TABLE t3;
271
272 --echo ########################################
273 --echo # Scenario 5: ADD/DROP few columns:
274 --echo ########################################
275
276 --echo # Current: n_def = (user columns + system columns + n_drop_cols)
277 --echo # Current: n_def = 1020 (1017 + 3 + 0)
278
279 --echo # -----------------------------------------------------------------------
280 --echo # ADD/DROP few columns together
281 --echo # -----------------------------------------------------------------------
282
283 --echo # DROP COLUMN should pass
284 ALTER TABLE t4
285 DROP COLUMN col_1016,
286 DROP COLUMN col_1015,
287 DROP COLUMN col_1014,
288 DROP COLUMN col_1013,
289 DROP COLUMN col_1012,
290 DROP COLUMN col_1011,
291 DROP COLUMN col_1010,
292 DROP COLUMN col_1009,
293 ALGORITHM=INSTANT;
294
295 --echo Current: n_def = 1020 (1009 + 3 + 8)
296 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
297 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t4";
298
299 --echo # ADD COLUMN should fail
300 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
301 ALTER TABLE t4
302 ADD COLUMN col_1016 INT,
303 DROP COLUMN col_1008,
304 ALGORITHM=INSTANT;
305
306 --echo # ADD COLUMN should fail
307 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
308 ALTER TABLE t4
309 ADD COLUMN col_1015 INT,
310 DROP COLUMN col_1007,
311 DROP COLUMN col_1006,
312 ALGORITHM=INSTANT;
313
314 --echo # ADD COLUMN should fail
315 --error ER_INNODB_INSTANT_ADD_NOT_SUPPORTED_MAX_FIELDS
316 ALTER TABLE t4
317 ADD COLUMN col_1014 INT,
318 ADD COLUMN col_1013 INT,
319 DROP COLUMN col_1005,
320 ALGORITHM=INSTANT;
321
322 --echo # -----------------------------------------------------------------------
323 --echo # Fallback to inplace for failed query
324 --echo # -----------------------------------------------------------------------
325
326 ALTER TABLE t4
327 ADD COLUMN col_1014 INT,
328 ADD COLUMN col_1013 INT,
329 DROP COLUMN col_1005;
330
331 --echo Current: n_def = 1013 (1010 + 3 + 0)
332 SELECT TOTAL_ROW_VERSIONS, N_COLS, CURRENT_COLUMN_COUNTS, INITIAL_COLUMN_COUNTS,
333 TOTAL_COLUMN_COUNTS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t4";
334
335 --echo # Cleanup
336 DROP TABLE t4;
337
338 DROP PROCEDURE create_table;