Bug #51307 widespread corruption with partitions and insert...select
Submitted: 19 Feb 2010 7:06 Modified: 18 Jun 2010 2:09
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:5.1.34, 5.1.44, 5.1.45-bzr,5.5.1-m2 OS:Any
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: corruption, myisam_sort_buffer_size
Triage: Triaged: D1 (Critical)

[19 Feb 2010 7:06] Shane Bester
Description:
simple workload containing 2 threads doing:

insert into t1 values ..
insert into t2 select * from t1
check table t2
truncate table t2;

leads to corruption in t2 with a small myisam_sort_buffer_size or a large table.

How to repeat:
see attached testcase.
[19 Feb 2010 7:09] Shane Bester
testcase

Attachment: bug51307.c (text/plain), 6.77 KiB.

[19 Feb 2010 7:10] Shane Bester
output of testcase and error log in a typical case.

Attachment: bug51307_typical_output.txt (text/plain), 1.54 KiB.

[19 Feb 2010 7:20] Sergey Vojtovich
May be relevant to BUG#49628
[19 Feb 2010 7:59] Valeriy Kravchuk
Verified just as described with recent 5.1.45-bzr on Linux:

openxs@suse:/home2/openxs/dbs/5.1> ./bug51307
running initializations..
client version=50145
server version=50145
about to spawn 2 threads
..
completed spawning new database worker threads
testcase is now running, so watch for error output
queries: 000000739
queries: 000001088
queries: 000001335
queries: 000001544
queries: 000001751
queries: 000001813
queries: 000001986
queries: 000002175
queries: 000002378
queries: 000002393
query failed 'replace into t1 select * from t2' : 145 (Table './test/t1#P#p1' is marked as crashed and should be repaired) (HY000)
queries: 000002635
queries: 000002776
queries: 000002960
queries: 000003069
queries: 000003184
queries: 000003256
queries: 000003325
queries: 000003429
queries: 000003533
query failed 'truncate table t1' : 145 (Table './test/t1#P#p1' is marked as crashed and should be repaired) (HY000)
query failed 'replace into t1 select * from t2' : 145 (Table './test/t1#P#p1' is marked as crashed and should be repaired) (HY000)
queries: 000003739
queries: 000003843
query failed 'truncate table t1' : 145 (Table './test/t1#P#p1' is marked as crashed and should be repaired) (HY000)
query failed 'replace into t1 select * from t2' : 145 (Table './test/t1#P#p1' is marked as crashed and should be repaired) (HY000)
queries: 000003947
queries: 000004049
queries: 000004153
query failed 'truncate table t1' : 145 (Table './test/t1#P#p1' is marked as crashed and should be repaired) (HY000)
query failed 'replace into t1 select * from t2' : 145 (Table './test/t1#P#p1' is marked as crashed and should be repaired) (HY000)
queries: 000004359
queries: 000004437
queries: 000004463
query failed 'truncate table t1' : 145 (Table './test/t1#P#p1' is marked as crashed and should be repaired) (HY000)
query failed 'replace into t1 select * from t2' : 145 (Table './test/t1#P#p1' is marked as crashed and should be repaired) (HY000)
queries: 000004669
queries: 000004773
queries: 000004773
...
[2 Mar 2010 9:46] 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/102007

3354 Sergey Vojtovich	2010-03-02
      BUG#51307 - widespread corruption with partitions and
                  insert...select
      
      Queries following bulk insert into an empty MyISAM table
      may break it. This was pure MyISAM problem.
      
      When bulk insert into an empty table is complete, MyISAM
      may want to enable indexes via repair by sort. If repair
      by sort fails (e.g. insufficient buffer), MyISAM failover
      to repair with key cache, requesting repair of data file.
      
      Repair of data file performs data file substitution. This
      means that current table instance will point to new data
      file. Other cached table instances are still pointing to
      an old, deleted data file.
      
      This is fixed by not requesting repair of data file
      during enable indexes.
      
      Explicit REPAIR is not affected, since it flushes all
      table instances.
     @ mysql-test/r/myisam.result
        A test case for BUG#51307.
     @ mysql-test/t/myisam.test
        A test case for BUG#51307.
     @ storage/myisam/ha_myisam.cc
        When enabling indexes do not attempt to repair data file.
[2 Mar 2010 10:56] Ingo Strüwing
Approved. No comments. All looks good.
[26 Mar 2010 8:23] Bugs System
Pushed into 5.5.4-m3 (revid:alik@sun.com-20100326080914-2pz8ns984e0spu03) (version source revid:alexey.kopytov@sun.com-20100312095153-t4rtoqc7p96lmxvh) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:26] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)
[26 Mar 2010 8:29] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100326081944-qja07qklw1p2w7jb) (version source revid:alik@sun.com-20100325073410-4t4i9gu2u1pge7xb) (merge vers: 6.0.14-alpha) (pib:16)
[29 Mar 2010 9:37] Jon Stephens
Category should be MyISAM, not Partitioning - see developer comments above.
[6 Apr 2010 7:56] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100310123212-eh0la0l088xyu89o) (merge vers: 5.1.45) (pib:16)
[12 Apr 2010 22:41] Paul Dubois
Noted in 5.1.46, 5.5.5, 6.0.14 changelogs.

Following a bulk insert into a MyISAM table, if MyISAM failed to
build indexes using repair by sort, data file corruption could occur.
[17 Jun 2010 11:45] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:23] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609211156-tsac5qhw951miwtt) (merge vers: 5.1.46-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:10] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)