Description:
We are encountering frequent deadlocks and crashes in our MySQL 8.0.37 environment due to InnoDB assertion failures. The issue results in MySQL terminating unexpectedly, showing messages about deadlocks, mutex contention, and an assertion failure, as well as a fatal error triggered by sync0arr.cc.
Error mentioned below
########################################
DEADLOCK of threads detected!
--Thread 140184117503744 has waited at fil0fil.cc line 8155 for 0 seconds the semaphore:
Mutex at 0x7f82d02c3ff0, Mutex FIL_SHARD created fil0fil.cc:2001, locked by 140184117503744
--Thread 140151595210496 has waited at fil0fil.cc line 8155 for 0 seconds the semaphore:
Mutex at 0x7f82d02c3ff0, Mutex FIL_SHARD created fil0fil.cc:2001, locked by 140184117503744
2024-10-29T11:29:51.166744+05:30 0 [ERROR] [MY-012982] [InnoDB] [FATAL] ######################################## Deadlock Detected!
2024-10-29T11:29:51.169106+05:30 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: sync0arr.cc:679:ib::fatal triggered thread 140183789868800
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-10-29T05:59:51Z UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
BuildID[sha1]=65bcd35eb78d52b6940dfa37f9e8d404865f0c66
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/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x2120c5d]
/usr/sbin/mysqld(print_fatal_signal(int)+0x37f) [0xfe0cdf]
/usr/sbin/mysqld(my_server_abort()+0x7e) [0xfe0e2e]
/usr/sbin/mysqld(my_abort()+0xa) [0x211ae2a]
/usr/sbin/mysqld(ut_dbg_assertion_failed(char const*, char const*, unsigned long)+0x31f) [0x233c49f]
/usr/sbin/mysqld() [0x233ee5f]
/usr/sbin/mysqld() [0x22fd4a8]
/usr/sbin/mysqld(sync_array_detect_deadlock()+0x8a) [0x22fd87a]
/usr/sbin/mysqld(srv_error_monitor_thread()+0x18e) [0x22e287e]
/usr/sbin/mysqld(void Detached_thread::operator()<void (*)()>(void (*&&)())+0xc2) [0x222c452]
/usr/sbin/mysqld() [0x2ad9074]
/lib64/libpthread.so.0(+0x7ea5) [0x7f82e6347ea5]
/lib64/libc.so.6(clone+0x6d) [0x7f82e4961b0d]
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.
2024-10-29T11:29:56.954797+05:30 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2024-10-29T11:29:56.955630+05:30 0 [Warning] [MY-011069] [Server] The syntax '--master-info-file' is deprecated and will be removed in a future release.
2024-10-29T11:29:56.955652+05:30 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-file' is deprecated and will be removed in a future release.
2024-10-29T11:29:56.955679+05:30 0 [Warning] [MY-011068] [Server] The syntax 'log_slave_updates' is deprecated and will be removed in a future release. Please use log_replica_updates instead.
2024-10-29T11:29:56.955704+05:30 0 [Warning] [MY-011069] [Server] The syntax '--master-info-repository' is deprecated and will be removed in a future release.
2024-10-29T11:29:56.955721+05:30 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-repository' is deprecated and will be removed in a future release.
2024-10-29T11:29:56.955742+05:30 0 [Warning] [MY-011068] [Server] The syntax 'slave_parallel_workers' is deprecated and will be removed in a future release. Please use replica_parallel_workers instead.
2024-10-29T11:29:56.955772+05:30 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2024-10-29T11:29:56.958889+05:30 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.37) starting as process 21699
2024-10-29T11:29:56.984415+05:30 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=2147483648. Please use innodb_redo_log_capacity instead.
2024-10-29T11:29:56.989437+05:30 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-10-29T11:30:43.853471+05:30 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-10-29T11:36:28.072233+05:30 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2024-10-29T11:36:29.603906+05:30 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2024-10-29T11:36:29.609305+05:30 0 [System] [MY-010232] [Server] XA crash recovery finished.
2024-10-29T11:36:31.181076+05:30 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-10-29T11:36:31.181160+05:30 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-10-29T11:36:31.250288+05:30 0 [Warning] [MY-010539] [Repl] Recovery from source pos 579956969 and file mysql-bin.005405 for channel ''. Previous relay log pos and relay log file had been set to 579957185, /mysqldata1/relay-bin/mysql-relay-bin.002740 respectively.
2024-10-29T11:36:31.252220+05:30 5 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
2024-10-29T11:36:31.256187+05:30 5 [System] [MY-014001] [Repl] Replica receiver thread for channel '': connected to source 'replica@192.168.1.106:3306' with server_uuid=34b10df4-233c-11ef-816e-000c292cd7eb, server_id=106. Starting replication from file 'mysql-bin.005405', position '579956969'.
2024-10-29T11:36:31.310491+05:30 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.37' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
2024-10-29T11:36:31.310522+05:30 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
How to repeat:
The issue occurs intermittently, especially under high load. However, specific steps to reliably reproduce it are unclear, as it does not follow a predictable pattern.
