Bug #47444 --myisam_repair_threads>1can result in all index cardinalities=1
Submitted: 18 Sep 2009 16:27 Modified: 18 Jun 2010 2:10
Reporter: Shawn Green Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.1.37, 5.1.38 OS:Any
Assigned to: Sergey Vojtovich
Tags: myisam, myisam_repair_threads
Triage: Triaged: D2 (Serious) / R2 (Low) / E2 (Low)

[18 Sep 2009 16:27] Shawn Green
Description:
This is a split/continuation of bug #18874. This new bug was created to keep the additional research and patch to repair this extension of bug #18847 separate and distinct from that previous effort. 
~~~
When filling a truncated table with data and --myisam-repair-threads>1, then all indexes on that table will not get cardinality values, they will all be =1. 

Running ANALYZE TABLE will not fix this problem with the index statistics. Only CHECK TABLE will resolve the problem. 

How to repeat:
        drop table if exists `t2`,t1;

CREATE TABLE `t2` (
`c1` int(11) NOT NULL,
`c2` int(11) NOT NULL,
`c3` int(11) NOT NULL,
`c4` int(11) NOT NULL,
`c5` int(11) NOT NULL,
`c6` int(11) NOT NULL,
`c7` int(11) NOT NULL,
`c8` int(11) NOT NULL,
`c9` int(11) NOT NULL,
`c10` int(11) NOT NULL,
`c11` int(11) NOT NULL,
`c12` int(11) NOT NULL,
`c13` int(11) NOT NULL,
`c14` int(11) NOT NULL,
`c15` varchar(50) NOT NULL,
`c16` int(11) NOT NULL,
`c17` int(11) NOT NULL,
`c18` int(11) NOT NULL,
`c19` int(11) NOT NULL,
`c20` int(11) NOT NULL,
KEY `ix1_2` (`c1`,`c2`),
KEY `ix20_2` (`c20`,`c2`),
KEY `IX6_5` (`c6`,`c5`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

create table t1 like `t2`;
alter table t1 disable keys;

insert into t1 values(
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000);
insert into t1 select * from t1;
insert into t1 select * from t1;
select count(*) from t1;
insert into t1 select rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000,
rand()*100000 from t1 a,t1 b,t1 c,t1 d,t1 f,t1 g,t1 h,t1 i,t1 j,t1 k;

insert into `t2` select * from t1;
show indexes from `t2`;
truncate `t2`;
insert into `t2` select * from t1;
show indexes from `t2`;

Notes:
1) The original test case only tested for the explicit REPAIR TABLE. It did not test for
the behavior of filling an empty table (implicitly using a similar code path).

2) The original test case for bug #18874 only tested a new, single-column table using a single index. 

3) The cardinality in my test case is correct for the initial load of data (first SHOW
INDEX) to the table but is incorrect following the truncate and reload if
myisam_repair_threads > 1 (second SHOW INDEX).

Suggested fix:
Need to finish the repairs to handle --myisam-repair-thread values > 1 started with bug #18874.
[12 Mar 2010 8:13] Sergey Vojtovich
BUG#49828 was marked as duplicate.
[12 Mar 2010 10:44] 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/103072

3395 Sergey Vojtovich	2010-03-12
      BUG#47444 - --myisam_repair_threads>1can result in all
                  index cardinalities=1
      
      Parallel repair didn't poroperly update index cardinality
      in certain cases.
      
      When myisam_sort_buffer_size is not enough to store all
      keys, index cardinality was updated before index was
      actually written, when no index statistic is available.
     @ mysql-test/r/myisam.result
        A test case for BUG#47444.
     @ mysql-test/t/myisam.test
        A test case for BUG#47444.
     @ storage/myisam/sort.c
        update_key_parts() must be called after all index
        entries are written, when index statistic is available.
[15 Mar 2010 9:30] Ingo Strüwing
Approved. No comments.
[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-20100320202342-3oapaq7r0t6qhexq) (merge vers: 5.5.3-m2) (pib:16)
[26 Mar 2010 8:27] 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:31] 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)
[6 Apr 2010 7:57] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100316173356-moq52r53w1tiokmg) (merge vers: 5.1.46) (pib:16)
[12 Apr 2010 23:09] Paul Dubois
Noted in 5.1.46, 5.5.5, 6.0.14 changelogs.

Setting myisam_repair_threads larger than 1 could result in the
cardinality for all indexes of a MyISAM table being set to 1 after 
parallel index repair.
[17 Jun 2010 12:14] 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 13:01] 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:42] 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)