Restore on fresh server fails

Iulian_Olaru · October 24, 2021, 9:12am

Hi guys,
I am set up a fresh server ( Ubuntu 20.4 on AWS ) with erpnext 12 running on it.

I am trying to restore database with private and public files from another erpnext 12 ( Debian )

However, when I run the restore command:

bench --site [sitename] --force restore [path to database backup file] --with-private-files [relative-path-to-private-files-backup-file] --with-public-files [relative-path-to-public-files-backup-file]

I am receiving an error and I am not sure what is wrong. I would significantly appreciate it if someone could help me

Here’s the error showing up:

~/frappe-bench$ bench --site management.neptunescubadiving.com --force restore 20211022_000114-management_neptunescubadiving_com-database.sql.gz --with-private-files 20211022_000114-management_neptunescubadiving_com-private-files.tar --with-public-files 20211022_000114-management_neptunescubadiving_com-files.tar

MySQL root password:
Creating Database…
225MiB 0:00:39 [5.75MiB/s] [=====================================================================================================================================================>] 100%
Traceback (most recent call last):
File “/usr/lib/python3.8/runpy.py”, line 194, in _run_module_as_main
return _run_code(code, main_globals, None,
File “/usr/lib/python3.8/runpy.py”, line 87, in _run_code
exec(code, run_globals)
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 99, 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/lib/python3.8/site-packages/click/core.py”, line 764, in call
return self.main(*args, **kwargs)
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 717, in main
rv = self.invoke(ctx)
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 1137, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 956, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/click/core.py”, line 555, in invoke
return callback(*args, **kwargs)
File “/home/frappe/frappe-bench/env/lib/python3.8/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 26, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py”, line 142, in restore
_new_site(frappe.conf.db_name, site, mariadb_root_username=mariadb_root_username,
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py”, line 94, in _new_site
frappe.utils.scheduler.toggle_scheduler(enable_scheduler)
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/scheduler.py”, line 240, in toggle_scheduler
frappe.db.set_value(“System Settings”, None, “enable_scheduler”, 1 if enable else 0)
File “/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py”, line 628, in set_value
modified = now()
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/data.py”, line 167, in now
return now_datetime().strftime(DATETIME_FORMAT)
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/data.py”, line 134, in now_datetime
dt = convert_utc_to_user_timezone(datetime.datetime.utcnow())
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/data.py”, line 157, in convert_utc_to_user_timezone
return utcnow.astimezone(timezone(get_time_zone()))
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/data.py”, line 151, in get_time_zone
return frappe.cache().get_value(“time_zone”, _get_time_zone)
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/redis_wrapper.py”, line 79, in get_value
val = generator()
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/data.py”, line 145, in _get_time_zone
return frappe.db.get_system_setting(‘time_zone’) or ‘Asia/Kolkata’ # Default to India ?!
File “/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py”, line 895, in get_system_setting
return frappe.cache().get_value(“system_settings”, _load_system_settings).get(key)
File “/home/frappe/frappe-bench/apps/frappe/frappe/utils/redis_wrapper.py”, line 79, in get_value
val = generator()
File “/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py”, line 894, in _load_system_settings
return self.get_singles_dict(“System Settings”)
File “/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py”, line 515, in get_singles_dict
result = self.sql(“”"
File “/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py”, line 156, in sql
self._cursor.execute(query, values)
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/cursors.py”, line 170, in execute
result = self._query(query)
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/cursors.py”, line 328, in _query
conn.query(q)
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 732, in _read_query_result
result.read()
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 1075, in read
first_packet = self.connection._read_packet()
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/connections.py”, line 684, in _read_packet
packet.check_error()
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/protocol.py”, line 220, in check_error
err.raise_mysql_exception(self._data)
File “/home/frappe/frappe-bench/env/lib/python3.8/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1146, “Table ‘_6a86be41eb1fc0dd.tabSingles’ doesn’t exist”)

jslover · October 24, 2021, 11:35am

hello, did you complete frappe+erpnext setup wizard first

Iulian_Olaru · October 24, 2021, 1:57pm

Yes I did before trying the restore.

bkm · October 24, 2021, 2:29pm

Ok… This looks like something I did myself a very long time ago when I was first learning about how to restore databases across servers.

I “think” what may have happened here is that the ‘new’ server was completely installed and ready for fresh data.

The ‘old’ server backup with files was sent to the new server and then the restore process was run.

However, when I was making similar mistakes long ago, I had forgotten to make sure the security key and database name from the ‘old’ server were copied to the ‘new’ server.

This information exists in the site_config.json file. You can find it at:

/home/your_user/frappe-bench/sites/site1.local/site_config.json

The database names need to match fro old to new, and the security key needs to be copied from old to new. In most cases I coped the whole site_config.json file and edited any minor changes on the new server.

Not sure if this still works, but it is how I solved the problem when I first started using ERPNext v8.x. Since that time I have always made image copies of my server and moved it to new hardware so everything was the same each time.

If nothing else it is worth a try.

Also, I never include the private and public files in the restore command. They seem to complicate the process. I always just restore the database and then separately copy in the private and public files only AFTER I have a successful restore.

Also probably worth a try.

BKM

PyJumper · October 24, 2021, 5:22pm

hello, i would also try the following
you can try to manually untar the database.sql.gz to get the .sql file then restore it to clean new site (reinstall) if that worked you can move public/private files manually to site directory

if this did not work you can use mysqldump to restore sql file manually to your site DB, this should work without problems then you do bench migrate.

Iulian_Olaru · October 25, 2021, 6:05am

Thank you for taking the time to write such an extensive explanation.

Would you be so kind to point me to the right direction where to find the security key ?

Ultimately, how can I make sure the database name matches on the new server considering it’s a fresh install ?

Julian

Iulian_Olaru · October 25, 2021, 6:06am

I just tried this and it fails with the same error… it seems to be something regarding the database name.

@bkm seems to be on to something but I don’t know how to change the database name without breaking the fresh install which was not easy to do.

bkm · October 25, 2021, 7:07pm

The security key is located in the site_config.json file.

That file is at:

/home/your_user/frappe-bench/sites/site1.local/site_config.json

Remember to change ‘your_user’ the the user you setup on the server before installing ERPNext. Also use whatever you might have changed your site name in place of ‘site1.local’

The ‘site1.local’ is the default name that would be used if you ran the easy install script with no special switches.

BKM

bkm · October 25, 2021, 7:11pm

Okay… one more try here.

Is management.neptunescubadiving.com the URL of your server? If so is there a directory in ~/frappe-bench/sites/ also of this name?

If not, then what directory(s) are in ~/frappe-bench/sites/ ?

That would help to better frame the proper restore command that might get you closer to success.

BKM

Iulian_Olaru · October 26, 2021, 8:56am

Thank you very much for your great assistance!

Indeed, the URL is management.neptunescubadiving.com and the folder in ~/frappe-bench/sites/ is also names the same as the URL.

Iulian_Olaru · October 27, 2021, 3:47am

Anyone has any idea how to make this work ?

I tried with copying the entire site_config.json from the old server to the new, and the error is still the same.

Thank you.

Support-at-ERPgulf · October 27, 2021, 1:27pm

Important to have latest benh
pip3 install -upgrade frappe-bench
bench setup requirements
bench update
bench build
bench migrate
bench update -build

After that restore the files.
Then again do
bench update
bench build
bench migrate
bench update -build

mymi14s · October 27, 2021, 5:27pm

You have an incomplete database backup.
Try using mysql dump to export the database.

PyJumper · October 27, 2021, 5:54pm

as mymi14s said, that may be the issue, it seems like the sql file is corrupted, i.e it doesn’t CREATE the SingleDoctype table and later it try to insert dead data into that table which doesn’t exist,

maybe you can try to backup your old site and try to restore it on the new site again, if this does not help then you can try some repair tool like mysqlcheck to repair your db