#At file:///home/satya/WORK/mysql-5.1-bugteam-40827/ based on revid:alfranio.correia@stripped
2751 Satya B 2009-02-18
BUG#40827 - Killing insert-select to MyISAM can cause table corruption
Killing insert-select statement on MyISAM corrupts the table.
Killing the insert-select statement corrupts the MyISAM table only
when the destination table has indexes. When we bulk insert huge data and if the
destination table is empty we disable the indexes for fast inserts,
data is then inserted and indexes are re-enabled after bulk_insert operation
Killing the query, aborts the repair table operation during enable indexes
phase leading to table corruption.
We now truncate the table when we detect that enable indexes is
killed for bulk insert query.As we have an empty table before the operation,
we can fix by truncating the table.
modified:
mysql-test/r/myisam.result
mysql-test/t/myisam.test
storage/myisam/ha_myisam.cc
per-file messages:
mysql-test/r/myisam.result
Result file for the modified myisam.test
mysql-test/t/myisam.test
Modified myisam.test to add testcase which tests if myisam table is ok, when
insert select query is killed.
storage/myisam/ha_myisam.cc
Fixed end_bulk_insert() method to truncate the table when we detect enable
index operation is killed.
=== modified file 'mysql-test/r/myisam.result'
--- a/mysql-test/r/myisam.result 2008-08-26 13:53:22 +0000
+++ b/mysql-test/r/myisam.result 2009-02-18 08:51:35 +0000
@@ -2226,4 +2226,37 @@ Key Start Len Index Type
1 2 30 multip. varchar
2 33 30 multip. char NULL
DROP TABLE t1;
+CREATE TABLE `t1` (
+`id` BIGINT(20) ,
+`id1` BIGINT(20) AUTO_INCREMENT,
+KEY(id1), KEY(id)
+) ENGINE=MyISAM;
+CREATE TABLE `t2` (
+`id` BIGINT(20) ,
+`id1` BIGINT(20) AUTO_INCREMENT,
+KEY (id1), KEY(id)
+) ENGINE=MyISAM;
+INSERT INTO t2 (id) VALUES (123);
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t2 (id) SELECT id FROM t2;
+INSERT INTO t1 SELECT id FROM t2;
+CHECK TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 check status OK
+DROP TABLE t1,t2;
End of 5.1 tests
=== modified file 'mysql-test/t/myisam.test'
--- a/mysql-test/t/myisam.test 2008-08-26 13:53:22 +0000
+++ b/mysql-test/t/myisam.test 2009-02-18 08:51:35 +0000
@@ -1476,6 +1476,43 @@ CREATE TABLE t1 (
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
--exec $MYISAMCHK -d $MYSQLTEST_VARDIR/master-data/test/t1
DROP TABLE t1;
+#
+# BUG#40827 - Killing insert-select to MyISAM can cause table corruption
+#
+CONNECT (adminConn, localhost, root,,);
+CONNECT (insertConn, localhost, root,,);
+let $id = query_get_value(SELECT CONNECTION_ID() AS Id, Id, 1);
+
+CREATE TABLE `t1` (
+`id` BIGINT(20) ,
+`id1` BIGINT(20) AUTO_INCREMENT,
+ KEY(id1), KEY(id)
+) ENGINE=MyISAM;
+
+CREATE TABLE `t2` (
+`id` BIGINT(20) ,
+`id1` BIGINT(20) AUTO_INCREMENT,
+KEY (id1), KEY(id)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 (id) VALUES (123);
+
+let $i = 17;
+while ($i > 0)
+{
+ INSERT INTO t2 (id) SELECT id FROM t2;
+ dec $i;
+}
+
+CONNECTION insertConn;
+SEND INSERT INTO t1 SELECT id FROM t2;
+CONNECTION adminConn;
+SLEEP 1;
+--disable_query_log
+--eval KILL $id;
+--enable_query_log
+CHECK TABLE t1;
+DROP TABLE t1,t2;
--echo End of 5.1 tests
=== modified file 'storage/myisam/ha_myisam.cc'
--- a/storage/myisam/ha_myisam.cc 2008-03-28 10:14:27 +0000
+++ b/storage/myisam/ha_myisam.cc 2009-02-18 08:51:35 +0000
@@ -1532,8 +1532,21 @@ int ha_myisam::end_bulk_insert()
{
mi_end_bulk_insert(file);
int err=mi_extra(file, HA_EXTRA_NO_CACHE, 0);
- return err ? err : can_enable_indexes ?
- enable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE) : 0;
+ if (!err)
+ {
+ if (can_enable_indexes)
+ {
+ /* Truncate the table when enable index operation is killed */
+ if (((err= enable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE)) != 0) &&
+ current_thd->killed)
+ {
+ delete_all_rows();
+ file->s->state.changed= STATE_NOT_SORTED_PAGES;
+ current_thd->main_da.reset_diagnostics_area();
+ }
+ }
+ }
+ return err;
}
