If you receive error like this:
|
1 2 3 4 |
Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dribblog.wp_posts.ID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 0.00079 sec |
You can fix it buy removing the ONLY_FULL_GROUP_BY option or by rewriting your queries. From MySQL 5.7. only_full_group_by comes as default. In short, with this option, columns in your select part of sql query must also be in you group by part of sql query. If in doubt check MySQL manual which has comprehensive explanation how MySQL is handling GROUP BY: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
Turn ONLY_FULL_GROUP_BY off
To turn off
ONLY_FULL_GROUP_BY check to value of the
sql_mode variable.
Execute this query in your favorite MySQL client:
|
1 2 3 4 5 |
mysql> show variables like 'sql_mode'\G *************************** 1. row *************************** Variable_name: sql_mode Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 1 row in set (0.00 sec) |
Edit MySQL config file my.conf . Usually in /etc/my.cnf or /etc/mysql/my.conf . If not sure, execute this command from your shell:
|
1 2 3 |
$ mysql --help | grep my.cnf order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf |
copy sql_mode values, remove ONLY_FULL_GROUP_BY and put this line in my.conf under [mysqld] section.
In my case it looks like this:
|
1 2 |
[mysqld] sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
Restart the mysql server
CentOS:
|
1 |
$ sudo service mysqld restart |
Ubuntu:
|
1 |
$ sudo restart mysqld |
References
- https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
- https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
I saw a lot of solutions to ONLY_FULL_GROUP_BY but your it’s the best!
Thank you!
Hi Stefan! Thank you for the feedback! 🙂
Thanks! Many solutions out there, but this is the only one that worked for me. Much appreciated!
Glad to hear that. Thank you for the feedback!
Looks like your reference links are directed to the same URL
Thx! Fixed!