How can I set sql_mode to a list of values

mysql> set sql_mode
mysql set sql_mode permanently
how to set sql_mode in mysql workbench
how to check sql mode in mysql
set sql_mode=no_engine_substitution
set global sql_mode = 'only_full_group_by'
cloud sql sql_mode
mysql strict mode disable

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

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

MySQL 5.7 Reference Manual :: 5.1.10 Server SQL Modes, To determine the current global or session sql_mode setting, select its value: SELECT of SQL Modes. The following list describes all supported SQL modes:. To set the SQL mode at server startup, use the --sql-mode=" modes " option on the command line, or sql-mode=" modes " in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas.

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.

MySQL 8.0 Reference Manual :: 5.1.11 Server SQL Modes, To determine the current global or session sql_mode setting, select its value: SELECT of SQL Modes. The following list describes all supported SQL modes:. CREATE TABLE t (id INT, tval TIME(1)); SET sql_mode=''; INSERT INTO t (id, tval) VALUES(1, 1.55); SET sql_mode='TIME_TRUNCATE_FRACTIONAL'; INSERT INTO t (id, tval) VALUES(2, 1.55); The resulting table contents look like this, where the first value has been subject to rounding and the second to truncation:

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.

SQL_MODE, To set the global value, the SUPER privilege is required, and the change affects This example shows how to get a readable list of enabled SQL_MODE flags: Next to the average and the median, there is another statistical value that you can calculate over a set: the mode. This is the value that appears the most often in the set. The average and the median are straight forward to calculate with T-SQL in SQL Server, but how can we determine the mode using T-SQL? Solution. The mode is a statistical

MySQL sql_mode, Get the current value that could be already modified by SET sql_mode statement -- executed in the current session SELECT @@sql_mode; -- Get the global  I have seen the answer from a previous post, which works fine but I have a small dilemma. Taking the same scenario: A table that list students' grades per class. I want a result set that looks lik

5.1.10 Server SQL Modes, To determine the current global or session sql_mode setting, select its value: SELECT of SQL Modes. The following list describes all supported SQL modes:. SQL Query: How to pass list of values to the IN operator 622048 Nov 16, 2009 8:21 PM Hi, I'm trying to pass a list of values to the WHERE <a> IN <list> I dont want to use dynamic cursors (REF CURSOR).

5.1.7 Server SQL Modes, To set the SQL mode at server startup, use the --sql-mode=" modes " option on the if the value occurs in a single-row statement or the first row of a multiple-​row  Using subquery to return a list of values. When the subquery returns a list of values, the subquery is only evaluated once and then the list of values is returned to outer query to use. This kind of subqueries are also known as column subquery. Column subquery is normally used in WHERE clause with an IN operator that tests each value in the

Comments
  • Is this still an issue? I'm trying to do this, and am facing the same problem.
  • No it is not an issue now. Google Cloud SQL console UI supports defining multiple flags now.
  • 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.
  • 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.