Debian Codegraph @

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

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