1# 2# Bug#20015132 ALTER TABLE FAILS TO CHECK IF TABLE IS CORRUPTED 3# 4CREATE TABLE t1(c1 INT PRIMARY KEY, c2 CHAR(1), c3 INT UNSIGNED) ENGINE=InnoDB; 5ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3); 6SET DEBUG='+d,dict_set_index_corrupted'; 7CHECK TABLE t1; 8Table Op Msg_type Msg_text 9test.t1 check Warning InnoDB: The B-tree of index c2 is corrupted. 10test.t1 check Warning InnoDB: The B-tree of index c3 is corrupted. 11test.t1 check error Corrupt 12# restart 13CHECK TABLE t1; 14Table Op Msg_type Msg_text 15test.t1 check Warning InnoDB: Index c2 is marked as corrupted 16test.t1 check Warning InnoDB: Index c3 is marked as corrupted 17test.t1 check error Corrupt 18ALTER TABLE t1 DROP INDEX c2; 19CHECK TABLE t1; 20Table Op Msg_type Msg_text 21test.t1 check Warning InnoDB: Index c3 is marked as corrupted 22test.t1 check error Corrupt 23ALTER TABLE t1 ADD INDEX (c2,c3); 24ERROR 42000: Can't open table 25ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL; 26CHECK TABLE t1; 27Table Op Msg_type Msg_text 28test.t1 check status OK 29ALTER TABLE t1 ADD INDEX (c2,c3); 30DROP TABLE t1; 31# 32# Bug #14669848 CRASH DURING ALTER MAKES ORIGINAL TABLE INACCESSIBLE 33# 34# -- Scenario 1: 35# Crash the server in ha_innobase::commit_inplace_alter_table() 36# just after committing the dictionary changes. 37CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=innodb; 38INSERT INTO t1 VALUES (1,2),(3,4); 39SET DEBUG='d,innodb_alter_commit_crash_after_commit'; 40ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); 41ERROR HY000: Lost connection to MySQL server during query 42# Restart mysqld after the crash and reconnect. 43# restart 44# Files in datadir after manual recovery. 45t1.ibd 46SHOW TABLES; 47Tables_in_test 48t1 49SHOW CREATE TABLE t1; 50Table Create Table 51t1 CREATE TABLE `t1` ( 52 `f1` int NOT NULL, 53 `f2` int NOT NULL 54) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 55INSERT INTO t1 VALUES (5,6),(7,8); 56SELECT * FROM t1; 57f1 f2 581 2 593 4 605 6 617 8 62DROP TABLE t1; 63CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB; 64ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); 65DROP TABLE t1; 66# -- Scenario 2: 67# Crash the server in ha_innobase::commit_inplace_alter_table() 68# just before committing the dictionary changes, but after 69# writing the MLOG_FILE_RENAME records. As the mini-transaction 70# is not committed, the renames will not be replayed. 71CREATE TABLE t2 (f1 int not null, f2 int not null) ENGINE=InnoDB; 72INSERT INTO t2 VALUES (1,2),(3,4); 73SET DEBUG='d,innodb_alter_commit_crash_before_commit'; 74ALTER TABLE t2 ADD PRIMARY KEY (f2, f1); 75ERROR HY000: Lost connection to MySQL server during query 76# Startup the server after the crash 77# restart 78SHOW TABLES; 79Tables_in_test 80t2 81INSERT INTO t2 VALUES (5,6),(7,8); 82SELECT * from t2; 83f1 f2 841 2 853 4 865 6 877 8 88SHOW CREATE TABLE t2; 89Table Create Table 90t2 CREATE TABLE `t2` ( 91 `f1` int NOT NULL, 92 `f2` int NOT NULL 93) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 94DROP TABLE t2; 95CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB; 96ALTER TABLE t2 ADD PRIMARY KEY (f2, f1); 97DROP TABLE t2; 98# ------------------------- 99# End of Testing Scenario 2 100# ------------------------- 101# 102# Bug#19330255 CRASH DURING ALTER TABLE LEADS TO 103# DATA DICTIONARY INCONSISTENCY 104# 105CREATE TABLE t1(a int PRIMARY KEY, b varchar(150), c int NOT NULL); 106INSERT INTO t1 SET a=1,c=2; 107SET DEBUG='d,innodb_alter_commit_crash_after_commit'; 108ALTER TABLE t1 ADD INDEX (b), CHANGE c d int; 109ERROR HY000: Lost connection to MySQL server during query 110# Restart mysqld after the crash and reconnect. 111# restart 112# Files in datadir after manual recovery. 113t1.ibd 114SHOW TABLES; 115Tables_in_test 116t1 117SHOW CREATE TABLE t1; 118Table Create Table 119t1 CREATE TABLE `t1` ( 120 `a` int NOT NULL, 121 `b` varchar(150) DEFAULT NULL, 122 `c` int NOT NULL, 123 PRIMARY KEY (`a`) 124) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 125SELECT * FROM t1; 126a b c 1271 NULL 2 128DROP TABLE t1; 129