Bug #40827 Killing insert-select to MyISAM can cause table corruption
Submitted: 18 Nov 2008 16:18 Modified: 13 May 2009 23:37
Reporter: Scott Noyes Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0+ OS:Any
Assigned to: Satya B CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[18 Nov 2008 16:18] Scott Noyes
Description:
CHECK TABLE reports the following after killing an INSERT...SELECT.

Table   Op      Msg_type        Msg_text
test.a  check   warning Table is marked as crashed and last repair failed
test.a  check   warning Size of indexfile is: 1337344      Should be: 1024
test.a  check   error   Record-count is not ok; is 92522   Should be: 0
test.a  check   warning Found 92522 key parts. Should be: 0
test.a  check   error   Partition pMin returned error
test.a  check   error   Corrupt

mysqltest script shown below.  Corruption occurs only with large data sets. It may be necessary to increase number of rows inserted on faster computers to duplicate issue.

How to repeat:
connect (adminConn);

connect (insertConn);
let $id = query_get_value(SELECT CONNECTION_ID() AS Id, Id, 1);

USE test;
DROP TABLE IF EXISTS a, b;

CREATE TABLE `a` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`f1` datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
KEY (`id`),
KEY (`f1`,`id`)
) ENGINE=MyISAM PARTITION BY RANGE ( TO_DAYS(f1)) (PARTITION pMin VALUES LESS THAN (733659));

CREATE TABLE `b` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`f1` datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
PRIMARY KEY (`id`),
KEY (`f1`,`id`)
) ENGINE=InnoDB;

INSERT INTO b (id) VALUES (NULL);
let $i = 17; -- faster computers may need to adjust this value higher to make the insert take long enough
while ($i > 0)
{
  INSERT INTO b (id) SELECT NULL FROM b;
  dec $i;
}

connection insertConn;
SEND INSERT INTO a SELECT * FROM b;

connection adminConn;
SLEEP 1;
eval KILL $id;
CHECK TABLE a;

Suggested fix:
Killing a query should not corrupt the table.
[8 Dec 2008 16:11] Mattias Jonsson
I can repeat this using MyISAM only (without using innodb or partitioning, in latest 5.1-bugteam tree)
[8 Dec 2008 17:29] Mattias Jonsson
Verified also on 5.0 (latest bugteam tree, aelkin@mysql.com-20081208142913-xqku2i1fvcopfv2j)

modified test case:
connect (adminConn, localhost, root,,);

connect (insertConn, localhost, root,,);
let $id = query_get_value(SELECT CONNECTION_ID() AS Id, Id, 1);

USE test;
DROP TABLE IF EXISTS a, b;

CREATE TABLE `a` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `f1` datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
  KEY (`id`),
  KEY (`f1`,`id`)
)
ENGINE=MyISAM
;
#PARTITION BY RANGE ( TO_DAYS(f1))
#(PARTITION pMin VALUES LESS THAN (733659));

CREATE TABLE `b` ( 
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `f1` datetime NOT NULL DEFAULT '1900-01-01 00:00:00',
  PRIMARY KEY (`id`),
  KEY (`f1`,`id`)
) ENGINE=MyISAM;

INSERT INTO b (id) VALUES (NULL);
let $i = 17; -- faster computers may need to adjust this value higher to make the insert take long enough 
while ($i > 0)
{
  INSERT INTO b (id) SELECT NULL FROM b;
  dec $i;
}

connection insertConn;
SEND INSERT INTO a SELECT * FROM b; 

connection adminConn;
SLEEP 1;
eval KILL $id;  
CHECK TABLE a;
[8 Dec 2008 17:33] Mattias Jonsson
also affects "LOAD DATA INFILE 'big-dump.txt' INTO TABLE a;" if killed
[18 Feb 2009 8:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66731

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
[25 Feb 2009 7:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67439

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
[27 Feb 2009 8:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67796

2751 Satya B	2009-02-27
      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
[16 Apr 2009 11:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/72275

2863 Satya B	2009-04-16
      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
[30 Apr 2009 7:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73078

2879 Satya B	2009-04-30 [merge]
      Fix for BUG#40827 - Killing insert-select to MyISAM can cause table corruption
                             
      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
[30 Apr 2009 12:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73101

2880 Satya B	2009-04-30
      Addition to the fix for BUG#40827 - Killing insert-select to MyISAM can cause
                                          table corruption
      
      
      Moved the testcase from the file myisam.test to the new testfile 
      mysiam_debug.test
       
      added:
        mysql-test/r/myisam_debug.result
        mysql-test/t/myisam_debug.test
      modified:
        mysql-test/r/myisam.result
        mysql-test/t/myisam.test
[4 May 2009 9:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/73280

2889 Satya B	2009-05-04
      Additional Fix for BUG#40827 - Killing insert-select to MyISAM can cause
                                     table corruption
      
      Disabling the mysiam_debug.test in embedded mode because of BUG#43733
      modified:
        mysql-test/t/myisam_debug.test
[5 May 2009 19:39] Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 14:06] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:satya.bn@sun.com-20090504102546-o7egrdzhogv1knea) (merge vers: 6.0.12-alpha) (pib:6)
[13 May 2009 23:37] Paul Dubois
Noted in 5.1.35, 6.0.12 changelogs.

Killing an INSERT ... SELECT statement for a MyISAM table could cause
table corruption if the table had indexes.
[15 Jun 2009 8:26] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:05] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:46] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)