Details
Description
After updating our cluster to MariaDB 11.4.2 our daily backups started failing to deadlocks such as this:
mariadb-dump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table `bowling_accountdata` at row: 851719
Before our system attempts to make a backup, it desyncs a node from the cluster and waits for a few minutes. Then it starts mariadb-dump, which now fails to a deadlock. We used the same backup process but never had backup deadlock issues with versions 10.6 and 10.7.
Exact Commands:
sudo /usr/bin/mariadb --execute 'SET GLOBAL wsrep_desync=ON' && sudo /usr/bin/mariadb --execute 'SET GLOBAL slow_query_log=OFF'
[wait 3min 30 sec]
[verify node is DESYNCED]
sudo /usr/bin/mariadb-dump [database_name] --flush-logs | gzip > [backup_filename]
Some Additional Information:
- It doesn't always fail to backup small databases, but it has always failed to backup our main database after producing ~20 MB of compressed output, while the expected compressed size is approx 1.7GB
- We used SHOW FULL PROCESSLIST to verify that there's no other queries running when the backup process starts. We also enabled innodb_print_all_deadlocks option but nothing shows up in the error log after this deadlock is reported. Also "SHOW ENGINE INNODB STATUS" doesn't show any deadlock after the failure.
We appreciate any ideas on how to investigate this further, and we are willing to provide any additional details that might help in resolving the issue. Attached server.cnf, full processlist from just before the failure and innodb status from just after the failure.