Sales Register report throws OperationalError

ERPNext
1

Traceback (most recent call last): File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 55, in application response = frappe.handler.handle() File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 19, in handle execute_cmd(cmd) File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 36, in execute_cmd ret = frappe.call(method, **frappe.form_dict) File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 879, in call return fn(*args, **newargs) File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 88, in run res = frappe.get_attr(method_name)(frappe._dict(filters)) File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/sales_register/sales_register.py", line 12, in execute invoice_list = get_invoices(filters) File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/sales_register/sales_register.py", line 126, in get_invoices conditions, filters, as_dict=1) File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 137, in sql self._cursor.execute(query, values) File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute self.errorhandler(self, exc, value) File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue OperationalError: (1054, "Unknown column 'mode_of_payment' in 'field list'")

Any help is appreciated. :slight_smile:

2

Run bench update and try again.

3

Did not work. Also not able to view error snapshots. Getting folowing in Chrome console:
Uncaught TypeError: Cannot read property 'indexOf' of undefined

@KanchanChauhan Anything else I can do?

4

I did a fresh install on Ubuntu 14.04 yesterday. Still getting the same error. I am using the latest production version on Google Cloud. There is nothing unusual in the Chrome Developer console. Can anybody else replicate this?

5

Your bench update ran without any errors?

6

Yes it did. If you want we can establish TeamViewer for further diagnosis.

7

I got the urllib ssl warnings but I don’t think that’s the culprit.

8

It is complaining about Mode of Payment fields in Sales Register report which was introduced in this commit.
https://github.com/frappe/erpnext/commit/3323d06be03f3d15c2fdfb71c506e4f544458783#diff-ba272964cab2a232a5ea6cb0bd6b52b4

Try bench migrate once. If that does not work, try manually matching you files with this commit.

9

@KanchanChauhan I did bench migrate. I also manually checked /apps/erpnext for changes. What now? Error still exists. It appears only in the sales register and item-wise sales register. Purchase registers are unaffected.

10

Did you do any customizations?

11

None. I am talking about a fresh install.

12

I updated to 7.0.43. Still getting same error

13

The same error comes up in v7.0.44 as well.

Sales Register:

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 55, in application
    response = frappe.handler.handle()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 19, in handle
    execute_cmd(cmd)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 36, in execute_cmd
    ret = frappe.call(method, **frappe.form_dict)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 879, in call
    return fn(*args, **newargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 88, in run
    res = frappe.get_attr(method_name)(frappe._dict(filters))
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/sales_register/sales_register.py", line 12, in execute
    invoice_list = get_invoices(filters)
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/sales_register/sales_register.py", line 126, in get_invoices
    conditions, filters, as_dict=1)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 137, in sql
    self._cursor.execute(query, values)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1054, "Unknown column 'mode_of_payment' in 'field list'")

Item Wise Sales Register:

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 55, in application
    response = frappe.handler.handle()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 19, in handle
    execute_cmd(cmd)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 36, in execute_cmd
    ret = frappe.call(method, **frappe.form_dict)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 879, in call
    return fn(*args, **newargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 88, in run
    res = frappe.get_attr(method_name)(frappe._dict(filters))
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py", line 14, in execute
    item_list = get_items(filters)
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py", line 90, in get_items
    order by si.posting_date desc, si_item.item_code desc""" % conditions, filters, as_dict=1)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 137, in sql
    self._cursor.execute(query, values)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1054, "Unknown column 'si.mode_of_payment' in 'field list'")

Shall I create a Github issue regarding this?

14

try:
bench update -patch.

15

Pushed a fix in hotfix branch Fix in Sales Register for multiple mode of payments by nabinhait · Pull Request #6420 · frappe/erpnext · GitHub

It will released by the end of the day.

16

Thank you so much!
The error in item_wise_sales_register.py is gone. However, the error in sales_register.py persists with a different line number:

Traceback (most recent call last): File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 55, in application response = frappe.handler.handle() File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 19, in handle execute_cmd(cmd) File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 36, in execute_cmd ret = frappe.call(method, **frappe.form_dict) File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 879, in call return fn(*args, **newargs) File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 88, in run res = frappe.get_attr(method_name)(frappe._dict(filters)) File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/sales_register/sales_register.py", line 12, in execute invoice_list = get_invoices(filters) File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/sales_register/sales_register.py", line 130, in get_invoices conditions, filters, as_dict=1) File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 137, in sql self._cursor.execute(query, values) File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute self.errorhandler(self, exc, value) File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue OperationalError: (1054, "Unknown column 'mode_of_payment' in 'field list'")

17

Additional error encountered in Item Wise Sales Register:

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/apps/frappe/frappe/app.py", line 55, in application
    response = frappe.handler.handle()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 19, in handle
    execute_cmd(cmd)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/handler.py", line 36, in execute_cmd
    ret = frappe.call(method, **frappe.form_dict)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/__init__.py", line 879, in call
    return fn(*args, **newargs)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py", line 88, in run
    res = frappe.get_attr(method_name)(frappe._dict(filters))
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/item_wise_sales_register/item_wise_sales_register.py", line 25, in execute
    mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
  File "/home/frappe/frappe-bench/apps/erpnext/erpnext/accounts/report/sales_register/sales_register.py", line 201, in get_mode_of_payments
    ', '.join(['%s']*len(invoice_list)), tuple(invoice_list), as_dict=1)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database.py", line 148, in sql
    self._cursor.execute(query)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') group by parent, mode_of_payment' at line 2")
18

I have tracked down the error. The column ‘mode_of_payment’ does not exist in tabSales Invoice. Is it just me?

19

Pushed a fix in the latest release, thanks for reporting.

I did not encounter the problem yesterday, because as we don’t delete columns from db while deleting a field, the mode_of_payment column was there in my local database.

20

Fantastic! This fixed it.

I learnt a lot more about python and mysql in the process.

Thank you! You may close this thread.

1 Like