instant_alter_debug.test 13.4 KB
Newer Older
1 2 3
--source include/have_innodb.inc
--source include/have_debug.inc
--source include/have_debug_sync.inc
4
--source include/have_sequence.inc
5

6 7 8
SET @save_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency;
SET GLOBAL innodb_purge_rseg_truncate_frequency=1;

9 10 11 12
SET @old_instant=
(SELECT variable_value FROM information_schema.global_status
WHERE variable_name = 'innodb_instant_alter_column');

13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
CREATE TABLE t1 (
 pk INT AUTO_INCREMENT PRIMARY KEY,
 c1 INT,
 c2 VARCHAR(255),
 c3 VARCHAR(255),
 c4 INT,
 c5 INT,
 c6 INT,
 c7 VARCHAR(255),
 c8 TIMESTAMP NULL
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (NULL,1,NULL,'foo',NULL,1,NULL,NULL,'2011-11-11 00:00:00');
ALTER TABLE t1 ADD COLUMN f INT;
REPLACE INTO t1 (c7) VALUES ('bar');

CREATE TABLE t2 (i INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t2 VALUES (-1),(1);
ALTER TABLE t2 ADD COLUMN j INT;
BEGIN;
DELETE FROM t2;
ROLLBACK;
TRUNCATE TABLE t2;
INSERT INTO t2 VALUES (1,2);

CREATE TABLE t3 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t3 () VALUES ();
ALTER TABLE t3 ADD COLUMN f INT;
UPDATE t3 SET pk = DEFAULT;
SELECT * FROM t3;

CREATE TABLE t4 (pk INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t4 VALUES (0);
ALTER TABLE t4 ADD COLUMN b INT;
46
SELECT COUNT(*)>0 FROM INFORMATION_SCHEMA.COLUMNS
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
LEFT JOIN t4 ON (NUMERIC_SCALE = pk);
ALTER TABLE t4 ADD COLUMN c INT;

CREATE TABLE t5 (i INT, KEY(i)) ENGINE=InnoDB;
INSERT INTO t5 VALUES (-42);
ALTER TABLE t5 ADD UNIQUE ui(i);
ALTER TABLE t5 ADD COLUMN i2 INT, DROP INDEX i;

CREATE TABLE t6 (i INT NOT NULL) ENGINE=InnoDB;
INSERT INTO t6 VALUES (0);
ALTER TABLE t6 ADD COLUMN j INT;
TRUNCATE TABLE t6;
INSERT INTO t6 VALUES (1,2);

CREATE TABLE t7 (i INT) ENGINE=InnoDB;
INSERT INTO t7 VALUES (1),(2),(3),(4),(5);
ALTER TABLE t7 ADD t TEXT DEFAULT '';

CREATE TABLE t8 (i INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
INSERT INTO t8 VALUES (NULL);
ALTER TABLE t8 ADD c CHAR(3);
SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR dml';
--send
ALTER TABLE t8 FORCE;
71
connect (dml,localhost,root,,);
72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176
SET DEBUG_SYNC='now WAIT_FOR rebuilt';
BEGIN;
INSERT INTO t8 SET i=1;
UPDATE t8 SET i=ISNULL(i);
ROLLBACK;
SET DEBUG_SYNC='now SIGNAL dml';
connection default;
reap;
SET DEBUG_SYNC='RESET';

CREATE TABLE t9 (
 pk INT AUTO_INCREMENT PRIMARY KEY,
 c1 BIGINT UNSIGNED,
 c2 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
 c3 BIGINT,
 c4 VARCHAR(257) CHARACTER SET utf8,
 c5 TINYINT UNSIGNED,
 c6 TINYINT,
 c7 VARCHAR(257) CHARACTER SET latin1,
 c8 VARCHAR(257) CHARACTER SET binary
) ENGINE=InnoDB;
INSERT INTO t9 () VALUES ();
ALTER TABLE t9 ADD COLUMN IF NOT EXISTS t TIMESTAMP NULL KEY;
SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR dml';
--send
OPTIMIZE TABLE t9;
connection dml;
SET DEBUG_SYNC='now WAIT_FOR rebuilt';
BEGIN;
INSERT INTO t9 () VALUES (),();
UPDATE t9 SET t=current_timestamp();
ROLLBACK;
SET DEBUG_SYNC='now SIGNAL dml';
disconnect dml;
connection default;
reap;
SET DEBUG_SYNC='RESET';

CREATE TABLE t10 (pk INT DEFAULT 0 KEY) ENGINE=InnoDB;
INSERT INTO t10 (pk) VALUES (1);
ALTER TABLE t10 ADD c INT;
TRUNCATE TABLE t10;
INSERT INTO t10 VALUES (1,1),(2,2);
ALTER TABLE t10 FORCE;

CREATE TABLE t11 (
 c01 enum('a','b'),
 c02 bit,
 c03 blob,
 c04 enum('c','d'),
 c05 blob,
 c06 decimal,
 c07 char(1),
 c08 int,
 c09 char(1),
 c10 set('e','f'),
 c11 char(1),
 c12 float,
 c13 bit,
 c14 char(1),
 c15 int,
 c16 float,
 c17 decimal,
 c18 char(1) CHARACTER SET utf8 not null default '',
 c19 float,
 c20 set('g','h'),
 c21 char(1),
 c22 int,
 c23 int,
 c24 int,
 c25 set('i','j'),
 c26 decimal,
 c27 float,
 c28 char(1),
 c29 int,
 c30 enum('k','l'),
 c31 decimal,
 c32 char(1),
 c33 decimal,
 c34 bit,
 c35 enum('m','n'),
 c36 set('o','p'),
 c37 enum('q','r'),
 c38 blob,
 c39 decimal,
 c40 blob not null default '',
 c41 char(1),
 c42 int,
 c43 float,
 c44 float,
 c45 enum('s','t'),
 c46 decimal,
 c47 set('u','v'),
 c48 enum('w','x'),
 c49 set('y','z'),
 c50 float
) ENGINE=InnoDB;
INSERT INTO t11 () VALUES  ();
ALTER TABLE t11 ADD COLUMN f INT;
INSERT INTO t11 () VALUES ();
UPDATE t11 SET c22 = 1;

--source include/wait_all_purged.inc
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;

177 178 179 180 181 182 183 184 185 186 187 188
--echo #
--echo # MDEV-15060 Assertion in row_log_table_apply_op after instant ADD
--echo # when the table is emptied during subsequent ALTER TABLE
--echo #

CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (NULL);
ALTER TABLE t1 ADD COLUMN b INT NOT NULL;
connect stop_purge,localhost,root;
START TRANSACTION WITH CONSISTENT SNAPSHOT;
connect ddl,localhost,root,,test;
DELETE FROM t1;
189
INSERT INTO t1 VALUES(1, 2);
190 191 192 193 194 195 196 197
SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
send ALTER TABLE t1 FORCE;
connection default;
SET DEBUG_SYNC='now WAIT_FOR copied';

BEGIN;
INSERT INTO t1 SET b=1;
ROLLBACK;
198 199 200
connection stop_purge;
COMMIT;
connection default;
201 202

# Wait for purge to empty the table.
Marko Mäkelä's avatar
Marko Mäkelä committed
203 204 205
let $wait_all_purged=2;
--source include/wait_all_purged.inc
let $wait_all_purged=0;
206 207

SET DEBUG_SYNC='now SIGNAL logged';
208 209 210
connection ddl;
reap;
connection default;
211 212 213
DROP TABLE t1;
SET DEBUG_SYNC='RESET';

214 215 216 217 218 219 220
--echo #
--echo # MDEV-16131 Assertion failed in dict_index_t::instant_field_value()
--echo #
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 SET a=0;
ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 2, ADD COLUMN c INT;

221 222 223 224 225 226 227 228 229
DELIMITER $$;
BEGIN NOT ATOMIC
  DECLARE c TEXT DEFAULT(SELECT CONCAT('ALTER TABLE t1 ADD (c',
                                       GROUP_CONCAT(seq SEPARATOR ' INT, c'), ' INT), ALGORITHM=INSTANT;') FROM seq_1_to_130);
  EXECUTE IMMEDIATE c;
END;
$$
DELIMITER ;$$

230 231 232 233 234 235 236 237 238 239
connection stop_purge;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

connection default;
DELETE FROM t1;

connection ddl;
SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
send ALTER TABLE t1 FORCE;

240 241
connection stop_purge;
COMMIT;
242 243 244 245 246

connection default;
SET DEBUG_SYNC = 'now WAIT_FOR copied';
let $wait_all_purged = 1;
--source include/wait_all_purged.inc
247
let $wait_all_purged = 0;
248 249 250 251 252 253
INSERT INTO t1 SET a=1;
INSERT INTO t1 SET a=2,b=3,c=4;
SET DEBUG_SYNC = 'now SIGNAL logged';

connection ddl;
reap;
254

255 256
connection default;
SET DEBUG_SYNC = RESET;
Marko Mäkelä's avatar
Marko Mäkelä committed
257
SELECT a, b, c FROM t1;
258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274
ALTER TABLE t1 DROP b, ALGORITHM=INSTANT;
connection stop_purge;
START TRANSACTION WITH CONSISTENT SNAPSHOT;

connection default;
DELETE FROM t1;

connection ddl;
SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
send ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 2 AFTER a, FORCE;

disconnect stop_purge;

connection default;
SET DEBUG_SYNC = 'now WAIT_FOR copied';
let $wait_all_purged = 1;
--source include/wait_all_purged.inc
275
let $wait_all_purged = 0;
276 277 278 279 280 281 282 283
INSERT INTO t1 SET a=1;
INSERT INTO t1 SET a=2,c=4;
SET DEBUG_SYNC = 'now SIGNAL logged';

connection ddl;
reap;
UPDATE t1 SET b = b + 1 WHERE a = 2;

284 285
connection default;
SET DEBUG_SYNC = RESET;
286
SELECT a, b, c FROM t1;
287 288 289 290 291 292 293 294 295 296

--echo #
--echo # MDEV-15872 Crash in online ALTER TABLE...ADD PRIMARY KEY
--echo # after instant ADD COLUMN ... NULL
--echo #
ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INSTANT;
UPDATE t1 SET d=1;

connection ddl;
SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
Marko Mäkelä's avatar
Marko Mäkelä committed
297
send ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a,d);
298 299 300 301 302 303 304 305 306 307 308

connection default;
SET DEBUG_SYNC = 'now WAIT_FOR copied';
BEGIN;
INSERT INTO t1 SET a=3;
ROLLBACK;
SET DEBUG_SYNC = 'now SIGNAL logged';

connection ddl;
--error ER_INVALID_USE_OF_NULL
reap;
309 310 311 312
disconnect ddl;

connection default;
SET DEBUG_SYNC = RESET;
313
SELECT a, b, c, d FROM t1;
314 315
DROP TABLE t1;

316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339
--echo #
--echo # MDEV-19916 Corruption after instant ADD/DROP and shrinking the tree
--echo #
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;

# Create an index tree with 2 levels of node pointer pages.

SET @old_limit = @@innodb_limit_optimistic_insert_debug;
SET GLOBAL innodb_limit_optimistic_insert_debug = 2;
INSERT INTO t1 VALUES (1),(5),(4),(3),(2);
SET GLOBAL innodb_limit_optimistic_insert_debug = @old_limit;

ALTER TABLE t1 ADD COLUMN b INT, ALGORITHM=INSTANT;

SET @old_defragment = @@innodb_defragment;
SET GLOBAL innodb_defragment = 1;
OPTIMIZE TABLE t1;
SET GLOBAL innodb_defragment = @old_defragment;

# Exploit MDEV-17468 to force the table definition to be reloaded
ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL;
CHECK TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;
340

341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362
--echo #
--echo # MDEV-21045 AddressSanitizer: use-after-poison in mem_heap_dup / row_log_table_get_pk_col
--echo #
CREATE TABLE t1 (a TEXT) ENGINE = InnoDB ROW_FORMAT=REDUNDANT;
INSERT INTO t1 (a) VALUES ('foo');

ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0,algorithm=instant;

--connect (con2,localhost,root,,test)
SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL onlinealter WAIT_FOR update';
--send
ALTER TABLE t1 ADD PRIMARY KEY (b);

--connection default
SET DEBUG_SYNC='now WAIT_FOR onlinealter';
UPDATE t1 SET b = 1;
SET DEBUG_SYNC='now SIGNAL update';

--connection con2
--reap

--connection default
363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388
DROP TABLE t1;

--echo #
--echo # MDEV-21658 Error on online ADD PRIMARY KEY after instant DROP/reorder
--echo #

CREATE TABLE t1 (a INT, b INT, c INT, col INT) ENGINE=InnoDB;
INSERT INTO t1 () VALUES ();
ALTER TABLE t1 DROP b, DROP c, DROP col;
ALTER TABLE t1 ADD COLUMN col INT;
ALTER TABLE t1 DROP a, DROP col, ADD COLUMN b INT;

--connection con2
SET SQL_MODE= '';
SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR dml';
send ALTER TABLE t1 ADD PRIMARY KEY(b);

--connection default
SET DEBUG_SYNC = 'now WAIT_FOR scanned';
UPDATE t1 SET b = 1;
SET DEBUG_SYNC = 'now SIGNAL dml';
--connection con2
reap;
--connection default
SELECT * FROM t1;

389 390 391
SET DEBUG_SYNC='RESET';
--disconnect con2
DROP TABLE t1;
392

393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411
--echo #
--echo # MDEV-24653 Assertion block->page.id.page_no() == index->page failed
--echo # in innobase_add_instant_try()
--echo #

SET @saved_limit = @@GLOBAL.innodb_limit_optimistic_insert_debug;
SET GLOBAL innodb_limit_optimistic_insert_debug = 2;

CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3),(4);
ALTER TABLE t1 ADD COLUMN b INT;
DELETE FROM t1;
--source include/wait_all_purged.inc
ALTER TABLE t1 ADD COLUMN c INT;

SELECT * FROM t1;
DROP TABLE t1;
SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit;

412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427
--echo #
--echo # MDEV-24796 Assertion page_has_next... failed
--echo # in btr_pcur_store_position()
--echo #

CREATE TABLE t1 (c INT KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES(1),(2);
SET GLOBAL innodb_limit_optimistic_insert_debug=2;
ALTER TABLE t1 ADD COLUMN d INT;
DELETE FROM t1;
--source include/wait_all_purged.inc
SELECT * FROM t1 WHERE c<>1 ORDER BY c DESC;
DROP TABLE t1;

SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit;

428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465
--echo #
--echo # MDEV-24620 ASAN heap-buffer-overflow in btr_pcur_restore_position()
--echo #

CREATE TABLE t1 (a VARCHAR(1) PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
connect (stop_purge,localhost,root,,);
START TRANSACTION WITH CONSISTENT SNAPSHOT;

connection default;
ALTER TABLE t1 ADD c INT;
BEGIN;
DELETE FROM t1;

connect (dml,localhost,root,,test);
SET DEBUG_SYNC='row_mysql_handle_errors SIGNAL s1 WAIT_FOR s2';
send UPDATE t1 SET c=1;

connection default;
SET DEBUG_SYNC='now WAIT_FOR s1';
COMMIT;

connection stop_purge;
COMMIT;
disconnect stop_purge;

connection default;
--source include/wait_all_purged.inc
SET DEBUG_SYNC='now SIGNAL s2';

connection dml;
reap;
disconnect dml;

connection default;
SET DEBUG_SYNC=RESET;
DROP TABLE t1;

466 467
--echo # End of 10.3 tests

Marko Mäkelä's avatar
Marko Mäkelä committed
468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499
--echo #
--echo # MDEV-17899 Assertion failures on rollback of instant ADD/DROP
--echo # MDEV-18098 Crash after rollback of instant DROP COLUMN
--echo #

SET @save_dbug = @@SESSION.debug_dbug;
SET debug_dbug='+d,ib_commit_inplace_fail_1';
CREATE TABLE t1 (a int, b int) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,2);
--error ER_INTERNAL_ERROR
ALTER TABLE t1 DROP COLUMN b;
--error ER_INTERNAL_ERROR
ALTER TABLE t1 DROP COLUMN b;
--error ER_INTERNAL_ERROR
ALTER TABLE t1 ADD COLUMN c INT;
SELECT * FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a int, b int) ENGINE=InnoDB;
--error ER_INTERNAL_ERROR
ALTER TABLE t1 ADD COLUMN c INT;
BEGIN;
INSERT INTO t1 VALUES(1, 1);
ROLLBACK;
--error ER_INTERNAL_ERROR
ALTER TABLE t1 DROP COLUMN b;
INSERT INTO t1 values (1,1);
SELECT * FROM t1;
DROP TABLE t1;

SET debug_dbug = @save_dbug;

500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515
--echo #
--echo # MDEV-24512 Assertion failed in rec_is_metadata()
--echo #            in btr_discard_only_page_on_level()
--echo #

SET @save_limit= @@GLOBAL.innodb_limit_optimistic_insert_debug;
SET GLOBAL innodb_limit_optimistic_insert_debug=2;
CREATE TABLE t1 (c CHAR(1) UNIQUE) ENGINE=InnoDB;

ALTER TABLE t1 ADD c2 INT NOT NULL DEFAULT 0 FIRST;
--error ER_DUP_ENTRY
INSERT INTO t1 (c) VALUES ('x'),('d'),('r'),('f'),('y'),('u'),('m'),('d');
SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit;
SELECT * FROM t1;
DROP TABLE t1;

516 517 518 519 520 521 522
--echo #
--echo # MDEV-25236 Online log apply fails for ROW_FORMAT=REDUNDANT tables
--echo #

CREATE TABLE t1
(a INT NOT NULL, b INT, c INT, d INT, e INT, f INT, g INT, h INT, i TEXT)
ENGINE=InnoDB;
523
INSERT INTO t1 VALUES(1, 2, 3, 4, 5, 6, 7, 8, "test");
524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541
ALTER TABLE t1 MODIFY a INT NULL;

SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL alter WAIT_FOR go';
send ALTER TABLE t1 ADD PRIMARY KEY (a);
connect(con1,localhost,root,,);
set DEBUG_SYNC='now WAIT_FOR alter';
BEGIN;
INSERT INTO t1 SET a=0, i=REPEAT('1', 10000);
ROLLBACK;
set DEBUG_SYNC='now SIGNAL go';
connection default;
reap;

disconnect con1;
SELECT * FROM t1;
DROP TABLE t1;
SET DEBUG_SYNC=RESET;

Marko Mäkelä's avatar
Marko Mäkelä committed
542 543
--echo # End of 10.4 tests

544
SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency;
545 546 547 548

SELECT variable_value-@old_instant instants
FROM information_schema.global_status
WHERE variable_name = 'innodb_instant_alter_column';