| 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 |