Posted 5 months ago. Visible to the public.

MySQL 5.7.5 enables `ONLY_FULL_GROUP_BY` mode per default

When using GROUP BY, MySQL now complains if the SELECT includes columns which are not part of the GROUP BY.

Reason:

There could be multiple values for those columns per group but only one value can be picked for the results.

The default behaviour of MySQL prior to version 5.7 will not complain and arbitrarily choose a value. But this leads to non-deterministic results. So MySQL now has enabled the only_full_group_by setting by default to prevent this.

In Rails this could lead to some trouble, because scopes do not have specific select columns in the most cases. A simple id can cause the error if the group method is used in a scope.

Solutions:

1) make your queries comply with it, if possible

2) change the sql mode in the config/database.yml:

Copy
variables: sql_mode: TRADITIONAL

(Be aware, that this solution disables the only_full_group_by setting globally and MySQL will select those values arbitrarily again)

MySQL 5.7 standard TRADITIONAL
STRICT_TRANS_TABLES STRICT_TRANS_TABLES
- STRICT_ALL_TABLES
NO_ZERO_IN_DATE NO_ZERO_IN_DATE
NO_ZERO_DATE NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION NO_ENGINE_SUBSTITUTION
ONLY_FULL_GROUP_BY -
Growing Rails Applications in Practice
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Author of this card:

Avatar
Natalie Krehan
Last edit:
5 months ago
by Natalie Krehan
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Natalie Krehan to makandropedia