Debian Codegraph @

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

raw
1 --source include/innodb_page_size.inc
2 --source include/have_sequence.inc
3 --source include/test_db_charset_latin1.inc
4
5 let $datadir=`select @@datadir`;
6
7 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
8 SET GLOBAL innodb_stats_persistent = 0;
9
10 --echo #
11 --echo # MDEV-11369: Instant ADD COLUMN for InnoDB
12 --echo #
13
14 SET @saved_allowance = @@GLOBAL.innodb_instant_alter_column_allowed;
15 SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT;
16
17 call mtr.add_suppression("Cannot add field `.*` in table `test`.`.*` because after adding it, the row size is");
18
19 let $format= `SELECT CASE WHEN @@GLOBAL.innodb_page_size>16384
20 THEN 'DYNAMIC' ELSE 'COMPRESSED' END`;
21 CREATE TABLE t(a INT UNIQUE)ENGINE=InnoDB ROW_FORMAT=COMPACT;
22 eval ALTER TABLE t ADD e INT, ROW_FORMAT=$format;
23 INSERT INTO t SET a=1;
24 SET @old_instant=
25 (SELECT variable_value FROM information_schema.global_status
26 WHERE variable_name = 'innodb_instant_alter_column');
27 --enable_info
28 ALTER TABLE t ADD b INT NOT NULL, ALGORITHM=COPY;
29 ALTER TABLE t ADD c INT NOT NULL, FORCE;
30 --disable_info
31 SELECT variable_value-@old_instant instants
32 FROM information_schema.global_status
33 WHERE variable_name = 'innodb_instant_alter_column';
34 --enable_info
35 ALTER TABLE t ADD d INT NOT NULL;
36 --disable_info
37 SELECT * FROM t;
38 SHOW CREATE TABLE t;
39 SELECT variable_value-@old_instant instants
40 FROM information_schema.global_status
41 WHERE variable_name = 'innodb_instant_alter_column';
42 SELECT variable_value-@old_instant instants
43 FROM information_schema.global_status
44 WHERE variable_name = 'innodb_instant_alter_column';
45 DROP TABLE t;
46
47 connect analyze, localhost, root;
48 connection default;
49 SET timestamp = 42;
50 SET time_zone='+03:00';
51
52 SET @old_instant=
53 (SELECT variable_value FROM information_schema.global_status
54 WHERE variable_name = 'innodb_instant_alter_column');
55
56 let $format= 3;
57 let $redundant_4k= `SELECT @@GLOBAL.innodb_page_size=4096`;
58 while ($format) {
59 let $engine= `SELECT CONCAT('ENGINE=InnoDB ROW_FORMAT=',CASE $format
60 WHEN 1 THEN 'DYNAMIC'
61 WHEN 2 THEN 'COMPACT'
62 ELSE 'REDUNDANT' END)`;
63
64 eval CREATE TABLE t1
65 (id INT PRIMARY KEY, c2 INT UNIQUE,
66 c3 POINT NOT NULL DEFAULT ST_GeomFromText('POINT(3 4)'),
67 SPATIAL INDEX(c3)) $engine;
68
69 INSERT INTO t1 (id, c2) values(1,1);
70 SELECT id,c2,ST_AsText(c3) c3 FROM t1;
71
72 --enable_info
73 ALTER TABLE t1 ADD COLUMN (
74 d1 INT, d2 INT UNSIGNED DEFAULT 10, d3 VARCHAR(20) NOT NULL DEFAULT 'abcde',
75 d4 TIMESTAMP NOT NULL DEFAULT current_timestamp());
76 ALTER TABLE t1 ADD INDEX(d3);
77 --disable_info
78
79 BEGIN;
80 UPDATE t1 SET d3='';
81 ROLLBACK;
82 SELECT id,c2,ST_AsText(c3) c3, d1, d2, d3, d4 FROM t1;
83 INSERT INTO t1 (id) VALUES(2),(3),(4),(5),(6);
84
85 --enable_info
86 ALTER TABLE t1 CHANGE d1 d1 INT DEFAULT 5, CHANGE d2 d2 INT DEFAULT 15,
87 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'fghij',
88 CHANGE d4 dfour TIMESTAMP NOT NULL DEFAULT now();
89 --disable_info
90
91 UPDATE t1 SET d3='foo' WHERE id = 2;
92 UPDATE t1 SET d3=DEFAULT WHERE id = 4;
93 INSERT INTO t1 SET id = 7;
94 SELECT id,c2,ST_AsText(c3) c3, d1, d2, d3, dfour FROM t1;
95 CHECK TABLE t1;
96
97 # add virtual columns
98 --enable_info
99 ALTER TABLE t1 ADD COLUMN e1 INT AS (id * 3);
100 ALTER TABLE t1 ADD COLUMN e2 VARCHAR(30) AS (d3);
101 ALTER TABLE t1 ADD COLUMN e3 INT AS (id * 2);
102
103 # instant alter
104 ALTER TABLE t1 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'foobar',
105 ADD COLUMN (d5 CHAR(20) DEFAULT 'hijkl', d6 INT DEFAULT -12345, d7 INT),
106 DROP INDEX d3;
107 --disable_info
108
109 INSERT INTO t1 SET id = 8;
110
111 # Updating a column by extending an existing record
112 UPDATE t1 SET d3 = 'yyyyy' WHERE id = 1;
113
114 # Updating an already materialized column
115 UPDATE t1 SET d3 = 'xxxxx' WHERE id = 2;
116
117 # transaction rollback
118 BEGIN;
119 UPDATE t1 SET d3 = 'xxxxx' WHERE id = 3;
120 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 3;
121 ROLLBACK;
122 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 3;
123
124 # NULL to NULL, no change
125 BEGIN;
126 UPDATE t1 SET d7 = NULL WHERE ID = 5;
127 ROLLBACK;
128 BEGIN;
129 UPDATE t1 SET d7 = NULL, d6 = 10 WHERE id = 5;
130 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 5;
131 ROLLBACK;
132 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7 FROM t1 WHERE id = 5;
133
134 # add virtual stored columns; not instant
135 --enable_info
136 ALTER TABLE t1 ADD COLUMN (f1 VARCHAR(20) AS (concat('x', e2)) STORED);
137
138 # instant add
139 ALTER TABLE t1 ADD COLUMN (d8 VARCHAR(20) DEFAULT 'omnopq');
140 --disable_info
141
142 SELECT id, c2, ST_AsText(c3) c3, d1, d2, d3, dfour, e1, e2, e3, d5, d6, d7, f1, d8 FROM t1;
143 SHOW CREATE TABLE t1;
144
145 --enable_info
146 ALTER TABLE t1
147 CHANGE c2 c2 INT DEFAULT 42,
148 CHANGE d1 d1 INT DEFAULT 1,
149 CHANGE d2 d2 INT DEFAULT 20,
150 CHANGE d3 d3 VARCHAR(20) NOT NULL DEFAULT 'boofar';
151 --disable_info
152 INSERT INTO t1 SET id=9;
153 --enable_info
154 ALTER TABLE t1 DROP c3;
155 --disable_info
156
157 SHOW CREATE TABLE t1;
158 SELECT * FROM t1;
159
160 eval CREATE TABLE t2
161 (id INT primary key, c1 VARCHAR(4000),
162 p GEOMETRY NOT NULL DEFAULT ST_GeomFromText('LINESTRING(0 0,0 1,1 1)'),
163 SPATIAL INDEX(p))
164 $engine;
165
166 BEGIN;
167 INSERT INTO t2 SET id=1, c1=REPEAT('a', 4000);
168 INSERT INTO t2 SET id=2, c1=REPEAT('a', 4000), p=ST_GeomFromText('POINT(1 1)');
169 COMMIT;
170
171 --enable_info
172 ALTER TABLE t2 ADD COLUMN d1 VARCHAR(2000) DEFAULT REPEAT('asdf',500);
173 --disable_info
174 SELECT id, c1, ST_AsText(p) p, d1 FROM t2;
175
176 # inplace update, rollback
177 BEGIN;
178 UPDATE t2 SET c1 = repeat(id, 4000);
179
180 connection analyze;
181 ANALYZE TABLE t2;
182 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
183 WHERE name = 'test/t2';
184 connection default;
185
186 ROLLBACK;
187 connection analyze;
188 ANALYZE TABLE t2;
189 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
190 WHERE name = 'test/t2';
191 connection default;
192
193 # non-inplace update. Rollback MUST NOT materialize off-page columns.
194 BEGIN;
195 UPDATE t2 SET d1 = repeat(id, 200);
196 connection analyze;
197 ANALYZE TABLE t2;
198 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
199 WHERE name = 'test/t2';
200 connection default;
201 ROLLBACK;
202 connection analyze;
203 ANALYZE TABLE t2;
204 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
205 WHERE name = 'test/t2';
206 connection default;
207
208 if ($redundant_4k)
209 {
210 --error ER_TOO_BIG_ROWSIZE
211 ALTER TABLE t2 DROP p;
212 SET innodb_strict_mode = OFF;
213 }
214 --enable_info
215 ALTER TABLE t2 DROP p;
216 --disable_info
217 if ($redundant_4k)
218 {
219 SET innodb_strict_mode = ON;
220 }
221 SELECT * FROM t2;
222
223 # datetime
224 eval CREATE TABLE t3
225 (id INT PRIMARY KEY, c2 INT UNSIGNED NOT NULL UNIQUE,
226 c3 POLYGON NOT NULL DEFAULT ST_PolyFromText('POLYGON((1 1,2 2,3 3,1 1))'),
227 SPATIAL INDEX(c3))
228 $engine;
229 INSERT INTO t3(id,c2) VALUES(1,1),(2,2),(3,3);
230 SELECT id, c2, ST_AsText(c3) c3 FROM t3;
231 --enable_info
232 ALTER TABLE t3 ADD COLUMN
233 (c4 DATETIME DEFAULT current_timestamp(),
234 c5 TIMESTAMP NOT NULL DEFAULT current_timestamp(),
235 c6 POINT);
236 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6 FROM t3;
237 ALTER TABLE t3 ADD COLUMN c7 TIME NOT NULL DEFAULT current_timestamp();
238 ALTER TABLE t3 ADD COLUMN c8 DATE NOT NULL DEFAULT current_timestamp();
239 --disable_info
240 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6, c7, c8 FROM t3;
241
242 --enable_info
243 ALTER TABLE t3 ADD COLUMN t TEXT CHARSET utf8
244 DEFAULT 'The quick brown fox jumps over the lazy dog';
245
246 if ($redundant_4k)
247 {
248 --error ER_TOO_BIG_ROWSIZE
249 ALTER TABLE t3 ADD COLUMN b BLOB NOT NULL;
250 SET innodb_strict_mode = OFF;
251 }
252 ALTER TABLE t3 ADD COLUMN b BLOB NOT NULL;
253 --error ER_NO_DEFAULT_FOR_FIELD
254 INSERT INTO t3 SET id=4;
255 INSERT INTO t3 SET id=4, c2=0, b=0xf09f98b1;
256
257 SET innodb_strict_mode = OFF;
258 ALTER TABLE t3 CHANGE t phrase TEXT DEFAULT 0xc3a4c3a448,
259 CHANGE b b BLOB NOT NULL DEFAULT 'binary line of business';
260 --disable_info
261 INSERT INTO t3 SET id=5, c2=9;
262 SELECT id, c2, ST_AsText(c3) c3, c4, c5, c6, c7, c8, phrase, b FROM t3;
263 --enable_info
264 ALTER TABLE t3 DROP c3, DROP c7;
265 --disable_info
266 SELECT * FROM t3;
267
268 eval CREATE TABLE t4
269 (id INT, foo INT DEFAULT 0, c1 VARCHAR(4000),
270 p GEOMETRY NOT NULL DEFAULT ST_GeomFromText('LINESTRING(0 0,0 1,1 1)'),
271 PRIMARY KEY(id,foo))
272 $engine;
273
274 INSERT INTO t4 (id,c1) VALUES (1, REPEAT('a', 4000)), (2, REPEAT('a', 4000));
275 ALTER TABLE t4 ADD COLUMN d1 INT;
276
277 BEGIN;
278 UPDATE t4 SET c1 = repeat('1', 4000), foo=1 WHERE id=1;
279 INSERT INTO t4 (id,c1) VALUES (1, REPEAT('a', 4000));
280 UPDATE t4 SET c1 = repeat('2', 4000), foo=1 WHERE id=2;
281 ROLLBACK;
282
283 BEGIN;
284 UPDATE t4 SET d1 = 1,foo=2 WHERE id=1;
285 INSERT INTO t4 (id,foo,c1) VALUES (1, 1, REPEAT('1', 4000));
286 COMMIT;
287
288 eval CREATE TABLE big
289 (id INT PRIMARY KEY, c1 VARCHAR(4000), c2 VARCHAR(4000), c3 VARCHAR(1000),
290 p POINT NOT NULL DEFAULT ST_GeomFromText('POINT(0 0)'), SPATIAL INDEX(p))
291 $engine;
292 BEGIN;
293 INSERT INTO big
294 SET id=1, c1=REPEAT('a', 200), c2=REPEAT('b', 200), c3=REPEAT('c', 159);
295 SET @i:=1;
296 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
297 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
298 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
299 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
300 INSERT INTO big SELECT @i:=@i+1, c1, c2, c3, p FROM big;
301 COMMIT;
302 connection analyze;
303 ANALYZE TABLE big;
304 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
305 WHERE name = 'test/big';
306 connection default;
307 --enable_info
308 ALTER TABLE big ADD COLUMN
309 (d1 INT DEFAULT 0, d2 VARCHAR(20) DEFAULT 'abcde',
310 d3 TIMESTAMP NOT NULL DEFAULT current_timestamp ON UPDATE current_timestamp);
311 --disable_info
312 CHECKSUM TABLE big;
313 BEGIN;
314 INSERT INTO big(id, c1, c2, c3) SELECT @i:=@i+1, c1, c2, c3 FROM big;
315 INSERT INTO big(id, c1, c2, c3) SELECT @i:=@i+1, c1, c2, c3 FROM big;
316 CHECKSUM TABLE big;
317 connection analyze;
318 ANALYZE TABLE big;
319 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
320 WHERE name = 'test/big';
321 connection default;
322 ROLLBACK;
323 CHECKSUM TABLE big;
324 connection analyze;
325 ANALYZE TABLE big;
326 SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
327 WHERE name = 'test/big';
328 connection default;
329
330 --source include/wait_all_purged.inc
331 DROP TABLE t1,t2,t3,t4,big;
332
333 # MDEV-14837 Duplicate primary keys are allowed after ADD COLUMN / UPDATE
334 eval CREATE TABLE t1 (a VARCHAR(1) PRIMARY KEY) $engine;
335 INSERT INTO t1 SET a='a';
336 ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 0;
337 UPDATE t1 SET b = 1;
338 --error ER_DUP_ENTRY
339 INSERT INTO t1 SET a='a';
340 SELECT * FROM t1;
341 DROP TABLE t1;
342
343 # MDEV-16065 Assertion failed in btr_pcur_restore_position_func on UPDATE
344 eval CREATE TABLE t1 (a INT, b VARCHAR(8), PRIMARY KEY(b,a)) $engine;
345 INSERT INTO t1 VALUES (1,'foo');
346 ALTER TABLE t1 ADD COLUMN c INT;
347 UPDATE t1 SET c = 1;
348 UPDATE t1 SET c = 2;
349 DROP TABLE t1;
350
351 # MDEV-16830 Crash in ALTER TABLE DROP FOREIGN KEY
352 eval CREATE TABLE t1 (a INT PRIMARY KEY) $engine;
353 eval CREATE TABLE t2 (b INT PRIMARY KEY, FOREIGN KEY(b) REFERENCES t1(a))
354 $engine;
355 INSERT INTO t1 SET a=1;
356 INSERT INTO t2 SET b=1;
357 ALTER TABLE t2 ADD COLUMN a INT, DROP FOREIGN KEY t2_ibfk_1;
358 ALTER TABLE t2 ADD INDEX(a);
359 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
360 ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a),
361 ALGORITHM=INSTANT;
362 SET foreign_key_checks=0;
363 ALTER TABLE t1 ADD COLUMN b INT, ADD FOREIGN KEY(a) REFERENCES t2(a),
364 ALGORITHM=INSTANT;
365 ALTER TABLE t2 ADD CONSTRAINT fk FOREIGN KEY(b) REFERENCES t1(a),
366 ALGORITHM=INSTANT;
367 SET foreign_key_checks=1;
368 ALTER TABLE t2 COMMENT 'domestic keys only', DROP FOREIGN KEY fk;
369 ALTER TABLE t1 DROP FOREIGN KEY t1_ibfk_1;
370 ALTER TABLE t1 ADD COLUMN big BLOB NOT NULL
371 DEFAULT REPEAT('a', @@GLOBAL.innodb_page_size * .75);
372 CHECK TABLE t2, t1;
373 DROP TABLE t2, t1;
374
375 #
376 # MDEV-17483 Insert on delete-marked record can wrongly inherit old values
377 # for instantly added column
378 #
379
380 eval CREATE TABLE t1 (a INT PRIMARY KEY) $engine;
381 INSERT INTO t1 SET a = 1;
382 ALTER TABLE t1 ADD COLUMN b TEXT;
383 BEGIN;
384 UPDATE t1 SET b = REPEAT('1', 32768);
385 UPDATE t1 SET a = 2;
386 INSERT INTO t1 SET a = 1;
387 SELECT a,LENGTH(b) FROM t1;
388 DELETE FROM t1;
389 COMMIT;
390 --source include/wait_all_purged.inc
391 DROP TABLE t1;
392
393 #
394 # MDEV-20066 Wrong value on instantly added column after DELETE and UPDATE
395 #
396
397 eval CREATE TABLE t1(a INT PRIMARY KEY) $engine;
398 INSERT INTO t1 VALUES (7);
399
400 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 0;
401 ALTER TABLE t1 ADD INDEX (c);
402
403 BEGIN;
404 DELETE FROM t1;
405 INSERT INTO t1 VALUES (4,0),(7,77);
406 COMMIT;
407 BEGIN;
408 DELETE FROM t1 WHERE a=7;
409 UPDATE t1 SET a=7;
410 COMMIT;
411 SELECT * FROM t1 FORCE INDEX(PRIMARY);
412 SELECT * FROM t1 FORCE INDEX(c);
413 DELETE FROM t1;
414 CHECK TABLE t1;
415 DROP TABLE t1;
416
417 # MDEV-21172 Memory leak during ADD PRIMARY KEY
418
419 SET innodb_strict_mode = ON;
420 eval CREATE TABLE t1 (a INT, b VARCHAR(500), c TEXT, UNIQUE(a,b)) $engine;
421 ALTER TABLE t1 ADD d TEXT;
422 --error 0,ER_TOO_BIG_ROWSIZE
423 ALTER TABLE t1 ADD PRIMARY KEY (b,a);
424 # Exploit MDEV-17468 to force the table definition to be reloaded
425 ALTER TABLE t1 ADD va INT AS (a) VIRTUAL;
426 DROP TABLE t1;
427 SET innodb_strict_mode = OFF;
428
429 # MDEV-20950 innodb_instant_alter_column_allowed
430 eval CREATE TABLE t1 (a INT PRIMARY KEY) $engine;
431 INSERT INTO t1 SET a=42;
432 SET GLOBAL innodb_instant_alter_column_allowed = never;
433 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
434 ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
435 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
436 ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT;
437 SET GLOBAL innodb_instant_alter_column_allowed = never;
438 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
439 ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
440 --enable_info
441 ALTER TABLE t1 MODIFY a INT DEFAULT 0;
442 --disable_info
443 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
444 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
445 ALTER TABLE t1 DROP b, ALGORITHM=INSTANT;
446 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
447 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
448 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
449 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
450 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
451 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
452 ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT;
453 SET GLOBAL innodb_instant_alter_column_allowed = add_drop_reorder;
454 ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT;
455 SET GLOBAL innodb_instant_alter_column_allowed = add_last;
456 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
457 ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT;
458 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
459 ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT;
460 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
461 ALTER TABLE t1 MODIFY a INT DEFAULT 0, ALGORITHM=INSTANT;
462 --enable_info
463 ALTER TABLE t1 MODIFY a INT DEFAULT 0;
464 --disable_info
465 ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT;
466 DROP TABLE t1;
467 SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT;
468
469 # MDEV-15562 Instant DROP/ADD/reorder columns
470
471 eval CREATE TABLE t1 (a INT, b INT UNIQUE) $engine;
472 INSERT INTO t1 (a) VALUES (NULL), (NULL);
473 ALTER TABLE t1 DROP a, ADD COLUMN a INT;
474 DELETE FROM t1;
475 BEGIN;INSERT INTO t1 SET a=NULL;ROLLBACK;
476 DELETE FROM t1;
477 DROP TABLE t1;
478
479 eval CREATE TABLE t1 (a INT PRIMARY KEY, t VARCHAR(33101) NOT NULL) $engine;
480 INSERT INTO t1 VALUES(347,'');
481 ALTER TABLE t1 DROP COLUMN t, ALGORITHM=INSTANT;
482 SELECT * FROM t1;
483 DROP TABLE t1;
484
485 eval CREATE TABLE t1 (a INT) $engine;
486 INSERT INTO t1() VALUES();
487 ALTER TABLE t1 ADD COLUMN b INT FIRST, ADD COLUMN c INT AFTER b;
488 SELECT * FROM t1;
489 DROP TABLE t1;
490
491 eval CREATE TABLE t1 (t TEXT) $engine;
492 SET @t=REPEAT('x',@@innodb_page_size / 2);
493 INSERT INTO t1 VALUES (@t),(@t),(@t),(@t),(@t),(@t),(NULL),(@t),(@t),(@t),(@t);
494 ALTER TABLE t1 ADD COLUMN a INT FIRST;
495 UPDATE t1 SET a = 0;
496 DROP TABLE t1;
497
498 eval CREATE TABLE t1 (t TEXT) $engine;
499 INSERT INTO t1 SET t = @x;
500 ALTER TABLE t1 DROP COLUMN t, ADD COLUMN i INT NOT NULL DEFAULT 1;
501 ALTER TABLE t1 ADD COLUMN t TEXT;
502 SELECT * FROM t1;
503 DROP TABLE t1;
504
505 #
506 # MDEV-17735 Assertion failure in row_parse_int() on first ADD/DROP COLUMN
507 # when an AUTO_INCREMENT column is not in PRIMARY KEY
508 #
509 eval CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, KEY(a)) $engine;
510 INSERT INTO t1 SET a=NULL;
511 ALTER TABLE t1 DROP COLUMN b;
512 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 42;
513 INSERT INTO t1 SET a=NULL;
514 UPDATE t1 SET a=a+2;
515 SELECT * FROM t1;
516 DROP TABLE t1;
517
518 eval CREATE TABLE t1 (i INT) $engine;
519 INSERT INTO t1 SET i=1;
520 ALTER TABLE t1 ADD COLUMN b BIT FIRST;
521 ALTER TABLE t1 ADD COLUMN v INT AS (i) VIRTUAL;
522 SELECT * FROM t1;
523 DROP TABLE t1;
524
525 # MDEV-17721 Corrupted data dictionary after instant DROP COLUMN
526 eval CREATE TABLE t1 (ts TIMESTAMP) $engine;
527 ALTER TABLE t1 ADD COLUMN f VARCHAR(8), ADD COLUMN dt DATETIME;
528 ALTER TABLE t1 ADD COLUMN b BIT, DROP COLUMN f, ADD COLUMN t TIME FIRST;
529 ALTER TABLE t1 ADD COLUMN ts2 TIMESTAMP;
530 DROP TABLE t1;
531
532 eval CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) $engine;
533 INSERT INTO t1 VALUES (4,4,4);
534 ALTER TABLE t1 DROP f1, DROP f2, ADD f4 INT, ADD f5 INT;
535 DELETE FROM t1;
536 ALTER TABLE t1 DROP COLUMN f4;
537 DROP TABLE t1;
538
539 eval CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) $engine;
540 ALTER TABLE t1 DROP f2, ADD COLUMN f4 INT;
541 ALTER TABLE t1 DROP f4;
542 ALTER TABLE t1 DROP f1;
543 DROP TABLE t1;
544
545 # MDEV-17901 Crash after instant DROP COLUMN of AUTO_INCREMENT column
546 eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) $engine;
547 ALTER TABLE t1 DROP COLUMN id;
548 INSERT INTO t1 () VALUES (),();
549 SELECT * FROM t1;
550 # Adding AUTO_INCREMENT column will always require rebuild.
551 ALTER TABLE t1 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD KEY(id);
552 SELECT * FROM t1;
553 DROP TABLE t1;
554
555 # MDEV-18149 Crash after DROP COLUMN of AUTO_INCREMENT column on nonempty table
556 eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) $engine;
557 INSERT INTO t1 SET f=NULL;
558 ALTER TABLE t1 DROP COLUMN id;
559 INSERT INTO t1 SET f=NULL;
560 SELECT * FROM t1;
561 DROP TABLE t1;
562
563 # MDEV-18076/MDEV-18077 Crash on AUTO_INCREMENT column after instant DROP
564 eval CREATE TABLE t1(f INT, k INT NOT NULL AUTO_INCREMENT, KEY(k)) $engine;
565 ALTER TABLE t1 DROP COLUMN f;
566 INSERT INTO t1 VALUES (1);
567 DROP TABLE t1;
568
569 eval CREATE TABLE t1(pk INT PRIMARY KEY, f INT, k INT AUTO_INCREMENT, KEY(k))
570 $engine;
571 ALTER TABLE t1 DROP COLUMN f;
572 INSERT INTO t1 (pk) VALUES (1);
573 DROP TABLE t1;
574
575 # MDEV-17763 Assertion `len == 20U' failed in rec_convert_dtuple_to_rec_comp
576 # upon DROP COLUMN
577 eval CREATE TABLE t1 (
578 pk INT PRIMARY KEY,
579 f1 INT,
580 f2 CHAR(255),
581 f3 BIGINT,
582 f4 INT,
583 f5 CHAR(255),
584 f6 CHAR(255),
585 f7 CHAR(255) NOT NULL,
586 f8 INT,
587 f9 CHAR(10)
588 ) $engine;
589
590 INSERT INTO t1 VALUES
591 (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'),
592 (2, 2, 'b', 2, 2, 'b', 'b', 'b', 2, 'b'),
593 (3, 3, 'c', 3, 3, 'c', 'c', 'c', 3, 'c'),
594 (4, 4, 'd', 4, 4, 'd', 'd', 'd', 4, 'd'),
595 (5, 5, 'e', 5, 5, 'e', 'e', 'e', 5, 'e'),
596 (6, 6, 'f', 6, 6, 'f', 'f', 'f', 6, 'f'),
597 (7, 7, 'g', 7, 7, 'g', 'g', 'g', 7, 'g'),
598 (8, 8, 'h', 8, 8, 'h', 'h', 'h', 8, 'h'),
599 (9, 9, 'i', 9, 9, 'i', 'i', 'i', 9, 'i'),
600 (10, 0, 'j', 0, 0, 'j', 'j', 'j', 0, 'j'),
601 (11, 1, 'k', 1, 1, 'k', 'k', 'k', 1, 'k'),
602 (12, 2, 'l', 2, 2, 'l', 'l', 'l', 2, 'l'),
603 (13, 3, 'm', 3, 3, 'm', 'm', 'm', 3, 'm'),
604 (14, 4, 'n', 4, 4, 'n', 'n', 'n', 4, 'n'),
605 (15, 5, 'o', 5, 5, 'o', 'o', 'o', 5, 'o');
606
607 DELETE FROM t1 WHERE pk=1;
608 --source include/wait_all_purged.inc
609 INSERT INTO t1 VALUES
610 (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a');
611 ALTER TABLE t1 DROP COLUMN f1;
612 DROP TABLE t1;
613
614 # MDEV-17820 Assertion failures on DROP COLUMN
615 eval CREATE TABLE t1 (
616 pk INT PRIMARY KEY,
617 f1 INT, f2 CHAR(32) NOT NULL,
618 f3 INT NOT NULL, f4 INT NOT NULL, f5 INT, f6 CHAR(32) NOT NULL,
619 f7 CHAR(32), f8 CHAR(32)
620 ) $engine;
621
622 INSERT INTO t1 VALUES
623 (1,9,'',2,88,88,'','',''),(2,48,'',8,68,92,'','',''),
624 (3,41,'',56,84,37,'','',''),(4,NULL,'',6,6,NULL,'','',''),
625 (5,52,'',37,44,20,'','',''),(6,44,'',53,4,NULL,'','',''),
626 (7,24,'',54,8,54,'','',''),(8,80,'',3,52,20,'','',''),
627 (9,71,'',34,32,NULL,'','',''),(10,14,'',6,64,88,'','',''),
628 (11,48,'',8,25,42,'','',''),(12,16,'',8,7,NULL,'','',''),
629 (13,NULL,'',22,0,95,'','',''),(14,4,'',72,48,NULL,'','',''),
630 (15,4,'',5,64,2,'','',''),(16,NULL,'',9,40,30,'','',''),
631 (17,92,'',48,2,NULL,'','',''),(18,36,'',48,51,7,'','',''),
632 (19,NULL,'',80,96,NULL,'','',''),(20,96,'',9,80,NULL,'','',''),
633 (21,50,'',16,40,NULL,'','',''),(22,NULL,'',7,84,8,'','',''),
634 (23,28,'',93,80,NULL,'','',''),(24,31,'',40,38,NULL,'','',''),
635 (25,85,'',8,5,88,'','',''),(26,66,'',8,32,4,'','',''),
636 (51,52,'',6,92,15,'','',''),(52,77,'',24,24,28,'','',''),
637 (53,8,'',75,31,NULL,'','',''),(54,48,'',5,8,1,'','',''),
638 (55,90,'',56,12,5,'','',''),(56,92,'',4,9,88,'','',''),
639 (57,83,'',23,40,72,'','',''),(58,7,'',4,40,32,'','',''),
640 (59,28,'',2,3,32,'','',''),(60,16,'',80,4,NULL,'','',''),
641 (61,44,'',88,24,NULL,'','',''),(62,4,'',5,25,3,'','',''),
642 (63,NULL,'',7,24,76,'','',''),(64,0,'',13,40,73,'','',''),
643 (101,NULL,'',1,49,75,'','',''),(102,34,'',10,17,20,'','',''),
644 (103,8,'',2,2,NULL,'','',''),(104,12,'',44,48,52,'','',''),
645 (105,8,'',4,19,38,'','',''),(106,20,'',6,80,9,'','',''),
646 (107,72,'',72,16,56,'','',''),(108,76,'',98,24,21,'','',''),
647 (109,67,'',16,91,NULL,'','',''),(110,72,'',72,3,48,'','',''),
648 (151,8,'',3,86,NULL,'','',''),(152,NULL,'',52,72,0,'','',''),
649 (153,NULL,'',46,30,92,'','',''),(154,80,'',1,40,48,'','',''),
650 (155,24,'',68,68,8,'','',''),(156,85,'',85,72,60,'','',''),
651 (157,7,'',7,12,6,'','',''),(158,NULL,'',48,48,80,'','',''),
652 (159,12,'',0,36,0,'','',''),(160,2,'',6,52,NULL,'','',''),
653 (201,0,'',1,3,NULL,'','',''),(202,NULL,'',3,53,14,'','',''),
654 (203,84,'',6,20,NULL,'','',''),(204,38,'',25,13,88,'','',''),
655 (205,1,'',2,69,5,'','',''),(206,7,'',60,22,NULL,'','',''),
656 (207,NULL,'',5,4,NULL,'','',''),(251,7,'',0,4,40,'','',''),
657 (252,4,'',16,8,NULL,'','',''),(253,14,'',60,12,99,'','',''),
658 (254,84,'',68,16,5,'','',''),(255,3,'',70,36,61,'','',''),
659 (256,7,'',18,48,NULL,'','',''),(257,NULL,'',68,53,NULL,'','',''),
660 (258,29,'',52,16,64,'','',''),(259,NULL,'',80,92,40,'','',''),
661 (301,68,'',1,48,48,'','',''),(302,2,'',1,1,32,'','',''),
662 (303,44,'',60,96,16,'','',''),(304,32,'',52,64,32,'','',''),
663 (305,88,'',37,72,NULL,'','',''),(306,5,'',35,60,20,'','',''),
664 (307,35,'',4,48,NULL,'','',''),(308,4,'',92,44,80,'','',''),
665 (351,48,'',60,4,40,'','',''),(352,7,'',9,61,13,'','',''),
666 (353,0,'',5,93,53,'','',''),(354,7,'',1,20,NULL,'','',''),
667 (355,84,'',5,48,96,'','',''),(356,NULL,'',39,92,36,'','',''),
668 (357,88,'',9,76,44,'','',''),(358,66,'',34,67,80,'','',''),
669 (359,8,'',8,52,NULL,'','',''),(360,3,'',53,83,NULL,'','',''),
670 (361,23,'',44,9,48,'','',''),(362,4,'',0,54,48,'','',''),
671 (363,75,'',66,76,52,'','','');
672
673 ALTER TABLE t1 ADD COLUMN x VARCHAR(255) DEFAULT ' foobar ';
674 UPDATE t1 SET f1 = 0;
675 ALTER TABLE t1 DROP COLUMN x;
676 DROP TABLE t1;
677
678 eval CREATE TABLE t1 (f1 VARCHAR(1), f2 VARCHAR(2)) $engine;
679 ALTER TABLE t1 MODIFY f2 VARCHAR (8) FIRST;
680 DROP TABLE t1;
681
682 # MDEV-18035 Failing assertion on DELETE
683 eval CREATE TABLE t1 (a INT UNIQUE, b INT UNIQUE, PRIMARY KEY(a,b)) $engine;
684 ALTER TABLE t1 DROP PRIMARY KEY;
685 ALTER TABLE t1 CHANGE COLUMN a a INT;
686 DELETE FROM t1 WHERE a = NULL OR a IS NULL;
687 DROP TABLE t1;
688
689 # MDEV-18048 Failing assertion on ALTER
690 eval CREATE TABLE t1 (a INT, b INT, c INT NOT NULL, d INT,
691 e INT, f INT, g INT, h INT, j INT) $engine;
692 ALTER TABLE t1 MODIFY COLUMN c INT, MODIFY COLUMN a INT AFTER b;
693 DROP TABLE t1;
694
695 # MDEV-18649 Failing assertion on ALTER for ROW_FORMAT=REDUNDANT
696 eval CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) $engine;
697 INSERT INTO t1 VALUES (0,0);
698 ALTER TABLE t1 MODIFY a INT AFTER b;
699 # Exploit MDEV-17468 to force the table definition to be reloaded
700 ALTER TABLE t1 ADD COLUMN v INT AS (a) VIRTUAL;
701 ALTER TABLE t1 MODIFY b INT NOT NULL AFTER a;
702 DROP TABLE t1;
703
704 # MDEV-18033/MDEV-18034 Failing assertion on ALTER
705 eval CREATE TABLE t1 (a INT NOT NULL) $engine;
706 INSERT INTO t1 VALUES (1);
707 ALTER TABLE t1 ADD COLUMN b INT;
708 ALTER TABLE t1 MODIFY COLUMN a INT NULL;
709 DROP TABLE t1;
710
711 # MDEV-18160/MDEV-18162 Failing assertion on ALTER
712 eval CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT) $engine;
713 INSERT INTO t1 SET a=1;
714 ALTER TABLE t1 DROP c;
715 ALTER TABLE t1 DROP b, ADD v INT AS (a);
716 DROP TABLE t1;
717
718 eval CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT) $engine;
719 INSERT INTO t1 SET a=1;
720 ALTER TABLE t1 DROP c;
721 ALTER TABLE t1 DROP b, ADD v INT AS (a);
722 DROP TABLE t1;
723
724 # MDEV-18218 Assertion `0' failed in btr_page_reorganize_low upon DROP COLUMN
725 eval CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, b BLOB NOT NULL) $engine;
726 INSERT INTO t1 VALUES (1,10,REPEAT('foobar',2000));
727 ALTER TABLE t1 DROP COLUMN b;
728 INSERT INTO t1 VALUES (2,20);
729 # this evicts and reloads the table definition until MDEV-17468 is fixed
730 ALTER TABLE t1 ADD COLUMN vpk INT AS (pk);
731 # this would load wrong metadata from the previous DROP COLUMN b, causing a crash
732 ALTER TABLE t1 DROP COLUMN i;
733 DROP TABLE t1;
734
735 # MDEV-18315 Assertion instant.fields[i].col->same_format(*fields[i].col)
736 # failed in dict_index_t::instant_add_field
737 eval CREATE TABLE t1 (a INT, b INT) $engine;
738 INSERT INTO t1 VALUES (1,1);
739 ALTER TABLE t1 ADD f DATE AFTER a;
740 ALTER TABLE t1 DROP b, DROP f;
741 DROP TABLE t1;
742
743 # MDEV-18316 Assertion is_added() failed in dict_col_t::instant_value
744 eval CREATE TABLE t1 (a INT, b INT) $engine;
745 INSERT INTO t1 VALUES (1,1);
746 ALTER TABLE t1 ADD COLUMN f INT AFTER a;
747 ALTER TABLE t1 DROP b, DROP f;
748 DROP TABLE t1;
749
750 # MDEV-18579 Assertion !ctx->online || num_fts_index == 0
751 eval CREATE TABLE t1(t TEXT NOT NULL, FULLTEXT(t)) $engine;
752 ALTER TABLE t1 MODIFY COLUMN t TEXT;
753 DROP TABLE t1;
754
755 # MDEV-18598 Assertions and wrong results after MDEV-15563 extending INT
756 eval CREATE TABLE t1 (f TINYINT, g SMALLINT UNSIGNED) $engine;
757 INSERT INTO t1 VALUES(127,6502),(-128,33101);
758 --enable_info
759 ALTER TABLE t1 MODIFY f SMALLINT DEFAULT 12345,
760 MODIFY g BIGINT UNSIGNED DEFAULT 1234567;
761 --disable_info
762 SELECT * FROM t1;
763 DROP TABLE t1;
764
765 eval CREATE TABLE t1 (f BIT(8)) $engine;
766 INSERT INTO t1 VALUES (b'10000000'),(b'00000001');
767 --enable_info
768 ALTER TABLE t1 MODIFY f BIT(16);
769 --disable_info
770 INSERT INTO t1 VALUES (b'1000000010101111'),(b'10000000');
771 SELECT HEX(f) FROM t1;
772 --error ER_WARN_DATA_OUT_OF_RANGE
773 ALTER TABLE t1 MODIFY f SMALLINT;
774 --enable_info
775 ALTER TABLE t1 MODIFY f SMALLINT UNSIGNED;
776 --disable_info
777 SELECT * FROM t1;
778 --error ER_DATA_TOO_LONG
779 ALTER TABLE t1 MODIFY f BIT;
780 --error ER_DATA_TOO_LONG
781 ALTER TABLE t1 MODIFY f BIT(15);
782 DELETE FROM t1 LIMIT 3;
783 --enable_info
784 ALTER TABLE t1 MODIFY f BIT(15);
785 ALTER TABLE t1 MODIFY f BIT(8);
786 --disable_info
787 SELECT HEX(f) FROM t1;
788 DROP TABLE t1;
789
790 eval CREATE TABLE t1 (b BIT NOT NULL) $engine DEFAULT CHARSET utf16;
791 INSERT INTO t1 SET b=b'1';
792 --enable_info
793 ALTER TABLE t1 CHANGE b c BIT NOT NULL;
794 --disable_info
795 SELECT HEX(c) FROM t1;
796 DROP TABLE t1;
797
798 eval CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') $engine;
799 INSERT INTO t1() VALUES();
800 --enable_info
801 ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42;
802 ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802,
803 MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory',
804 ADD d DATETIME;
805 --disable_info
806 INSERT INTO t1() VALUES();
807 INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now());
808 SELECT * FROM t1;
809 DROP TABLE t1;
810
811 eval CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) $engine;
812 --disable_cursor_protocol
813 SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
814 WHERE name = 'test/t1';
815 --enable_cursor_protocol
816 INSERT INTO t1 VALUES (-42, -123456);
817 --enable_info
818 ALTER TABLE t1 CHANGE t s SMALLINT;
819 --disable_cursor_protocol
820 SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
821 WHERE name = 'test/t1';
822 --enable_cursor_protocol
823 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
824 ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT;
825 ALTER TABLE t1 CHANGE m i INT;
826 --disable_cursor_protocol
827 SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
828 WHERE name = 'test/t1';
829 --enable_cursor_protocol
830 --disable_info
831 SELECT @table_id1 = @table_id2, @table_id2 = @table_id3;
832 INSERT IGNORE INTO t1 VALUES (0, -123456);
833 REPLACE INTO t1 VALUES(-42, 123456);
834 INSERT IGNORE INTO t1 VALUES(32768, 2147483648);
835 SELECT * FROM t1;
836 DROP TABLE t1;
837
838 eval CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) $engine;
839 INSERT INTO t1 (c) VALUES(1),(2),(3);
840 --enable_info
841 ALTER TABLE t1 MODIFY c BIGINT;
842 --disable_info
843 UPDATE t1 SET b=1 WHERE c=2;
844 UPDATE t1 SET c=4 WHERE a=3;
845 UPDATE t1 SET b=2 WHERE c>3;
846 --error ER_DUP_ENTRY
847 UPDATE t1 SET c=c+1;
848 SELECT * FROM t1;
849 DROP TABLE t1;
850
851 # MDEV-18719 Assertion (c.prtype ^ o->prtype) & ... failed on ALTER TABLE
852 eval CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1)) $engine;
853 INSERT INTO t1 VALUES(1,'a');
854 ALTER TABLE t1 MODIFY b VARCHAR(256), ADD COLUMN c INT;
855 INSERT INTO t1 VALUES(2,'bah',3);
856 SELECT * FROM t1;
857 DROP TABLE t1;
858
859 # MDEV-21088 Table cannot be loaded after instant ADD/DROP COLUMN
860 eval CREATE TABLE t1(a CHAR(5) CHARACTER SET utf8 PRIMARY KEY) $engine;
861 INSERT INTO t1 VALUES('barf');
862 ALTER TABLE t1 ADD b INT FIRST, ALGORITHM=INSTANT;
863 # this evicts and reloads the table definition until MDEV-17468 is fixed
864 ALTER TABLE t1 ADD vb INT AS (b);
865 SELECT * FROM t1;
866 DROP TABLE t1;
867
868 # MDEV-21148 Assertion failure index->n_core_fields + n_add >= index->n_fields
869 eval CREATE TABLE t1 (a INT, b TEXT, PRIMARY KEY(b(9))) $engine;
870 ALTER TABLE t1 ADD COLUMN c TEXT FIRST;
871 ALTER TABLE t1 ADD COLUMN d TEXT GENERATED ALWAYS AS (SUBSTR(b,1,499)) FIRST;
872 DROP TABLE t1;
873
874 eval CREATE TABLE t1(a CHAR(5), b INT, c CHAR(1), d CHAR(1), PRIMARY KEY(a,b))
875 DEFAULT CHARACTER SET utf8 $engine;
876 INSERT INTO t1 SET a='fubar',b=42;
877 ALTER TABLE t1 DROP c, DROP d, ALGORITHM=INSTANT;
878 # this evicts and reloads the table definition until MDEV-17468 is fixed
879 ALTER TABLE t1 ADD vb INT AS (b);
880 SELECT * FROM t1;
881 DROP TABLE t1;
882
883 eval CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) $engine;
884 ALTER TABLE t1 MODIFY b INT FIRST;
885 DROP TABLE t1;
886
887 # MDEV-21744 Assertion `!rec_offs_nth_sql_null(offsets, n)' failed
888 eval CREATE TABLE t1 (pk INT PRIMARY KEY) $engine;
889 INSERT INTO t1 SET pk=1;
890 ALTER TABLE t1 ADD COLUMN b INT;
891 BEGIN;
892 UPDATE t1 SET pk=2;
893 UPDATE t1 SET pk=1;
894 connection analyze;
895 SELECT * FROM t1;
896 connection default;
897 DROP TABLE t1;
898
899 dec $format;
900 let $redundant_4k= 0;
901 }
902 disconnect analyze;
903
904 --echo #
905 --echo # MDEV-22465: DROP COLUMN is wrongly claimed to be ALGORITHM=INSTANT
906 --echo #
907
908 CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB;
909 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
910 ALTER TABLE t1 DROP b, ALGORITHM=INSTANT;
911 ALTER TABLE t1 DROP b, ALGORITHM=NOCOPY;
912 DROP TABLE t1;
913
914 SELECT variable_value-@old_instant instants
915 FROM information_schema.global_status
916 WHERE variable_name = 'innodb_instant_alter_column';
917 SET GLOBAL innodb_instant_alter_column_allowed = @saved_allowance;
918
919 --echo #
920 --echo # MDEV-18266: Changing an index comment unnecessarily rebuilds index
921 --echo #
922
923 CREATE TABLE t1(a INT, b INT) ENGINE=INNODB;
924 CREATE INDEX i1 ON t1(a) COMMENT 'comment1';
925 ALTER TABLE t1 DROP INDEX i1, ADD INDEX i1(a) COMMENT 'comment2', ALGORITHM=INSTANT;
926 SHOW CREATE TABLE t1;
927 DROP TABLE t1;
928
929 --echo #
930 --echo # MDEV-25057 Assertion `n_fields < dtuple_get_n_fields(entry)'
931 --echo # failed in dtuple_convert_big_rec
932 --echo #
933 CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT,
934 a CHAR(255) NOT NULL,
935 b CHAR(255) NOT NULL, c INT) ENGINE=InnoDB CHARSET utf32;
936 ALTER TABLE t1 DROP c;
937 INSERT INTO t1(a, b) SELECT '', '' FROM seq_1_to_16;
938 SELECT COUNT(*) FROM t1;
939 # Cleanup
940 DROP TABLE t1;
941
942 --echo #
943 --echo # MDEV-25630 Rollback of instant operation adds wrong
944 --echo # column to secondary index
945 --echo #
946 CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, f4 INT,
947 PRIMARY KEY(f1, f4),
948 KEY(f2))ENGINE=InnoDB;
949 CREATE TABLE t2 (f1 INT, f2 INT, PRIMARY KEY(f1),
950 FOREIGN KEY fk (f2) REFERENCES t2(f1)
951 )ENGINE=InnoDB;
952
953 ALTER TABLE t1 ADD f5 INT;
954 SET FOREIGN_KEY_CHECKS=0;
955 --error ER_FK_FAIL_ADD_SYSTEM
956 ALTER TABLE t1 DROP COLUMN f3, ADD FOREIGN KEY fk (f1)
957 REFERENCES x(x);
958 ALTER TABLE t1 DROP COLUMN f5;
959 SHOW CREATE TABLE t1;
960 DROP TABLE t1, t2;
961
962
963 --echo #
964 --echo # MDEV-28822 Table from older version requires table rebuild when adding column to table with multi-column index
965 --echo #
966
967 CREATE TABLE mdev28822_100427_innodb (
968 id int not null primary key,
969 msg varchar(10),
970 index(id, msg)
971 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
972 FLUSH TABLES;
973 remove_file $datadir/test/mdev28822_100427_innodb.frm;
974 copy_file std_data/mysql_upgrade/mdev28822_100427_innodb.frm $datadir/test/mdev28822_100427_innodb.frm;
975 ALTER TABLE mdev28822_100427_innodb ADD i1 INTEGER, ALGORITHM=INSTANT;
976 DROP TABLE mdev28822_100427_innodb;
977 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
978
979 --source include/test_db_charset_restore.inc