Unable to dump database manually or during a backup process on Plesk: Error 2013: Lost connection to server during query when dumping table example at row: 0

Symptoms

• Plesk Obsidian running on a Debian-based operating system

• Database dumps initiated by Plesk fail with an error that is similar to the following:

  error mysqldump: Error 2013: Lost connection to server during query when dumping table example at row: 0

• The backup generation process from Plesk provides an error as follows:

  (Database object 'db_example_exampledb') Unable to make database dump. Error: Failed to exec mysqldump: Exit code: 3: WARNING: Forcing protocol to TCP due to option specification. Please explicitly state intended protocol.

• The database on which the database dump actions fail has a large size of 10GB or above and contains very large tables of 512MB or above.

Cause

The default database dumping MySQL limits are reached and the connection between the MySQL client and server is interrupted.

Resolution

1. Connect to the server via SSH

2. Open the /etc/mysql/mariadb.conf.d/50-client.cnf. Vi editor can be used for this.

3. Add the parameter max_allowed_packet with value 1024M on the [mysqld] and [mysqldump] sections as follows:

   [mysqld]
   max_allowed_packet = 1024M
   ...
   [mysqldump]
   max_allowed_packet = 1024M

4. Restart the MySQL/MariaDB service:

   # systemctl restart mariadb || systemctl restart mysql || systemctl restart mysqld