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
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` = ''