Bug #35473 All select processes are in stat "Waiting for table"
Submitted: 21 Mar 2008 6:29 Modified: 27 Apr 2008 7:58
Reporter: Anand Gupta Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.22-max-log OS:Linux (Linux 2.6.9-42.ELsmp #1 SMP Wed Jul 12 23:27:17 EDT 2006 i686 athlon i386 GNU/Linux)
Assigned to: CPU Architecture:Any
Tags: hang, locking, waiting

[21 Mar 2008 6:29] Anand Gupta
Description:
My server was running fine but sometimes it runs out of connections even I increased number of connections to 1000. Show full processlist shows all processes waiting for table on a particular table. I can't shutdown the server cleanly so I have to kill mysqld process on OS level and reboot the server. After reboot it runs fine for couple days and then again we have same kinda situation. It happens on different tables, so it is not problem with particular table or storage engine. There is nothing in errorlog or command log. In this situation, dump database also hangs. After this situations I see lot of entries in slow log. Sample of processlist is here:

| Id    | User     | Host                       | db         | Command     | Time   | State                                                          | Info

                                                                                                                                           |
+-------+----------+----------------------------+------------+-------------+--------+----------------------------------------------------------------+--------
--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------

| 50946 | portal   | web3w16m3:48094 | liferayDB  | Prepare     |   3244 | Waiting for table                                              | SELECT
Id, AlertTypeId, Subject, ReceivedTs, State, TargetURL, ExtRefId FROM ET_CorpAlert WHERE SentTo = ? AND State IN (1, 2) ORDER BY ReceivedTs DESC limit ?

                                                                                                                                           |
| 50948 | portal   | web2w11m3:60754 | liferayDB  | Prepare     |   3241 | Waiting for table                                              | SELECT
Id, AlertTypeId, Subject, ReceivedTs, State, TargetURL, ExtRefId FROM ET_CorpAlert WHERE SentTo = ? AND State IN (1, 2) ORDER BY ReceivedTs DESC limit ?

                                                                                                                                           |
| 50949 | portal   | web2w11m3:60757 | liferayDB  | Prepare     |   3240 | Waiting for table                                              | SELECT
Id, AlertTypeId, Subject, ReceivedTs, State, TargetURL, ExtRefId FROM ET_CorpAlert WHERE SentTo = ? AND State IN (1, 2) ORDER BY ReceivedTs DESC limit ?

                                                                                                                                           |
| 50951 | portal   | web2w11m3:60759 | liferayDB  | Prepare     |   3237 | Waiting for table                                              | SELECT
Id, AlertTypeId, Subject, ReceivedTs, State, TargetURL, ExtRefId FROM ET_CorpAlert WHERE SentTo = ? AND State IN (1, 2) ORDER BY ReceivedTs DESC limit ?

                                                                                                                                           |
| 50952 | portal   | web2w11m3:60763 | liferayDB  | Prepare     |   3232 | Waiting for table                                              | SELECT
Id, AlertTypeId, Subject, ReceivedTs, State, TargetURL, ExtRefId FROM ET_CorpAlert WHERE SentTo = ? AND State IN (1, 2) ORDER BY ReceivedTs DESC limit ?

How to repeat:
Don't know yet.

Suggested fix:
It looks like a bug. Table is being locked by some other process but we can't find what is locking the table.
[21 Mar 2008 6:35] Valeriy Kravchuk
Please, send the results of:

show create table ET_CorpAlert;
[21 Mar 2008 15:35] Anand Gupta
mysql> show create table ET_CorpAlert;
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ET_CorpAlert | CREATE TABLE `ET_CorpAlert` (
  `Id` bigint(20) NOT NULL auto_increment COMMENT 'Unique Id for Each Alert',
  `AlertTypeId` int(11) NOT NULL COMMENT 'Type of alert (Refer ET_CorpAlertType)',
  `Subject` varchar(255) default NULL COMMENT 'Subject text of the Alert',
  `Body` text COMMENT 'BodyText of the alert',
  `ReceivedTs` datetime NOT NULL COMMENT 'When the alert was received',
  `SentTs` datetime default NULL COMMENT 'When the alert was sent',
  `ReadTs` datetime default NULL COMMENT 'When the alert was read by the user',
  `SentBy` varchar(255) default NULL COMMENT 'Sent by user id',
  `SentTo` varchar(255) NOT NULL COMMENT 'Sent to UserId',
  `State` smallint(6) NOT NULL COMMENT 'State of the alert (1-Unread, 2-Read,3-Deleted)',
  `BodyMimeType` int(11) default NULL COMMENT 'Body mime type',
  `CreateTs` datetime NOT NULL COMMENT 'Standard Col',
  `ChangeTs` datetime NOT NULL COMMENT 'Standard Col',
  `CreateBy` varchar(255) default NULL COMMENT 'Standard Col',
  `ChangeBy` varchar(255) default NULL COMMENT 'Standard Col',
  `Host` varchar(255) default NULL COMMENT 'Standard Col',
  `TextBody` text,
  `TargetURL` varchar(1200) default NULL,
  `ExtRefId` varchar(255) default NULL,
  PRIMARY KEY  (`Id`),
  KEY `FK_ET_CorpAlert` (`AlertTypeId`),
  KEY `SentTo` (`SentTo`),
  KEY `ReceivedTs` (`ReceivedTs`),
  CONSTRAINT `ET_CorpAlert_ibfk_1` FOREIGN KEY (`AlertTypeId`) REFERENCES `ET_CorpAlertType` (`AlertTypeId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='Transaction Table for CorpAlerts' |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[22 Mar 2008 7:57] Valeriy Kravchuk
So, this is InnoDB table. Please, upload to the issue

SHOW INNODB STATUS\G

results next time you'll see this problem.
[22 Mar 2008 7:58] Sveta Smirnova
Thank you for the feedback.

Version 5.0.22 is quite old. Please upgrade to current version 5.0.51a, try with it and if problem still exists run SHOW ENGINE INNODB STATUS in time when this happens next time and provide us output of this command.
[25 Mar 2008 21:56] Anand Gupta
It doesn't happen only on InnoDB table. It happened on MyISAM table too.

Here is the DDL for MyIsam Table where we had the same issue:

mysql> show create table ET_IPPhoneDeviceList;
+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IPPhoneDeviceList | CREATE TABLE `IPPhoneDeviceList` (
  `DeviceTAttr` varchar(255) default NULL,
  `MACAddress` varchar(255) NOT NULL,
  `Description` varchar(255) default NULL,
  `DeviceCAttr` varchar(255) default NULL,
  `DevicePAttr` varchar(255) default NULL,
  `IPAddress` varchar(255) default NULL,
  `DeviceSAttr` varchar(255) default NULL,
  `Status` int(11) NOT NULL,
  `CreateTs` datetime default NULL,
  `CreateBy` varchar(32) default NULL,
  `ChangeTs` datetime default NULL,
  `ChangeBy` varchar(32) default NULL,
  `Host` varchar(255) default NULL,
  `CMAddress` varchar(255) default NULL,
  `Extn` varchar(255) default NULL,
  PRIMARY KEY  (`MACAddress`,`Status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[26 Mar 2008 8:50] Susanne Ebrecht
We still need to know if you will get this issues by using newer version. Our newest version is MySQL 5.0.51a.
[26 Apr 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[27 Apr 2008 7:58] Sveta Smirnova
Thank you for the feedback.

SHOW ENGINE INNODB STATUS shows there is DEADLOCK and shows locked table is liferayDB/ET_CorpAlert which is in output of SHOW PROCESSLIST too. So I close this report as "Not a Bug". Please read at http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html about How to Cope with Deadlocks