Getting error while restoring and migrating site,"pymysql.err.DataError: (1265, u"Data truncated for column 'discount_amount' at row 1")"

Trying to restore an erpnext 9 version to a new server, getting error while trying to migrate the newly created site, after uploading database.

Main error that I am getting is

“pymysql.err.DataError: (1265, u"Data truncated for column ‘discount_amount’ at row 1”)"

it is refering to table “tabDelivery Note”

tried a similar thread on the form and followed to use their solution as below

Their solution was as below for similar error , but different table

update tabLeave Type set max_days_allowed = ‘0’ where trim(coalesce(max_days_allowed, ‘’)) = ‘’;

I used below command

update tabDelivery Note set discount_amount = ‘0’ where trim(coalesce(discount_amount, ‘’)) = ‘’;

still same error, even deleted all data from tabLeave , still getting same error

Any help will be really appriciated.Below is the last part of the error , when trying to get migrate site command work.

File “/home/frappe/frappe-bench/env/lib/python2.7/site-packages/click/”, line 17, in new_func
return f(get_current_context(), *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/”, line 25, in _func
ret = f(frappe._dict(ctx.obj), *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/commands/”, line 227, in migrate
migrate(context.verbose, rebuild_website=rebuild_website)
File “/home/frappe/frappe-bench/apps/frappe/frappe/”, line 38, in migrate
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/”, line 29, in run_all
if not run_single(patchmodule = patch):
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/”, line 63, in run_single
return execute_patch(patchmodule, method, methodargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/modules/”, line 83, in execute_patch
frappe.get_attr(patchmodule.split()[0] + “.execute”)()
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/patches/v8_7/”, line 17, in execute
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/regional/india/”, line 309, in make_custom_fields
create_custom_fields(custom_fields, ignore_validate = frappe.flags.in_patch, update=update)
File “/home/frappe/frappe-bench/apps/frappe/frappe/custom/doctype/custom_field/”, line 148, in create_custom_fields
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 260, in save
return self._save(*args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 313, in _save
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 905, in run_post_save_methods
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 772, in run_method
out = Document.hook(fn)(self, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 1048, in composer
return composed(self, method, *args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 1031, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 766, in
fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/custom/doctype/custom_field/”, line 68, in on_update
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 78, in updatedb
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 160, in sync
File “/home/frappe/frappe-bench/apps/frappe/frappe/model/”, line 360, in alter
raise e
pymysql.err.DataError: (1265, u"Data truncated for column ‘discount_amount’ at row 1")

If you’ve changed versions, you should run a bench migrate to ensure that the DB’s are aligned with the code

I had installed latest erpnext on new server, and had some previous projects in erpnext, to restore.

After restoring database, I faced this issue, while trying to complete restore using “sudo site (site name) migrate” command.

Although only solution I found was to disable sql secure mode, and then migrate site.

Still trying to understand if it was right approach and secure or there is any better way.