How can I migrate mysql to remote server

run bench new-site site1.local command,
I get this message :

Please add this to MariaDB’s my.cnf and restart MariaDB before proceeding

[mysqld]
innodb-file-format=barracuda
innodb-file-per-table=1
innodb-large-prefix=1
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

================================================================================

But I have not privilege to edit it on the server, is there any way can skip it?

@pdvyas, if I want to just restore sqlbackupfile to existing site, just point to existing sitename using same command?

bench restore {existing-sitename} {/path/to/sqlbackupfile}

Yes with --force

We’re forcing this for v5.

@pdvyas, I run below command and got this error

bench restore {site1.local} {20150526_38263610_database.sql}
Usage: bench  restore [OPTIONS] SQL_FILE_PATH

Error: Got unexpected extra argument ({20150526_38263610_database.sql}

How to restore site1’s db then restore it to site2? Site2 is existing site…

Tks

skip the curlys, they are for placeholders :smile:

I ran this command and worked

bench --force --site site2 restore ~/path/20150526_38263610_database.sql

That command for the restore worked for me too, but now I get this when I try to access the site:
“Internal Server Error”

Run bench migrate after restore

1 Like

I get errors as per below when I try to run bench migrate. I suspect I might be missing some modules?

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/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 77, in
main()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 14, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 664, in call
return self.main(*args, **kwargs)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 644, in main
rv = self.invoke(ctx)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 991, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 991, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 837, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 464, in invoke
return callback(*args, **kwargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands.py”, line 28, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands.py”, line 205, in migrate
frappe.modules.patch_handler.run_all()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 23, in run_all
for patch in get_all_patches():
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 37, in get_all_patches
patches.extend(frappe.get_file_items(frappe.get_pymodule_path(app, “patches.txt”)))
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/init.py”, line 593, in get_pymodule_path
return os.path.join(os.path.dirname(get_module(scrub(modulename)).file), *joins)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/init.py”, line 556, in get_module
return importlib.import_module(modulename)
File “/usr/lib/python2.7/importlib/init.py”, line 37, in import_module
import(name)
ImportError: No module named frappe_subscription

It should ignore this module. Working on the fix

OK, thanks for letting us know. Please let us know when it is ready, and I
will try again.

I’ve fixed this on develop branch, will be in master in the next release

You can bench switch-to-develop and try again (ie. restore again)

OK, I ran bench switch-to-develop, and then bench migrate, and I still get the same error messages

ubuntu@ubuntu-amd64:~$ bench switch-to-develop
remote: Counting objects: 344, done.
remote: Compressing objects: 100% (275/275), done.
Receiving objects: 100% (344/344), 483.50 KiB | 0 bytes/s, done.
remote: Total 344 (delta 181), reused 39 (delta 33), pack-reused 35
Resolving deltas: 100% (183/183), done.
From https://github.com/frappe/frappe
3ea5a17…7af38c8 master -> upstream/master
a2d1e60…f51f430 develop -> upstream/develop

  • [new tag] v5.0.17 -> v5.0.17
  • [new tag] v5.0.11 -> v5.0.11
  • [new tag] v5.0.12 -> v5.0.12
  • [new tag] v5.0.13 -> v5.0.13
  • [new tag] v5.0.14 -> v5.0.14
  • [new tag] v5.0.15 -> v5.0.15
  • [new tag] v5.0.16 -> v5.0.16
    Branch develop set up to track remote branch develop from upstream.
    Switched to a new branch ‘develop’
    Already up-to-date.
    remote: Counting objects: 393, done.
    remote: Compressing objects: 100% (297/297), done.
    remote: Total 393 (delta 202), reused 54 (delta 50), pack-reused 42
    Receiving objects: 100% (393/393), 151.08 KiB | 0 bytes/s, done.
    Resolving deltas: 100% (204/204), done.
    From https://github.com/frappe/erpnext
    dcbc4d1…b8dd046 master -> upstream/master
    7f1d5ef…b8dd046 develop -> upstream/develop
  • [new tag] v5.0.15 -> v5.0.15
  • [new tag] v5.0.11 -> v5.0.11
  • [new tag] v5.0.12 -> v5.0.12
  • [new tag] v5.0.13 -> v5.0.13
  • [new tag] v5.0.14 -> v5.0.14
    Branch develop set up to track remote branch develop from upstream.
    Switched to a new branch ‘develop’
    Already up-to-date.

Switched to develop
Please run bench update --patch to be safe from any differences in database schema
ubuntu@ubuntu-amd64:~$ ^C
ubuntu@ubuntu-amd64:~$ bench update --patch
Migrating fbademo
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/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 77, in
main()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 14, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 664, in call
return self.main(*args, **kwargs)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 644, in main
rv = self.invoke(ctx)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 991, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 991, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 837, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 464, in invoke
return callback(*args, **kwargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands.py”, line 28, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands.py”, line 205, in migrate
frappe.modules.patch_handler.run_all()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 23, in run_all
for patch in get_all_patches():
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 37, in get_all_patches
patches.extend(frappe.get_file_items(frappe.get_pymodule_path(app, “patches.txt”)))
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/init.py”, line 601, in get_pymodule_path
return os.path.join(os.path.dirname(get_module(scrub(modulename)).file), *joins)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/init.py”, line 564, in get_module
return importlib.import_module(modulename)
File “/usr/lib/python2.7/importlib/init.py”, line 37, in import_module
import(name)
ImportError: No module named frappe_subscription
ubuntu@ubuntu-amd64:~$ bench migrate
Migrating site1.local
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/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 77, in
main()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 14, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 664, in call
return self.main(*args, **kwargs)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 644, in main
rv = self.invoke(ctx)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 991, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 991, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 837, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 464, in invoke
return callback(*args, **kwargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands.py”, line 28, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands.py”, line 205, in migrate
frappe.modules.patch_handler.run_all()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 23, in run_all
for patch in get_all_patches():
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 37, in get_all_patches
patches.extend(frappe.get_file_items(frappe.get_pymodule_path(app, “patches.txt”)))
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/init.py”, line 601, in get_pymodule_path
return os.path.join(os.path.dirname(get_module(scrub(modulename)).file), *joins)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/init.py”, line 564, in get_module
return importlib.import_module(modulename)
File “/usr/lib/python2.7/importlib/init.py”, line 37, in import_module
import(name)
ImportError: No module named frappe_subscription

Sorry for the late reply,

I forgot mention that you should ignore bench update --patch

Can you try again

bench update --pull
bench restore /path/to/sqlfile

I am able to enter the first command with error messages, but when I try to
restore, I get this now:

ubuntu@ubuntu-amd64:~$ bench --force restore
/home/ubuntu/en_fbademo_may2015.sqlMySQL root password:
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/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line
77, in
main()
File
“/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line
14, in main
click.Group(commands=commands)(prog_name=‘bench’)
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”,
line 664, in call
return self.main(*args, **kwargs)
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”,
line 644, in main
rv = self.invoke(ctx)
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”,
line 991, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”,
line 991, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”,
line 837, in invoke
return ctx.invoke(self.callback, **ctx.params)
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”,
line 464, in invoke
return callback(*args, **kwargs)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands.py”, line 28,
in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/ubuntu/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=context.verbose, install_apps=install_app,
source_sql=sql_file_path, force=context.force)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/commands.py”, line 86,
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=source_sql,force=force, reinstall=reinstall)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/installer.py”, line
38, in install_db
remove_missing_apps()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/installer.py”, line
242, in remove_missing_apps
frappe.db.set_global(“installed_apps”, json.dumps(installed_apps))
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database.py”, line
621, in set_global
self.set_default(key, val, user)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/database.py”, line
629, in set_default
frappe.defaults.set_default(key, val, parent, parenttype)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/defaults.py”, line 87,
in set_default
add_default(key, value, parent)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/defaults.py”, line 98,
in add_default
d.insert(ignore_permissions=True)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/model/document.py”,
line 170, in insert
self._set_docstatus_user_and_timestamp()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/model/document.py”,
line 281, in _set_docstatus_user_and_timestamp
self.modified = now()
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/data.py”, line
136, in now
return now_datetime().strftime(DATETIME_FORMAT)
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/data.py”, line
108, in now_datetime
return convert_utc_to_user_timezone(datetime.datetime.utcnow())
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/data.py”, line
126, in convert_utc_to_user_timezone
return utcnow.astimezone(timezone(get_time_zone()))
File “/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/data.py”, line
120, in get_time_zone
return frappe.cache().get_value(“time_zone”, _get_time_zone)
File
“/home/ubuntu/frappe-bench/apps/frappe/frappe/utils/redis_wrapper.py”, line
41, in get_value
val = self.get(key)
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/redis/client.py”,
line 863, in get
return self.execute_command(‘GET’, name)
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/redis/client.py”,
line 565, in execute_command
return self.parse_response(connection, command_name, **options)
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/redis/client.py”,
line 577, in parse_response
response = connection.read_response()
File
“/home/ubuntu/frappe-bench/env/local/lib/python2.7/site-packages/redis/connection.py”,
line 574, in read_response
raise response
redis.exceptions.ResponseError: WRONGTYPE Operation against a key holding
the wrong kind of value
ubuntu@ubuntu-amd64:~$ ^C

Try

redis-cli -p 11311 flushall
bench restore /path/to/sqlfile

Try,

redis-cli -p 11311 flushall



It worked! Thanks so much for your help

One last question - is there any way I get get the syntax for bench
commands? bench --help only gives me a list of commands, with no examples
Ex. I wanted to migrate the other sites, so I tried various combinations
like bench migrate sitename, bench migrate --site sitename etc.
None of them worked

bench --site all migrate

You can
bench {{command}} --help