Hi Guys !
Recently I came across the exception while executing the SQL. The exception is:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘saw.SR.RoleId’ which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by
The reason behind this is, by default MySql mode is set to only_full_group_by and all column names from select clause of my query are not defined in Group By clause. The MySql reference defines as below-
ONLY_FULL_GROUP_BY:
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to non-aggregated columns that are not named in the GROUP BY clause.
There are two ways to eliminate the above exception:
- For the query to be legal, the column(s) must be omitted from the select list or named in the GROUP BY clause.
- OR change the sql_mode to the TRADITIONAL
Note
With TRADITIONAL mode enabled, an INSERT or UPDATE aborts as soon as an error occurs. If you are using a nontransactional storage engine, this may not be what you want because data changes made prior to the error may not be rolled back, resulting in a “partially done” update
As I’m already using Spring JDBC Transactional handling setting sql_mode to Traditional not impacted the application. So I changed the sql_mode in Google Cloud SQL to TRADITIONAL. Navigate to Storage -> SQL and choose the database instance, edit it and look out for the option “Add Database Flag”
Note: Project has MySql 5.7 on Google Cloud .
That’s it. Suggestions and feedback are welcome.
Happy Coding.
