Bug #55821 MySQL Hangs in state: statistics
Submitted: 7 Aug 2010 15:44 Modified: 8 Aug 2010 12:43
Reporter: Piotr S Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.45 OS:Linux (Fedora Core 7)
Assigned to: CPU Architecture:Any
Tags: For Update, hangs, SELECT, statistics state

[7 Aug 2010 15:44] Piotr S
I'd like to report following problem:
while executing query (SELECT .. FOR UPDATE) in transaction, the mysql process goes into 'statistics' state in which it hangs. Query is killed after some time, there is no lock waiting. I've updated MySQL to 5.0.45 revision (using fc7 repository), previous version was probably 5.0.12.

# Time: 
# User@Host: 
# Query_time: 51  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use www_extra;
SELECT * FROM `www_extra`.`restaurant_dates`
                WHERE idx_edition = 'T10_10_01'
                AND id = 2
                FOR UPDATE;

mysql> show processlist;
| Query   |    33 | statistics        | SELECT * FROM `www_extra`.`restaurant_dates`
                WHERE idx_edition = 'T10_10_01'
                AND id = 2
                FOR UPDATE |

mysql> show create table restaurant_dates;
| restaurant_dates | CREATE TABLE `restaurant_dates` (
  `id` int(16) unsigned NOT NULL auto_increment,
  `idx_edition` varchar(10) NOT NULL,
  `meeting_date` datetime NOT NULL,
  `end_meeting_date` datetime NOT NULL,
  `tables_count` int(16) NOT NULL,
  `persons_at_table` int(16) NOT NULL,
  `current_max` int(11) NOT NULL,
  PRIMARY KEY  (`id`)

no foreign keys, no joins, just simple relation.

I found another problems (http://bugs.mysql.com/bug.php?id=26339 and http://bugs.mysql.com/bug.php?id=20932) which looks like related, but no answer found in there.

Executed on 64bit.

[]# uname -a
Linux 2.6.21-1.3228.fc7 #1 SMP Tue Jun 12 14:56:37 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux

How to repeat:
I was executing consecutive (not parallel) queries in separate transactions. Sometimes, I was in the same transaction updating selected row, sometimes not. Transaction was always finished with commit (if update performed) or rollback (if not).


SELECT * FROM `www_extra`.`restaurant_dates`
                WHERE idx_edition = 'T10_10_01'
                AND id = 2
                FOR UPDATE;
UPDATE `www_extra`.`restaurant_date` SET `current_max` = 3 WHERE `id` = 2;

SELECT * FROM `www_extra`.`restaurant_dates`
                WHERE idx_edition = 'T10_10_01'
                AND id = 2
                FOR UPDATE;

[8 Aug 2010 7:25] Valeriy Kravchuk
Please, check with a newer version, 5.0.91 (MySQL binaries if possible). In case of the same problem, please, send the results of:

show table status like 'restaurant_dates'\G

show innodb status\G
[8 Aug 2010 12:43] Piotr S
Well - in my case it was the lock problem. 
When select for update selected none rows, I sometimes forget to rollback transaction, and another select was hanging.. 

Nevertheless - state "statistics" was so misleading, that I didn't dig through my code very deeply, despite the fact, that my first thought was "lock"... :)