Setting MySql flags in SQL Google Cloud Database

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:

  1. For the query to be legal, the column(s) must be omitted from the select list or named in the GROUP BY clause.
  2. 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”

mysql_db_para

Note: Project has MySql 5.7 on Google Cloud .

That’s it. Suggestions and feedback are welcome.

Happy Coding.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s