Comment 4 for bug 331659

Seeing something like this in a 5.5.21 install.

Example, a bunch of queries waiting for this to be released caused a long-semaphore-wait crash:

InnoDB: ###### Diagnostic info printed to the standard error stream
InnoDB: Error: semaphore wait has lasted > 600 seconds
InnoDB: We intentionally crash the server, because it appears to be hung.
120609 23:38:53 InnoDB: Assertion failure in thread 139683626649344 in file srv0srv.c line 2839

---TRANSACTION 42D176FD, ACTIVE 948 sec
mysql tables in use 5, locked 0
, holds adaptive hash latch
MySQL thread id 43, OS thread handle 0x7f0a9d51f700, query id 4619343 10.0.113.13 fppuser Copying to tmp table
SELECT
    SQL_NO_CACHE
    t1.ApplicationUserName,
...

This query returns <20 records, only uses primary keys. Seems to acquire the latch, get the rows immediately I'm sure, and then hang in "Copying to tmp table" for 15 mins as seen here without releasing it.

Full procedure call:

BEGIN
  SELECT
    SQL_NO_CACHE
    t1.ApplicationUserName,
    t1.ApplicationUserId,
    Font,
    FontColour,
    FontSize,
    IsScroll,
    IsSound,
    t2.ResetLockedOutDateTime,
    DateOfBirth,
    Sex,
    COALESCE(t3.MinSavedFileName,t3.MedSavedFileName, t3.MaxSavedFileName, ChatroomUserDefaultImage(t1.Sex)) AS Avatar,
    CAST(COALESCE(IsBlocked, 0) AS UNSIGNED) AS IsBlocked,
    CAST(COALESCE(t2.IsVisible, 1) AS UNSIGNED) AS IsVisible,
    CAST(IF(t4.RoleName = 'Administrator', 1, 0) AS UNSIGNED) AS IsAdministrator,
    t5.ChatroomId,
    t5.EnteredRoomDateTime,
    t5.LastPingedDateTime,
    t5.LastPostedDateTime,
    t5.LastTypedDateTime,
    IF(DATE_ADD(COALESCE(t5.LastTypedDateTime, '1900-01-01'), INTERVAL 2 SECOND) > NOW(), true, false) AS IsTyping,
    IF(DATE_ADD(COALESCE(t5.LastTypedDateTime, '1900-01-01'), INTERVAL 300 SECOND) < NOW(), true, false) AS IsOffline
  FROM
    ApplicationUser t1
  LEFT JOIN
    UserChatroomSettings t2
  ON
    t1.ApplicationUserId = t2.ApplicationUserId
  LEFT JOIN
    Photograph t3
  ON
    t1.DefaultPhotographId = t3.PhotographId
  INNER JOIN
    Role t4
  ON
    t1.DefaultRoleId = t4.RoleId
  INNER JOIN
    ChatroomOnlineUser t5
  ON
    t1.ApplicationUserId = t5.ApplicationUserId
  WHERE
    t5.ChatroomId = IPChatroomId
  ORDER BY
    EnteredRoomDateTime,
    t1.ApplicationUserName ASC;
END

explain:
+----+-------------+-------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+
| 1 | SIMPLE | t5 | ref | PRIMARY | PRIMARY | 4 | const | 19 | Using temporary; Using filesort |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | dating.t5.ApplicationUserId | 1 | |
| 1 | SIMPLE | t4 | eq_ref | PRIMARY | PRIMARY | 4 | dating.t1.DefaultRoleId | 1 | Using where |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | dating.t1.ApplicationUserId | 1 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY | PRIMARY | 4 | dating.t1.DefaultPhotographId | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------------+------+---------------------------------+

mysql> show variables like 'version%';
+-------------------------+------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------+
| version | 5.5.21-55-log |
| version_comment | Percona Server (GPL), Release 25.0 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------------+