Bug #75517 InnoDB: Warning: a long semaphore wait - followed by crash
Submitted: 15 Jan 2015 15:28 Modified: 12 Jan 2019 15:17
Reporter: Van Stokes Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.6.21 OS:Linux (Ubuntu)
Assigned to: CPU Architecture:Any

[15 Jan 2015 15:28] Van Stokes
Description:
We are getting many of these errors in the error.log:

InnoDB: Warning: a long semaphore wait:

Eventually the server freezes. After the server has been locked for five mins MySQL restarts itself.
See the attached log file for complete details.

How to repeat:
I found this post:
http://stackoverflow.com/questions/24860111/warning-a-long-semaphore-wait

which references an old bug report:
http://bugs.mysql.com/bug.php?id=66402

It is suggested that it has something to so with:
innodb_adaptive_hash_index

In our environment it's set to default (ON).

Suggested fix:
Not sure. Review log.
[15 Jan 2015 15:28] Van Stokes
Error log from server

Attachment: error.log (text/x-log), 1.17 MiB.

[15 Jan 2015 15:29] Van Stokes
Server configuration file

Attachment: my.cnf (application/octet-stream, text), 8.46 KiB.

[16 Jan 2015 18:51] Van Stokes
Just had another lockup in the server. Attempting to run this command:
SET GLOBAL innodb_adaptive_hash_index=0
did nothing during the lockup because the sever was not responding to commands. I attempted to kill one connection to see if I could free the lock and that appeared to cause the server to crash.
[16 Jan 2015 21:00] Sveta Smirnova
Thank you for the report.

According to the error log your database server was not shutdown normally, but I don't see any error in the log which can reflect the fact it was shutdown inside MySQL server intentionally. Looks like OS killed mysqld process, because it, for example, used too much resources such as RAM. This is not common symptom of a crash due to long semaphore wait: in this case message "InnoDB: We intentionally crash the server" should be printed into error log file. In your case there is no such a message.

Please check resource usage of your OS next time when you see similar issue.
[19 Jan 2015 12:39] Van Stokes
The reason you don't see: "InnoDB: We intentionally crash the server" is because we cannot wait for the timeout to occur before MySQL crashes it. MySQL HAS crashed itself in the past but we cannot wait for the MySQL server to crash itself. When we begin to notice the 'semaphore' error in the log we purposefully try to shutdown the server gracefully however that always leads to a crash because the shutdown takes over five minutes. Ubuntu purposefully does a KILL -9 on all services that take longer than five minutes to shutdown. 

We need to know what is causing the "InnoDB: Warning: a long semaphore wait" to appear in the logs so that we can ensure that our application isn't causing this issue. This issue is new to us and began right after we upgrade to 5.6.21. Our application has also been updated but we cannot identify what could cause this warning. Our application performs basic SQL commands.

Please document what causes this warning.
[27 Jan 2017 1:54] Valentin Gjorgjioski
Got this error on 5.7.16 twice this week. 

The server is seems not to crush itself, but it it inaccessible for few minutes. 

2017-01-26T16:28:00.783095Z 0 [Warning] InnoDB: A long semaphore wait:
--Thread 139915469956864 has waited at row0purge.cc line 861 for 241.00 seconds the semaphore:
S-lock on RW-latch at 0x91e5458 created in file dict0dict.cc line 1184
a writer (thread id 139915280357120) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0purge.cc line 861
Last time write locked in file /build/mysql-5.7-jI7i5c/mysql-5.7-5.7.16/storage/innobase/dict/dict0stats.cc line 2366
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:

2017-01-26T16:35:42.813290Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 714537ms. The settings might not be optimal. (flushed=97 and evicted=173, during the time.)
[12 Dec 2018 15:17] MySQL Verification Team
Hi,

What you are reporting here is expected behaviour with InnoDB storage engine, particularly when your DMLs and schema are not designed correctly.

Our Reference Manual describes in high detail why those semaphore waits occur , why a server has intentionally to crash and what tools we have provided for your benefit in order to diagnose the problem.

This is not a bug, unless you discover with our diagnostic tools something extraordinary. 

This is all fully explained in our Reference Manual.
[13 Jan 2019 1: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".