MySQL Bugs: #51307: widespread corruption with partitions and insert...select

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

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.

testcase

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

output of testcase and error log in a typical case.

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

May be relevant to BUG#49628

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
...

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.

Approved. No comments. All looks good.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100326081116-m3v4l34yhr43mtsv) (version source revid:alik@sun.com-20100325072612-4sds00ix8ajo1e84) (pib:16)

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)

Category should be MyISAM, not Partitioning - see developer comments above.

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)

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.

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)

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)

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)