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.
BuildID[sha1]=40c1f0e0d826a573f7f320e98f4b61484baf534a
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.
----------
SEMAPHORES
----------
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
------------
TRANSACTIONS
------------
Trx id counter 2698831
Purge done for trx's n:o < 2698215 undo n:o < 0 state: running but idle
History list length 215
LIST OF TRANSACTIONS FOR EACH SESSION:
---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 192.168.16.23 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` = ''
