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; 5SET DEBUG='+d,ib_create_table_fail_too_many_trx'; 6ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3); 7ERROR HY000: Too many active concurrent transactions 8SET DEBUG='-d,ib_create_table_fail_too_many_trx'; 9ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3); 10SET DEBUG='+d,dict_set_index_corrupted'; 11CHECK TABLE t1; 12Table Op Msg_type Msg_text 13test.t1 check Warning InnoDB: The B-tree of index c2 is corrupted. 14test.t1 check Warning InnoDB: The B-tree of index c3 is corrupted. 15test.t1 check error Corrupt 16# restart 17CHECK TABLE t1; 18Table Op Msg_type Msg_text 19test.t1 check Warning InnoDB: Index c2 is marked as corrupted 20test.t1 check Warning InnoDB: Index c3 is marked as corrupted 21test.t1 check error Corrupt 22ALTER TABLE t1 DROP INDEX c2; 23CHECK TABLE t1; 24Table Op Msg_type Msg_text 25test.t1 check Warning InnoDB: Index c3 is marked as corrupted 26test.t1 check error Corrupt 27ALTER TABLE t1 ADD INDEX (c2,c3); 28ERROR 42000: Can't open table 29ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL; 30CHECK TABLE t1; 31Table Op Msg_type Msg_text 32test.t1 check status OK 33ALTER TABLE t1 ADD INDEX (c2,c3); 34DROP TABLE t1; 35# 36# Bug #14669848 CRASH DURING ALTER MAKES ORIGINAL TABLE INACCESSIBLE 37# 38# -- Scenario 1: 39# Crash the server in ha_innobase::commit_inplace_alter_table() 40# just after committing the dictionary changes. 41CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=innodb; 42INSERT INTO t1 VALUES (1,2),(3,4); 43SET DEBUG='d,innodb_alter_commit_crash_after_commit'; 44ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); 45ERROR HY000: Lost connection to MySQL server during query 46# Restart mysqld after the crash and reconnect. 47# restart 48# Manual *.frm recovery begin. 49# Manual recovery end 50FLUSH TABLES; 51# Drop the orphaned original table. 52# Files in datadir after manual recovery. 53t1.frm 54t1.ibd 55SHOW TABLES; 56Tables_in_test 57t1 58SHOW CREATE TABLE t1; 59Table Create Table 60t1 CREATE TABLE `t1` ( 61 `f1` int(11) NOT NULL, 62 `f2` int(11) NOT NULL, 63 PRIMARY KEY (`f2`,`f1`) 64) ENGINE=InnoDB DEFAULT CHARSET=latin1 65INSERT INTO t1 VALUES (5,6),(7,8); 66SELECT * FROM t1; 67f1 f2 681 2 693 4 705 6 717 8 72DROP TABLE t1; 73CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB; 74ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); 75DROP TABLE t1; 76# -- Scenario 2: 77# Crash the server in ha_innobase::commit_inplace_alter_table() 78# just before committing the dictionary changes, but after 79# writing the MLOG_FILE_RENAME records. As the mini-transaction 80# is not committed, the renames will not be replayed. 81CREATE TABLE t2 (f1 int not null, f2 int not null) ENGINE=InnoDB; 82INSERT INTO t2 VALUES (1,2),(3,4); 83SET DEBUG='d,innodb_alter_commit_crash_before_commit'; 84ALTER TABLE t2 ADD PRIMARY KEY (f2, f1); 85ERROR HY000: Lost connection to MySQL server during query 86# Startup the server after the crash 87# restart 88# Read and remember the temporary table name 89# Manual *.frm recovery begin. The dictionary was not updated 90# and the files were not renamed. The rebuilt table 91# was left behind on purpose, to faciliate data recovery. 92# Manual recovery end 93# Drop the orphaned rebuilt table. 94SHOW TABLES; 95Tables_in_test 96t2 97INSERT INTO t2 VALUES (5,6),(7,8); 98SELECT * from t2; 99f1 f2 1001 2 1013 4 1025 6 1037 8 104SHOW CREATE TABLE t2; 105Table Create Table 106t2 CREATE TABLE `t2` ( 107 `f1` int(11) NOT NULL, 108 `f2` int(11) NOT NULL 109) ENGINE=InnoDB DEFAULT CHARSET=latin1 110DROP TABLE t2; 111CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB; 112ALTER TABLE t2 ADD PRIMARY KEY (f2, f1); 113DROP TABLE t2; 114# ------------------------- 115# End of Testing Scenario 2 116# ------------------------- 117# 118# Bug#19330255 WL#7142 - CRASH DURING ALTER TABLE LEADS TO 119# DATA DICTIONARY INCONSISTENCY 120# 121CREATE TABLE t1(a int PRIMARY KEY, b varchar(255), c int NOT NULL); 122INSERT INTO t1 SET a=1,c=2; 123SET DEBUG='d,innodb_alter_commit_crash_after_commit'; 124ALTER TABLE t1 ADD INDEX (b), CHANGE c d int; 125ERROR HY000: Lost connection to MySQL server during query 126# Restart mysqld after the crash and reconnect. 127# restart 128# Manual *.frm recovery begin. 129# Manual recovery end 130FLUSH TABLES; 131# Drop the orphaned original table. 132# Files in datadir after manual recovery. 133t1.frm 134t1.ibd 135SHOW TABLES; 136Tables_in_test 137t1 138SHOW CREATE TABLE t1; 139Table Create Table 140t1 CREATE TABLE `t1` ( 141 `a` int(11) NOT NULL, 142 `b` varchar(255) DEFAULT NULL, 143 `d` int(11) DEFAULT NULL, 144 PRIMARY KEY (`a`), 145 KEY `b` (`b`) 146) ENGINE=InnoDB DEFAULT CHARSET=latin1 147UPDATE t1 SET d=NULL; 148SELECT * FROM t1; 149a b d 1501 NULL NULL 151DROP TABLE t1; 152