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,
     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?

Accepted Answer

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

Answer 2

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

Answer 3

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;

Answer 4

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

** your query **

This will resolve your problem.

Answer 5

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

Answer 6

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.

Answer 7

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;

Answer 8

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.

Answer 9

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:

Answer 10

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

Error Code: 1055 Incompatible with Sql_Mode=Only_Full_Group_By

10 Answers

Must Read

Your Answer

Robert Thorne

Error Code: 1055 Incompatible with Sql_Mode=Only_Full_Group_By

10 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

Robert Thorne