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