I recently upgraded the MySQL from 5.6 to 5.7.14, and now all the criteria queries which were using groupProperty are failing with the error similar to the one mentioned below:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_dev.this_.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

This is due to a current change in MySQL's approach of handling GROUP BY, which is mentioned here.

So, according to these changelogs, we can fix this issue by:

  1. Disabling ONLY_FULL_GROUP_BY, which I believe is not a great approach.
  2. Using ANY_VALUE() to refer to the nonaggregated column.

But there is no such option to use any_value() in grails criteria query. Updating the query to low-level SQL statement, or using HQL can help, but that'll require changes in multiple places.

So, is there any better approach using which we can handle the above-mentioned scenario?

1 Answers

Salman A On

You can simply use MIN or MAX if ANY_VALUE is not available.

Having said that, I would rather investigate each query and start with fixing the GROUP BY part.