[MDEV-5109] SLEEP() inside complex SELECT hangs indefinitely Created: 2013-10-08  Updated: 2013-11-11  Due: 2013-11-08  Resolved: 2013-11-11

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.33
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Dorian Assignee: Elena Stepanova
Resolution: Cannot Reproduce Votes: 0
Labels: None
Environment:

VMware Workstation, Fedora Core 19 vanilla install
mysql Ver 15.1 Distrib 5.5.32-MariaDB, for Linux (x86_64) using readline 5.1


 Description    

SELECT SLEEP(4), ID FROM Region;

The above statement hangs indefinitely instead of 4 seconds like intended.
EXPLAIN also doesn't show too much information.

EXPLAIN SELECT SLEEP(4), ID FROM Region;
 
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
 
| id   | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
 
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
 
|    1 | SIMPLE      | Region | index | NULL          | PRIMARY | 4       | NULL |    1 | Using index |
 
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
 
 
 
SHOW CREATE TABLE Region output:
 
 
 
CREATE TABLE `Region` (
 
  `ID` int(11) NOT NULL AUTO_INCREMENT,
 
  `Name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
 
  PRIMARY KEY (`ID`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci



 Comments   
Comment by Elena Stepanova [ 2013-10-08 ]

Hi,

Do you mean that it hangs on an empty table?
Could you please run SHOW FULL PROCESSLIST from a different connection while the query hangs?
Thanks.

Comment by Dorian [ 2013-10-08 ]

Hi,

I tried both empty and non-empty tables and views. They all hang.

I'll run that later tonight, but I'm sure you can also reproduce it.

-Dorian

Sent from my iPhone

Comment by Elena Stepanova [ 2013-10-08 ]

Actually I can't, that's why I'm asking. It looks pretty consistent to me, SELECT on an empty table returns immediately, on a 1-row table it takes 4 sec, on 2-row table it takes 8 sec, etc.

MariaDB [test]> select count from Region;
----------

count

----------

0

----------
1 row in set (0.00 sec)

MariaDB [test]> SELECT SLEEP(4), ID FROM Region;
Empty set (0.01 sec)

MariaDB [test]> insert into Region (Name) Values ('Region1');
Query OK, 1 row affected (0.03 sec)

MariaDB [test]> SELECT SLEEP(4), ID FROM Region;
------------+

SLEEP(4) ID

------------+

0 3

------------+
1 row in set (4.01 sec)

MariaDB [test]> insert into Region (Name) Values ('Region2');
Query OK, 1 row affected (0.04 sec)

MariaDB [test]> SELECT SLEEP(4), ID FROM Region;
------------+

SLEEP(4) ID

------------+

0 3
0 4

------------+
2 rows in set (8.00 sec)

MariaDB [test]> show create table Region;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table Create Table

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Region CREATE TABLE `Region` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Comment by Elena Stepanova [ 2013-11-11 ]

Closing for now according to the previous comment. If you have more information, please comment to re-open the bug.

Generated at Thu Feb 08 07:01:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.