0

I am unable to fetch data from database at my server I am using ubantu 16.04 and mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper. It is showing error

2018-01-08 09:18:04 ERROR SqlExceptionHelper:146 - Expression #7 of SELECT
list is not in GROUP BY clause and contains nonaggregated column 
 db11.billpaidde6_.id' which is not functionally dependent on 
columns in GROUP BY clause; this is incompatible with 
 sql_mode=only_full_group_by

I have checked all queries that are working in my local host but showing error in server how to resolve this

xrcwrn
  • 5,339
  • 17
  • 68
  • 129

1 Answers1

3

You are getting this error because sql_mode is set to ONLY_FULL_GROUP_BY. This setting means that MySQL will error when you select data that you are not aggregating in any way and you have a GROUP BY in your query.

This will take effect in the following scenario:

table: users
id | name  | favourite_colour
----------------------------
1  | Fred  | blue
2  | John  | green
3  | David | blue

Take the following query:

SELECT id, favourite_colour FROM users GROUP BY favourite_colour

If you were MySQL, what would you return for the id column? For the row that is grouping green it's an easy choice because there will only be 1 aggregation. However, for the colour blue there are 2 rows being aggregated. In reality MySQL will choose one of the id's to show for you but I do not think it is reliable.

Another query that could trigger this error would be:

SELECT * FROM users GROUP BY favourite_colour

This is still selecting columns that are not aggregated in any way, and will still fail.

You can resolve this error by omitting any non-aggregated columns from your query, or aggregating the ones that you may care about.

SELECT favourite_colour FROM users GROUP BY favourite_colour
SELECT count(*), favourite_colour FROM users GROUP BY favourite_colour

Alternatively you can disable this SQL mode.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))

The above command will not set the sql_mode global permanently. Please find the line (or similar) in my.cnf and remove ONLY_FULL_GROUP_BY.

[mysqld] 
sql_mode = ONLY_FULL_GROUP_BY

If you do not have access to the server then you will need to contact your hosting provider and ask them to turn off ONLY_FULL_GROUP_BY.

Jim Wright
  • 5,905
  • 1
  • 15
  • 34
  • I tried simple one `SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))` this is not doing anything – xrcwrn Jan 08 '18 at 09:47
  • What is the result of `SELECT @@sql_mode`? I have also updated the answer with instructions to make this change permanent. – Jim Wright Jan 08 '18 at 09:54
  • I am getting ` SELECT @@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 | – xrcwrn Jan 08 '18 at 10:12