MySQL server crashed and was in a zombie state. Attempted to send kill -s SIGCHLD to the parent process but failed. Killing the parent was not an option since it was the init process and I was forced to restart the server.

Once the server started, MySQL started without issues and I have checked the database for corruption. No corruption detected using the mysqlcheck utility.

I checked the error logs and found the entries as below. Server is AlmaLinux 8.8 (4.18.0-477.21.1.el8_8.x86_64 x86_64 x86_64) and MySQL version is 8.0.32.

I see it received signal 6 which is SIGABRT and the cause is because the semaphore has been waiting for quite some time:

InnoDB: ###### Diagnostic info printed to the standard error stream
2023-11-11T03:24:14.418528Z 0 [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2023-11-11T03:24:14.424144Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: srv0srv.cc:1871:ib::fatal triggered thread 140054773094144
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/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2023-11-11T03:24:14Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 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 = 0 thread_stack 0x100000
/usr/libexec/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x558268818c51]
/usr/libexec/mysqld(print_fatal_signal(int)+0x3c3) [0x55826785ef43]
/usr/libexec/mysqld(my_server_abort()+0x76) [0x55826785f066]
/usr/libexec/mysqld(my_abort()+0xe) [0x558268812c1e]
/usr/libexec/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x32a) [0x558268a7142a]
/usr/libexec/mysqld(ib::fatal::~fatal()+0xde) [0x558268a7406e]
/usr/libexec/mysqld(srv_error_monitor_thread()+0x86f) [0x558268a056bf]
/usr/libexec/mysqld(std::thread::_State_impl<std::thread::_Invoker<std::tuple<Detached_thread, void (*)()> > >::_M_run()+0xc4) [0x55826891f154]
/lib64/libstdc++.so.6(+0xc2b13) [0x7f613b028b13]
/lib64/libpthread.so.0(+0x81ca) [0x7f613f5cb1ca]
/lib64/libc.so.6(clone+0x43) [0x7f613a640e73]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

UPDATE: I have further analyzed the logs and it appears to be one query causing semaphore to wait. I have the query's logic looked and possibly have other optimisation performed on the table.

OS WAIT ARRAY INFO: reservation count 363360
--Thread 140054537078528 has waited at row0ins.cc line 2409 for 941 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f6117dae9d8 created in file buf0buf.cc line 794
a writer (thread id 140054537078528) has reserved it in mode wait exclusive
number of readers 1 (thread id 140051430221568), waiters flag 0, lock_word: ffffffffffffffff
Last time read locked in file row0sel.cc line 4857
Last time write locked in file /builddir/build/BUILD/mysql-8.0.32/storage/innobase/buf/buf0buf.cc line 4936
OS WAIT ARRAY INFO: signal count 813966
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
Trx id counter 2698831
Purge done for trx's n:o < 2698215 undo n:o < 0 state: running but idle
History list length 215
---TRANSACTION 421530309145384, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309144576, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309143768, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309148616, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309146192, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309142960, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421530309142152, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 2698715, ACTIVE 942 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1128, 0 row lock(s)
MySQL thread id 138425, OS thread handle 140054537078528, query id 34297984 webapps-m25 dms update
INSERT INTO `bg_cms_series` (`brch`,`model`,`series`,`description`,`price`, `gstCode`,`image`) VALUES ('Z','200SAH5G15GT','Mazda2','MAZDA2 S 6AUTO G15 GT HATCH',25381.82,'','') ON DUPLICATE KEY UPDATE `series` = 'Mazda2',`description` = 'MAZDA2 S 6AUTO G15 GT HATCH',`price` = 25381.82,`gstCode` = ''


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.