MySQL Bugs: #47444: --myisam_repair_threads>1can result in all index cardinalities=1

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	CPU Architecture:	Any
Tags:	myisam, myisam_repair_threads

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.

BUG#49828 was marked as duplicate.

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.

Approved. No comments.

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)

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)

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)

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.

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)