Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.0.14, 10.0(EOL), 10.1(EOL)
-
None
-
Linux 64-bit
Description
Server hangs after select count(distinct name) from t2 where a=8366 and b>=5 and b<=5;
How to repeat:
create table t2 (a smallint(6) not null, b int(10) not null, name varchar(20), primary key(a,b), key(name)) engine=InnoDB; |
insert into t2 values (8355,3,"sanja"),(8355,4,"wlad"),(8366,5, "lawrin"),(8366,6,"markusjm"); |
select count(distinct name) from t2 where a=8366 and b>=5 and b<=5; |
I was able to reproduce it also on 10.1 and 10.2 (debug)
Attachments
Activity
Not sure if this is important, but I see no problem on Percona Server 5.7:
[openxs@centos ~]$ mysql -uroot test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 3
|
Server version: 5.7.10-3-log Percona Server (GPL), Release 3, Revision 63dafaf
|
|
|
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
|
Copyright (c) 2000, 2015, 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> drop table if exists t2; |
Query OK, 0 rows affected (0.13 sec) |
|
|
mysql> create table t2 (a smallint(6) not null, b int(10) not null, name varchar(20), primary key(a,b), key(name)) engine=InnoDB; |
Query OK, 0 rows affected (0.18 sec) |
|
|
mysql>
|
mysql> insert into t2 values (8355,3,"sanja"),(8355,4,"wlad"),(8366,5, "lawrin"),(8366,6,"markusjm"); |
Query OK, 4 rows affected (0.07 sec) |
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
mysql> select count(distinct name) from t2 where a=8366 and b>=5 and b<=5; |
+----------------------+ |
| count(distinct name) | |
+----------------------+ |
| 1 |
|
+----------------------+ |
1 row in set (0.06 sec) |
|
|
mysql> explain select count(distinct name) from t2 where a=8366 and b>=5 and b<=5; |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------+ |
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------+ |
| 1 | SIMPLE | t2 | NULL | range | PRIMARY,name | name | 29 | NULL | 5 | 100.00 | Using where; Using index for group-by (scanning) | |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------+ |
1 row in set, 1 warning (0.01 sec) |
No problem with Percona Server 5.6.28 as well:
[openxs@centos p5.6]$ bin/mysql -uroot test
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 1
|
Server version: 5.6.28-76.1 MySQL Community Server (GPL)
|
|
|
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
|
Copyright (c) 2000, 2015, 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> drop table if exists t2; |
Query OK, 0 rows affected, 1 warning (0.00 sec) |
|
|
mysql> create table t2 (a smallint(6) not null, b int(10) not null, name varchar(20), primary key(a,b), key(name)) engine=InnoDB; |
Query OK, 0 rows affected (0.13 sec) |
|
|
mysql> insert into t2 values (8355,3,"sanja"),(8355,4,"wlad"),(8366,5, "lawrin"),(8366,6,"markusjm"); |
Query OK, 4 rows affected (0.02 sec) |
Records: 4 Duplicates: 0 Warnings: 0
|
|
|
mysql> explain select count(distinct name) from t2 where a=8366 and b>=5 and b<=5; |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+ |
| 1 | SIMPLE | t2 | range | PRIMARY,name | name | 29 | NULL | 5 | Using where; Using index for group-by (scanning) | |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------------------------------+ |
1 row in set (0.00 sec) |
|
|
mysql> select count(distinct name) from t2 where a=8366 and b>=5 and b<=5; |
+----------------------+ |
| count(distinct name) | |
+----------------------+ |
| 1 |
|
+----------------------+ |
1 row in set (0.00 sec) |
commit 8103526b38391e41be4b410b52eed9ff1c2ea698
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Fri Mar 11 13:36:29 2016 +0200
MDEV-9667: Server hangs after select count(distinct name) from t2 where a=8366 and b>=5 and b<=5;
In row_search_for_mysql function on XtraDB there was a old logic
where null bytes were inited. This caused server to think that
key value is null and continue on incorrect path.
All 'magic' of engine calls happens in QUICK_GROUP_MIN_MAX_SELECT::get_next better start there.