Emty SELECT with index use

[Andrew Okhmat]

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.

[Raghavendra D Prabhu (raghavendra-prabhu)]

@Andrew,

Do you see the same issue (and/or replicate this) with Percona Server?

[Andrew Okhmat (n-andy-y)]

@Raghavendra

We didn't make test on Percona Server. What kind version Percona Server we can use for tests?

[Raghavendra D Prabhu (raghavendra-prabhu)]

@Andrew,

The versions of Percona Server that you had issue with PXC, and also the latest. This is mostly an issue in PS or upstream MySQL, since indexes are kept pretty much as is in PXC.

[Andrew Okhmat (n-andy-y)]

@Raghavendra
Percona-Server-server-55-5.5.40 - not working
Percona-Server-server-55-5.5.37 - not working
Percona-Server-server-55-5.5.34 - working

[Valerii Kravchuk (valerii-kravchuk)]

Not specific to PXC, so let's consider this a PS (and maybe upstream MySQL) bug.

[Nilnandan Joshi (nilnandan-joshi)]

Hi Andrew,

I tried but unable to reproduce the same with PS 5.5.40 + partition table with hash + merge index. There is known bug related to this scenario but as per the comment, it should resolved in 5.5.36+ versions. http://bugs.mysql.com/bug.php?id=70588

I also found this bug related to intersect but I'm not sure it relates to your issue. http://bugs.mysql.com/bug.php?id=72745
Is it possible for you to provide test data which we can use to reproduce this? Also can you try to check this with PS 5.5.40?

My test:

mysql> 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 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED /*!50100 PARTITION BY HASH (record_id) PARTITIONS 10 */;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into goods_comments(text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html) values ('abcdeef',now(), 123455677,1,123,'nilnandan','<email address hidden>',12345,2,4,5,'sdfsdfsdf','sdfsdf','sdfsdf',123,123,1,23,34,34,'asdasd',now(),5,6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into goods_comments(text,created,ip,status,record_id,name,email,user_id,mark,rating,count_voices,summary,dignity,shortcomings,parent_comment_id,subscribed_on_replies,positive_vote_count,negative_vote_count,percent_dignity,points,service_info,date_approved,from_buyer,allow_html) values ('abcdeef',now(), 123455677,1,123,'nilnandan','<email address hidden>',14565,2,4,5,'sdfsdfsdf','sdfsdf','sdfsdf',123,123,1,23,34,34,'asdasd',now(),5,6);
Query OK, 1 row affected (0....

[Andrew Okhmat (n-andy-y)]

You can test with original table data https://drive.google.com/file/d/0B01KodGVnAgBVHlsbjhxWm12Qlk/view?usp=sharing

[Nilnandan Joshi (nilnandan-joshi)]

Verified on PS 5.5.40 with given table data.

nilnandan@desktop:~/sandboxes/msb_5_5_40$ mysql -uroot -p --socket=/tmp/mysql_sandbox5540.sock
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
nilnandan@desktop:~/sandboxes/msb_5_5_40$ mysql -uroot -p --socket=/tmp/mysql_sandbox5540.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.40-36.1-log Percona Server (GPL), Release 36.1, Revision 707

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> source /home/nilnandan/Downloads/goods_comments.sql
..
mysql> show create table goods_comments \G
*************************** 1. row ***************************
       Table: goods_comments
Create Table: CREATE TABLE `goods_comments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `text` text NOT NULL COMMENT 'Текст',
  `created` datetime NOT NULL COMMENT 'Дата создания',
  `ip` int(11) unsigned NOT NULL COMMENT 'ip',
  `status` tinyint(3) unsigned NOT NULL COMMENT 'Статус',
  `record_id` mediumint(8) unsigned NOT NULL,
  `name` varchar(64) NOT NULL COMMENT 'Имя',
  `email` varchar(64) NOT NULL COMMENT 'Электронная почта',
  `user_id` mediumint(8) unsigned NOT NULL,
  `mark` tinyint(3) unsigned DEFAULT NULL COMMENT 'Оценка товара',
  `rating` smallint(5) NOT NULL COMMENT 'Рейтинг коментария',
  `count_voices` smallint(5) unsigned NOT NULL COMMENT 'Общее количество голосов коментария',
  `summary` varchar(255) NOT NULL COMMENT 'Краткое содержание',
  `dignity` text NOT NULL COMMENT 'Плюсы',
  `shortcomings` text NOT NULL COMMENT 'Минусы',
  `parent_comment_id` int(11) unsigned NOT NULL,
  `subscribed_on_replies` tinyint(3) unsigned NOT NULL COMMENT 'Подписан на ответы',
  `positive_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'Количество положительных отзывов',
  `negative_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'Количество отрицательных отзывов',
  `percent_dignity` tinyint(3) unsigned NOT NULL COMMENT 'Полезность комментария',
  `points` smallint(5) NOT NULL COMMENT 'Количество положительных баллов',
  `service_info` varchar(1024) NOT NULL COMMENT 'Cлужебная информация',
  `date_approved` datetime NOT NULL COMMENT 'Дата одобрения',
  `from_buyer` tinyint(3) unsigned NOT NULL COMMENT 'От купившего',
  `allow_html` tinyint(3) unsigned NOT NULL COMMENT 'Позволять html-теги',
  `favorite_for_author` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Избранный для автора',
  `is_answers_readed` tinyint(3) unsigned DEFAULT '0' COMMENT 'Ответы, добавленные после этого коммента прочитанны',
  `last_readed_answer_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Дата последнего прочитанного комментария',
  PRIMARY KEY (`record_id`,`id`),
  KEY `id_index` (`id`),
  KEY `mark_index` (`mark`),
  KEY `parent_comment_id_index` (`par...

[Nilnandan Joshi (nilnandan-joshi)]

Above problem is solved in PS 5.6.22. Unable to reproduce it.

mysql> show create table goods_comments \G
*************************** 1. row ***************************
       Table: goods_comments
Create Table: CREATE TABLE `goods_comments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `text` text NOT NULL COMMENT 'Текст',
  `created` datetime NOT NULL COMMENT 'Дата создания',
  `ip` int(11) unsigned NOT NULL COMMENT 'ip',
  `status` tinyint(3) unsigned NOT NULL COMMENT 'Статус',
  `record_id` mediumint(8) unsigned NOT NULL,
  `name` varchar(64) NOT NULL COMMENT 'Имя',
  `email` varchar(64) NOT NULL COMMENT 'Электронная почта',
  `user_id` mediumint(8) unsigned NOT NULL,
  `mark` tinyint(3) unsigned DEFAULT NULL COMMENT 'Оценка товара',
  `rating` smallint(5) NOT NULL COMMENT 'Рейтинг коментария',
  `count_voices` smallint(5) unsigned NOT NULL COMMENT 'Общее количество голосов коментария',
  `summary` varchar(255) NOT NULL COMMENT 'Краткое содержание',
  `dignity` text NOT NULL COMMENT 'Плюсы',
  `shortcomings` text NOT NULL COMMENT 'Минусы',
  `parent_comment_id` int(11) unsigned NOT NULL,
  `subscribed_on_replies` tinyint(3) unsigned NOT NULL COMMENT 'Подписан на ответы',
  `positive_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'Количество положительных отзывов',
  `negative_vote_count` mediumint(8) unsigned NOT NULL COMMENT 'Количество отрицательных отзывов',
  `percent_dignity` tinyint(3) unsigned NOT NULL COMMENT 'Полезность комментария',
  `points` smallint(5) NOT NULL COMMENT 'Количество положительных баллов',
  `service_info` varchar(1024) NOT NULL COMMENT 'Cлужебная информация',
  `date_approved` datetime NOT NULL COMMENT 'Дата одобрения',
  `from_buyer` tinyint(3) unsigned NOT NULL COMMENT 'От купившего',
  `allow_html` tinyint(3) unsigned NOT NULL COMMENT 'Позволять html-теги',
  `favorite_for_author` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'Избранный для автора',
  `is_answers_readed` tinyint(3) unsigned DEFAULT '0' COMMENT 'Ответы, добавленные после этого коммента прочитанны',
  `last_readed_answer_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Дата последнего прочитанного комментария',
  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`),
  KEY `favorite_for_author_index` (`favorite_for_author`),
  KEY `user_id_status_index` (`user_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=7519748 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
/*!50100 PARTITION BY HASH (record_id)
PARTITIONS 10 */
1 row in set (0.00 sec)

mysql>
mysql> select count(*) from goods_comments;
+----------+
| count(*) |
+----------+
| 2686398 |
+----------+
1 row in set (0.57 sec)

mysql> SELECT id, user_id FROM goods_comments WHERE user_id=197827 AND id=5273769;
+---------+---------+
| id | user_id |
+---------+---------+
| 5273769 | 197827 |
+---------+---------+
1 row in set (0.01 sec)

mysql> explain SELECT id, user_id FROM goods_comments WHERE user_id=197827 AND id=5273769;
+----+-------------+----------------+...

[Laurynas Biveinis (laurynas-biveinis)]

What about upstream 5.5?

[Nilnandan Joshi (nilnandan-joshi)]

No, its not in upstream. Unable to reproduce with MySQL 5.5.40.

nilnandan.joshi@bm-support01:~$ mysql -uroot -p --socket=/tmp/mysql_sandbox5540.sock
Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead.
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.40 MySQL Community Server (GPL)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> select count(*) from goods_comments;
+----------+
| count(*) |
+----------+
| 2686398 |
+----------+
1 row in set (1.71 sec)

mysql> SELECT id, user_id FROM goods_comments WHERE user_id=197827 AND id=5273769;
+---------+---------+
| id | user_id |
+---------+---------+
| 5273769 | 197827 |
+---------+---------+
1 row in set (0.00 sec)

mysql> explain SELECT id, user_id FROM goods_comments WHERE user_id=197827 AND id=5273769;
+----+-------------+----------------+------+---------------------------------------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------------------------------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | goods_comments | ref | id_index,user_id_index,user_id_status_index | id_index | 4 | const | 8 | Using where |
+----+-------------+----------------+------+---------------------------------------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql>

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3245