Restore DB ERPNext v6

Frappe Framework
1

Hello.

I’m trying to restore a database to an earlier point. I’m doing the next steps.

First I change the default site to the one I want to take a backup.

bench use test.site

Then, take a backup of the database with

bench backup

It generates a file on private/backups called backup.test.sql.gz

So, let’s suppose I made a few changes on my site test.site and I want to revert them, so naturally, I’ll use the backup just created backup.test.sql.gz

Again, I’ll make use I’m using the correct site

bench use test.site

Restore with the following command

bench restore path/to/my/backup.test.sql.gz

After that I get the following error.

Traceback (most recent call last):
  File "/usr/lib/python2.7/runpy.py", line 162, 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 79, in <module>
    main()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 16, 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 716, in __call__
    return self.main(*args, **kwargs)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 696, in main
    rv = self.invoke(ctx)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 1060, 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 1060, 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 889, 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 534, 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.py", line 29, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands.py", line 130, in restore
    _new_site(db_name, site, mariadb_root_username=mariadb_root_username, mariadb_root_password=mariadb_root_passwo
rd, admin_password=admin_password, verbose=context.verbose, install_apps=install_app, source_sql=sql_file_path, for
ce=context.force)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands.py", line 87, in _new_site
    install_db(root_login=mariadb_root_username, root_password=mariadb_root_password, db_name=db_name, admin_passwo
rd=admin_password, verbose=verbose, source_sql=source_sql,force=force, reinstall=reinstall)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 31, in install_db
    create_database_and_user(force, verbose)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 53, in create_database_and_user
    raise Exception("Database %s already exists" % (db_name,))
Exception: Database 109f4b3c50 already exists

Ok, so It didn’t work, next I try to use the same command but trying to force the RESTORE

bench --site test.site --force restore path/to/my/backup.test.sql.gz

That also fails, getting the following error.

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-m
ode to 1 if ASCII '\0' is expected. Query: ''.
Traceback (most recent call last):
  File "/usr/lib/python2.7/runpy.py", line 162, 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 79, in <module>
    main()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 16, 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 716, in __call__
    return self.main(*args, **kwargs)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 696, in main
    rv = self.invoke(ctx)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py", line 1060, 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 1060, 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 889, 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 534, 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.py", line 29, in _func
    ret = f(frappe._dict(ctx.obj), *args, **kwargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands.py", line 130, in restore
    _new_site(db_name, site, mariadb_root_username=mariadb_root_username, mariadb_root_password=mariadb_root_password, admin_password=admin_password, verbose=contex
t.verbose, install_apps=install_app, source_sql=sql_file_path, force=context.force)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands.py", line 87, in _new_site
    install_db(root_login=mariadb_root_username, root_password=mariadb_root_password, db_name=db_name, admin_password=admin_password, verbose=verbose, source_sql=so
urce_sql,force=force, reinstall=reinstall)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 38, in install_db
    remove_missing_apps()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 296, in remove_missing_apps
    installed_apps = json.loads(frappe.db.get_global("installed_apps") or "[]")
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 642, in get_global
    return self.get_default(key, user)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 654, in get_default
    d = self.get_defaults(key, parent)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 660, in get_defaults
    defaults = frappe.defaults.get_defaults(parent)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/defaults.py", line 72, in get_defaults
    globald = get_defaults_for()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/defaults.py", line 177, in get_defaults_for
    where parent = %s order by creation""", (parent,), as_dict=1)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 136, in sql
    self._cursor.execute(query, values)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (1146, "Table '109f4b3c50.tabDefaultValue' doesn't exist")

Is there something I’m missing or something I’m doing wrong?

Thanks

2

I have had issues in the past with restoring the DB. Generally I erase the DB through the MariaDB shell and then run bench restore, or use the manual funnel:

mysql -u (username) -p (password) dbname < /path/to/file.sql

With this method I can watch progress with a tool like pipeviewer:

pv sqlfile.sql | mysql -u (username) -p (password)

Although I don’t know if that’s recommended by the Frappe team (@rmehta, thoughts ?)

3

Have you had trouble restoring like you mentioned above?

@alec_ruizramon1

4

It seems to work well, I prefer using pipe viewer to get some idea of the progress.

1 Like
5

Did not work sir.

Got the following error.

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-m
ode to 1 if ASCII '\0' is expected. Query: ''.
6

What command did you type, exactly?

That looks like an error unrelated to the db operation, but not sure.

7

Got it to work man. Thanks! @alec_ruizramon1 The error was a typo…

8

Good to hear! Glad I could help .

1 Like
9

what was the typo error?