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

Reason:

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

• Prevents you from data breaches and liability risks
• Upgrade at your own pace
• Works with modern Rubies

Read more Show archive.org snapshot

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 (and that of all your colleagues, staging and production servers!):

development:
  adapter: mysql2
  ...
  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)

Also see our card on SQL compatibility modes.

3. Install MySQL 5.6 in parallel, using mysql-sandbox

4. Use MariaDB 10.x

What TRADITIONAL changes