diff options
| -rw-r--r-- | mysql-test/suite/innodb/r/alter_crash.result | 151 | ||||
| -rw-r--r-- | mysql-test/suite/innodb/t/alter_crash.test | 225 |
2 files changed, 376 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/alter_crash.result b/mysql-test/suite/innodb/r/alter_crash.result new file mode 100644 index 00000000000..e1539d20a65 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_crash.result @@ -0,0 +1,151 @@ +# +# Bug#20015132 ALTER TABLE FAILS TO CHECK IF TABLE IS CORRUPTED +# +CREATE TABLE t1(c1 INT PRIMARY KEY, c2 CHAR(1), c3 INT UNSIGNED) ENGINE=InnoDB; +SET DEBUG='+d,ib_create_table_fail_too_many_trx'; +ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3); +ERROR HY000: Too many active concurrent transactions +SET DEBUG='-d,ib_create_table_fail_too_many_trx'; +ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3); +SET DEBUG='+d,dict_set_index_corrupted'; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check Warning InnoDB: The B-tree of index c2 is corrupted. +test.t1 check Warning InnoDB: The B-tree of index c3 is corrupted. +test.t1 check error Corrupt +# restart +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check Warning InnoDB: Index c2 is marked as corrupted +test.t1 check Warning InnoDB: Index c3 is marked as corrupted +test.t1 check error Corrupt +ALTER TABLE t1 DROP INDEX c2; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check Warning InnoDB: Index c3 is marked as corrupted +test.t1 check error Corrupt +ALTER TABLE t1 ADD INDEX (c2,c3); +ERROR 42000: Can't open table +ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL; +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +ALTER TABLE t1 ADD INDEX (c2,c3); +DROP TABLE t1; +# +# Bug #14669848 CRASH DURING ALTER MAKES ORIGINAL TABLE INACCESSIBLE +# +# -- Scenario 1: +# Crash the server in ha_innobase::commit_inplace_alter_table() +# just after committing the dictionary changes. +CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=innodb; +INSERT INTO t1 VALUES (1,2),(3,4); +SET DEBUG='d,innodb_alter_commit_crash_after_commit'; +ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); +ERROR HY000: Lost connection to MySQL server during query +# Restart mysqld after the crash and reconnect. +# restart +# Manual *.frm recovery begin. +# Manual recovery end +FLUSH TABLES; +# Drop the orphaned original table. +# Files in datadir after manual recovery. +t1.frm +t1.ibd +SHOW TABLES; +Tables_in_test +t1 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) NOT NULL, + `f2` int(11) NOT NULL, + PRIMARY KEY (`f2`,`f1`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES (5,6),(7,8); +SELECT * FROM t1; +f1 f2 +1 2 +3 4 +5 6 +7 8 +DROP TABLE t1; +CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB; +ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); +DROP TABLE t1; +# -- Scenario 2: +# Crash the server in ha_innobase::commit_inplace_alter_table() +# just before committing the dictionary changes, but after +# writing the MLOG_FILE_RENAME records. As the mini-transaction +# is not committed, the renames will not be replayed. +CREATE TABLE t2 (f1 int not null, f2 int not null) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1,2),(3,4); +SET DEBUG='d,innodb_alter_commit_crash_before_commit'; +ALTER TABLE t2 ADD PRIMARY KEY (f2, f1); +ERROR HY000: Lost connection to MySQL server during query +# Startup the server after the crash +# restart +# Read and remember the temporary table name +# Manual *.frm recovery begin. The dictionary was not updated +# and the files were not renamed. The rebuilt table +# was left behind on purpose, to faciliate data recovery. +# Manual recovery end +# Drop the orphaned rebuilt table. +SHOW TABLES; +Tables_in_test +t2 +INSERT INTO t2 VALUES (5,6),(7,8); +SELECT * from t2; +f1 f2 +1 2 +3 4 +5 6 +7 8 +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `f1` int(11) NOT NULL, + `f2` int(11) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t2; +CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB; +ALTER TABLE t2 ADD PRIMARY KEY (f2, f1); +DROP TABLE t2; +# ------------------------- +# End of Testing Scenario 2 +# ------------------------- +# +# Bug#19330255 WL#7142 - CRASH DURING ALTER TABLE LEADS TO +# DATA DICTIONARY INCONSISTENCY +# +CREATE TABLE t1(a int PRIMARY KEY, b varchar(255), c int NOT NULL); +INSERT INTO t1 SET a=1,c=2; +SET DEBUG='d,innodb_alter_commit_crash_after_commit'; +ALTER TABLE t1 ADD INDEX (b), CHANGE c d int; +ERROR HY000: Lost connection to MySQL server during query +# Restart mysqld after the crash and reconnect. +# restart +# Manual *.frm recovery begin. +# Manual recovery end +FLUSH TABLES; +# Drop the orphaned original table. +# Files in datadir after manual recovery. +t1.frm +t1.ibd +SHOW TABLES; +Tables_in_test +t1 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `b` varchar(255) DEFAULT NULL, + `d` int(11) DEFAULT NULL, + PRIMARY KEY (`a`), + KEY `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +UPDATE t1 SET d=NULL; +SELECT * FROM t1; +a b d +1 NULL NULL +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_crash.test b/mysql-test/suite/innodb/t/alter_crash.test new file mode 100644 index 00000000000..5417ec88e70 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_crash.test @@ -0,0 +1,225 @@ +# Crash-safe InnoDB ALTER operations + +--source include/not_valgrind.inc +--source include/not_embedded.inc +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/not_crashrep.inc + +--disable_query_log +call mtr.add_suppression('InnoDB: cannot find a free slot for an undo log'); +call mtr.add_suppression('InnoDB: row_merge_rename_index_to_add failed with error 47'); +call mtr.add_suppression('InnoDB: Flagged corruption of `c[23]`'); +call mtr.add_suppression('InnoDB: Index `c[23]` .*is corrupted'); +--enable_query_log + +--echo # +--echo # Bug#20015132 ALTER TABLE FAILS TO CHECK IF TABLE IS CORRUPTED +--echo # + +CREATE TABLE t1(c1 INT PRIMARY KEY, c2 CHAR(1), c3 INT UNSIGNED) ENGINE=InnoDB; +SET DEBUG='+d,ib_create_table_fail_too_many_trx'; +--error ER_TOO_MANY_CONCURRENT_TRXS +ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3); + +SET DEBUG='-d,ib_create_table_fail_too_many_trx'; +ALTER TABLE t1 ADD INDEX (c2), ADD INDEX (c3); +# Flag the secondary indexes corrupted. +SET DEBUG='+d,dict_set_index_corrupted'; +CHECK TABLE t1; + +# Ensure that the corruption is permanent. +--source include/restart_mysqld.inc +CHECK TABLE t1; +ALTER TABLE t1 DROP INDEX c2; +CHECK TABLE t1; +# We refuse an ALTER TABLE that would modify the InnoDB data dictionary +# while leaving some of the table corrupted. +--error ER_CHECK_NO_SUCH_TABLE +ALTER TABLE t1 ADD INDEX (c2,c3); +# This will rebuild the table, uncorrupting all secondary indexes. +ALTER TABLE t1 CHANGE c3 c3 INT NOT NULL; +CHECK TABLE t1; +ALTER TABLE t1 ADD INDEX (c2,c3); +DROP TABLE t1; + +let $MYSQLD_DATADIR= `select @@datadir`; +let datadir= `select @@datadir`; + +# These are from include/shutdown_mysqld.inc and allow to call start_mysqld.inc +--let $_server_id= `SELECT @@server_id` +--let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect + +--echo # +--echo # Bug #14669848 CRASH DURING ALTER MAKES ORIGINAL TABLE INACCESSIBLE +--echo # +--echo # -- Scenario 1: +--echo # Crash the server in ha_innobase::commit_inplace_alter_table() +--echo # just after committing the dictionary changes. + +CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=innodb; +INSERT INTO t1 VALUES (1,2),(3,4); +SET DEBUG='d,innodb_alter_commit_crash_after_commit'; + +let $orig_table_id = `SELECT table_id + FROM information_schema.innodb_sys_tables + WHERE name = 'test/t1'`; + +# Write file to make mysql-test-run.pl expect crash +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +--error 2013 +ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); + +--echo # Restart mysqld after the crash and reconnect. +--source include/start_mysqld.inc + +let $temp_table_name = `SELECT SUBSTR(name, 6) + FROM information_schema.innodb_sys_tables + WHERE table_id = $orig_table_id`; + +--echo # Manual *.frm recovery begin. + +--move_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/$temp_table_name.frm + +perl; +my @frm_file = glob "$ENV{'datadir'}/test/#sql-*.frm"; +my $t1_frm = "$ENV{'datadir'}/test/t1.frm"; +rename($frm_file[0], $t1_frm); +EOF + +--echo # Manual recovery end + +FLUSH TABLES; + +--echo # Drop the orphaned original table. +--disable_query_log +eval DROP TABLE `#mysql50#$temp_table_name`; +--enable_query_log + +--echo # Files in datadir after manual recovery. +--list_files $MYSQLD_DATADIR/test + +SHOW TABLES; +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (5,6),(7,8); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB; +ALTER TABLE t1 ADD PRIMARY KEY (f2, f1); +DROP TABLE t1; + +--echo # -- Scenario 2: +--echo # Crash the server in ha_innobase::commit_inplace_alter_table() +--echo # just before committing the dictionary changes, but after +--echo # writing the MLOG_FILE_RENAME records. As the mini-transaction +--echo # is not committed, the renames will not be replayed. + +CREATE TABLE t2 (f1 int not null, f2 int not null) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1,2),(3,4); +SET DEBUG='d,innodb_alter_commit_crash_before_commit'; + +let $orig_table_id = `SELECT table_id + FROM information_schema.innodb_sys_tables + WHERE name = 'test/t2'`; + +# Write file to make mysql-test-run.pl expect crash +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +--error 2013 +ALTER TABLE t2 ADD PRIMARY KEY (f2, f1); + +--echo # Startup the server after the crash +--source include/start_mysqld.inc + +--echo # Read and remember the temporary table name +let $temp_table_name = `SELECT SUBSTRING(name,6) + FROM information_schema.innodb_sys_tables + WHERE name LIKE "test/#sql-ib$orig_table_id%"`; +# This second copy is an environment variable for the perl script below. +let temp_table_name = $temp_table_name; + +--echo # Manual *.frm recovery begin. The dictionary was not updated +--echo # and the files were not renamed. The rebuilt table +--echo # was left behind on purpose, to faciliate data recovery. + +perl; +my @frm_file = glob "$ENV{'datadir'}/test/#sql-*.frm"; +my $target_frm = "$ENV{'datadir'}/test/$ENV{'temp_table_name'}.frm"; +rename($frm_file[0], $target_frm); +EOF + +--echo # Manual recovery end + +--echo # Drop the orphaned rebuilt table. +--disable_query_log +eval DROP TABLE `#mysql50#$temp_table_name`; +--enable_query_log + +SHOW TABLES; +INSERT INTO t2 VALUES (5,6),(7,8); +SELECT * from t2; +SHOW CREATE TABLE t2; +DROP TABLE t2; + +CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL) ENGINE=InnoDB; +ALTER TABLE t2 ADD PRIMARY KEY (f2, f1); +DROP TABLE t2; +--list_files $MYSQLD_DATADIR/test + +--echo # ------------------------- +--echo # End of Testing Scenario 2 +--echo # ------------------------- + +--echo # +--echo # Bug#19330255 WL#7142 - CRASH DURING ALTER TABLE LEADS TO +--echo # DATA DICTIONARY INCONSISTENCY +--echo # + +CREATE TABLE t1(a int PRIMARY KEY, b varchar(255), c int NOT NULL); +INSERT INTO t1 SET a=1,c=2; +SET DEBUG='d,innodb_alter_commit_crash_after_commit'; + +let $orig_table_id = `select table_id from + information_schema.innodb_sys_tables where name = 'test/t1'`; + +# Write file to make mysql-test-run.pl expect crash +--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect + +--error 2013 +ALTER TABLE t1 ADD INDEX (b), CHANGE c d int; + +--echo # Restart mysqld after the crash and reconnect. +--source include/start_mysqld.inc + +let $temp_table_name = `SELECT SUBSTR(name, 6) + FROM information_schema.innodb_sys_tables + WHERE table_id = $orig_table_id`; + +--echo # Manual *.frm recovery begin. +--move_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/$temp_table_name.frm + +perl; +my @frm_file = glob "$ENV{'datadir'}/test/#sql-*.frm"; +my $t1_frm = "$ENV{'datadir'}/test/t1.frm"; +rename($frm_file[0], $t1_frm); +EOF + +--echo # Manual recovery end + +FLUSH TABLES; + +--echo # Drop the orphaned original table. +--disable_query_log +eval DROP TABLE `#mysql50#$temp_table_name`; +--enable_query_log + +--echo # Files in datadir after manual recovery. +--list_files $MYSQLD_DATADIR/test + +SHOW TABLES; +SHOW CREATE TABLE t1; +UPDATE t1 SET d=NULL; +SELECT * FROM t1; +DROP TABLE t1; |