1

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.

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
4
  • Thanks for the response. What is confusing here is that, the only_full_group_by is not enabled, and yet we are occasionally still seeing errors. I'm familiar with the requirements for sql statements when this mode is enabled, but in our case, it is not included in our set of enabled modes.
    – iresearchhere
    Commented Mar 23, 2021 at 17:23
  • Amozon has enabled fill group by in some products lately, as we have seen request s like your. TRY ´SELECT @@GLOBAL.sql_mode;´ and ´SELECT @@SESSION.sql_mode;´ and see if ot is enabled, but you should enaböle the ONLY FULL GroUp BY that makes the query more predictable. Also amazon can enable the sql mode everytime, so you are prepared
    – nbk
    Commented Mar 23, 2021 at 17:55
  • You make a good point that it is something we should be prepared for if amazon makes it a requirement in the future. Its also completely fair that the best approach would be to fix the root cause, instead of disabling. Unfortunately its not an option for us right now. Both the session and the global sql_mode variable does not include only_full_group_by, which, from what I understand, means we should not be seeing these errors ever and yet we still are.
    – iresearchhere
    Commented Mar 23, 2021 at 18:13
  • Yes amazon has its own mysql code, so you can always have some suprises installed
    – nbk
    Commented Mar 23, 2021 at 19:20
0

Turns out the cause of this issue is likely related to the version of mysql.data.entity we are using for Entity Framework. It appears in the version we are using (6.9.12) the package will call "SET SESSION sql_mode='ANSI';" before running an insert. We found this issue by viewing queries run on our db through performance_schema tables, and confirmed by reports such as this one https://bugs.mysql.com/bug.php?id=79678 and by viewing the source here: https://www.fuget.org/packages/MySql.Data.Entity/6.9.12/lib/net45/MySql.Data.Entity.EF6.dll/MySql.Data.Entity/InsertStatement?code=true

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.