Mysql – Intermittent only_full_group_by errors in AWS Aurora MySQL cluster db

aws-auroraMySQLmysql-5.7

We recently upgraded from MySQL 5.6 to 5.7 and are running into errors related to only_full_group_by sql mode. We are only seeing the problem intermittently. Our database lives in an AWS Aurora MySQL cluster which has a single instance. We originally included the only_full_group_by sql mode in the cluster parameter group setting, but began running into these errors and decided the number of queries which required updating, are outside the scope of this project.

Looking for some insight into why the errors are intermittent and the best course of action to resolve the errors completely.

Best Answer

If only_full_group_by is enabled

All Columns of a SELECT must be in the GROUP BY or use a aggregation function

so simply a is in the GROUP BY and b uses an aggregation function

SELECT
 a, MIN(b)
FROM
 mytable
GROUP BY a

So you have now to check all queries with GROUP BY and "correct" them.

see also the manual

Try all queries, if they give the correct answer, that will cost some time.

Related Question