incompatible with sql_mode=only_full_group_by in mysql

Question

So while upgrading a project from Rails 3.2 to Rails 4.2, somehow this query in one of my models which looks like:

rows = ActiveRecord::Base.connection.execute("Select q.id,q.times_taken,avg(qr.correct)*100,q.difficulty,q.weight,sk.name,sa.name,sub.name,q.tag_list from 
  (select qu.id,qu.times_taken,qu.difficulty,qu.weight,group_concat(tags.name) tag_list,qu.subject_id,qu.subject_area_id
    from questions qu left join taggings t on (qu.id = taggable_id) left join tags on (t.tag_id = tags.id) 
    where qu.id in (#{@questions_out_ids.join(',')}) and t.taggable_type='Question' group by t.taggable_id) q
  left join subjects sub on (q.subject_id = sub.id) left join subject_areas sa on (q.subject_area_id = sa.id) 
  left join skills_subject_areas ssa on (sa.id = ssa.subject_area_id) left join skills sk on (ssa.skill_id = sk.id), 
  archived_question_results qr,attempts a where  qr.question_id = q.id and a.id = qr.attempt_id and a.is_normalized = 1 
  and a.state = 'complete' group by qr.question_id order by q.id")

has started producing a mysql error as under:

ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'faces_development.sk.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I've looked at other answers of the similar questions, and I know I need to include a certain table's id column too in the GROUP BY clause but since my query is extremely complex, the options I've tried haven't been of much use. I am using gem mysql2 0.3.0 Suggestions ?

Possible duplicate of [Is there ANY\_VALUE capability for mysql 5.6?](http://stackoverflow.com/questions/37089347/is-there-any-value-capability-for-mysql-5-6) — e4c5, Dec 28 '16 at 07:50
No, this one is related to rails. They are related but not duplicated. — taiar, Apr 02 '19 at 12:22

score 32 · Answer 1 · edited Jan 15 '19 at 13:24

32

config/database.yml:

development: # Or test, or production, or all of them.
  ...
  variables:
    sql_mode: TRADITIONAL

edited Jan 15 '19 at 13:24

Sebastián Palma

32,692
6
40
59

answered Mar 12 '17 at 22:22

Pavel

2,103
4
23
41

3

Worked perfectly!! – Mr. bug Feb 21 '18 at 00:15
1

This seems to apply the rule whenever you reboot the app - it doesn't need you to recreate or migrate the DB or anything, which is nice. – Jimbali Apr 25 '20 at 17:49
Hint: Suddenly we startet to get those exception again, even though we set the sql_mode like above. BUT apt unattended-upgrades made an automatic security update of MySQL and that seem to have reset the server default back again to only_full. In our case **restarting the app** reapplied the variables setting and it worked again. – stwienert Feb 04 '22 at 15:14

score 6 · Answer 2 · answered Aug 19 '18 at 15:12

6

in your config/database.yml file:

default: &default
  variables:
    sql_mode: TRADITIONAL

answered Aug 19 '18 at 15:12

Jeremy Moritz

13,864
7
39
43

score 0 · Answer 3 · answered Dec 29 '16 at 10:45

I think I found the solution to my issue, apparently it was the 6th expression in outer select, i.e sk.name which was producing problems. Adding the same expression to the outer group by clause fixed it for me.

rows = ActiveRecord::Base.connection.execute("select q.id,q.times_taken,avg(qr.correct)*100,q.difficulty,q.weight,sk.name,sa.name,sub.name,q.tag_list from 
  (select qu.id,qu.times_taken,qu.difficulty,qu.weight,group_concat(tags.name) tag_list,qu.subject_id,qu.subject_area_id
    from questions qu left join taggings t on (qu.id = taggable_id) left join tags on (t.tag_id = tags.id) 
    where qu.id in (#{@questions_out_ids.join(',')}) and t.taggable_type='Question' group by t.taggable_id) q
  left join subjects sub on (q.subject_id = sub.id) left join subject_areas sa on (q.subject_area_id = sa.id) 
  left join skills_subject_areas ssa on (sa.id = ssa.subject_area_id) left join skills sk on (ssa.skill_id = sk.id), 
  archived_question_results qr,attempts a where  qr.question_id = q.id and a.id = qr.attempt_id and a.is_normalized = 1 
  and a.state = 'complete' group by sk.name, qr.question_id order by q.id")

incompatible with sql_mode=only_full_group_by in mysql

3 Answers3