List:Commits« Previous MessageNext Message »
From:Satya B Date:February 18 2009 8:51am
Subject:bzr commit into mysql-5.1-bugteam branch (satya.bn:2751) Bug#40827
View as plain text  
#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;
 }
 
 

Thread
bzr commit into mysql-5.1-bugteam branch (satya.bn:2751) Bug#40827Satya B18 Feb
  • Re: bzr commit into mysql-5.1-bugteam branch (satya.bn:2751)Bug#40827Sergei Golubchik19 Feb
    • Re: bzr commit into mysql-5.1-bugteam branch (satya.bn:2751) Bug#40827satya20 Feb
      • Re: bzr commit into mysql-5.1-bugteam branch (satya.bn:2751)Bug#40827Sergei Golubchik20 Feb