Details
-
Type:
Bug
-
Status: Stalled
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 10.0, 5.5
-
Component/s: Storage Engine - MyISAM
-
Environment:OpenSuSE 13.1 64-bit
Description
Indexes missing cardinality after importing data from the dump. Specifically cardinality is not populated after indexes enabled after being disabled. Simple query to demonstrate the problem:
DROP TABLE IF EXISTS `bad_table`;
CREATE TABLE `bad_table` (
`id1` INT(10) UNSIGNED NOT NULL,
`id2` INT(10) UNSIGNED NOT NULL,
INDEX `idx1` (`id1`, `id2`)
)
ENGINE=MyISAM;
/*!40000 ALTER TABLE `jobListDevices` DISABLE KEYS */;
INSERT INTO `bad_table` VALUES
(1,1),
(1,2),
(1,3),
(1,4),
(2,1),
(2,2),
(2,3),
(2,4),
(3,1),
(3,2),
(3,3),
(3,4)
;
/*!40000 ALTER TABLE `jobListDevices` ENABLE KEYS */;
show index from `bad_table`;
analyze table `bad_table`;
show index from `bad_table`;
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Oh, sorry, here is the corrected SQL to replicate the problem:
DROP TABLE IF EXISTS `bad_table`;
CREATE TABLE `bad_table` (
`id1` INT(10) UNSIGNED NOT NULL,
`id2` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id1`, `id2`),
INDEX `idx2` (`id2`)
)
ENGINE=MyISAM;
/*!40000 ALTER TABLE `bad_table` DISABLE KEYS */;
INSERT INTO `bad_table` VALUES
(1,1),
(1,2),
(1,3),
(1,4),
(2,1),
(2,2),
(2,3),
(2,4),
(3,1),
(3,2),
(3,3),
(3,4)
;
/*!40000 ALTER TABLE `bad_table` ENABLE KEYS */;
show index from `bad_table`;
analyze table `bad_table`;
show index from `bad_table`;
Output:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment bad_table 0 PRIMARY 1 id1 A 4 NULL NULL BTREE bad_table 0 PRIMARY 2 id2 A 12 NULL NULL BTREE bad_table 1 idx2 1 id2 A NULL NULL NULL BTREE Table Op Msg_type Msg_text NATCMTS.bad_table analyze status OK Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment bad_table 0 PRIMARY 1 id1 A 3 NULL NULL BTREE bad_table 0 PRIMARY 2 id2 A 12 NULL NULL BTREE bad_table 1 idx2 1 id2 A 4 NULL NULL BTREE
Output of "show variables"
Thank you.
It's an upstream bug (or rather "not a bug") http://bugs.mysql.com/bug.php?id=54031. It was closed with the following comment:
1. disable and enable key commands have nothing to do with primary key cardinality counting, thus there are NULLs;
2. cardinality values in the b/c column of the multi-column index are cardinality values of key(a,b)/(a,b,c) respectively, and they are correct.
Thus, I'm also closing it as not a bug. Please comment with your reasoning if you disagree.
The explanation makes sense, since there is no way to disable primary index. At least that's what documentation says. But if you look at idx2 it is not a primary index. Nor is it a unique index. And it should not be part of the primary index either.
Sorry, when I used your test case, for me it was exactly the first part of the primary key that didn't get the cardinality value; I didn't notice it was different for you.
MariaDB [test]> /*!40000 ALTER TABLE `bad_table` ENABLE KEYS */; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> show index from `bad_table`; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | bad_table | 0 | PRIMARY | 1 | id1 | A | NULL | NULL | NULL | | BTREE | | | | bad_table | 0 | PRIMARY | 2 | id2 | A | 12 | NULL | NULL | | BTREE | | | | bad_table | 1 | idx2 | 1 | id2 | A | 4 | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.01 sec)
I'll give it another shot.
The culprit here is myisam_repair_threads=4 which you have among the settings. With it (or with any value greater than 1) I am getting the same result as you:
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | bad_table | 0 | PRIMARY | 1 | id1 | A | 4 | NULL | NULL | | BTREE | | | | bad_table | 0 | PRIMARY | 2 | id2 | A | 12 | NULL | NULL | | BTREE | | | | bad_table | 1 | idx2 | 1 | id2 | A | NULL | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
With the default myisam_repair_threads=1, the cardinality is missing for the first part of the PK as expected:
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | bad_table | 0 | PRIMARY | 1 | id1 | A | NULL | NULL | NULL | | BTREE | | | | bad_table | 0 | PRIMARY | 2 | id2 | A | 12 | NULL | NULL | | BTREE | | | | bad_table | 1 | idx2 | 1 | id2 | A | 4 | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
It is reproducible on all of 5.1, 5.5, 10.0, as well as MySQL 5.6.
Since it's an upstream bug, normally we would re-file it at bugs.mysql.com according to our standard procedure. Are you willing to do so?
I must say though that I don't believe it's going to be fixed upstream because a) non-critical MyISAM bugs are hardly ever fixed anymore, since the focus has been shifted to InnoDB, and b) according to http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_myisam_repair_threads, multi-threaded repair is still considered beta-quality.
How critical is this problem for you?
I am pretty familiar with this bug. Was hopping after so many years it will get fixed.
You can close this bug if you'd like.
As discussed, I will keep it open, hopefully we'll get to it some day.
I'm also keeping the tag 'upstream' for information, even though the upstream bug was not filed as I don't see much point in doing it.
For me cardinality is populated.
With your test case (with the different table name in ALTER TABLE statements) it happens after analyze table:
If I fix the table name, it happens upon ALTER .. ENABLE KEYS:
MariaDB [test]> ALTER TABLE `bad_table` ENABLE KEYS; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> show index from `bad_table`; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | bad_table | 1 | idx1 | 1 | id1 | A | 3 | NULL | NULL | | BTREE | | | | bad_table | 1 | idx1 | 2 | id2 | A | 12 | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)Please provide the output of your last show index and your cnf file(s) our output of show variables. Thanks.