How can I set sql_mode to a list of values

Question

I am trying to use the 2nd gen cloud sql and would like to change the sql mode. In the UI, I can only set sql_mode to one value from a drop-down list, but not multiple of them (eg, "STRICT_MODE_TRANS,ALLOW_INVALID_DATES"). What would be the best way to accomplish that?

Cheers, Andres

Systematics

Is this still an issue? I'm trying to do this, and am facing the same problem. – Cocytus

No it is not an issue now. Google Cloud SQL console UI supports defining multiple flags now. – Runabout

Answer 1

This is not supported right now by Google Cloud SQL. You can only set one value.

Theodoretheodoric

This...this makes me sad. But at least I can stop feeling like I'm taking crazy pills trying to get multiple values to work. – Katharinakatharine

Answer 2

I know this post is 1 year old, but I stumbled upon this now when I had a problem with sql_mode when I tried migrating a database from MySQL 5.5 to Google SQL using 5.7. Though I know that we could SET GLOBAL sql_mode='' to any valid value we want, it took me hours to give up and concluded we could not set multiple values on Google Cloud SQL.

Google only allows one value to be set on sql_mode flag for now. If your problem pertains to removing ONLY_FULL_GROUP_BY (OP does not mention why he wants to customize values) without removing the rest of the values of sql_mode, using the value TRADITIONAL in the Console or gcloud sql instances patch <instance_name> --database-flags sql_mode=TRADITIONAL will remove that value from the rest of the string.

From MySQL 5.7 Documentation:

Before MySQL 5.7.4, and in MySQL 5.7.8 and later, TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

I would have only added this as a comment above, but I can't add one yet due to lacking points.

Livingston

Great answer! If you struggle to update your Cloud SQL instance sql_mode via the UI, use the gcloud command line utilities. Works like a charm. – Tercentenary

thx dude, I finally solved the ONLY_FULL_GROUP_BY issue! – Repress

Answer 3

Another potential solution is to set the sql_mode to HIGH_NOT_PRECEDENCE

Once set in Cloud SQL the string for sql_mode will become:

HIGH_NOT_PRECEDENCE

All other flags are removed!

I was coming from an older project so this solution might not work for all, but seems to be working well for us, plus it's something that can be tried quickly.

Mistakable