When you upgrade your MySQL server version to 5.7.5 or later and any of your queries include GROUP BY clause. Then you will get an error cased by the update to MySQL 5.7.5 or later as below
Error Number: 1055
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘columnname.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
This is due to MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant . This may affect some queries.
According to MySQL Documentation
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
This not only means that you cannot mess up your GROUP BY-queries anymore as MySQL will now reject an improper GROUP BY query, it will also not require you to write non-sensical “dummy” aggregates over expressions that can only have one value per aggregated result row.
If you are running MySQL 5.7.7+ , edit your
my.cnf file and remove the
ONLY_FULL_GROUP_BY option from
If you used Homebrew to install/upgrade Mysql, then you can fix the issue by copying the default
my.cnf file located in MySQL project path to
sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf
and then change
/etc/my.cnf to this
Then Restart MySQL