Rails 3.2 breaks hard on MySQL 5.7

[bjm88]

Starting in MySQL 5.7 rails 3.2 and the corresponding version of ActiveRecord seem to break the general SQL rule that in earlier versions of MySQL was just allowed. Generally, you should not try to order by something if its not in the select. MySQL 5.7 will now throw an error, example query

Expression #1 of ORDER BY clause is not in SELECT list, references column 'caredox_prod_copy2.organizations.name' which is not in SELECT list; this is incompatible with DISTINCT

We had perfectly good Rails 3.2 working app, but about 10 models declared
deafult_scope order()
declarations, and those get put into the generated sql even when the model is not being selected, but just part of some join/associations. This seems incorrect. I know 3.2.x is security patch fix only, but also thought it maybe exists in 4.x, didn't test. Since MySQL 5.7 is out would be great to fix this if simple patch.

[bjm88]

For a general idea, if I was in a model "order_record" and wanted to get association organization_groups and organization had default_scope order declared and this was order_record's associations..
has_many :enrollments, dependent: :destroy
has_many :programs, through: :enrollments
has_many :organizations, through: :programs
has_many :organization_groups, through: :organizations, uniq: true

this would cause the bombing sql

[bjm88]

From MySQL release notes

Relying on implicit GROUP BY sorting in MySQL 5.7 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead.

http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html#mysql-nutshell-deprecations

[rafaelfranca]

Rails 3 is not supported anymore, not matter the reason that it is broken.

[will3216]

To fix the error above, you need to change some settings in sql. The error you are getting is caused by running strict sql_mode rules, specifically 'ONLY_FULL_GROUP_BY'

In the mysql client...

To see your current settings:

select @@sql_mode;
> "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Remove the mode from the list and set it:

set sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

To make this permanent, follow the steps described here: http://stackoverflow.com/a/26104070/1340525

[mahemoff]

set sql-mode solved it for me (note the dash, not underscore, btw).

This may be useful for people using CircleCI or similar. It's a script to replace MySQL 5.6 with 5.7, add this sql_mode to my.cnf so it's permanent, and restart MySQL:
https://gist.github.com/mahemoff/abe3494ed02467a79341a574da090aea