#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by

Question

my Query :

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) GROUP BY libelle order by libelle asc

Mysql 5.7.9 runs the query without any problem but mysql 5.7.12 display the error above Could you please help me with that

it is expecting you to have libelle, credit_initila, and disponible_v all in the group by statement — Marshall Tigerus, Jun 21 '16 at 18:19
http://mechanics.flite.com/blog/2013/02/12/why-i-use-only-full-group-by/ — bishop, Jun 21 '16 at 18:20
Possible duplicate of [I am getting an error in MySql related to only\_full\_group\_by when executing a query](http://stackoverflow.com/questions/34115174/i-am-getting-an-error-in-mysql-related-to-only-full-group-by-when-executing-a-qu) — gr1zzly be4r, Jun 21 '16 at 18:23

Miloud BAKTETE · Accepted Answer · 2020-04-05T23:58:10.233

110

I had a struggle getting this to work i've tested it and it's working on lamp server mysql version 5.12

So, steps to success:

sudo vim /etc/mysql/conf.d/mysql.cnf
Scroll to the bottom of file Copy and paste

[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

to the bottom of the file

save and exit input mode
sudo service mysql restart to restart MySQL.

Done!

edited Apr 05 '20 at 23:58

answered Jun 23 '16 at 22:24

Miloud BAKTETE

2,404
3
19
30

6

I resolved the same issue by removing only: STRICT_TRANS_TABLES from the sql_mode= string in /etc/mysql/my.cnf in Ubuntu 16.04 (MySQL 5.7) and restarting mysql: systemctl restart mysql – Grant Aug 25 '16 at 20:21
2

**For Those Who Cannot Find This File** my file was called **my.ini** is the same as **mysql.cnf** – Radmation Nov 19 '16 at 19:48
6

There's some info in the documentation on what's going on here: [https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) And a less drastic way of working around than editing mysql.cnf (or just fixing the sql as gr1zzly be4r and scaisEdge suggested): [https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value](https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value) – keithpjolley Jan 12 '17 at 12:18
1

You can find the original value by executing this SQL before you change my.cnf: `show global variables like 'sql_mode';` – Henry May 09 '17 at 05:14
1

I added these settings to `/etc/mysql/my.cnf` – Henry Sep 12 '17 at 05:19
5

Don't confuse `[mysqld]` with `[mysql]` as I did. – Henry May 06 '18 at 00:18
1

It's a good idea to check that you are only changing one setting by running this sql statement first: `SELECT @@sql_mode;` – Henry May 06 '18 at 00:20
3

This answer is wrong. The correct answer is to fix the query. – Salman A Apr 20 '19 at 16:38
1

@SalmanA feel free to fix the query and present it to us. by the way the solution works. – devasia2112 Apr 28 '19 at 13:49
1

@deepcell answers below (grizzly bear and drapp) provide the correct fix. Most people unfortunately won't scroll down. – Salman A Apr 28 '19 at 21:49
1

This is still required for MAMP. :/ – vr_driver Oct 21 '19 at 03:54

score 70 · Answer 2 · answered Feb 12 '17 at 02:49

70

This worked for me:

mysql -u root -p
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

You might need sudo for the first step:

sudo mysql -u root -p

answered Feb 12 '17 at 02:49

Henry

7,721
2
38
38

3

in the end this was the only thing that worked for me too – Xoundboy Feb 23 '17 at 18:27
1

I've come to think this solution only persists until mysql is restarted. The accepted solution is better. – Henry May 09 '17 at 05:13
3

Henry, the advantage this approach has is that it can be applied selectively. If you modify my.cnf, every app and DB on the server is affected. OTOH, if the app/account has privileges for SET GLOBAL (which is not assured but can be implemented), you can run this query from the app in the DB preparation phase before other queries are run. It has saved my bacon with a elderly legacy app that violated FULL_ORDER_GROUP_BY ... I simply call this as soon as the DB connection is initalized ... – Kevin_Kinsey May 18 '17 at 19:31
1

if MySQL is restarted then the issue will arise again. so this is not a permanent fix. – Star Mar 26 '19 at 13:22
1

i use uniserverZero, open console and write that code, now working, thanks – matasoy Jul 23 '20 at 07:44
1

use SUDO first sudo mysql -u root -p – Shehan Hasintha May 16 '22 at 07:35

score 32 · Answer 3 · answered Jun 21 '16 at 18:21

You need to specify all of the columns that you're not using for an aggregation function in your GROUP BY clause like this:

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type where type.id_type=annee.id_type and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) GROUP BY libelle,credit_initial,disponible_v order by libelle asc

The full_group_by mode basically makes you write more idiomatic SQL. You can turn off this setting if you'd like. There are different ways to do this that are outlined in the MySQL Documentation. Here's MySQL's definition of what I said above:

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

You're getting the error because you're on a version < 5.7.5

I'm on ubuntu mysql 5.7.12 any other option available because i have other queries that i need how to turn only full group by of — Miloud BAKTETE, Jun 22 '16 at 19:34
This is an actual solution if you are currently writing your db logic, since this will make your code more precise and reduce a lot of uncertainty in your output. — Mohd Abdul Mujib, Jun 19 '18 at 22:38

score 23 · Answer 4 · answered Aug 29 '17 at 18:18

23

You can disable sql_mode=only_full_group_by by some command you can try this by terminal or MySql IDE

mysql> 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';

mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

I have tried this and worked for me. Thanks :)

answered Aug 29 '17 at 18:18

Anil Gupta

1,593
1
19
21

3

if MySQL is restarted then the issue will arise again. so this is not a permanent fix. – Star Mar 26 '19 at 13:21
You could add this statement to your `my.cnf` file to make the setting change permanent. – Keiron Stoddart Jul 17 '23 at 22:01

score 20 · Answer 5 · edited Jun 28 '18 at 13:14

20

Solution 1: Remove ONLY_FULL_GROUP_BY from mysql console

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

you can read more here

Solution 2: Remove ONLY_FULL_GROUP_BY from phpmyadmin

Open phpmyadmin & select localhost
Click on menu Variables & scroll down for sql mode
Click on edit button to change the values & remove ONLY_FULL_GROUP_BY & click on save.

click here to see image

edited Jun 28 '18 at 13:14

andreas

16,357
12
72
76

answered Jun 28 '18 at 12:53

Tauqueer Hassan

217
2
5

1

Hi Tauqueer, Please format the mysql line as code. Also you are missing the link referring to additional reading material. – hakamairi Jun 28 '18 at 13:01
3

if MySQL is restarted then the issue will arise again. so this is not a permanent fix. – Star Mar 26 '19 at 13:21
SOLUTION IS NOT WORKING – Kamlesh Jan 13 '23 at 18:04

score 8 · Answer 6 · edited Jan 13 '17 at 19:00

8

You have to aggregate by anything NOT IN the group by clause.

So,there are two options...Add Credit_Initial and Disponible_v to the group by

OR

Change them to MAX( Credit_Initial ) as Credit_Initial, MAX( Disponible_v ) as Disponible_v if you know the values are constant anyhow and have no other impact.

edited Jan 13 '17 at 19:00

denny

2,084
2
15
19

answered Jun 21 '16 at 18:20

DRapp

47,638
12
72
142

1

THIS is the correct way. Assigning a name to the Max value was the way to do it, thank you 6 years later – bonjonbovi Sep 29 '22 at 17:16

score 7 · Answer 7 · answered Jun 21 '16 at 18:21

7

Base ond defualt config of 5.7.5 ONLY_FULL_GROUP_BY You should use all the not aggregate column in your group by

select libelle,credit_initial,disponible_v,sum(montant) as montant 
FROM fiche,annee,type 
where type.id_type=annee.id_type 
and annee.id_annee=fiche.id_annee 
and annee = year(current_timestamp) 
GROUP BY libelle,credit_initial,disponible_v order by libelle asc

answered Jun 21 '16 at 18:21

ScaisEdge

131,976
10
91
107

1

it does not make any sens, if you count by some id(index pointing to another table), then why would you need to add the other collumns that are not related to the counting in the group by, especialy if you dont want to group by these columns – Phil Apr 27 '17 at 06:04
1

@fdsfdsfdsfds This is what the clause forces you to do. ONLY_FULL_GROUP_BY requires all columns that are not managed by aggregation functions to be declared in the group by .. thse solution provided by the OP change the ONLY_FULL_GROUP_BY sql_mode but this is not sql correctly (also if is allwed by mysql until 5.7 by default) – ScaisEdge Apr 27 '17 at 06:11

score 7 · Answer 8 · 2019-10-30T13:08:23.483

So let's fully understand, Let's say you have a query which works in localhost but does not in production mode, This is because in MySQL 5.7 and above they decided to activate the sql_mode=only_full_group_by by default, basically it is a strict mode which prevents you to select non aggregated fields.

Here's the query (works in local but not in production mode) :

SELECT post.*, YEAR(created_at) as year
FROM post
GROUP BY year

SELECT post.id, YEAR(created_at) as year  // This will generate an error since there are many ids
FROM post
GROUP BY year

To verify if the sql_mode=only_full_group_by is activated for, you should execute the following query :

SELECT @@sql_mode;   //localhost

Output : IGNORE_SPACE, STRICT_TRANS, ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION (If you don't see it, it means it is deactivated)

But if try in production mode, or somewhere where it gives you the error it should be activated:

SELECT @@sql_mode;   //production

Output: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO... And it's ONLY_FULL_GROUP_BY we're looking for here.

Otherwise, if you are using phpMyAdmin then go to -> Variables and search for sql_mode

Let's take our previous example and adapt it to it :

SELECT MIN(post.id), YEAR(created_at) as year  //Here we are solving the problem with MIN()
FROM post
GROUP BY year

And the same for MAX()

And if we want all the IDs, we're going to need:

SELECT GROUP_CONCAT(post.id SEPARATOR ','), YEAR(created_at) as year
FROM post
GROUP BY year

or another newly added function:

SELECT ANY_VALUE(post.id), YEAR(created_at) as year 
FROM post
GROUP BY year

⚠️ ANY_VALUE does not exist for MariaDB

And If you want all the fields, then you could use the same:

SELECT ANY_VALUE(post.id), ANY_VALUE(post.slug), ANY_VALUE(post.content) YEAR(created_at) as year 
FROM post
GROUP BY year

❌ To deactivate the sql_mode=only_full_group_by then you'll need to execute this query:

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

Sorry for the novel, hope it helps.

score 4 · Answer 9 · answered Mar 02 '21 at 11:53

4

Also if you use MySql 8+ try in console this command:

Open MySql console

mysql -u user_name -p

And set global SQL mode

SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';

answered Mar 02 '21 at 11:53

AlexSh

1,468
15
12

Perfect answer!!! +1 Made my day :) – SagarPPanchal Jul 14 '23 at 07:32

score 4 · Answer 10 · answered May 21 '22 at 01:48

4

In Laravel, you just set false in file database.php.

...
'mysql' => [
    ...
    'strict' => false,
    ...
]

answered May 21 '22 at 01:48

Ronald Araújo

1,395
4
19
30

score 1 · Answer 11 · answered Jul 24 '19 at 12:03

Same thing is happened with 8.0+ versions as well. By default in 8.0+ version it is "enabled" by default. Here is the link official document reference

In case of 5.6+, 5.7+ versions, the property "ONLY_FULL_GROUP_BY" is disabled by default.

To disabled it, follow the same steps suggested by @Miloud BAKTETE

score 0 · Answer 12 · answered Aug 25 '17 at 06:52

0

just go to the window bottom tray click on wamp icon ,click mysql->my.ini,then there is option ;sql-mode="" uncomment this make it like sql-mode="" and restart wamp worked for me

answered Aug 25 '17 at 06:52

bilal

11

score 0 · Answer 13 · edited Jun 29 '23 at 05:42

0

# This solution work for me:: Centos 7
# sudo vi /etc/my.cnf
Add in your my.cnf file :
sql_mode = ""

Make sure to restart server, in order to reflect the change.

edited Jun 29 '23 at 05:42

Ankit Jindal

3,672
3
25
37

answered Jun 27 '23 at 05:04

Sonu Chohan

141
1
5

#1055 - Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by

13 Answers13

Linked

Related