Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_sequence.inc
3 --source include/test_db_charset_latin1.inc
4
5 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
6 SET GLOBAL innodb_stats_persistent = 0;
7
8 --echo #
9 --echo # MDEV-17821 Assertion `!page_rec_is_supremum(rec)' failed
10 --echo # in btr_pcur_store_position
11 --echo #
12
13 CREATE TABLE t1 (pk INT PRIMARY KEY, c INT) ENGINE=InnoDB;
14 INSERT INTO t1 VALUES (1,2);
15 ALTER TABLE t1 ADD COLUMN f VARCHAR(255);
16 ALTER TABLE t1 DROP COLUMN f;
17 DELETE FROM t1;
18 HANDLER t1 OPEN;
19 HANDLER t1 READ `PRIMARY` <= (3);
20 DROP TABLE t1;
21
22 CREATE TABLE t1 (
23 pk INT AUTO_INCREMENT,
24 f1 CHAR(32),
25 f2 CHAR(32),
26 f3 CHAR(32),
27 f4 INT,
28 f5 CHAR(32),
29 f6 INT,
30 f7 INT,
31 f8 INT,
32 PRIMARY KEY (pk),
33 UNIQUE (f6)
34 ) ENGINE=InnoDB;
35
36 INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8) VALUES
37 ('reality', 'llt', 'within', -1996816384, 'j', 160, 7, -1822687232),
38 ('h', 'j', 'j', 251, 'civilian', NULL, 211, NULL),
39 ('ltq', 'b', 'mud', 111, 'v', 90, 0, NULL),
40 ('toxic', 'breakfast', 'series', 2, 'x', NULL, 118, 2),
41 ('h', 'n', 'vision', 84, 'n', NULL, 197, 103),
42 ('h', 'tq', 'q', 6, 'bet', -1927151616, -446038016, 3),
43 ('pocket', 'qjt', 'jtl', 0, 'blink', NULL, 12, 0),
44 ('k', 'uv', 'exist', 37, 'g', 149, -1610219520, NULL),
45 ('parent', 'motion', 'u', 70, 'promote', NULL, 178, NULL),
46 ('get', 'convict', 'liquid', -780337152, 'd', NULL, 4, NULL),
47 ('vp', 'px', 'xi', -631111680, 'support', NULL, 8, NULL),
48 ('ck', 'z', 'minority', 8, 'k', NULL, 864878592, NULL),
49 ('kxx', 'g', 'container', 1, 'cholesterol', NULL, 4, 1404436480),
50 ('xxv', 'rail', 'j', 219, 'serious', NULL, -816119808, 62),
51 ('x', 'v', 'vr', 146, 'm', 16, 170, -1765867520),
52 ('age', 'm', 'q', -1251278848, 'rte', 6, 224, NULL),
53 ('discrimination', 't', 'q', 31, 'exl', 0, 2, 244),
54 ('water', 'x', 'ldb', 98, 'r', 235, 4, 63),
55 ('d', 'db', 'p', 248, 'so-called', 102, -255524864, 198),
56 ('last', 'bz', 'us', 0, 'experienced', 137, 240, 134),
57 ('q', 'z', 'attract', 67, 'code', 67, 75, 1758920704),
58 ('yellow', 'c', 'u', 6, 'v', 1731985408, 528154624, 2),
59 ('cognitive', 'he', 'protective', 590020608, 'sentence', NULL, 4, 102),
60 ('eih', 'l', 'ih', 1266024448, 'traditionally', NULL, 190, NULL),
61 ('pine', 'i', 'y', 6, 'glimpse', 214, 7, -1486880768),
62 ('jo', 'everyone', 'ol', 0, 'lj', NULL, 1, 0),
63 ('blood', 'f', 'scientist', 54, 'j', 1341128704, 168, NULL),
64 ('z', 'brief', 'ambassador', 115, 'ygp', 82, 129, NULL),
65 ('gp', 'severe', 'consist', 7, 'p', -1829961728, 602669056, 154),
66 ('admit', 'poetry', 'x', 116, 'enemy', 174, -2128543744, -407764992),
67 ('s', 'norm', 'decide', 2055667712, 'rtz', NULL, 99, -1233715200),
68 ('tzg', 'f', 'beg', 2016280576, 'w', NULL, -643825664, 137),
69 ('zgg', 'x', 'f', 148, 'y', -987496448, -708116480, 8),
70 ('attorney', 'perfectly', 's', 49, 'z', -1865285632, 56, -1518534656),
71 ('concentrate', 's', 'k', -1028849664, 'tir', 83, -1592459264, 1820065792),
72 ('t', 'sacrifice', 'ir', -2143879168, 'recipe', 156, 217, NULL),
73 ('wdf', 'd', 'f', 137, 'empty', NULL, 188, NULL),
74 ('o', 'customer', 'qd', -2135293952, 'z', 1527840768, 227, -1174929408),
75 ('d', 'qow', 'o', 1472266240, 'whe', NULL, 7, 197),
76 ('deny', 'reputation', 'dutch', 59, 'v', 124, 2, 191),
77 ('m', 'liver', 'fv', 0, 'policy', 781582336, 198574080, 177),
78 ('vk', 'kx', 'immigrant', -1452736512, 'x', 163, 76, 6) ,
79 ('j', 'ru', 'r', 67, 'joke', NULL, 3, NULL),
80 ('o', 'u', 'a', -569442304, 'uz', NULL, 933298176, NULL),
81 ('g', 'zb', 'c', -1694760960, 'fish', 18, -390463488, 1),
82 ('bb', 'o', 'b', 6, 'z', 9, 12, NULL),
83 ('compelling', 'xe', 'debut', 89, 'e', -734724096, 119, 175),
84 ('md', 'r', 'object', 1046478848, 'frequently', 915537920, 0, 1506410496),
85 ('dwn', 'wnz', 'x', 1767571456, 'nz', 241, -882180096, 9),
86 ('zvf', 'vfo', 'g', -844824576, 'w', NULL, 1, 9),
87 ('w', 'pose', 'r', 1029308416, 'a', -48627712, 1756168192, NULL),
88 ('o', 'jwp', 'patient', 172, 'i', 297140224, 45809664, 3),
89 ('w', 'p', 'american', 450297856, 'z', 20, 4, 186),
90 ('ridiculous', 'helpful', 'vy', -2022899712, 'conspiracy', NULL, 162, -264634368),
91 ('t', 'g', 'spite', 289931264, 'y', 4, 13, NULL),
92 ('performer', 'i', 'tomato', -1519386624, 'mz', 8, 87, 106),
93 ('m', 'z', 'hang', 3, 'crowded', -537919488, 1, 2),
94 ('fu', 'uot', 'j', 1, 'o', 179, 220, -2084569088),
95 ('ts', 'n', 'su', 1, 'o', 198, 9, 68),
96 ('ball', 'halfway', 'uf', 40, 'l', 145948672, 9, 149),
97 ('hunting', 'n', 'teenager', 0, 'neat', 209, 2044461056, 68),
98 ('independent', 'along', 'fpn', 5, 'pn', 3, 1353252864, 217),
99 ('p', 'presumably', 'n', -1977548800, 'balanced', 1909260288, 197, NULL),
100 ('pink', 'h', 'tear', 8, 'n', 254, 8, 1006305280),
101 ('tyy', 'n', 'yyr', 1107820544, 'yr', NULL, 0, 219),
102 ('u', 'retirement', 'thread', -2083192832, 'rx', -678232064, 209, 1048969216),
103 ('xk', 'kb', 'z', 9, 'ba', 218, 7, 8),
104 ('a', 'plenty', 'forget', 36, 'c', 215, 2027094016, NULL),
105 ('i', 'compromise', 'n', -1090256896, 'o', 10, 66, 1872887808),
106 ('x', 'disappointment', 'cognitive', 753860608, 'ua', 77, 123, 10),
107 ('e', 'added', 'aub', 2, 'u', NULL, 9, 92),
108 ('bc', 'h', 'n', 146, 'master', NULL, 1003945984, NULL),
109 ('execution', 'f', 'cgp', 574423040, 'gp', 2, -518782976, -1189085184),
110 ('pv', 'bad', 'v', 132, 'r', 195, 6, 5),
111 ('modify', 'participation', 'vol', 237, 'j', -842924032, 88, -747765760),
112 ('substantially', 'i', 'congressional', 2, 'edit', NULL, 1003159552, NULL),
113 ('tell', 'forty', 'v', -910098432, 'd', 43, 3, NULL),
114 ('crawl', 'ad', 'respect', -1851195392, 'p', 72, -1709047808, 1343225856),
115 ('w', 'reception', 'fiber', 56, 's', NULL, 2, -993787904),
116 ('successful', 'instruct', 'dug', 2, 'u', 7, -411500544, NULL),
117 ('appointment', 'pregnant', 'weird', 2, 'r', NULL, -897384448, 76),
118 ('g', 'j', 'thin', 663617536, 'oan', 1, 7, NULL),
119 ('secretary', 'a', 'o', 103, 'nj', 1977745408, -1291124736, -1314521088),
120 ('g', 'jq', 'q', 1875116032, 'blame', NULL, 1, 4),
121 ('oj', 'j', 'breast', 150, 'c', NULL, 3, NULL),
122 ('rd', 'm', 'comprehensive', 1723334656, 't', NULL, 155, -312344576) ,
123 ('a', 'd', 'criminal', -1155137536, 'airplane', 242, -662896640, 1),
124 ('fast', 'i', 'k', -386662400, 'zxe', NULL, 7, 119),
125 ('xe', 'mouse', 'c', -205717504, 'ew', NULL, -729612288, 86),
126 ('hang', 'j', 'o', 3, 'hungry', NULL, 200, 49),
127 ('expense', 'z', 'sum', 2, 'gob', -472055808, -538181632, NULL),
128 ('nest', 'o', 'k', 116, 'weak', NULL, 223, NULL);
129 --error ER_DUP_ENTRY
130 INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8) VALUES ('impact', 'b', 'h', 185, 'fj', 7, 7, 3);
131
132 ALTER TABLE t1 ADD COLUMN filler VARCHAR(255) DEFAULT '';
133 SELECT * INTO OUTFILE 'load.data' FROM t1;
134 UPDATE IGNORE t1 SET pk = 0;
135 LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1;
136 HANDLER t1 OPEN AS h;
137 HANDLER h READ `PRIMARY` PREV WHERE 0;
138
139 # Cleanup
140 HANDLER h CLOSE;
141 DROP TABLE t1;
142
143 --echo #
144 --echo # MDEV-19630 ALTER TABLE ... ADD COLUMN damages foreign keys
145 --echo # which are pointed to the table being altered
146 --echo #
147 CREATE TABLE t1(f1 int not null, primary key(f1))engine=innodb;
148 CREATE TABLE t2(f1 INT AUTO_INCREMENT NOT NULL, f2 INT NOT NULL,
149 status ENUM ('a', 'b', 'c'), INDEX idx1(f2),
150 PRIMARY KEY(f1),
151 FOREIGN KEY (f2) REFERENCES t1(f1))ENGINE=InnoDB;
152
153 ALTER TABLE t1 CHANGE f1 f1_id INT NOT NULL, ADD f3 VARCHAR(255) DEFAULT NULL;
154 ALTER TABLE t1 CHANGE f1_id f1 INT NOT NULL;
155
156 SHOW CREATE TABLE t1;
157 SHOW CREATE TABLE t2;
158
159 ALTER TABLE t2 CHANGE status status VARCHAR(20) DEFAULT NULL;
160 DROP TABLE t2, t1;
161
162 --let $datadir= `select @@datadir`
163 --remove_file $datadir/test/load.data
164
165 --echo #
166 --echo # MDEV-20938 Double free of dict_foreign_t during instant ALTER TABLE
167 --echo #
168
169 CREATE TABLE t1 (id INT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;
170 CREATE TABLE t2 (a INT UNSIGNED PRIMARY KEY, b INT UNSIGNED UNIQUE,
171 FOREIGN KEY fk1 (b) REFERENCES t1 (id)) ENGINE=InnoDB;
172 ALTER TABLE t2
173 DROP FOREIGN KEY fk1,
174 CHANGE b d INT UNSIGNED,
175 ADD c INT;
176 DROP TABLE t2, t1;
177
178 --echo #
179 --echo # MDEV-22446 InnoDB aborts while adding instant column
180 --echo # for discarded tablespace
181 --echo #
182
183 let MYSQLD_DATADIR =`SELECT @@datadir`;
184 CREATE TABLE t1(c1 INT NOT NULL, c2 INT NOT NULL DEFAULT 0)ENGINE=InnoDB;
185 INSERT INTO t1(c1) VALUES(1);
186
187 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10;
188 --replace_regex /, .*\).*t1.cfg/, Bad file descriptor) t1.cfg/
189 FLUSH TABLES t1 FOR EXPORT;
190
191 perl;
192 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
193 ib_backup_tablespaces("test", "t1");
194 EOF
195 UNLOCK TABLES;
196 DROP TABLE t1;
197
198 # Restore of instant table
199 CREATE TABLE t1(c1 INT NOT NULL)Engine=InnoDB;
200 ALTER TABLE t1 DISCARD TABLESPACE;
201 FLUSH TABLES;
202 ALTER TABLE t1 ADD COLUMN c2 INT NOT NULL;
203 ALTER TABLE t1 ADD COLUMN c3 INT DEFAULT 10;
204 # Restore files
205 perl;
206 do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
207 ib_restore_tablespaces("test", "t1");
208 EOF
209 ALTER TABLE t1 IMPORT TABLESPACE;
210 SHOW CREATE TABLE t1;
211 SELECT * FROM t1;
212 DROP TABLE t1;
213
214
215 --echo #
216 --echo # MDEV-23801 Assertion index->table->instant... failed
217 --echo # in btr_pcur_store_position()
218 --echo #
219
220 CREATE TABLE t (
221 pk int auto_increment,
222 c01 char(255) not null default repeat('a',255),
223 c02 char(255) default repeat('a',255),
224 c03 char(255) default repeat('a',255),
225 c04 char(255) default repeat('a',255),
226 c05 char(255) not null default repeat('a',255),
227 c06 char(255) default repeat('a',255),
228 c07 char(255) default repeat('a',255),
229 c08 char(255) not null default repeat('a',255),
230 c09 char(255) default repeat('a',255),
231 c10 char(255) default repeat('a',255),
232 c11 char(255) default repeat('a',255),
233 c12 char(255) not null default repeat('a',255),
234 primary key (pk)
235 ) ENGINE=InnoDB CHARACTER SET ucs2;
236
237 INSERT INTO t () VALUES ();
238 ALTER TABLE t ADD c INT;
239
240 BEGIN;
241 INSERT INTO t () VALUES (),();
242 ROLLBACK;
243 DELETE FROM t;
244
245 --source include/wait_all_purged.inc
246
247 CREATE TABLE tt ENGINE=InnoDB AS SELECT c FROM t;
248 DROP TABLE t, tt;
249
250 --echo # End of 10.3 tests
251
252 --disable_query_log
253 SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
254 SET GLOBAL innodb_read_only_compressed=OFF;
255 --enable_query_log
256 create table t (
257 a varchar(9),
258 b int,
259 c int,
260 row_start bigint unsigned generated always as row start invisible,
261 row_end bigint unsigned generated always as row end invisible,
262 period for system_time (row_start, row_end)
263 ) engine=innodb row_format=compressed with system versioning;
264 insert into t values (repeat('a', 9), 1, 1);
265 set @@system_versioning_alter_history = keep;
266 alter table t modify a varchar(10), algorithm=instant;
267 alter table t change b bb int, algorithm=instant;
268 alter table t modify c int without system versioning, algorithm=instant;
269 set @@system_versioning_alter_history = error;
270 --disable_query_log
271 SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
272 --enable_query_log
273 check table t;
274 drop table t;
275
276 --echo #
277 --echo # MDEV-18219 Assertion `index->n_core_null_bytes <= ...' failed
278 --echo # in rec_init_offsets after instant DROP COLUMN
279 --echo #
280 CREATE TABLE t1 (a INT, b INT NOT NULL) ENGINE=InnoDB;
281 INSERT INTO t1 VALUES
282 (0,9),(2,7),(3,1),(3,4),(8,4),(3,7),(6,1),(3,8),(1,2),(4,1),(0,8),(5,3),
283 (1,3),(1,6),(2,1),(8,7),(6,0),(1,9),(9,4),(0,6),(9,3),(0,9),(9,4),(2,4),
284 (2,7),(7,8),(8,2),(2,5),(6,1),(4,5),(5,3),(6,8),(4,9),(5,7),(7,5),(5,1),
285 (8,8),(5,7),(3,8),(0,1),(8,4),(8,3),(9,7),(4,8),(1,1),(0,4),(2,6),(8,5),
286 (8,8),(8,7),(6,7),(1,7),(9,6),(3,6),(1,9),(0,3),(5,3),(2,4),(0,6),(2,0),
287 (6,5),(1,6),(2,4),(9,1),(3,0),(6,4),(1,3),(0,8),(3,5),(3,1),(8,9),(9,9),
288 (7,9),(4,5),(2,2),(3,8),(0,8),(7,1),(2,0),(1,5),(7,3),(4,4),(3,9),(7,2),
289 (6,2),(0,4),(2,0),(1,5),(5,7),(4,5),(3,7),(6,0),(2,1),(5,0),(1,0),(2,0),
290 (8,4),(5,7),(3,5),(0,5),(7,6),(5,9),(1,2),(4,2),(8,5),(8,7),(2,8),(1,8),
291 (4,3),(1,6),(7,8),(3,7),(4,6),(1,1),(3,0),(1,6),(2,0),(3,4),(4,8),(3,9),
292 (8,0),(4,9),(4,0),(3,9),(6,4),(7,4),(5,8),(4,7),(7,3),(5,9),(2,3),(7,3),
293 (0,4),(5,9),(9,8),(4,2),(3,6),(2,6),(1,8),(7,0),(0,0),(2,3),(1,2),(3,3),
294 (2,7),(6,0),(9,0),(6,9),(4,6),(9,8),(0,7),(9,1),(9,6),(4,3),(7,7),(7,7),
295 (4,1),(4,7),(7,3),(2,8),(5,8),(8,9),(3,9),(7,7),(0,8),(4,9),(3,2),(5,0),
296 (1,7),(0,3),(2,9),(9,7),(7,5),(6,9),(8,5),(3,6),(1,1),(2,8),(7,9),(4,9),
297 (6,6),(5,9),(5,3),(9,8),(3,3),(5,6),(0,9),(3,9),(7,9),(7,3),(5,2),(1,4),
298 (4,4),(8,2),(2,2),(8,3),(9,1),(4,9),(9,8),(1,8),(1,8),(9,1),(1,1),(3,0),
299 (4,6),(9,3),(3,3),(5,2),(0,1),(3,4),(3,2),(1,3),(4,4),(7,0),(4,6),(7,2),
300 (4,5),(8,7),(7,8),(8,1),(3,5),(0,6),(3,5),(2,1),(4,4),(3,4),(2,1),(4,1);
301 INSERT INTO t1 SELECT * FROM t1;
302 ALTER TABLE t1 DROP a;
303 # Exploit MDEV-17468 to force the table definition to be reloaded
304 ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL;
305 DROP TABLE t1;
306
307 --echo #
308 --echo # MDEV-19030 Assertion index->n_core_null_bytes <= ... failed
309 --echo # in rec_init_offsets after instant DROP COLUMN
310 --echo #
311 CREATE TABLE t1 (a INT, b INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
312 INSERT INTO t1 () VALUES (),(),(),();
313 INSERT INTO t1 SELECT * FROM t1;
314 INSERT INTO t1 SELECT * FROM t1;
315 INSERT INTO t1 SELECT * FROM t1;
316 INSERT INTO t1 SELECT * FROM t1;
317 INSERT INTO t1 SELECT * FROM t1;
318 ALTER TABLE t1 FORCE;
319 INSERT INTO t1 SELECT * FROM t1;
320 ALTER TABLE t1 DROP a, ADD a SMALLINT NOT NULL;
321 INSERT INTO t1 SELECT * FROM t1;
322 INSERT INTO t1 SELECT * FROM t1;
323 # Exploit MDEV-17468 to force the table definition to be reloaded
324 ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL;
325 DROP TABLE t1;
326
327 --echo #
328 --echo # MDEV-18623 Assertion after DROP FULLTEXT INDEX and removing NOT NULL
329 --echo #
330 CREATE TABLE t1 (c TEXT NOT NULL, FULLTEXT INDEX ftidx(c)) ENGINE=InnoDB
331 ROW_FORMAT=REDUNDANT;
332 ALTER TABLE t1 DROP INDEX ftidx;
333 --error ER_ALTER_OPERATION_NOT_SUPPORTED
334 ALTER TABLE t1 MODIFY c TEXT NULL, ALGORITHM=INSTANT;
335 ALTER TABLE t1 MODIFY c TEXT NULL;
336 DROP TABLE t1;
337
338 --echo #
339 --echo # MDEV-20048 dtuple_get_nth_field(): Assertion 'n < tuple->n_fields'
340 --echo # failed on ROLLBACK after instant DROP COLUMN
341 --echo #
342 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
343 INSERT INTO t1 VALUES (1);
344 ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
345 SET @b = REPEAT('b', @@innodb_page_size / 2 + 1);
346 INSERT INTO t1 VALUES(2, @b), (3, @b);
347 BEGIN;
348 DELETE FROM t1 WHERE a=2;
349
350 # Stop purge so that it doesn't remove the delete-marked entry.
351 connect (purge_control,localhost,root);
352 START TRANSACTION WITH CONSISTENT SNAPSHOT;
353 connection default;
354
355 COMMIT;
356
357 ALTER TABLE t1 DROP b, ALGORITHM=INSTANT;
358 BEGIN;
359 INSERT INTO t1 VALUES (2);
360
361 connection purge_control;
362 SELECT * FROM t1;
363 disconnect purge_control;
364 connection default;
365 ROLLBACK;
366
367 SELECT * FROM t1;
368 DROP TABLE t1;
369
370
371 --echo #
372 --echo # MDEV-20479: assertion failure in dict_table_get_nth_col() after INSTANT DROP COLUMN
373 --echo #
374
375 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
376 ALTER TABLE t1 ADD COLUMN (b INT, c INT, d INT, e INT NOT NULL DEFAULT 0);
377 ALTER TABLE t1 ADD UNIQUE INDEX(e);
378 ALTER TABLE t1 DROP b, DROP c, DROP d, DROP e;
379 DROP TABLE t1;
380
381 --echo #
382 --echo # MDEV-20565 Assertion on CHANGE COLUMN...SYSTEM VERSIONING
383 --echo #
384 set @@system_versioning_alter_history = keep;
385 CREATE TABLE t (a INT WITHOUT SYSTEM VERSIONING, b INT) ENGINE=InnoDB
386 WITH SYSTEM VERSIONING;
387 ALTER TABLE t CHANGE COLUMN a alpha INT WITH SYSTEM VERSIONING,
388 ALGORITHM=INSTANT;
389 DROP TABLE t;
390
391 CREATE TABLE t (alpha INT, b INT) ENGINE=InnoDB WITH SYSTEM VERSIONING;
392 ALTER TABLE t CHANGE COLUMN alpha a INT WITHOUT SYSTEM VERSIONING,
393 ALGORITHM=INSTANT;
394 DROP TABLE t;
395 set @@system_versioning_alter_history = error;
396
397 --echo #
398 --echo # MDEV-20117 Assertion 0 failed in row_sel_get_clust_rec_for_mysql
399 --echo #
400
401 # This is not repeating the bug itself, but demonstrating that both
402 # parts of the fix are needed.
403 # To repeat the original bug, we should be somehow able to empty
404 # the table of user records while purgeable undo log records exist.
405 CREATE TABLE t (b INT PRIMARY KEY) ENGINE=InnoDB;
406 INSERT INTO t SET b=1;
407 ALTER TABLE t ADD COLUMN a INT FIRST, ALGORITHM=INSTANT;
408 DELETE FROM t;
409 ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;
410 # If page_cur_delete_rec() emptied the page (and wrongly reset the
411 # page type) during the previous ALTER TABLE, the following would hit
412 # an assertion failure because of root page type mismatch.
413 ALTER TABLE t DROP COLUMN c, ALGORITHM=INSTANT;
414 SELECT * FROM t;
415 DROP TABLE t;
416
417 # The following is nondeterministically repeating the bug in a
418 # different scenario: the table is empty at the time the ALTER TABLE
419 # is invoked, apparently because purge already processed the records
420 # for the DELETE, but not the record for the UPDATE.
421 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT) ENGINE=InnoDB;
422 INSERT INTO t1 SET a=1;
423 INSERT INTO t1 SET a=2;
424 BEGIN;
425 UPDATE t1 SET b=1;
426 DELETE FROM t1;
427 COMMIT;
428
429 ALTER TABLE t1 DROP b, DROP c, DROP d, DROP e;
430 --source include/wait_all_purged.inc
431 SELECT * FROM t1;
432 DROP TABLE t1;
433
434 --echo #
435 --echo # MDEV-20190 Instant operation fails when add column and collation
436 --echo # change on non-indexed column
437 --echo #
438
439 CREATE TABLE t1 (a CHAR)ENGINE=INNODB;
440 ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs;
441 ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT;
442 SHOW CREATE TABLE t1;
443 DROP TABLE t1;
444
445 CREATE TABLE t1 (a CHAR NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
446 ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs;
447 ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT;
448 SHOW CREATE TABLE t1;
449 DROP TABLE t1;
450
451 CREATE TABLE t1 (a CHAR NOT NULL) CHARSET latin2 COLLATE latin2_bin
452 ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
453 ALTER TABLE t1 DEFAULT COLLATE = latin2_general_ci;
454 ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT;
455 SHOW CREATE TABLE t1;
456 DROP TABLE t1;
457
458 --echo #
459 --echo # MDEV-21645 SIGSEGV in innobase_get_computed_value
460 --echo #
461 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, va INTEGER GENERATED ALWAYS AS (a))
462 ENGINE=InnoDB;
463 INSERT INTO t1 SET a=1, b=NULL;
464 ALTER TABLE t1 MODIFY COLUMN b INT FIRST;
465 ALTER TABLE t1 ADD UNIQUE INDEX (va);
466 DROP TABLE t1;
467
468 --echo #
469 --echo # MDEV-22651 Assertion dict_col_get_fixed_size...
470 --echo # in dict_table_t::init_instant()
471 --echo #
472 --source include/have_innodb.inc
473 CREATE TABLE t (i INT PRIMARY KEY) ENGINE=InnoDB;
474 INSERT INTO t SET i=1;
475 ALTER TABLE t ADD e CHAR(255) CHARACTER SET UTF32 FIRST, ALGORITHM=INSTANT;
476 DROP TABLE t;
477
478 --echo #
479 --echo # MDEV-23499 Assertion c.same_type(*o) failed
480 --echo #
481 CREATE TABLE t (pk SERIAL, b TEXT CHARACTER SET utf8) ENGINE=InnoDB;
482 ALTER TABLE t MODIFY b TEXT CHARACTER SET utf8mb4 FIRST;
483 DROP TABLE t;
484
485 --echo #
486 --echo # MDEV-23672 Assertion `v.v_indexes.empty()' failed in dict_table_t::instant_column
487 --echo #
488 create table t1 (
489 col_int integer,
490 col_text text not null,
491 col_int_g integer generated always as (col_int) unique,
492 col_text_g text generated always as (substr(col_text,1,499)) )
493 engine innodb row_format = redundant;
494 insert into t1 (col_int,col_text) values (0, 'a'), (null, 'b');
495 alter table t1 modify column col_text text null, algorithm = instant;
496 insert into t1 (col_int,col_text) values (1, null), (null, null);
497 update t1 set col_text= 'd';
498 select * from t1;
499 check table t1;
500 drop table t1;
501
502 --echo #
503 --echo # MDEV-24072 Assertion 'ib_table.n_v_cols' failed
504 --echo # in instant_alter_column_possible()
505 --echo #
506 CREATE TABLE t (a BLOB) ENGINE=InnoDB;
507 INSERT INTO t VALUES ('a');
508 --error ER_TRUNCATED_WRONG_VALUE
509 ALTER TABLE t ADD c INT GENERATED ALWAYS AS (a+1) VIRTUAL, ADD KEY(c);
510 --enable_info
511 ALTER TABLE t ADD d INT;
512 --disable_info
513 DROP TABLE t;
514
515 --echo #
516 --echo # MDEV-28060 Online DDL fails while checking for instant
517 --echo # alter condition
518 --echo #
519 CREATE TABLE t1(f1 CHAR(10) NOT NULL)ROW_FORMAT=REDUNDANT,ENGINE=InnoDB;
520 ALTER TABLE t1 ADD COLUMN(f2 INT NOT NULL, f3 INT NOT NULL,
521 f4 INT NOT NULL, f5 INT NOT NULL),
522 CHANGE COLUMN f1 f1 CHAR(10) DEFAULT NULL;
523 DROP TABLE t1;
524
525 --echo #
526 --echo # MDEV-26420 Buffer overflow on instant ADD/DROP of generated column
527 --echo #
528 CREATE TABLE t1 (i int AS (0) STORED, j INT) ENGINE=InnoDB;
529 ALTER TABLE t1 ADD COLUMN i INT GENERATED ALWAYS AS (1), DROP COLUMN i;
530 DROP TABLE t1;
531
532 --echo #
533 --echo # MDEV-18322 Assertion "wrong_page_type" on instant ALTER
534 --echo #
535
536 DELIMITER $$;
537 BEGIN NOT ATOMIC
538 DECLARE c TEXT
539 DEFAULT(SELECT CONCAT('CREATE TABLE t1 (c',
540 GROUP_CONCAT(seq SEPARATOR ' CHAR(200), c'),
541 ' CHAR(211)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT')
542 FROM seq_1_to_40);
543 EXECUTE IMMEDIATE c;
544 END;
545 $$
546 DELIMITER ;$$
547 INSERT INTO t1 SET c1=NULL;
548 --error ER_TOO_BIG_ROWSIZE
549 ALTER TABLE t1 ADD c41 INT FIRST;
550 --error ER_TOO_BIG_ROWSIZE
551 ALTER TABLE t1 ADD c41 INT FIRST;
552 CHECK TABLE t1;
553 SELECT COUNT(*) FROM t1;
554 DROP TABLE t1;
555
556 --echo # End of 10.4 tests
557
558 --echo #
559 --echo # MDEV-29010 Table cannot be loaded after instant ALTER
560 --echo #
561 CREATE TABLE t1 (a CHAR(255), b INT,
562 c INT as (b) VIRTUAL)ENGINE=InnoDB CHARACTER SET utf32;
563 ALTER TABLE t1 DROP COLUMN a;
564 ALTER TABLE t1 DROP COLUMN c;
565 DROP TABLE t1;
566
567 --echo #
568 --echo # MDEV-35122 Incorrect NULL value handling for instantly
569 --echo # dropped BLOB columns
570 --echo #
571 CREATE TABLE t1 (c1 INT, c2 BLOB, c3 BLOB NOT NULL) ROW_FORMAT=REDUNDANT,ENGINE=InnoDB;
572 ALTER TABLE t1 DROP c2;
573 ALTER TABLE t1 DROP c3;
574 INSERT INTO t1 VALUES(1);
575 DROP TABLE t1;
576
577 --echo # End of 10.5 tests
578
579 --echo # End of 10.6 tests
580
581 --source include/test_db_charset_restore.inc
582
583 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;