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
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 ApplicationUser Name,
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.
...
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 ApplicationUser Name, ApplicationUser Id, ResetLockedOutD ateTime, t3.MinSavedFile Name,t3. MedSavedFileNam e, t3.MaxSavedFile Name, ChatroomUserDef aultImage( t1.Sex) ) AS Avatar, COALESCE( IsBlocked, 0) AS UNSIGNED) AS IsBlocked, COALESCE( t2.IsVisible, 1) AS UNSIGNED) AS IsVisible, IF(t4.RoleName = 'Administrator', 1, 0) AS UNSIGNED) AS IsAdministrator, EnteredRoomDate Time, LastPingedDateT ime, LastPostedDateT ime, LastTypedDateTi me, DATE_ADD( COALESCE( t5.LastTypedDat eTime, '1900-01-01'), INTERVAL 2 SECOND) > NOW(), true, false) AS IsTyping, DATE_ADD( COALESCE( t5.LastTypedDat eTime, '1900-01-01'), INTERVAL 300 SECOND) < NOW(), true, false) AS IsOffline mSettings t2 ApplicationUser Id = t2.ApplicationU serId DefaultPhotogra phId = t3.PhotographId DefaultRoleId = t4.RoleId ineUser t5 ApplicationUser Id = t5.ApplicationU serId DateTime, ApplicationUser Name ASC;
SELECT
SQL_NO_CACHE
t1.
t1.
Font,
FontColour,
FontSize,
IsScroll,
IsSound,
t2.
DateOfBirth,
Sex,
COALESCE(
CAST(
CAST(
CAST(
t5.ChatroomId,
t5.
t5.
t5.
t5.
IF(
IF(
FROM
ApplicationUser t1
LEFT JOIN
UserChatroo
ON
t1.
LEFT JOIN
Photograph t3
ON
t1.
INNER JOIN
Role t4
ON
t1.
INNER JOIN
ChatroomOnl
ON
t1.
WHERE
t5.ChatroomId = IPChatroomId
ORDER BY
EnteredRoom
t1.
END
explain: ------- -----+- ------+ ------- -+----- ------- ---+--- ------+ ------- --+---- ------- ------- ------- ------+ ------+ ------- ------- ------- ------- -----+ ------- -----+- ------+ ------- -+----- ------- ---+--- ------+ ------- --+---- ------- ------- ------- ------+ ------+ ------- ------- ------- ------- -----+ t5.ApplicationU serId | 1 | | t1.DefaultRoleI d | 1 | Using where | t1.ApplicationU serId | 1 | | t1.DefaultPhoto graphId | 1 | | ------- -----+- ------+ ------- -+----- ------- ---+--- ------+ ------- --+---- ------- ------- ------- ------+ ------+ ------- ------- ------- ------- -----+
+----+-
| 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.
| 1 | SIMPLE | t4 | eq_ref | PRIMARY | PRIMARY | 4 | dating.
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | dating.
| 1 | SIMPLE | t3 | eq_ref | PRIMARY | PRIMARY | 4 | dating.
+----+-
mysql> show variables like 'version%'; ------- ------- -----+- ------- ------- ------- ------- ------- + ------- ------- -----+- ------- ------- ------- ------- ------- + compile_ machine | x86_64 | ------- ------- -----+- ------- ------- ------- ------- ------- +
+------
| Variable_name | Value |
+------
| version | 5.5.21-55-log |
| version_comment | Percona Server (GPL), Release 25.0 |
| version_
| version_compile_os | Linux |
+------