MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Satya B Date:February 25 2009 7:37am
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-25
      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 is empty and when it 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
    Modified result file for testcase added for BUG#40827
  mysql-test/t/myisam.test
    Modified myisam.test for BUG#40827 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-25 07:37:13 +0000
@@ -2226,4 +2226,47 @@ 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 t2 (id) SELECT id  FROM t2;
+# Switch to insert Connection
+# Send insert data
+INSERT INTO t1(id) SELECT id  FROM t2;
+# Switch to default Connection
+# Wait for insert data to reach the debug point
+SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE STATE = 'Sending data' AND 
+INFO = "INSERT INTO t1(id) SELECT id  FROM t2" 
+INTO @thread_id;
+KILL QUERY @thread_id;
+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-25 07:37:13 +0000
@@ -1476,6 +1476,55 @@ 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 (insertConn, localhost, root,,);
+
+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 = 18; 
+while ($i > 0)
+{
+  INSERT INTO t2 (id) SELECT id  FROM t2; 
+  dec $i; 
+}
+
+--echo # Switch to insert Connection
+CONNECTION insertConn;
+--echo # Send insert data
+SEND INSERT INTO t1(id) SELECT id  FROM t2; 
+
+--echo # Switch to default Connection
+CONNECTION default;
+--echo # Wait for insert data to reach the debug point
+
+let $wait_condition=
+  SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.PROCESSLIST
+  WHERE STATE = "Sending data" AND 
+  INFO = "INSERT INTO t1(id) SELECT id  FROM t2";
+--source include/wait_condition.inc
+
+SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST
+WHERE STATE = 'Sending data' AND 
+INFO = "INSERT INTO t1(id) SELECT id  FROM t2" 
+INTO @thread_id;
+
+KILL QUERY @thread_id;
+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-25 07:37:13 +0000
@@ -1499,6 +1499,9 @@ void ha_myisam::start_bulk_insert(ha_row
     /*
       Only disable old index if the table was empty and we are inserting
       a lot of rows.
+      Note that in end_bulk_insert() we may truncate the table if
+      enable_indexes() failed, thus it's essential that indexes are
+      disabled ONLY for an empty table.
       We should not do this for only a few rows as this is slower and
       we don't want to update the key statistics based of only a few rows.
     */
@@ -1532,8 +1535,27 @@ 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. 
+        After truncating the table we don't need to enable the 
+        indexes, because the last repair operation is aborted after 
+        setting the indexes as active and  trying to recreate them. 
+     */
+   
+      if (((err= enable_indexes(HA_KEY_SWITCH_NONUNIQ_SAVE)) != 0) && 
+                                                  current_thd->killed)
+      {
+        delete_all_rows();
+        /* not crashed, despite being killed during repair */
+        file->s->state.changed&= ~(STATE_CRASHED|STATE_CRASHED_ON_REPAIR);
+      }
+    } 
+  }
+  return err;
 }
 
 

Thread
bzr commit into mysql-5.1-bugteam branch (satya.bn:2751) Bug#40827Satya B25 Feb
  • Re: bzr commit into mysql-5.1-bugteam branch (satya.bn:2751) Bug#40827satya25 Feb