Bug #83965 InnoDB: Assertion failure in thread 140259732973312 in file srv0srv.cc line 1750

• Submitted: 25 Nov 2016 12:07
• Modified: 1 Dec 2016 16:23
• Reporter: Gaurav Tomar
• Status: Not a Bug
• Category: MySQL Server: Data Dictionary
• Severity: S3 (Non-critical)
• Version: 5.6.24
• OS: Debian (Linux 3.2.0-4-amd64 #1 SMP Debian 3.2.65-1 x86_64 GNU/Linux)
• CPU Architecture: Any
• Tags: data dictionary

[25 Nov 2016 12:07] Gaurav Tomar

Description:
-> MySQL getting restarted after queries being stuck, 
-> getting error InnoDB: Assertion failure in thread 140259732973312 in file srv0srv.cc line 1750 and a lot of semaphores.
-> State of main thread of mysql at the time of issue was enforcing dict cache
-> happens atleast once a week
-> server configuration: RAM: 250GB, Cores:24 

-> my.cnf, errorlog and show engine innodb status output is attached below.

How to repeat:
Don't know

[25 Nov 2016 12:16] Gaurav Tomar

my.cnf

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

[25 Nov 2016 12:16] Gaurav Tomar

Error Log

Attachment: error_log.txt (text/plain), 328.84 KiB.

[25 Nov 2016 12:17] Gaurav Tomar

Engine innodb output

Attachment: engine_status.txt (text/plain), 1.00 MiB.

[25 Nov 2016 15:48] MySQL Verification Team

Thank you for the bug report. Please try latest released version current one is 5.6.34 if still you get the issue then provide a repeatable test case. Thanks.

http://dev.mysql.com/downloads/mysql/5.6.html#downloads

[28 Nov 2016 7:03] Gaurav Tomar

Can you please share the reason why this is occurring? Thanks.

[1 Dec 2016 16:23] MySQL Verification Team

Hi!

What you are experiencing is not a bug, but a semaphore and lock waits that are happening on million of installations around the globe.

With transactional databases, semaphore waits, lock waits and deadlocks are natural phenomena, that are expected to happen.

In your specific case, index statistics, for a specific table, is locked by a thread with a long running transaction, which is waiting on something completely different and had not committed yet. It could be waiting on the end user, I/O, CPU .... Meanwhile, in another thread, a concurrent transaction would like to do DML on the same index. Hence the wait. When the limit on wait is surpassed, InnoDB asserts intentionally !!!! It is non-configurable timeout as we have encountered so many times that entire server is waiting on single user to provide input, or on very badly written statement that had to scan large tables, or on disk space, or on some remote server, NAS or similar ...

You should diagnose yourself what are the causes of the waits in your case.