Bug #18874 Setting myisam_repair_threads > 1, index cardinality always 1
Submitted: 7 Apr 2006 3:28 Modified: 18 Sep 2009 16:30
Reporter: Jeremy Cole (Basic Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:5.0,21-BK, 5.0.19,5.1.37,5.1.38 OS:Any (n/a)
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: myisam, myisam_repair_threads, regression

[7 Apr 2006 3:28] Jeremy Cole
Description:
Setting myisam_repair_threads (globally or per-session) causes any repair of the table (repair, alter, create as select) to fail to update the cardinality of the index to always equal 1.

How to repeat:
Run this script:

>>>>>
drop table if exists at;
create table at (a int);
insert into at (a) values (1), (2), (3), (4), (5);

set session myisam_repair_threads=1;
drop table if exists mt;
create table mt (a int, index(a)) as select a from at;
show index from mt\G

set session myisam_repair_threads=2;
drop table if exists mt;
create table mt (a int, index(a)) as select a from at;
show index from mt\G
<<<<<

Note that the cardinality in the first show index is 5 (correct), in the second it is 1 (very wrong).  This holds true for any table definition and any number of rows.  This effectively makes all indexes (and thus the table) useless.
[7 Apr 2006 3:30] Jeremy Cole
Bad english.  I meant: Setting myisam_repair_threads (globally or per-session) causes any repair of the table (repair, alter, create as select) to fail to update the cardinality of the
index, instead making it always equal 1.
[10 Apr 2006 11:06] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.21-BK (ChangeSet@1.2143, 2006-04-10 08:29:24+02:00) on Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.21

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists at;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table at (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into at (a) values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> set session myisam_repair_threads=1;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists mt;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> create table mt (a int, index(a)) as select a from at;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show index from mt\G
*************************** 1. row ***************************
       Table: mt
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: 5
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)

mysql> set session myisam_repair_threads=2;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists mt;
Query OK, 0 rows affected (0.01 sec)

mysql> create table mt (a int, index(a)) as select a from at;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show index from mt\G
*************************** 1. row ***************************
       Table: mt
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: 1
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)
[10 Apr 2006 23:41] Calvin Sun
It is in the same area as bug#11527.
[10 Apr 2006 23:52] Jeremy Cole
Are you sure?  Looks different to me.  11527 requires >4GB of data, this one does not.  Valeriy claims to have verified it on 5.0.21-BK, which should have the fix for 11527 (since almost two months ago).

Can you double check before calling this a dupe?
[11 Apr 2006 2:05] Calvin Sun
Jeremy - I did not make it a dup. It is still open.
[10 Aug 2006 13: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/10267

ChangeSet@1.2533, 2006-08-10 18:44:41+05:00, svoj@may.pils.ru +3 -0
  BUG#18874 - Setting myisam_repair_threads > 1, index cardinality always 1
  
  When myisam_repair_threads value to > 1, index repair operation always
  sets index cardinality to 1.
  
  Fixed wrong index cardinality calculation for index repair operation
  when myisam_repair_threads is > 1.
[14 Aug 2006 12:16] 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/10357

ChangeSet@1.2533, 2006-08-14 17:05:02+05:00, svoj@may.pils.ru +3 -0
  BUG#18874 - Setting myisam_repair_threads > 1, index cardinality always 1
  Fixed by moving update_key_parts() down to be after write_index().
[14 Aug 2006 15:21] Sergey Vojtovich
Pushed into 4.1.22, 5.0.25.
[15 Aug 2006 7:40] Sergey Vojtovich
Pushed into 5.1.12.
[18 Aug 2006 13:23] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 4.1.22, 5.0.25, and 5.1.12 changelogs.
[17 Sep 2009 1:27] MySQL Verification Team
I would like to reopen this bug with the following script. I was able to reproduce this behavior on 5.1.37-Advanced

~~~~
drop procedure if exists p1;
delimiter $
create procedure p1()
begin

        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`;

end $

delimiter ;

call p1();
~~~~
[17 Sep 2009 14:21] MySQL Verification Team
Notes on my test case:

1) This does not need to execute as a stored procedur to reproduce the problem. I only bundled it that way for convenience during testing.

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

3) The original test case only tested a single-column table using a single index. 

4) 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).
[18 Sep 2009 16:30] MySQL Verification Team
Re-closing bug #18874. We have split the new report into bug #47444 and will handle the repairs from there.