Error Code: 1055 incompatible with sql_mode=only_full_group_by

Question

I have been having issues switching to an offline version of the Lahman SQL baseball database. I was using a terminal embed into an EDX course. This command runs fine on the web terminal:

SELECT concat(m.nameFirst,concat(" ",m.nameLast)) as Player, p.IPOuts/3 as IP, p.W,p.L,p.H,p.BB,p.ER,p.SV,p.SO as K, p.IPOuts+p.W*5+p.SV+p.SO-p.BB-p.L-p.H as PTS, p.yearID as Year FROM Pitching p Inner Join Master m ON p.playerID=m.playerID WHERE p.yearID=2014 AND p.IPOuts>=50 GROUP BY m.playerID ORDER BY PTS DESC;

Which is running SQL 5.5.46, but when I use my offline version running 5.7.10 I get the following error code:

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.m.nameFirst' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I've been reading a lot of solutions to people's problems, but they haven't helped in this case. That's never happened before, so I think this is either super obvious or maybe I'm getting ok at coding. Anyway, anyone know how to fix this?

you don't need a group by.remove it.

Vamsi Prabhala
– Vamsi Prabhala

2016-03-24 18:05:18 +00:00
Commented Mar 24, 2016 at 18:05 — Vamsi Prabhala
– Vamsi Prabhala, Commented Mar 24, 2016 at 18:05
See stackoverflow.com/documentation/mysql/8245/…

O. Jones
– O. Jones

2016-12-21 14:45:27 +00:00
Commented Dec 21, 2016 at 14:45 — O. Jones
– O. Jones, Commented Dec 21, 2016 at 14:45

White Feather · Accepted Answer · 2016-03-24 18:35:40Z

79

In 5.7 the sqlmode is set by default to:

 ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

To remove the clause ONLY_FULL_GROUP_BY you can do this:

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

This supposed you need to make that GROUP BY with non aggregated columns.

Regards

answered Mar 24, 2016 at 18:35

White Feather

2,7931 gold badge17 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frydaddy07 Over a year ago

You are right, and that does work. But it looks like I goofed and didn't need the group by clause at all. Thanks though, had I been doing it right this would have worked for me.

White Feather Over a year ago

These changes of default from 5.5 to 5.6 are making life difficult for a lot of people. They are more than motivated, but generally not noticed. Regards

Abeer Sul Over a year ago

NOTE: changing the sql_mode will not affect stored procedures. So you need to drop and execute SP again to take affect

Anis · Accepted Answer · 2016-12-18 09:53:20Z

36

The accepted solution above didn't work for me on version 5.7.9, for osx10.9 (x86_64).

Then the following worked -

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

answered Dec 18, 2016 at 9:53

Anis

3,6191 gold badge24 silver badges17 bronze badges

5 Comments

Abderrahim Soubai-Elidrisi Over a year ago

The accepted solution above didn't work for me on version 5.7.18 on Ubuntu 16.04 , but this works

Kiran Reddy Over a year ago

Is there any permanent solution, resolve this instead of running this daily. anis?

Anis Over a year ago

@Kiran, yes. You've to add it in options file - dev.mysql.com/doc/refman/5.7/en/option-files.html

Syed Imran Ertaza Over a year ago

Yes! this solution is working on 5.7.35-0ubuntu0.18.04.1 - (Ubuntu). Thanks @Anis

Kafaa Billahi Syahida Over a year ago

@AbderrahimSoubai-Elidrisi maybe an accepted solution change your sql_mode to ,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION, or not set sql_mode in global

Philip E · Accepted Answer · 2018-04-28 17:42:53Z

For other use cases: You don't necessarily have to disable ONLY_FULL_GROUP_BY Given a case like this, According to mysql docs, "This query is invalid if name is not a primary key of t or a unique NOT NULL column. In this case, no functional dependency can be inferred and an error occurs:"

SELECT name, address, MAX(age) FROM t GROUP BY name; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Instead you can use this ANY_VALUE('my_column_name') my_column_name Quoting the mysql docs, "In this case, MySQL ignores the nondeterminism of address values within each name group and accepts the query." Use ANY_VALUE() to refer to address:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

Thank you!!! The same GROUP BY type of question can be found many times in this site, but the ANY_VALUE option is never mentioned... Perfect, thanks again!
Thank you sir! Was searching for a minute on this one scratching my head.

bekt · Accepted Answer · 2019-12-06 08:51:33Z

9

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); ** your query **

This will resolve your problem.

edited Dec 6, 2019 at 8:51

bekt

6075 silver badges16 bronze badges

answered Dec 6, 2019 at 7:34

Kesha Viveki

2193 silver badges2 bronze badges

1 Comment

byhuang1998 Over a year ago

This worked for me.

Suraj Rao · Accepted Answer · 2021-10-05 13:16:27Z

using mysql version 8.0.26. I tried a lot of solutions but nothing work. I changed Full groupBy mode change Engine_subsitution. at last, I tried this

mysql -u root -p -e "SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';"

this worked for me

double-beep · Accepted Answer · 2019-11-07 19:46:25Z

If you do as the picked answer, the @sql_mode may be like this—

',STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'.

there's a comma in front of the 'STRICT_TRANS_TABLES' string.

Just execute this—

set @@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 works.

Also, you can try following exp,

SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,','');

I didn't test it, but I guess it may works.

Anushil Kumar · Accepted Answer · 2020-02-19 17:22:07Z

You can set the variables in mysql:

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

Remember NO_AUTO_CREATE_USER will not work with mysql 8.

If that doesn't work just do:

mysql > set sql_mode = ''

Vu Van Dung · Accepted Answer · 2021-04-10 19:26:16Z

I think it is impossible to change *.cnf file in recent version. Instead of, you can change mysql.service file

/lib/systemd/system/mysql.service ExecStart=/usr/sbin/mysqld --sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

It works well in recent version but it is not a best method You can use following method

 SELECT name, max(address), MAX(age) FROM t GROUP BY name;

Nawodya Jayalath · Accepted Answer · 2022-07-18 05:31:18Z

Find mysql client.cnf file in (Linux)

/etc/my.cnf.d/client.cnf

Then Add

[mysqld] sql_mode = ""

Then save and restart the MySQL service using

sudo systemctl restart mysqld

then check whether the MySQL service is running without error

sudo systemctl status mysqld

if this is running without error, it will fix your error.

vikas kumar · Accepted Answer · 2023-07-04 12:19:29Z

There are several ways we can go about resolving this issue. Below are six options for dealing with this issue.

Example of Error First, here’s an example of code that produces the error:

SELECT District, Name, SUM(Population) FROM City WHERE CountryCode = 'AUS' GROUP BY District; Result:

ERROR 1055 (42000): Expression #2 of the SELECT list is not in the GROUP BY clause and c

Here, #2 means that the second expression in our SELECT list is causing the problem. In our case, it’s the Name column. We included the Name column in our SELECT list but not the GROUP BY clause.

Solution 1 Another way to deal with this issue is to include the Name column in the GROUP BY clause:

SELECT District, Name, SUM(Population) FROM City WHERE CountryCode = 'AUS' GROUP BY District, Name;

Result:

Solution 2 Another way to deal with this error is with the ANY_VALUE() function.

SELECT District, ANY_VALUE(Name), SUM(Population) FROM City WHERE CountryCode = 'AUS' GROUP BY District; Result:

Solution 3 Another option is to use the GROUP_CONCAT() function:

SELECT District, GROUP_CONCAT(Name), SUM(Population) FROM City WHERE CountryCode = 'AUS' GROUP BY District;

Solution 4 We only get the error when our sql_mode contains ONLY_FULL_GROUP_BY.

We can check our sql_mode like this:

SELECT @@sql_mode; Result:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION As expected, ONLY_FULL_GROUP_BY is enabled, which is why I got the error.

We can disable ONLY_FULL_GROUP_BY from our session’s sql_mode like this:

SET @@sql_mode = SYS.LIST_DROP(@@sql_mode, 'ONLY_FULL_GROUP_BY'); SELECT @@sql_mode; Result:

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION By using the SYS.LIST_DROP system function, we removed the item from the list without affecting any other items. We can see that ONLY_FULL_GROUP_BY is no longer in our sql_mode.

Let’s now run the original query that caused the error:

SELECT District, Name, SUM(Population) FROM City WHERE CountryCode = 'AUS' GROUP BY District; Result:

Collectives™ on Stack Overflow

Error Code: 1055 incompatible with sql_mode=only_full_group_by

10 Answers 10

3 Comments

5 Comments

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

3 Comments

5 Comments

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Related