After_migrate hook problem with implicit commit

We have a customed Sales Order Item doctype that worked, on Version 13.beta

At the same time, we need to change the database field type of this field (and some related fields) from decimal(18,6) to decimal(18,3) due to high exchange rate of VND over USD (around 24,000 VND per 1 USD). We did this by making a patch, this can run properly with bench update or directly from console.

But every time we run bench migrate, the customised json applied again on the database and change the field type back to decimal(18,6). So we tried to put an after_migrate hook to run the patch right after each migrate. But keep getting: “Exception: This statement can cause implicit commit” which is a safety checking method of frappe.

Could you please help on how we can apply this patch on every migrate?

Run bench update while we need only bench migrate is not desiable.

I’m having the same issue. Did anyone find out the cause?

Could you show the code that you’re executing here?

This code is on the patch change_data_type.py file:

frappe.db.sql(“ALTER table tabSales Order Item MODIFY gross_profit decimal(18,3) DEFAULT 0 NOT NULL;”)

This is the code on hook.py:
after_migrate = “eupapp.eupapp.change_data_type”

The patch can run on console, but failed if run on the after_migrate hook.

Instead of doing this, can’t you just increase the Precision of the field via Customize Form?

The precision field is empty here, I think adjusting the value here should be enough.

No, it’s not how Frappe present the number which you can adjust on settings like that, but how MariaDB keep it.
For us, we need to hold numbers over 999 billions (over 12 digits) but DB can only hold up to 999 billions, so we must alter the table column data type. This is doable via above script using bench console, but not work with hook.

Any suggestion?

~~Don’t think so, seems to be from https://mariadb.com/kb/en/sql-statements-that-cause-an-implicit-commit/~~

I don’t think you can bypass this :confused:

Yes, the checking is in Frappe code for a safety reason so I don’t want to override it (which is doable).
As the bench update script can run similar patches like in the bellow discussion, so I just want to find a way to do the same, maybe by turn off the checking, then run, and turn it back on.

I’ve found a workaround of overrides the frappe.databse.schema.get_definition function to set the data type to 15 digits so currently solved the problems.
Here it is for anyone who have same problem:
Create an schema_overrides.py with:

#
def get_definition(fieldtype, precision=None, length=None):
	d = frappe.db.type_map.get(fieldtype)

	# convert int to long int if the length of the int is greater than 11
	if fieldtype == "Int" and length and length > 11:
		d = frappe.db.type_map.get("Long Int")

	if not d: return

	coltype = d[0]
	size = d[1] if d[1] else None

	if size:
		# Overrides the function to get 15 digits before the dot
		if fieldtype in ["Float", "Currency", "Percent"] and cint(precision) <= 3 :
			size = '18,3'
		elif fieldtype in ["Float", "Currency", "Percent"] and cint(precision) > 3 :
			size = '21,6'
		
		if coltype == "varchar" and length:
			size = length

	if size is not None:
		coltype = "{coltype}({size})".format(coltype=coltype, size=size)

	return coltype

And on the hook.py put this to override the schema get_definition function:
#
import frappe.database.schema as _fschema
import path_to_override_file.schema_overrides as _cschema
_fschema.get_definition = _cschema.get_definition