Error Code: 1055 incompatible with sql_mode=only_full_group_by

user_6110761 0
Viewed 99464

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?

mysql-error-1055 mysql-workbench mysql sql
9 Answers

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;
user_43340880 
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

** your query **

This will resolve your problem.

user_119609890

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

user_162519210

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.

user_113775300

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

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

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.

user_174349670
Related