SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated

Question

when i got upgraded my ubuntu from 15.10 to 16.04 i have this erro in my yii2 project

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3
of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'iicityYii.opportunity_conditions.money' which is not functionally dependent 
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The SQL being executed was:

SELECT SUM(oc.money),op.id,oc.money,
            op.mantaghe,
            op.`time`, op.`id`, `op`.`logo`,
           `pd`.`user_id`, `op`.`name`, 
           `pd`.`co_name`, `op`.`address`, 
           `op`.`project_type_id`, `op`.`state_id`
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

how to solve my problem ?

score 113 · Answer 1 · edited Jun 14 '21 at 01:03

Run:

sudo mysql -u root -p
mysql> SELECT @@global.sql_mode;

(Then optionally copy the output to your notes somewhere in case you want to revert to those original settings later.)

And change the SQL Mode for your MySQL Server Instance:

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

(If you ever want to roll back, you could run something like mysql> SET GLOBAL sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; using the value you saved.)

A more permanent way (which will survive restarts of MySQL) would be using the MySQL configs. Go to /etc/mysql/my.cnf (or you may need to run sudo vim /etc/mysql/mysql.conf.d/mysql.cnf):

Add a section for [mysqld] and right below it add the statement sql_mode = "" or something like sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION".
Restart the MySQL service:
```
 sudo systemctl restart mysql
```

(or sudo service mysql restart)

See also https://dba.stackexchange.com/a/113153/18098

score 101 · Answer 2 · edited Oct 30 '18 at 13:47

101

In laravel with MySql go to file config/database.php and it change in array MySql mode strict to false.

'connections' => [
    'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => false, //from true
        'engine' => null,
    ],
],

edited Oct 30 '18 at 13:47

Florian Lauterbach

1,330
1
11
25

answered May 17 '18 at 13:45

pedro.caicedo.dev

2,269
2
16
19

2

Even the answer seems valid, I would recommend not to put images of code but the code itself. The OP can then just copy / paste without risk of misspelling during the copy. – рüффп May 17 '18 at 16:34
1

the perfect answer for laravel user-facing group by issue. – Ranjan Fadia Dec 23 '20 at 08:11
I was super optimistic about this answer, but unfortunately it didn't help me, even if I also ran `php artisan config:clear`. – Ryan Jun 10 '21 at 23:06
Actually, suddenly this did start working for me today, so thanks. I'm using WSL 1 with MySQL 8. But I'm not sure that I want to set "strict" to `false` because of https://stackoverflow.com/a/42104555/470749 – Ryan Jun 14 '21 at 00:25
1

Great work @pedro.caicedo.dev – Paulo Teixeira Jan 24 '22 at 20:57
The perfect solution even now for Laravel 9.x! – DreamBold Jul 21 '23 at 08:25

croppio.com · Answer 3 · 2020-04-18T16:21:16.830

16

The solution is to edit the MySQL config file because the config will revert after every restart...

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

and add

[mysqld]
sql-mode=""

then restart

sudo systemctl restart mysql

Works on ubuntu 18.04.

edited Apr 18 '20 at 16:21

answered Oct 15 '18 at 05:57

croppio.com

1,823
5
28
44

What are security implications of doing something like this? – Aleksandar Pavić Oct 15 '20 at 05:27
Works well on Ubuntu 18. – Vishal Kumar Nov 25 '21 at 06:18

Humphrey · Answer 4 · 2019-05-10T13:55:00.113

15

Please just copy this line and run it. it worked for me.

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

Youtube video to fix this

edited May 10 '19 at 13:55

answered Mar 30 '18 at 09:40

Humphrey

2,659
3
28
38

2

Unfortunately the error returned again, but running the command again fixed it. Not sure why it came back. – Don King Apr 13 '19 at 15:07
I put for you a youtube link in there please try to visit and watch how to fix – Humphrey May 10 '19 at 13:55
1

The youtube video shows how to remove STRICT_ALL_TABLES in phpmyadmin. Removing this and saving did not solve the problem. No change at all. Running SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); as a SQL query in phpmyadmin solves the problem temporarily but it comes back automagically by itself after some time. – Don King Jun 03 '19 at 20:27
2

I had to also add `[mysqld] sql-mode=""` to /etc/mysql/my.cnf Because without that when restarting mysql service issue came back again. – Mārcis P Mar 10 '20 at 20:32

score 13 · Accepted Answer · edited Jul 13 '21 at 07:13

In select you have an aggregate function named sum and a set of column name, the error tell you that you have not specified the correct list of column name in group by clause. could be you should add more columns name in group by probably related to the profile_details, opportunity_conditions table

You have also ,(opportunity.id),(opportunity_conditions.money), (opportunity.mantaghe), why the () if you need sum you must add sum to all column

sum(opportunity.id), sum(opportunity_conditions.money),

sum(opportunity.mantaghe),

otherwise if this are normal columns you should use the normal syntax without ()

opportunity.id, opportunity_conditions.money,opportunity.mantaghe,

I have tried to rewrite a possible query

 SELECT SUM(opportunity_conditions.money),
        `opportunity`.`id`,
        `opportunity_conditions.money`,
        `opportunity.mantaghe`, 
        `opportunity`.`time`, 
        `opportunity`.`logo`, 
        `profile_details`.`user_id`,
        `opportunity`.`name`, 
        `profile_details`.`co_name`,
        `opportunity`.`address`, 
        `opportunity`.`project_type_id`,
        `opportunity`.`state_id` 
FROM `opportunity` 
INNER JOIN `profile_details` ON `opportunity`.`user_id`= `profile_details`.`user_id` 7
INNER JOIN `opportunity_conditions` ON `opportunity`.`id`=`opportunity_conditions`.`opportunity_id` 
GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  
ORDER BY `opportunity`.`id` DESC

with group by on the essential column name (i hope)

GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,

score 3 · Answer 6 · answered May 10 '22 at 05:28

3

in laravel

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'turn.barnamehs.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by select * from tables where date(end_date) >= 2022-05-10 00:00:00 group by speciality_id

solution: change 'strict' => false, in config/database.php

answered May 10 '22 at 05:28

Javad Heidarpour

45
4

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 11 '22 at 05:36

score 2 · Answer 7 · answered Jul 26 '20 at 17:37

It is a good idea to update your query to:

    SELECT SUM(any_value(oc.money)),op.id,any_value(oc.money) as money,
            op.mantaghe,
            op.`time`, op.`id`, `op`.`logo`,
           any_value(`pd`.`user_id`) as user_id, `op`.`name`, 
           any_value(`pd`.`co_name`) as co_name, `op`.`address`, 
           `op`.`project_type_id`, `op`.`state_id`
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

Understanding
When your query contains group by you should only select columns from the table you are grouping by, but if not, you ought to use the any_value() function to call the columns from other table.

I personally try not to update the sql_mode global variable.

You can read more about it on https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

score 1 · Answer 8 · answered Aug 31 '17 at 14:51

Thx, this helped to me, but this will not set it PERMANENTLY, and it will revert after every restart.

So you should set this in your config file (e.g. /etc/mysql/my.cnf in the [mysqld] section), so that the changes remain in effect after a MySQL restart:

Config File: /etc/mysql/my.cnf

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

score 1 · Answer 9 · answered Mar 05 '20 at 07:17

1

Simply set strict false on following

Config > database.php  set 'strict' => false,

answered Mar 05 '20 at 07:17

Vinay Kaithwas

1,415
10
17

score 1 · Answer 10 · edited Jun 23 '23 at 13:49

1

For CentOS8 I use webmin config file /etc/my.cnf.d/mysql-server.cnf

[mysqld]
sql-mode=""

edited Jun 23 '23 at 13:49

linktoahref

7,812
3
29
51

answered Jun 15 '20 at 15:01

Hoeun ANN

31
3

score 0 · Answer 11 · answered Mar 07 '20 at 18:30

You can use ANY_VALUE for the non-aggregated columns. So, you could use:

SELECT SUM(oc.money),
       op.id,
       ANY_VALUE(oc.money),
       ANY_VALUE(op.mantaghe),
       ANY_VALUE(op.`time`), op.`id`, ANY_VALUE(`op`.`logo`),
       ANY_VALUE(`pd`.`user_id`), ANY_VALUE(`op`.`name`), 
       ANY_VALUE(`pd`.`co_name`), ANY_VALUE(`op`.`address`), 
       ANY_VALUE(`op`.`project_type_id`), ANY_VALUE(`op`.`state_id`)
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

More details here.

score 0 · Answer 12 · answered Oct 19 '22 at 09:11

0

sudo mysql -u root -p

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

answered Oct 19 '22 at 09:11

Mohammed Abir

185
5

This answer essentially directs you to put duct tape over the `check engine` light. – reformed Jun 16 '23 at 21:04

score 0 · Answer 13 · answered Nov 29 '22 at 09:20

(Solved) Just include all selected columns in the Group By clause, Please have a look at bellow example

Invalid

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

Valid (in this query we just include c.name also in group by)

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid,**c.name**;

For more information please visit Main Source

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated

13 Answers13

Linked