| 2014-12-23 16:18:59 |
Andrew Okhmat |
description |
After the upgrade our Percona XtraDB Cluster version 5.5.34-23.7.6 to version 5.5.37-25.10 we encountered the following problem.
Some SELECTs do not work properly while using indexes. If we ignore an index we get a correct result.
SELECT with indexes using:
SELECT id, user_id FROM goods_comments WHERE user_id=197827 AND id=5273769;
Empty SET (0,00 sec)
EXPLAIN:
+----+-------------+----------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------------------+
| 1 | SIMPLE | goods_comments | index_merge | id_index,user_id_index | id_index,user_id_index | 4,3 | NULL | 1 | 100.00 | Using intersect(id_index,user_id_index); Using where; Using index |
+----+-------------+----------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------------------+
------------------------------------------------------------------------------------
if SELECT without using indexes:
SELECT id, user_id FROM goods_comments IGNORE INDEX (user_id_ind) WHERE user_id=197827 AND id=5273769;
+---------+---------+
| id | user_id |
+---------+---------+
| 5273769 | 197827 |
+---------+---------+
1 ROW IN SET (0,00 sec)
EXPLAIN:
+----+-------------+----------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | goods_comments | ref | id_index | id_index | 4 | const | 10 | 100.00 | Using where |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+----------+-------------+
------------------------------------------------------------------------------------
Table structure:
| goods_comments | CREATE TABLE `goods_comments` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`text` text NOT NULL COMMENT 'x',
`created` datetime NOT NULL COMMENT 'x',
`ip` int(11) unsigned NOT NULL COMMENT 'x',
`status` tinyint(3) unsigned NOT NULL COMMENT 'x',
`record_id` mediumint(8) unsigned NOT NULL,
`name` varchar(64) NOT NULL COMMENT 'x',
`email` varchar(64) NOT NULL COMMENT 'x',
`user_id` mediumint(8) unsigned NOT NULL,
`mark` tinyint(3) unsigned DEFAULT NULL COMMENT 'x',
`rating` smallint(5) NOT NULL COMMENT 'x',
`count_voices` smallint(5) unsigned NOT NULL COMMENT 'x',
`summary` varchar(255) NOT NULL COMMENT 'x',
`dignity` text NOT NULL COMMENT 'x',
`shortcomings` text NOT NULL COMMENT 'x',
`parent_comment_id` int(11) unsigned NOT NULL,
`subscribed_on_replies` tinyint(3) unsigned NOT NULL COMMENT 'x',
`positive_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'x',
`negative_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'x',
`percent_dignity` tinyint(3) unsigned NOT NULL COMMENT 'x',
`points` smallint(5) NOT NULL COMMENT 'x',
`service_info` varchar(1024) NOT NULL COMMENT 'x',
`date_approved` datetime NOT NULL COMMENT 'x',
`from_buyer` tinyint(3) unsigned NOT NULL COMMENT 'x',
`allow_html` tinyint(3) unsigned NOT NULL COMMENT 'x',
PRIMARY KEY (`record_id`,`id`),
KEY `id_index` (`id`),
KEY `mark_index` (`mark`),
KEY `parent_comment_id_index` (`parent_comment_id`),
KEY `status_index` (`status`),
KEY `user_id_index` (`user_id`),
KEY `record_id_index` (`record_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7002593 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (record_id)
PARTITIONS 10 */ |
If we downgrade the server down to version 5.5.34-23.7.6, everything works properly. |
After the upgrade our Percona XtraDB Cluster version 5.5.34-23.7.6 to version 5.5.37-25.10 we encountered the following problem.
Some SELECTs do not work properly while using indexes. If we ignore an index we get a correct result.
SELECT with indexes using:
SELECT id, user_id FROM goods_comments WHERE user_id=197827 AND id=5273769;
Empty SET (0,00 sec)
EXPLAIN:
+----+-------------+----------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------------------+
| 1 | SIMPLE | goods_comments | index_merge | id_index,user_id_index | id_index,user_id_index | 4,3 | NULL | 1 | 100.00 | Using intersect(id_index,user_id_index); Using where; Using index |
+----+-------------+----------------+-------------+------------------------+------------------------+---------+------+------+----------+-------------------------------------------------------------------+
-----------------------------------------------------------------------------
if SELECT without using indexes:
SELECT id, user_id FROM goods_comments IGNORE INDEX (user_id_ind) WHERE user_id=197827 AND id=5273769;
+---------+---------+
| id | user_id |
+---------+---------+
| 5273769 | 197827 |
+---------+---------+
1 ROW IN SET (0,00 sec)
EXPLAIN:
+----+-------------+----------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | goods_comments | ref | id_index | id_index | 4 | const | 10 | 100.00 | Using where |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+----------+-------------+
------------------------------------------------------------------------------------
Table structure:
| goods_comments | CREATE TABLE `goods_comments` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`text` text NOT NULL COMMENT 'x',
`created` datetime NOT NULL COMMENT 'x',
`ip` int(11) unsigned NOT NULL COMMENT 'x',
`status` tinyint(3) unsigned NOT NULL COMMENT 'x',
`record_id` mediumint(8) unsigned NOT NULL,
`name` varchar(64) NOT NULL COMMENT 'x',
`email` varchar(64) NOT NULL COMMENT 'x',
`user_id` mediumint(8) unsigned NOT NULL,
`mark` tinyint(3) unsigned DEFAULT NULL COMMENT 'x',
`rating` smallint(5) NOT NULL COMMENT 'x',
`count_voices` smallint(5) unsigned NOT NULL COMMENT 'x',
`summary` varchar(255) NOT NULL COMMENT 'x',
`dignity` text NOT NULL COMMENT 'x',
`shortcomings` text NOT NULL COMMENT 'x',
`parent_comment_id` int(11) unsigned NOT NULL,
`subscribed_on_replies` tinyint(3) unsigned NOT NULL COMMENT 'x',
`positive_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'x',
`negative_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'x',
`percent_dignity` tinyint(3) unsigned NOT NULL COMMENT 'x',
`points` smallint(5) NOT NULL COMMENT 'x',
`service_info` varchar(1024) NOT NULL COMMENT 'x',
`date_approved` datetime NOT NULL COMMENT 'x',
`from_buyer` tinyint(3) unsigned NOT NULL COMMENT 'x',
`allow_html` tinyint(3) unsigned NOT NULL COMMENT 'x',
PRIMARY KEY (`record_id`,`id`),
KEY `id_index` (`id`),
KEY `mark_index` (`mark`),
KEY `parent_comment_id_index` (`parent_comment_id`),
KEY `status_index` (`status`),
KEY `user_id_index` (`user_id`),
KEY `record_id_index` (`record_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7002593 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (record_id)
PARTITIONS 10 */ |
If we downgrade the server down to version 5.5.34-23.7.6, everything works properly. |
|
