MySQL Error 1055 information_schema.PROFILING.SEQ on every query

Question

I'm using a recent install of mysql from the mysql repository, installed on Ubuntu 14.04. Every query I run results in the error below and I have been unable to find anything that discusses this via google or here.

For example, this (obviously for demonstration purposes only) query returns the following:

---

[SQL]SELECT * FROM tabcLocations

Affected rows: 0 Time: 0.705s

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

It returns the results of the query just fine, but throws an error on every query, which will obviously affect error handling in my applications. Any suggestions on how to resolve this? It's fairly maddening at the moment.

Answer 1

Did you by any chance remove the information_schema or the mysql database? These two databases/file shoud never be removed or your database won't work as it should.

Answer 2

Is your Mysql Client navicat? Some old versions of navicat will send profiling sql after your sql by default.The profiling sql like this

SELECT STATE , ROUND(SUM(DURATION),7) , CONCAT(ROUND(SUM(DURATION)/0.000846*100,3), '%')
FROM INFORMATION_SCHEMA.PROFILING 
WHERE QUERY_ID=39 GROUP BY STATE ORDER BY SEQ

But this sql is not valid for SQL92.So you can consider close this default behavior of navicat.

Answer 3

Put a line with sql-mode="" into your mysqld.cnf (linux) or my.ini (win) and restart MySQL server.

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.

See docs here: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html