Bug #89896 Thread 139967096907520 has waited at trx0trx.c line 715 for 784.00 seconds the s
Submitted: 3 Mar 2018 8:43 Modified: 5 Mar 2018 4:14
Reporter: sachin watane Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.41 OS:Linux
Assigned to: CPU Architecture:Any

[3 Mar 2018 8:43] sachin watane
Description:
A mysql instance was crashed because of long semaphore wait . There's is Workload is very low..

Mutex at 0x7f59939be2b8 created file srv0srv.c line 872, lock var 1
waiters flag 1
InnoDB: Warning: a long semaphore wait:
--Thread 139967087310592 has waited at trx0trx.c line 715 for 784.00 seconds the semaphore:
Mutex at 0x7f59939be2b8 created file srv0srv.c line 872, lock var 1
waiters flag 1
InnoDB: Warning: a long semaphore wait:
--Thread 139967090284288 has waited at trx0trx.c line 715 for 783.00 seconds the semaphore:
Mutex at 0x7f59939be2b8 created file srv0srv.c line 872, lock var 1
waiters flag 1
InnoDB: Warning: a long semaphore wait:
--Thread 139967090554624 has waited at trx0trx.c line 715 for 783.00 seconds the semaphore:
Mutex at 0x7f59939be2b8 created file srv0srv.c line 872, lock var 1
waiters flag 1
InnoDB: Warning: a long semaphore wait:
--Thread 139961297770240 has waited at trx0trx.c line 213 for 783.00 seconds the semaphore:
Mutex at 0x7f59939be2b8 created file srv0srv.c line 872, lock var 1
waiters flag 1
InnoDB: Warning: a long semaphore wait:
--Thread 139967761344256 has waited at trx0trx.c line 371 for 768.00 seconds the semaphore:
Mutex at 0x7f59939be2b8 created file srv0srv.c line 872, lock var 1
waiters flag 1
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0
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.
180302 22:28:28InnoDB: Assertion failure in thread 139967786247936 in file srv0srv.c line 2093
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.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
180302 22:28:28 - mysqld got signal 11;
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=2097152
read_buffer_size=2093056
max_used_connections=854
max_connections=1200
threads_connected=668
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4912438 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=(nil)
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...
frame pointer is NULL, did you compile with
-fomit-frame-pointer? Aborting backtrace!
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
180302 22:28:33  mysqld restarted
180302 22:28:40  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
180302 22:29:26  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 138 2069787782.
InnoDB: Doing recovery: scanned up to log sequence number 138 2070867699
180302 22:29:27  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 206, file name dmh-amritalive-bin.010371
InnoDB: Last MySQL binlog file position 0 30839612, file name /db_master/data/db/MySql_5.0.41/log/mysql_repl_log/dmh-amritalive-bin.000127
180302 22:29:54  InnoDB: Started; log sequence number 138 2070867699
180302 22:29:54 [Note] Recovering after a crash using /db_master/data/db/MySql_5.0.41/log/mysql_repl_log/dmh-amritalive-bin
180302 22:29:54 [Note] Starting crash recovery...
180302 22:29:54 [Note] Crash recovery finished.
180302 22:29:54 [Warning] 'user' entry 'root@ahisdb1.dmhrc.org' ignored in --skip-name-resolve mode.
180302 22:29:54 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.41-log'  socket: '/db_master/data/db/MySql_5.0.41/mysql.sock'  port: 3306  MySQL Community Server (GPL)

How to repeat:
Don't Know 

Suggested fix:
Don't Know
[3 Mar 2018 10:54] MySQL Verification Team
Hi,  5.0 is very old.  Consider upgrading to 5.7.21 and see if same issues re-occur.

http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf
(page 19)
[5 Mar 2018 4:14] sachin watane
Hi,

We are trying to upgrade it on same, also we are developing the new application to support mysql upgrade db, But mean while can you please suggest for same so that it is easy for us to stop this kind of issue.

Thanks & Regards