(1365, 'Division by 0') On submit of invoices with zero grand total

Frappe Framework Bug
1

After doing some major updates on ERPNext and MariaDB on our system, a problem appears with submitting Sales Invoices or Delivery Notes with grand total of zero and are linked back to parent Sales Order. (We have been using it for free give away promotions)

Stack trace as follow -
Traceback (most recent call last):
File “/home/naymin/frappe-bench/apps/frappe/frappe/app.py”, line 57, in application
response = frappe.handler.handle()
File “/home/naymin/frappe-bench/apps/frappe/frappe/handler.py”, line 22, in handle
data = execute_cmd(cmd)
File “/home/naymin/frappe-bench/apps/frappe/frappe/handler.py”, line 53, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “/home/naymin/frappe-bench/apps/frappe/frappe/init.py”, line 935, in call
return fn(*args, **newargs)
File “/home/naymin/frappe-bench/apps/frappe/frappe/desk/form/save.py”, line 19, in savedocs
doc.submit()
File “/home/naymin/frappe-bench/apps/frappe/frappe/model/document.py”, line 778, in submit
self._submit()
File “/home/naymin/frappe-bench/apps/frappe/frappe/model/document.py”, line 767, in _submit
self.save()
File “/home/naymin/frappe-bench/apps/frappe/frappe/model/document.py”, line 256, in save
return self._save(*args, **kwargs)
File “/home/naymin/frappe-bench/apps/frappe/frappe/model/document.py”, line 307, in _save
self.run_post_save_methods()
File “/home/naymin/frappe-bench/apps/frappe/frappe/model/document.py”, line 831, in run_post_save_methods
self.run_method(“on_submit”)
File “/home/naymin/frappe-bench/apps/frappe/frappe/model/document.py”, line 702, in run_method
out = Document.hook(fn)(self, *args, **kwargs)
File “/home/naymin/frappe-bench/apps/frappe/frappe/model/document.py”, line 965, in composer
return composed(self, method, *args, **kwargs)
File “/home/naymin/frappe-bench/apps/frappe/frappe/model/document.py”, line 948, in runner
add_to_return_value(self, fn(self, *args, **kwargs))
File “/home/naymin/frappe-bench/apps/frappe/frappe/model/document.py”, line 696, in
fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
File “/home/naymin/frappe-bench/apps/erpnext/erpnext/accounts/doctype/sales_invoice/sales_invoice.py”, line 122, in on_submit
self.update_prevdoc_status()
File “/home/naymin/frappe-bench/apps/erpnext/erpnext/controllers/status_updater.py”, line 110, in update_prevdoc_status
self.update_qty()
File “/home/naymin/frappe-bench/apps/erpnext/erpnext/controllers/status_updater.py”, line 227, in update_qty
self._update_percent_field_in_targets(args, update_modified)
File “/home/naymin/frappe-bench/apps/erpnext/erpnext/controllers/status_updater.py”, line 272, in _update_percent_field_in_targets
self._update_percent_field(args, update_modified)
File “/home/naymin/frappe-bench/apps/erpnext/erpnext/controllers/status_updater.py”, line 287, in _update_percent_field
where name=‘%(name)s’“”" % args)
File “/home/naymin/frappe-bench/apps/frappe/frappe/database.py”, line 163, in sql
self._cursor.execute(query)
File “/home/naymin/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 250, in execute
self.errorhandler(self, exc, value)
File “/home/naymin/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 50, in defaulterrorhandler
raise errorvalue
OperationalError: (1365, ‘Division by 0’)

But the code seems fine and hasn’t changed much before I faced this error.

Further digging shows this probably has to do with Feb 2017 update of MariaDB. There is a change in default setting of mariadb that deals with division by 0. Original setting is to return NULL and the change in version 10.2.4. The particular setting is ERROR_FOR_DIVISION_BY_ZERO.

Disabling this setting would be a quick fix (or may be removing strict mode entirely) but does this call for code fix on ERPNext as well?

2

You might wanna check it again by pulling the latest code. Can’t seem to recreate it. Also as far as I know, making an invoice with grand total of zero wont make a GL entry of it, so I don’t know if that will be an issue or not. But as for making a zero total record, seems to be working.

3

The invoice or delivery note has to be linked to parent Sales Order. The problem is not with GL Entry but with updating parent Sales Order percentages.

Also MariaDB version needs to be >10.2.4 with default settings.

4

Hey @nay_min, any fix on this issue?

5

The fix might cause too much regression for such a fringe case like this.
I think just changing MariaDB setting is a better solution.

6

@nay_min Simply removing ERROR_FOR_DIVISION_BY_ZERO did not help since it is set again on mariadb services restart. These steps worked for me:

  • In my.cnf (mine was located in user/local/etc) add lines (you are remove what all you don’t need):

[mysqld]
sql_mode = “STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

  • Restart mariadb
7

Is there any other way to rectify above issue.
Previous solution doesn’t work for me.

8

Even after removing ERROR_FOR_DIVISION_BY_ZERO?

9

At my sql_mode there is only this option NO_ENGINE_SUBSTITUTION,TRADITIONAL and I added STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER to it.

10

Is your mariadb version over 10.2.4?

11

Mariadb version is 10.2.16

12

You got the same Division by Zero error?

13

Yes the same error. Operational Error : Division By 0

14

Shall I degrade the Mariadb ver to 10.0.35

15

@nay_min thanks for you recursive support and time so far. Your suggestion helped me to solve the problem.