Incompatible with sql_mode 'only_full_group_by

Question

I am trying to run a query but I am getting the following error:

SQL Error (1055): Expression #14 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'gamesmart.gps.plays' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

When I check my sql_mode it is set to the following for both session and global:

So, why is it still giving me this error?

Mysql Version: 5.7.14

Answer 1

Here are two options that I found that worked (I was able to do one or the other):

• convert all the aggregate functions from something like this ifnull(gps.plays,0) to this ifnull(gps.plays,0) as plays in the select clause.
• in the .ini file set the following:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Answer 2

In your terminal:

mysql -u yourusername -p

Then type your password. It will bring you to the MySQL monitor.

Write this query:

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

After that, go to etc/mysql/my.cnf, open the file, and paste the code below the line.

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

It worked for me.

Answer 3

Try to resolve the Query due to 5.7 Requirements:

• disable 'only_full_group_by' by setting your sql_mode. NOT RECOMMENDED!
• rewrite your query either by setting the appropriate column name in der group by list
• Set ANY_VALUE(fieldname) within your SELECT query if your really know that this is what you expect.