| Bug #39185 | Cardinality for merge tables calculated incorrectly. | ||
|---|---|---|---|
| Submitted: | 2 Sep 2008 12:38 | Modified: | 5 Mar 2009 14:15 |
| Reporter: | Kari Mäkinen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Merge storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1.26-rc/6.0 | OS: | Any |
| Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
| Tags: | Contribution, merge, regression | ||
[2 Sep 2008 13:02]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described:
C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.26-rc-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop table if exists t;
Query OK, 0 rows affected (0.45 sec)
mysql> drop table if exists t0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t0 (a int not null, key (a)) engine=MyISAM;
Query OK, 0 rows affected (0.28 sec)
mysql> insert into t0 (a) values (0),(1),(2),(3),(4);
Query OK, 5 rows affected (0.03 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> analyze table t0;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t0 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.05 sec)
mysql> create table t (id int not null, key (id)) engine=MRG_MyISAM, INSERT_METH
OD=LAST
-> UNION=(t0);
Query OK, 0 rows affected (0.11 sec)
mysql> show indexes from t\G
*************************** 1. row ***************************
Table: t
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set (0.03 sec)
mysql> show indexes from t\G
*************************** 1. row ***************************
Table: t
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set (0.02 sec)
mysql> show indexes from t\G
*************************** 1. row ***************************
Table: t
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
[2 Sep 2008 13:04]
Valeriy Kravchuk
5.0.x is NOT affected, so it looks like regression:
C:\Program Files\MySQL\MySQL Server 6.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.66a-enterprise-gpl-nt MySQL Enterprise Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table if exists t0;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t0 (a int not null, key (a)) engine=MyISAM;
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t0 (a) values (0),(1),(2),(3),(4);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> analyze table t0;
+---------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t0 | analyze | status | OK |
+---------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> create table t (id int not null, key (id)) engine=MRG_MyISAM, INSERT_METH
OD=LAST
-> UNION=(t0);
Query OK, 0 rows affected (0.05 sec)
mysql> show indexes from t\G
*************************** 1. row ***************************
Table: t
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
mysql> show indexes from t\G
*************************** 1. row ***************************
Table: t
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
mysql> show indexes from t\G
*************************** 1. row ***************************
Table: t
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
1 row in set (0.02 sec)
[5 Feb 2009 14:06]
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/65347 2785 Sergey Vojtovich 2009-02-05 BUG#39185 - Cardinality for merge tables calculated incorrectly. Every subsequent query to a merge table with indexes was lowering down cardinality. The problem was that key statistics was not cleared when merge children were detached. Causing next attach children perform incremental key statistics calculation. Fixed by clearing key statistics when attaching first child.
[6 Feb 2009 10:47]
Ingo Strüwing
Approved. Please see email for a question/suggestion.
[19 Feb 2009 13:04]
Bugs System
Pushed into 6.0.10-alpha (revid:sergey.glukhov@sun.com-20090218125737-5y5b2xo3duo1wlvo) (version source revid:azundris@mysql.com-20090213182753-tsqikd41gkwowrqq) (merge vers: 6.0.10-alpha) (pib:6)
[5 Mar 2009 14:15]
Tony Bedford
An entry was added to the 6.0.10 changelog: Cardinality for merge tables kept approaching zero in myrg_attach_children() because m_info->rec_per_key_part was initialized to 0 only when the function was first called.
[13 Mar 2009 19:02]
Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:vvaintroub@mysql.com-20090218093153-sjzxf01i4ezte0xp) (merge vers: 5.1.33) (pib:6)
[9 May 2009 16:39]
Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:36]
Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:34]
Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)
Description: Cardinality for merge tables keep approaching zero in myrg_attach_children() because m_info->rec_per_key_part is initialized to 0 only during the first time the function is called. How to repeat: drop table if exists t; drop table if exists t0; create table t0 (a int not null, key (a)) engine=MyISAM; insert into t0 (a) values (0),(1),(2),(3),(4); analyze table t0; create table t (id int not null, key (id)) engine=MRG_MyISAM, INSERT_METHOD=LAST UNION=(t0); show indexes from t\G *************************** 1. row *************************** Table: t Non_unique: 1 Key_name: id Seq_in_index: 1 Column_name: id Collation: A Cardinality: 5 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: show indexes from t\G *************************** 1. row *************************** Table: t Non_unique: 1 Key_name: id Seq_in_index: 1 Column_name: id Collation: A Cardinality: 2 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: show indexes from t\G *************************** 1. row *************************** Table: t Non_unique: 1 Key_name: id Seq_in_index: 1 Column_name: id Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: show indexes from t\G *************************** 1. row *************************** Table: t Non_unique: 1 Key_name: id Seq_in_index: 1 Column_name: id Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: show indexes from t\G *************************** 1. row *************************** Table: t Non_unique: 1 Key_name: id Seq_in_index: 1 Column_name: id Collation: A Cardinality: 1 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: show indexes from t\G *************************** 1. row *************************** Table: t Non_unique: 1 Key_name: id Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Suggested fix: --- a/storage/myisammrg/myrg_open.c 2008-07-01 01:36:32.000000000 +0300 +++ b/storage/myisammrg/myrg_open.c 2008-09-02 14:06:31.000000000 +0300 @@ -424,10 +424,12 @@ if (!m_info->rec_per_key_part) { if(!(m_info->rec_per_key_part= (ulong*) - my_malloc(key_parts * sizeof(long), MYF(MY_WME|MY_ZEROFILL)))) + my_malloc(key_parts * sizeof(long), MYF(MY_WME)))) goto err; /* purecov: inspected */ errpos= 1; } + for (idx=0; idx < key_parts; idx++) + m_info->rec_per_key_part[idx] = 0; } /* Add MyISAM table info. */ @@ -457,11 +459,13 @@ if (min_keys > myisam->s->base.keys) min_keys= myisam->s->base.keys; /* purecov: inspected */ for (idx= 0; idx < key_parts; idx++) - m_info->rec_per_key_part[idx]+= (myisam->s->state.rec_per_key_part[idx] / - m_info->tables); + m_info->rec_per_key_part[idx]+= myisam->s->state.rec_per_key_part[idx]; child_nr++; } - + + for (idx= 0; idx < key_parts; idx++) + m_info->rec_per_key_part[idx]/= m_info->tables; + if (my_errno == HA_ERR_WRONG_MRG_TABLE_DEF) goto err; if (sizeof(my_off_t) == 4 && file_offset > (ulonglong) (ulong) ~0L)