1

I recently upgraded to MySQL v5.7 from v5.6 on RDS.

Now I sometimes get the error:

 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'USER.t.training_team_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I checked my config with SELECT @@GLOBAL.sql_mode and only get:

 NO_ENGINE_SUBSTITUTION

However this still happens.

The weird part is, that it only happens sometimes. I cannot force it to happen and I can't seem to disable the only_full_group_by option.

How do I change this?

Oxholm
  • 324
  • 6
  • 16
  • http://stackoverflow.com/questions/31058210/how-to-set-amazon-rds-sql-mode-with-multiple-values – Anthony Neace Apr 10 '17 at 01:25
  • Don't assume your current `@@SQL_MODE` is equal to the global mode. `SELECT @@SESSION.sql_mode;` and compare. The error, though, is trying to warn you that your query, as written, isn't deterministic, and may be giving you a correct answer by accident. – Michael - sqlbot Apr 10 '17 at 02:29
  • I get the same sql_mode with `SELECT @@SESSION.sql_mode;` – Oxholm Apr 10 '17 at 06:13

1 Answers1

0

I found the fix.

I went through all my views and added "ANY_VALUE()" to the columns that gave the error.

I hope this helps anyone else in the same position :)

Oxholm
  • 324
  • 6
  • 16
  • I ran into this as well, then I read the documentation of Group by and realised that I didn't need those extra columns or what I actually needed was something else. There is a possibility to disable only_full_group_by: http://programmingisalifestyle.com/solve-only_full_group_by-issues-in-mysql-5-7/ – user3399858 Apr 16 '17 at 19:40