Once a day or two we get the bellow error and the mysql crashingt:
2024-07-14T05:31:59.327696Z 0 [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2024-07-14T05:31:59.327741Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: srv0srv.cc:1878:ib::fatal triggered thread 140039700203264
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.
2024-07-14T05:31:59Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=1758de1e111952b1f61480360c447dc27d6caddc
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 0x30000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x41) [0x2130f51]
/usr/sbin/mysqld(print_fatal_signal(int)+0x397) [0xfee797]
/usr/sbin/mysqld(my_server_abort()+0x75) [0xfee8e5]
/usr/sbin/mysqld(my_abort()+0xe) [0x212adee]
usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x309) [0x237b9b9]
/usr/sbin/mysqld(ib::fatal::~fatal()+0xcf) [0x237e2ff]
/usr/sbin/mysqld(srv_error_monitor_thread()+0x7aa) [0x231c41a]
/usr/sbin/mysqld(void Detached_thread::operator()<void (*)()>(void (*&&)())+0xca) [0x224a87a]
/lib64/libstdc++.so.6(+0xc2b13) [0x7f5de9854b13]
/lib64/libpthread.so.0(+0x81da) [0x7f5dea8bc1da]
/lib64/libc.so.6(clone+0x43) [0x7f5de8e6be73]
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.
And before are alot of those:
Mutex at 0x7fde903975e8, Mutex TRX_SYS created trx0sys.cc:565, locked by 140504716736256
--Thread 140504724723456 has waited at trx0sys.h line 598 for 240 seconds the semaphore:
2024-07-13T04:43:26.039036Z 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait:
{}
I also see a lot of those warnings in the logs (I think mostly after the restart):
[Warning] [MY-013865] [InnoDB] Redo log writer is waiting for a new redo log file. Consider increasing innodb_redo_log_capacity.
I couldn't figure out what is the cause of this problem. I increased the innodb_redo_log_capacity by a lot but it happened anyway. I also tried turning off innodb_adaptive_hash_index but it didn't help. I thought of increasing innodb_log_buffer_size which is currently set to 1M because I thouth maybe its causing a transaction to be to slow.
Its important to say that MySQL seems like it stops doing anything in those 600 seconds. I can't connect to the server at this time, CPU is down to nothing and then we get this error and a crush.
Since it is not possible to connect to DB during the downtime, I thought running a Scheduled Event from MySQL itself writing running queries to the disk should do the trick - but I was wrong. Even using a Scheduled Event stopped writing the same time downtime started.
Another thing that I tried is to enable innodb_status_output which logs SHOW ENGINE INNODB STATUS; to stderr every 15 seconds, even this stopped writing to stderr the same time downtime started.
We use MySQL version 8.0.34
