Restore from backup - Lost connection to MySQL server during query?

1

@sebadamus continuing from Newbie: How to backup? - #15 by sebadamus

Hi,

Any idea why I get this error? “(2013, ‘Lost connection to MySQL server during query’)”

Its an almost empty site, the sql db backup uncompressed is 8mb, private/public files just 1 jpg.

I run this command to restore DB and its public/private files from the backup created within ERPNext, but it takes something like 20 minutes until the error appears.

Will try to restore manually, restoring db with phpmyadmin and coping the files manually… but would be good to be able to restore with bench commands.

bench --site site1-test.local restore ../DB-Backups/Backups_Manuales/20180318_132828-site1_local-database.sql --with-public-files ../DB-Backups/Backups_Manuales/20180318_132828-site1_local-files.tar --with-private-files ../DB-Backups/Backups_Manuales/20180318_132828-site1_local-private-files.tar

frappe@erpnext:~/frappe-bench$ bench --site site1-test.local restore …/DB-Backups/Backups_Manuales/20180318_132828-site1_local-database.sql --with-public-files …/DB-Backups/Backups_Manuales/20180318_132828-site1_local-files.tar --with-private-files …/DB-Backups/Backups_Manuales/20180318_132828-site1_local-private-files.tar
MySQL root password:
Traceback (most recent call last):
File “/usr/lib/python2.7/runpy.py”, line 174, in _run_module_as_main
"main", fname, loader, pkg_name)
File “/usr/lib/python2.7/runpy.py”, line 72, in _run_code
exec code in run_globals
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 94, in 
main()
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 18, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 722, in call
return self.main(*args, **kwargs)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 697, in main
rv = self.invoke(ctx)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 1066, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 1066, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 895, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 535, in invoke
return callback(*args, **kwargs)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/decorators.py”, line 17, in new_func
return f(get_current_context(), *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/init.py”, line 24, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py”, line 115, in restore
force=context.force)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py”, line 65, in _new_site
admin_password=admin_password, verbose=verbose, source_sql=source_sql,force=force, reinstall=reinstall)
File “/home/frappe/frappe-bench/apps/frappe/frappe/installer.py”, line 43, in install_db
if not ‘tabDefaultValue’ in frappe.db.get_tables():
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 784, in get_tables
return [d[0] for d in self.sql(“show tables”)]
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 176, in sql
self._cursor.execute(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 165, in execute
result = self._query(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/cursors.py”, line 321, in _query
conn.query(q)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 860, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1061, in _read_query_result
result.read()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1349, in read
first_packet = self.connection._read_packet()
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 991, in _read_packet
packet_header = self._read_bytes(4)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py”, line 1037, in _read_bytes
CR.CR_SERVER_LOST, “Lost connection to MySQL server during query”)
pymysql.err.OperationalError: (2013, ‘Lost connection to MySQL server during query’)
2

For a sanity check connect by other means?

Say manually

mysql -u root -p

or via the bench

bench mysql

3

Thanks Clarkej,

First command response right and asks for root mysql pass, then just mysql prompt.

Second command opens bench mysql prompt (didnt knew this command, useful!)

But the ending is the same pymysql.err.OperationalError: (2013, ‘Lost connection to MySQL server during query’)

its a ubuntu 16.04 LTS server, everything running locally on the same server…

4

increase max_allowed_packet in mysql settings

5

I had this problem repeatedly until I logged into mariadb and imported the sql file.

Then ran bench --site sitename migrate from the frappe-bench folder.

and it worked.

6

it is 16 MB by default.
I have 3 GB back up file need to restore.
So what is the value we need to put in my sql packet size?

I have increased to 32 MB , but getting above error again.

7

I got the same problem today with a v11 instance. Also used the same command to restore the db (with the --force option).

as …

… did not help I figured that

  1. login in to the mysql console mysql -u root -p -h localhost
  2. identifying the db name with show databases;
  3. dropping the database (still in the mysql console) `drop database [db-name];
  4. logging out of the mysql console, and finally
  5. restoring the backup with bench --site [site-name] restore /path/to/backup-file.sql

worked

show databsesnd then doing

8

hi,

did you able to solve this issue? please let me know how you did.

thanks and reg,