Error while open module: "Expression #1 of ORDER BY clause is not in GROUP BY clause"

Frappe Framework Bug
1

HI all,

I just have installed new version of erpnext in my laptop…
I installed and setup. But when i try to open the module or docs, it shows the following error message.

Traceback (most recent call last):
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/app.py", line 55, in application
    response = frappe.handler.handle()
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/handler.py", line 19, in handle
    execute_cmd(cmd)
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/handler.py", line 36, in execute_cmd
    ret = frappe.call(method, **frappe.form_dict)
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/__init__.py", line 879, in call
    return fn(*args, **newargs)
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/desk/reportview.py", line 151, in get_stats
    filters=["ifnull(`%s`,'')!=''" % tag], group_by=tag, as_list=True)
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/desk/reportview.py", line 21, in execute
    return DatabaseQuery(doctype).execute(*args, **kwargs)
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/db_query.py", line 79, in execute
    result = self.build_and_run()
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/model/db_query.py", line 103, in build_and_run
    return frappe.db.sql(query, as_dict=not self.as_list, debug=self.debug, update=self.update)
  File "/home/erpnext/frappe-bench/apps/frappe/frappe/database.py", line 148, in sql
    self._cursor.execute(query)
  File "/home/erpnext/frappe-bench/env/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/erpnext/frappe-bench/env/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (1055, "Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column '8188f50dfdd09ad8.tabCustomer.modified' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

Anybody can help me to resolve this please?

2

@Amalendu Are you still facing this issue?

3

@KanchanChauhan
Yes, the issue is still there in my laptop…

4

@Amalendu

Try bench --site <site_name> migrate.

Hope this works :smile:

Also, how did you install new version on your laptop?

5

Do bench --site <site-name> mysql
Then type SELECT @@sql_mode;

And tell us what does it return.

6 
  <deleted>
File "/home/me/python/cobaenv/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
        raise errorclass, errorvalue
    OperationalError: (1055, "Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column '00299a408dc3498a.tabArticle.modified' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

I try to develop library mangement sample app using frappe following this tutorial, and apparently came up with the same problem.

Since there is no progress on this topic, I hope someone can help me. Or maybe @Amalendu already has the solution, please share.

Following the discussion, as @KanchanChauhan request, here are the result:

> mysql> SELECT @@sql_mode;
> +-------------------------------------------------------------------------------------------------------------------------------------------+
> | @@sql_mode                                                                                                                                |
> +-------------------------------------------------------------------------------------------------------------------------------------------+
> | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
> +-------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0.00 sec)

Thanks in advance.

7

Hello @grinbin

I was having the same problem and I couldn’t solved it using the method suggested above. Since I had no time, I reinstalled everything.

8

Thanks for the information @Amalendu, I’m curious with how to solve the problem. :slight_smile:

9

@grinbin

:thumbsup: