Emty SELECT with index use
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS |
Invalid
|
Undecided
|
Unassigned | ||
5.5 |
Triaged
|
High
|
Unassigned | ||
5.6 |
Invalid
|
Undecided
|
Unassigned |
Bug 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,
+----+-
-------
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_
`subscribed_
`positive_
`negative_
`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_
KEY `status_index` (`status`),
KEY `user_id_index` (`user_id`),
KEY `record_id_index` (`record_id`)
) ENGINE=InnoDB AUTO_INCREMENT=
/*!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.
description: | updated |
@Andrew,
Do you see the same issue (and/or replicate this) with Percona Server?