pymysql.OperationalError: (1292, "Truncated Incorrect DECIMAL value: 'No'")

ERPNext
1

Hi

I am getting gui error while trying to submit a purchase receipt.

Screenshot_20230226_111703
Screenshot_20230226_1117031114×341 28.1 KB

Detailed error log:

App Versions
{
“erpnext”: “13.46.1”,
“frappe”: “13.49.2”,
“ksa_vat”: “0.0.1”
}

Route

Form/Purchase Receipt/CBX-PREC-00453
Trackeback

Traceback (most recent call last):
File “apps/frappe/frappe/app.py”, line 69, in application
response = frappe.api.handle()
File “apps/frappe/frappe/api.py”, line 55, in handle
return frappe.handler.handle()
File “apps/frappe/frappe/handler.py”, line 38, in handle
data = execute_cmd(cmd)
File “apps/frappe/frappe/handler.py”, line 76, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “apps/frappe/frappe/init.py”, line 1473, in call
return fn(*args, **newargs)
File “apps/frappe/frappe/desk/form/save.py”, line 22, in savedocs
doc.submit()
File “apps/frappe/frappe/model/document.py”, line 1020, in submit
return self._submit()
File “apps/frappe/frappe/model/document.py”, line 1009, in _submit
return self.save()
File “apps/frappe/frappe/model/document.py”, line 312, in save
return self._save(*args, **kwargs)
File “apps/frappe/frappe/model/document.py”, line 366, in _save
self.run_post_save_methods()
File “apps/frappe/frappe/model/document.py”, line 1090, in run_post_save_methods
self.run_method(“on_submit”)
File “apps/frappe/frappe/model/document.py”, line 943, in run_method
out = Document.hook(fn)(self, *args, **kwargs)
File “apps/frappe/frappe/model/document.py”, line 1264, in composer
return composed(self, method, *args, **kwargs)
File “apps/frappe/frappe/model/document.py”, line 1246, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File “apps/frappe/frappe/model/document.py”, line 940, in fn
return method_object(*args, **kwargs)
File “apps/erpnext/erpnext/stock/doctype/purchase_receipt/purchase_receipt.py”, line 230, in on_submit
self.update_stock_ledger()
File “apps/erpnext/erpnext/controllers/buying_controller.py”, line 549, in update_stock_ledger
self.make_sl_entries(
File “apps/erpnext/erpnext/controllers/stock_controller.py”, line 426, in make_sl_entries
make_sl_entries(sl_entries, allow_negative_stock, via_landed_cost_voucher)
File “apps/erpnext/erpnext/stock/stock_ledger.py”, line 84, in make_sl_entries
repost_current_voucher(args, allow_negative_stock, via_landed_cost_voucher)
File “apps/erpnext/erpnext/stock/stock_ledger.py”, line 117, in repost_current_voucher
update_qty_in_future_sle(args, allow_negative_stock)
File “apps/erpnext/erpnext/stock/stock_ledger.py”, line 1264, in update_qty_in_future_sle
frappe.db.sql(
File “apps/frappe/frappe/database/database.py”, line 187, in sql
self._cursor.execute(query, values)
File “env/lib/python3.10/site-packages/pymysql/cursors.py”, line 148, in execute
result = self._query(query)
File “env/lib/python3.10/site-packages/pymysql/cursors.py”, line 310, in _query
conn.query(q)
File “env/lib/python3.10/site-packages/pymysql/connections.py”, line 548, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File “env/lib/python3.10/site-packages/pymysql/connections.py”, line 775, in _read_query_result
result.read()
File “env/lib/python3.10/site-packages/pymysql/connections.py”, line 1156, in read
first_packet = self.connection._read_packet()
File “env/lib/python3.10/site-packages/pymysql/connections.py”, line 725, in _read_packet
packet.raise_for_error()
File “env/lib/python3.10/site-packages/pymysql/protocol.py”, line 221, in raise_for_error
err.raise_mysql_exception(self._data)
File “env/lib/python3.10/site-packages/pymysql/err.py”, line 143, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.OperationalError: (1292, “Truncated incorrect DECIMAL value: ‘No’”)

Request Data

{
“type”: “POST”,
“args”: {
“doc”: “{“name”:“CBX-PREC-00453”,“owner”:”

…
…
…

“action”: “Submit”
},
“btn”: {
“jQuery224042775570635278041”: {
“events”: {
“click”: [
{
“type”: “click”,
“origType”: “click”,
“guid”: 2518,
“namespace”: “”
}
]
}
}
},
“freeze”: true,
“headers”: {},
“error_handlers”: {},
“url”: “/api/method/frappe.desk.form.save.savedocs”
}

Response Data

{
“exception”: “pymysql.err.OperationalError: (1292, “Truncated incorrect DECIMAL value: ‘No’”)”,
“_server_messages”: “[“{"message": "Serial No <a href=\"[http://erp.abc.com/app/serial-no/FGT60FTK2209E\\\\\\\](http://erp.abc.com/app/serial-no/FGT60FTK2209E\\\\\\\)”>FGT60FTK2209E Created", "title": "Purchase Receipt Created"}”]"
}

I had tried many workarrounds and could not find a solution. Could any one please help to resolve this?

2

Hi,

The code should be examined to identify where the incorrect value is coming from and ensure that it is properly converted to a Decimal. It may also be necessary to update the database schema to ensure that the column in question is properly defined as a Decimal type.

It is worth noting that this error is specific to the ksa_vat app, as that is the only custom app mentioned in the error log. If the issue cannot be resolved by examining the ksa_vat app, it may be necessary to look at other parts of the codebase that interact with the make_sl_entries function.

Hope this will help you out.

Thank you.

3

@VINOTH

Thanks for the quick response.

Unfortunately, I am not a developer to dig into the code.

For your info, this is a migrated server from V12 to V13

One thing noticed now, the error pops up only while creating a purchase receipt for existing serialized items. There is no issue in creating purchase receipt for non-serialized items or newly created serialized items.

Any thoughts?

4

As I mentioned, the error pops only for existing items (not for all non-serialized items, but for all serialized-items). There is no problem in creating a purchase receipt for a new item (serialized/non-serialized).

After trying many options I can confirm that the error throws only for stock maintaining existing items.

I have cross checked the Item table column data type between a fresh V13 install and the migrated V13. All good. But, noted that there was a difference in decimal precision of few columns of table “Stock Ledger Entry”. It was decimal(18,6) on migrated V13 and decimal(21,9) on fresh V13. I have updated all the columns of migrated V13 to decimal(21,9). This did not resolve my problem.

Anyone have experience or knowledge about this issue, please help.

5

Finally…Got it solved!!!

This was due to the column name ‘is_cancelled’ in table tabStock Ledger Entry.
The column type was in varchar. I have changed it to int. And everything works great!

Thanks everyone for supporting.