| 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: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
| Version: | 5.0+ | OS: | Any |
| Assigned to: | Satya B | CPU Architecture: | Any |
[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)
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.