Issues importing a version 7.2 database into current version

Dritzzka · November 8, 2017, 3:17pm

Hi guys, I’m experiencing a issue trying to import my version 7.2 database into a fresh install.

Here is the error:

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
You have new mail.
Last login: Tue Nov 7 14:09:06 2017 from 10.1.10.69
virtual@DevERP:~$ cd /home/frappe/frappe-bench/
virtual@DevERP:/home/frappe/frappe-bench$ sudo su
[sudo] password for virtual:
root@DevERP:/home/frappe/frappe-bench# bench --force restore 20171107_132629_5aa2f73f_database.sql
MySQL 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/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 110, in restore
force=context.force)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py”, line 60, 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 768, in get_tables
return [d[0] for d in self.sql(“show tables”)]
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 163, in sql
self._cursor.execute(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 250, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 50, in defaulterrorhandler
raise errorvalue
_mysql_exceptions.OperationalError: (2006, ‘MySQL server has gone away’)

Any recommendations would be greatly appreciated.

clarkej · November 8, 2017, 5:28pm

Welcome!

A search of the forum for this:

OperationalError: (2006, ‘MySQL server has gone away

will point you what to do for this

good luck

Dritzzka · November 14, 2017, 3:24pm

I managed to get my database in finally, now I’m running into problems running bench migrate

Also to note I’m on a VMware EXSi install.

Here are the steps of what I did:

Install a new instance of ERPnext current.

Take a VM snapshot before database import(after copying to the VM)

Get DATABASE NAME AND PASSWORD(on new install of erpNext) from sites/site_name/site_config.json

gzip -d database_file_name.sql.gz

mysql -u root -p database_name_found_in_JSONfile < Filename.sql

Get any custome apps you might have.
In my case it was just one:
bench get-app Metalcraft GitHub - cgpurbaugh/metalcraft
bench install-app Metalcraft

sudo bench migrate from /frappe/frappe-bench/ folder

For every traceback from a patch, I commented out the following patches in apps/frappe/frappe/patches.txt
execute:frappe.reload_doc(‘core’, ‘doctype’, ‘module_def’) #2017-09-22 in site1.local
Executing frappe.patches.v8_0.rename_page_role_to_has_role #2017-03-16 in site1.local
Executing frappe.patches.v7_2.setup_custom_perms #2017-01-19 in site1.local
Executing frappe.patches.v8_0.set_user_permission_for_page_and_report #2017-03-20 in site1.local
Executing frappe.patches.v7_2.update_communications in site1.local
Executing frappe.patches.v8_0.newsletter_childtable_migrate in site1.local
Executing frappe.patches.v8_0.set_currency_field_precision # 2017-05-09
Executing frappe.patches.v8_0.rename_print_to_printing in site1.local
Executing execute:frappe.get_single(‘Domain Settings’).save() in site1.local

For every traceback I commented out patches in apps/erpnext/erpnext/patches.txt
Executing erpnext.patches.v8_0.create_domain_docs #16-05-2017
Executing erpnext.patches.v7_2.update_assessment_modules in site1.local
Executing erpnext.patches.v7_2.arrear_leave_encashment_as_salary_component in site1.local
Executing erpnext.patches.v7_2.make_all_assessment_group in site1.local
Executing erpnext.patches.v8_4.make_scorecard_records in site1.local
Executing erpnext.patches.v8_5.update_existing_data_in_project_type in site1.local
Executing erpnext.patches.v8_5.remove_project_type_property_setter in site1.local
Executing erpnext.patches.v8_7.set_offline_in_pos_settings #11-09-17 in site1.local
Executing erpnext.patches.v8_9.set_print_zero_amount_taxes in site1.local
Executing erpnext.patches.v8_9.set_default_customer_group in site1.local
Executing erpnext.patches.v8_9.set_default_fields_in_variant_settings in site1.local
Executing erpnext.patches.v9_0.set_uoms_in_variant_field in site1.local
Executing erpnext.patches.v9_0.copy_old_fees_field_data in site1.local

Now I got new error…

virtual@DevERP:/home/frappe/frappe-bench$ sudo bench migrate
Migrating site1.local
Updating DocTypes for frappe : [=== ]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 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 217, in migrate
migrate(context.verbose, rebuild_website=rebuild_website)
File “/home/frappe/frappe-bench/apps/frappe/frappe/migrate.py”, line 33, in migrate
frappe.model.sync.sync_all(verbose=verbose)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/sync.py”, line 19, in sync_all
sync_for(app, force, verbose=verbose, reset_permissions=reset_permissions)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/sync.py”, line 45, in sync_for
reset_permissions=reset_permissions, for_sync=True)
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/import_file.py”, line 58, in import_file_by_path
ignore_version=ignore_version, reset_permissions=reset_permissions)
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/import_file.py”, line 131, in import_doc
doc.insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 245, in insert
self.run_post_save_methods()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 828, in run_post_save_methods
self.run_method(“on_update”)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 702, in run_method
out = Document.hook(fn)(self, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 965, in composer
return composed(self, method, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 948, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 696, in
fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/core/doctype/doctype/doctype.py”, line 206, in on_update
make_module_and_roles(self)
File “/home/frappe/frappe-bench/apps/frappe/frappe/core/doctype/doctype/doctype.py”, line 783, in make_module_and_roles
r.insert()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 239, in insert
self.run_method(“after_insert”)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 702, in run_method
out = Document.hook(fn)(self, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 965, in composer
return composed(self, method, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 948, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 696, in
fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/core/doctype/role/role.py”, line 19, in after_insert
user.add_roles(self.name)
File “/home/frappe/frappe-bench/apps/frappe/frappe/core/doctype/user/user.py”, line 369, in add_roles
self.save()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 256, in save
return self._save(*args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 289, in _save
self._validate_links()
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/document.py”, line 671, in _validate_links
frappe.LinkValidationError)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 319, in throw
msgprint(msg, raise_exception=exc, title=title, indicator=‘red’)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 309, in msgprint
_raise_exception()
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 282, in _raise_exception
raise raise_exception(encode(msg))
frappe.exceptions.LinkValidationError: Could not find Row #39: Role: Physician, Row #40: Role: Healthcare Administrator, Row #41: Role: Laboratory User, Row #42: Role: Nursing User, Row #43: Role: LabTest Approver, Row #44: Role: Restaurant Manager, Row #47: Role: Patient, Row #65: Role: Laboratory User, Row #73: Role: Nursing User, Row #75: Role: Physician, Row #83: Role: Restaurant Manager

clarkej · November 14, 2017, 5:01pm

Umm each patch typically is a result of a database schema change - for eg a table structure change, a field that is moved or an attribute change. What each patch does is migrate and revise the database in the affected table(s) so the data remains ‘in sync’ as ERPNext evolves.

To comment out a few patches in a pinch can be ok assuming you know sql and can identify how to fix database issues that occur. But to comment out many patches means your database integrity is now questionable.

IMO best to restore your v7.2 data with you V9 instance, and run the patches (as prescribed is sequence) and deal with db migrate issues that arise.

Someone else may want to offer their opinion here of any other alternative strategy to follow here?

edit: The custom app by cpurbaugh that may require patching.

clarkej · November 14, 2017, 5:48pm

OTOH thinking it over a bit more you have come this far, what’s to lose -

If you can say delete these ‘nuisance’ rows assuming they do not apply in your domain and see what does or doesn’t blow up…

frappe.exceptions.LinkValidationError: Could not find Row #39: Role: Physician, Row #40: Role: Healthcare Administrator, Row #41: Role: Laboratory User, Row #42: Role: Nursing User, Row #43: Role: LabTest Approver, Row #44: Role: Restaurant Manager, Row #47: Role: Patient, Row #65: Role: Laboratory User, Row #73: Role: Nursing User, Row #75: Role: Physician, Row #83: Role: Restaurant Manager

Dritzzka · November 14, 2017, 6:40pm

I’ve taken another snapshot.
How would I go about trying to removing these rows?

clarkej · November 14, 2017, 8:23pm

ok in a ‘bench mysql’ session I hope to find a table that stores the links but none obvious to me. Sorry, not much of a learning to report. One would need to do some sql too that might clobber.

So I suggest back to the other plan to uncomment the patches and see how it goes!

Dritzzka · November 17, 2017, 3:59pm

I restored snapshot and tried again with updating.

frappe.exceptions.DuplicateEntryError: (u’Custom Script’, u’Purchase Order-Client’, IntegrityError(1062, “Duplicate entry ‘Purchase Order-Client’ for key ‘PRIMARY’”))

How would I go about deleteing this.?

Dritzzka · November 21, 2017, 7:55pm

I finally managed to get it to finally updated 9.2.15…
This is with no database modifications and the patches got installed.

This is what I did.

First I restored my snapshot of dev. back to working 6.12

I also got @cparthiban2k10 to remove a custom script.
That was the error for

frappe.exceptions.DuplicateEntryError: (u’Custom Script’, u’Purchase Order-Client’, IntegrityError(1062, “Duplicate entry ‘Purchase Order-Client’ for key ‘PRIMARY’”))

Next this is very important.

When it asks to stash changes… Make sure you run “git commands” as the frappe user. (DO NOT SUDO git stash)
This means cd’ing into folders apps/frappe apps/erpnext and in our case apps/metalcraft.
Then running sudo su frappe then followed by git stash.
If it says nothing to stash run “git add .”(including that period) then git stash
Update nodeJS to 8 prior to doing a bench update.

Updating Nodejs
curl -sL https://deb.nodesource.com/setup_8.x | sudo -E bash -
sudo apt-get install -y nodejs

Also recommend try updating Mariadb…

Now this is when I ran
bench update
If it still complains about stashing changes
cd back into a apps folders and try again.

Migrating site1.local
First patch error I got was for::

AttributeError: ‘DocType’ object has no attribute ‘restrict_to_domain’

This happened during executing:

frappe.reload_doc(‘core’, ‘doctype’, ‘module_def’) #2017-09-22

I commented it out in apps/frappe/frappe/patches.txt
Run bench update -patch or bench migrate.

If you run bench update the patches.txt file will revert back to regular.

Second error was for::

_mysql_exceptions.OperationalError: (1048, “Column ‘is_custom’ cannot be null”)

this happened during executing:

erpnext.patches.v8_0.create_domain_docs #16-05-2017

Commented it out in apps/erpnext/erpnext/patches.txt
reran bench update -patch

So it went and got a new error that I haven’t seen before.
This is during executing:
bench.patches.v3.deprecate_old_config

Which is located in frappe-bench/patches.txt

I tried then both bench update -patch and bench migrate here nothing to get past it.
Tried commenting it out… It would reappear. Tried running as bench sudo…
Nothing worked.

I tried updating Mariadb.

Updating Mariadb commands:
service mysql stop
sudo apt-get install mariadb-server mariadb-common libmariadbclient-dev
service mysql start
mysql_upgrade -p

Then ran bench update --reset as frappe.
It then ran and got through those patches 2 batches I commented out above.

clarkej · November 21, 2017, 8:56pm

Well done excellent news - you are an update expert graduate.

And by documenting your tribulations, you have inspired lots to persist

felix · November 22, 2017, 4:13am

Looks like you followed the right process. Always restore onto the same version the backup was taken from. Never restore onto a new version (yes there can be instances where this is fine, but those are very few, and this is not one of them).