Error restoring sql database from backup dump (ERROR 2013 (HY000))

ℹ️ Support
1

Nextcloud version: 15.0.4
Operating system and version: Raspian Buster
Apache or nginx version (eg, Apache 2.4.38 (Raspbian)):
PHP version: 7.3.14:

The issue you are facing:

I am migrating my nextcloud server from a raspberry pi 3 running raspian stretch to an rpi 4 running raspian buster. I created a dump of the database and am tyring to restore it with the following commands:

mysql -h localhost -u nextcloud -p<password> -e "CREATE DATABASE nextcloud"

mysql -h localhost -uroot -p<password> -e "GRANT ALL PRIVILEGES on nextcloud.* to nextcloud@localhost"

mysql -h localhost -u nextcloud -p<password> nextcloud < <backup_db_file>

However, I get the following error:

ERROR 2013 (HY000) at line 746: Lost connection to MySQL server during query

Bsed on google searching, I tried modifying my mariadb configuration file in /etc/mysql/my.cnf so it is like this:

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

[mysqld]
max_allowed_packet = 160M
net_read_timeout = 300
net_write_timeout = 600
innodb_buffer_pool_size = 2G

[mysqldump]
max_allowed_packet = 160M

But I get the same error. Can anyone suggest what I might be doing wrong, or how I can work around this?

2

Hi,

From what I found after googling that for you, I am happy to run my own Nextcloud in an actual Dell enterprise class server I bought and installed ESXi on… The two main culprits for this are timeouts and low memory. By trying to restore the full database in one shot, the PI either runs out of memory or is too slow and the mysql client times out. For the time out, you can play with some variables to increase them. For the memory, I doubt you can do much… How big is that database dump ? Can you run your Nextcloud from actual server gear ?

Good luck fixing this,

3

Thanks, the database dump is 119MB, which doesn’t seem outrageous to me.

The settings I added to the my.cnf file to get around this were these ones:

[mysqld]
max_allowed_packet = 160M
net_read_timeout = 300
net_write_timeout = 600
innodb_buffer_pool_size = 2G

[mysqldump]
max_allowed_packet = 160M

Are there any others that might help?

4

So, actually, I rebooted the server, and after that the database could be imported. I thought the new settings would be picked up when I restarted the mysql service, which I certainly attempted to do before rebooting.