Encounter Error in v4 to v5 Migration - patches v5_0 rename_table_fieldnames

ERPNext
1

I encounter this issue when performing an upgrade to v5. This is a pretty vanilla version of v4 with very minimal customizations. I get this when executing “bench update --patch”.

I have also tried “bench update --requirements” and “bench retry-upgrade” before executing “bench update --patch”.

Executing erpnext.patches.v5_0.rename_table_fieldnames in erp_wondev (erp_wondev)
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/won/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 77, in
main()
File “/home/won/frappe-bench/apps/frappe/frappe/utils/bench_helper.py”, line 14, in main
click.Group(commands=commands)(prog_name=‘bench’)
File “/home/won/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 610, in call
return self.main(*args, **kwargs)
File “/home/won/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 590, in main
rv = self.invoke(ctx)
File “/home/won/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 936, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/won/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 936, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/home/won/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 782, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/home/won/frappe-bench/env/local/lib/python2.7/site-packages/click/core.py”, line 416, in invoke
return callback(*args, **kwargs)
File “/home/won/frappe-bench/apps/frappe/frappe/commands.py”, line 28, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/won/frappe-bench/apps/frappe/frappe/commands.py”, line 202, in migrate
frappe.modules.patch_handler.run_all()
File “/home/won/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 25, in run_all
if not run_single(patchmodule = patch):
File “/home/won/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 52, in run_single
return execute_patch(patchmodule, method, methodargs)
File “/home/won/frappe-bench/apps/frappe/frappe/modules/patch_handler.py”, line 68, in execute_patch
frappe.get_attr(patchmodule.split()[0] + “.execute”)()
File “/home/won/frappe-bench/apps/erpnext/erpnext/patches/v5_0/rename_table_fieldnames.py”, line 231, in execute
frappe.reload_doc(get_doctype_module(dn), “doctype”, scrub(dn))
File “/home/won/frappe-bench/apps/frappe/frappe/init.py”, line 538, in reload_doc
return frappe.modules.reload_doc(module, dt, dn, force=force)
File “/home/won/frappe-bench/apps/frappe/frappe/modules/init.py”, line 37, in reload_doc
return import_files(module, dt, dn, force=force)
File “/home/won/frappe-bench/apps/frappe/frappe/modules/import_file.py”, line 17, in import_files
return import_file(module, dt, dn, force=force)
File “/home/won/frappe-bench/apps/frappe/frappe/modules/import_file.py”, line 22, in import_file
ret = import_file_by_path(path, force)
File “/home/won/frappe-bench/apps/frappe/frappe/modules/import_file.py”, line 54, in import_file_by_path
import_doc(doc, force=force, data_import=data_import)
File “/home/won/frappe-bench/apps/frappe/frappe/modules/import_file.py”, line 120, in import_doc
doc.insert()
File “/home/won/frappe-bench/apps/frappe/frappe/model/document.py”, line 194, in insert
self.run_post_save_methods()
File “/home/won/frappe-bench/apps/frappe/frappe/model/document.py”, line 558, in run_post_save_methods
self.run_method(“on_update”)
File “/home/won/frappe-bench/apps/frappe/frappe/model/document.py”, line 506, in run_method
return Document.hook(fn)(self, *args, **kwargs)
File “/home/won/frappe-bench/apps/frappe/frappe/model/document.py”, line 619, in composer
return composed(self, method, *args, **kwargs)
File “/home/won/frappe-bench/apps/frappe/frappe/model/document.py”, line 602, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File “/home/won/frappe-bench/apps/frappe/frappe/model/document.py”, line 500, in
fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
File “/home/won/frappe-bench/apps/frappe/frappe/core/doctype/doctype/doctype.py”, line 102, in on_update
updatedb(self.name)
File “/home/won/frappe-bench/apps/frappe/frappe/model/db_schema.py”, line 62, in updatedb
tab.sync()
File “/home/won/frappe-bench/apps/frappe/frappe/model/db_schema.py”, line 88, in sync
self.alter()
File “/home/won/frappe-bench/apps/frappe/frappe/model/db_schema.py”, line 237, in alter
frappe.db.sql(“alter table {} {}”.format(self.name, ", ".join(query)))
File “/home/won/frappe-bench/apps/frappe/frappe/database.py”, line 143, in sql
self._cursor.execute(query)
File “/home/won/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 205, in execute
self.errorhandler(self, exc, value)
File “/home/won/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1918, “Encountered illegal value ‘’ when converting to DECIMAL”)
Traceback (most recent call last):
File “/usr/local/bin/bench”, line 9, in
load_entry_point(‘bench==0.1’, ‘console_scripts’, ‘bench’)()
File “/home/won/bench-repo/bench/cli.py”, line 55, in cli
bench()
File “/usr/local/lib/python2.7/dist-packages/click/core.py”, line 610, in call
return self.main(*args, **kwargs)
File “/usr/local/lib/python2.7/dist-packages/click/core.py”, line 590, in main
rv = self.invoke(ctx)
File “/usr/local/lib/python2.7/dist-packages/click/core.py”, line 936, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
File “/usr/local/lib/python2.7/dist-packages/click/core.py”, line 782, in invoke
return ctx.invoke(self.callback, **ctx.params)
File “/usr/local/lib/python2.7/dist-packages/click/core.py”, line 416, in invoke
return callback(*args, **kwargs)
File “/home/won/bench-repo/bench/cli.py”, line 263, in retry_upgrade
patch_sites()
File “/home/won/bench-repo/bench/utils.py”, line 118, in patch_sites
run_frappe_cmd(‘–site’, ‘all’, ‘migrate’, bench=bench)
File “/home/won/bench-repo/bench/utils.py”, line 427, in run_frappe_cmd
subprocess.check_call((f, ‘-m’, ‘frappe.utils.bench_helper’, ‘frappe’) + args, cwd=sites_dir)
File “/usr/lib/python2.7/subprocess.py”, line 540, in check_call
raise CalledProcessError(retcode, cmd)
subprocess.CalledProcessError: Command ‘(’/home/won/frappe-bench/env/bin/python’, ‘-m’, ‘frappe.utils.bench_helper’, ‘frappe’, ‘–site’, ‘all’, ‘migrate’)’ returned non-zero exit status 1

2

Looks like you could be having a custom field that has had non-decimal values but you recently changed its fieldtype to float or currency. Can you check this?

-Anand.

3

I just checked and we only have a handful of custom fields which are all Data types. I checked and we did not change fieldtype as well. Is there a way I can see what commands are in the patch? Perhaps I can check why my DB is returning such an error.

4

I ran a query log and found the SQL statement that was causing this error:

alter table tabJournal Entry add column title varchar(255), add column stock_entry varchar(255), change user_remark user_remark text, change remark remark text, change total_amount total_amount decimal(18,6), alter column write_off_amount set default null, alter column total_credit set default null, alter column difference set default null, alter column total_debit set default null

based on the query i believe its the total_amount field which is currently a VARCHAR that has error when changing to Decimal.

The following is a list of all the total_amount entries in the tabJournal Entry table. I highly suspect it is the currency characters that are causing this problem. Let me try to remove them and retry the patch. Will let you know once done.

5

Once i removed the currency characters I get the following error " (1292, “Truncated incorrect DECIMAL value: ‘6,559.97’”)"

I noticed that there are “,” commas in the values and so proceed to remove them. Once I removed them I finally get through the patch :smile:

Perhaps there are some tweaks required to the patch? Hope this helps in fixing it for other users.

6

I have been encountering a similar issue.

I then found, that I had a column “skonto” in the database with type varchar(140). When I checken in ERPNext UI it was field type “Currency” which is database type decimal(21,9). I deleted the column 1st from erpnext UI, then from database. Then recreated with the correct type.

The problem arose through creating the field as type “data” first and trying to change at a later point in time. Something most of gone wrong there…