mariadb-dump: Error 2013: Lost connection to server

[msebald]

Summary

When backing up a huge MariaDB database (for openHAB) I get the following error:
mariadb-dump: Error 2013: Lost connection to server during query when dumping table Item144 at row: 331906
The error is not the problem. The problem is that there are no notifications. Also a file is in the backup directory, everything looks normal - except the file size.

Steps to reproduce

See above.

What is the expected correct behavior?

Triggered notifications which bring this error to the attention of the user.

Relevant logs and/or screenshots

I only see this error in the Docker logs:
mariadb-dump: Error 2013: Lost connection to server during query when dumping table Item144 at row: 331906

Interesting is that it cannot be seen in /var/log/container.container.log inside the container. There I only see this:

2025-01-26.04:02:09 [NOTICE] /etc/services.available/dbbackup-01/run ** [01-x.x.x.x__ALL] Dumping MySQL/MariaDB database: 'openhab' and compressing with 'zstd'
2025-01-26.04:05:29 [DEBUG] /etc/services.available/dbbackup-01/run ** [01-x.x.x.x__ALL] DB Backup exit Code is 0
2025-01-26.04:05:29 [INFO] /etc/services.available/dbbackup-01/run ** [01-x.x.x.x__ALL] DB Backup of 'mariadb_openhab_x.x.x.x_20250126-040209.sql.zst' completed successfully
2025-01-26.04:05:29 [NOTICE] /etc/services.available/dbbackup-01/run ** [01-x.x.x.x__ALL] Backup of 'mariadb_openhab_x.x.x.x_20250126-040209.sql.zst' created with the size of 21970228 bytes
2025-01-26.04:05:29 [DEBUG] /etc/services.available/dbbackup-01/run ** [01-x.x.x.x__ALL] Moving backup to filesystem
2025-01-26.04:05:29 [DEBUG] /etc/services.available/dbbackup-01/run ** [01-x.x.x.x__ALL] Move exit Code is 0
2025-01-26.04:05:30 [DEBUG] /etc/services.available/dbbackup-01/run ** [01-x.x.x.x__ALL] Moving of backup 'mariadb_openhab_x.x.x.x_20250126-040209.sql.zst' completed successfully
2025-01-26.04:05:30 [NOTICE] /etc/services.available/dbbackup-01/run ** [01-x.x.x.x__ALL] DB Backup for 'openhab' time taken: Hours: 0 Minutes: 03 Seconds: 21

As I see an exit code 0 it looks like the error is not catched. It only shows in Docker errors and before other "interesting" logs:

As mentioned, the error is not the problem, but that it is not reported and there might be backups not working or incomplete. For myself, I have mail and Matrix notifications configured, at least mail should work as I sent some test mails out of the container.

I wil also try to get around the error - also because this more likely seems to be a server problem, and not so much of the client (db-backup). Google says, I shoud put max_allowed_packet from 16MB to 1G in [mysqld] and [mysqldump] sections, which I will try.

max_allowed_packet = 1G

Others write that I should play with timeout values. I will also try, but one after the other.

Environment

• Image version / tag: latest
• Host OS: Unraid

[tiredofit]

How big is your database and maybe in more detail, the table in question?
Just on personal experience over the years as you have correctly searched that it is related to either timeout periods or max_allowed_packet.

It also could be that your database table is corrupted as well. Rebuilding the table with mysqlcheck might be a good place to start as well...

[msebald]

I was able to solve it today. I set innodb_force_recovery=1 on the database server and dumped the database. Then I unset it, deleted the database and restored it from the backup. That worked. After that I did a backup with db-backup and that worked, too.

Playing around with max_allowed_packet up to 1G did not work on db-backup for the mysqldump and also not on the server for mysqld.

Anyway, the problem stays that db-backup does not inform about a bad backup. Can this be fixed somehow?

[tiredofit]

I think with some significant effort we could trap the output of mysqldump and watch for keywords like you had identified and then at least throw some warnings that something happened. Right now it is very basic.. Trapping the exit code of the dump application which returns a limited amount of detail and is really a Pass or Fail. I suppose there could be some options to set in mysqldump that fail upon first issue as well as opposed to persisting with the backup as well. It's an enhancement to investigate in future releases.

[msebald]

I was thinking that it is impossible that mysqldump does not throw an error when something like this happens. Pass or fail is okay, because with a fail and a notification you can look into it. You have to look into it. But if nothing happens in this kind of szenario you think that everything is okay and that your backup is allright.

[tiredofit]

If you have a copy of your old database you could use maybe you could try it out with mysql dump manually and then report back. We can certainly work in command line arguments into the dump process.

[msebald]

I have a dump from the old version of the database. It should contain the error/problem, but I did not check.

What I did: I dumped this database inside a Debian virtual machine and restored it to a MariaDB container on Unraid. As I needed backup there, too, I installed the db-backup container as well.

I did daily backups inside the VM, which never was a problem. Interesting is that none of the mentioned settings where changed for the MariaDB inside the VM. So max_allowed_packet is 16M (for mysqld and mysqldump) innodb_force_recovery is not set. The MariaDB version differs. Inside the VM it is 10.3.39-0+deb10u2, the container should be 11.4.4-r1-ls171, according to the Github page.