MySQL Bugs: #52409: Assertion failure in innobase/srv/srv0srv.c line 2209: long semaphore wait

Bug #52409	Assertion failure in innobase/srv/srv0srv.c line 2209: long semaphore wait
Submitted:	27 Mar 2010 16:19	Modified:	13 Feb 2012 21:31
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.6.99-m4	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
There is bug#47183, but it was converted into a feature request, while the root cause of the issue we are observing seems to be that the server hangs on the test flow. Some other bugs also mention the problem, but they have been in 'can't repeat' or 'no feedback' status for quite a while. In this bug, we will try to provide a repeatable test case for the failure we are getting in our tests.

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.
100325  3:37:18  InnoDB: Assertion failure in thread 1140881728 in file mysql-5.6.99-m4/storage/innobase/srv/srv0srv.c line 2209
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
100325  3:37:18 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=25165824
read_buffer_size=131072
max_used_connections=79
max_threads=120
thread_count=68
connection_count=67
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 71991 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = (nil) thread_stack 0x40000
mysql-5.6.99-m4-linux-x86_64-test/sbin/mysqld(my_print_stacktrace+0x32)[0xa358ca]
mysql-5.6.99-m4-linux-x86_64-test/sbin/mysqld(handle_segfault+0x317)[0x52c781]
/lib64/libpthread.so.0[0x2ae040c3bc00]
/lib64/libc.so.6(gsignal+0x35)[0x2ae04151ebb5]
/lib64/libc.so.6(abort+0x110)[0x2ae04151ffb0]
mysql-5.6.99-m4-linux-x86_64-test/sbin/mysqld[0x942d61]
/lib64/libpthread.so.0[0x2ae040c34143]
/lib64/libc.so.6(__clone+0x6d)[0x2ae0415af8cd]

How to repeat:
If there is a repeatable test case, it will be provided.

A similar issue with a reproducible test case has been reported as Bug#56529 - however that test case requires the ICP (index_condition_pushdown) optimizer feature (not yet in main branches).

Bug #56855 could be a duplicate of this.

I did not analyze the core dump, because it might no longer be available, and 5.6 has changed a lot in the past 18 months. There are two possibilities. Either the was a genuine hang, or InnoDB crashed due to a bug in the watchdog.

Quoting Bug #11877216 INNODB TOO EAGER TO COMMIT SUICIDE ON A BUSY SERVER:

Added to changelog for 5.1.57, 5.5.12, 5.6.3:

The server could halt if InnoDB interpreted a very heavy I/O load for
15 minutes or more as an indication that the server was hung. This
change fixes the logic that measures how long InnoDB threads were
waiting, which formerly could produce false positives.

Added to changelog for 5.1.57, 5.5.13, 5.6.3: 

The MySQL server could hang during CREATE TABLE, OPTIMIZE TABLE, or
ALTER TABLE or other DDL operation that performs a table copy for an
InnoDB table, if such operations were performed by multiple sessions
simultaneously. The error was reported as:

InnoDB: Error: semaphore wait has lasted > 600 seconds

Added to changelog for 5.1.57, 5.5.13, 5.6.3: 

The MySQL server could hang during CREATE TABLE, OPTIMIZE TABLE, or
ALTER TABLE or other DDL operation that performs a table copy for an
InnoDB table, if such operations were performed by multiple sessions
simultaneously. The error was reported as:

InnoDB: Error: semaphore wait has lasted > 600 seconds

Added to changelog for 5.1.57, 5.5.13, 5.6.3: 

The MySQL server could hang during CREATE TABLE, OPTIMIZE TABLE, or
ALTER TABLE or other DDL operation that performs a table copy for an
InnoDB table, if such operations were performed by multiple sessions
simultaneously. The error was reported as:

InnoDB: Error: semaphore wait has lasted > 600 seconds