Problem:

I installed mysql DB 5.7.19 on my ubuntu server by default. After that all my PHP projects that used queries with “GROUPING BY” got errors like this:

Error: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY

Solution 1

Temporary solution is to execute query:

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

or with CLI MODE

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

Solution 2

Configure your mysql server. Add this:

[mysqld]
 sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

to one of those files:

/etc/mysql/my.cnf

or /etc/mysql/conf.d/mysql.cnf