Google Cloud SQL and sql_mode flag

Question

I am trying to run the following command

gcloud beta sql instances create my_replica \
--master-instance-name=db-master01 \
--master-username=replication_user \
--master-password='replication_password' \
--master-dump-file-path=gs://path/to/dump.sql.gz \
--database-flags default_time_zone='-05:00' \
                 sql_mode='STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION' \
--region=us-central \
--tier=db-g1-small

But I get the following error:

ERROR: (gcloud.beta.sql.instances.create) unrecognized arguments: sql_mode=STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

To search the help text of gcloud commands, run:
  gcloud help -- SEARCH_TERMS

Am I typing something incorrectly? Is this not supported?

UPDATE: From Stefan's response I updated my command to fix the syntax. The new error I get is the following:

Here is my command:

gcloud beta sql instances create $replica_name \
--master-instance-name=db-master01 \
--master-username=replication_user \
--master-password='replication_password' \
--master-dump-file-path=gs://path/to/dump.sql.gz \
--database-flags default_time_zone='-05:00',sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' \
--region=us-central \
--tier=db-g1-small

and here is the error:

ERROR: (gcloud.beta.sql.instances.create) argument --database-flags: Bad syntax for dict arg: [NO_ZERO_IN_DATE]. Please see `gcloud topic flags-file` or `gcloud topic escaping` for information on providing list or dictionary flag values with special characters.
Usage: gcloud beta sql instances create INSTANCE [optional flags]
  optional flags may be  --activation-policy | --assign-ip | --async |
                         --authorized-gae-apps | --authorized-networks |
                         --availability-type | --backup | --backup-start-time |
                         --client-certificate-path | --client-key-path | --cpu |
                         --database-flags | --database-version |
                         --enable-bin-log | --failover-replica-name |
                         --follow-gae-app | --gce-zone | --help | --labels |
                         --maintenance-release-channel |
                         --maintenance-window-day | --maintenance-window-hour |
                         --master-ca-certificate-path |
                         --master-dump-file-path | --master-instance-name |
                         --master-password | --master-username | --memory |
                         --network | --pricing-plan |
                         --prompt-for-master-password | --region |
                         --replica-type | --replication | --require-ssl |
                         --root-password | --source-ip-address | --source-port |
                         --storage-auto-increase |
                         --storage-auto-increase-limit | --storage-size |
                         --storage-type | --tier | --zone

For detailed information on this command and its flags, run:
  gcloud beta sql instances create --help

I don't understand why NO_ZERO_IN_DATE is a problem. Is it complaining because I'm passing it more than one value for the flag?

Answer 1

--database-flags take several parameters that you can pass using. ","

You are cutting --database-flags at default_time_zone='-05:00' use "," there.

Try to pass the --database-flags like this instead of splitting them with \

--database-flags default_time_zone='-05:00' , sql_mode='STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION' \

Here you have more specific information of how the database-flags are supposed to be passed.

Let me know.

EDIT:

Have a look at this, this is quite interesting. Apparently the default --sql_mode flag includes all the flags you are attempting to pass.

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

Google Cloud SQL is using 5.7 as default so all these flags should come applied if you set.

Here a look here.

The flag I spoke about is this one.

After deploying it you can check the flags by using the following command.

gcloud sql instances describe [INSTANCE_NAME]

If you want to read more about the command.

EDIT2:

You are not able to set those flags because they are not available to be passed as --database-flags in Google Cloud Platform, here you have a list of all the flags that you can pass to the --database-flags parameter. The problem is that most of them are either deprecated according to MySQL documentation or they are already implemented in the sql_mode that you establish. Have a look here.

The best way to achieve all the flags that you want is by setting the sql_mode to traditional,I've been looking into strict mode and it contains the one you do not wish to have.

Let me know.

Answer 2

I know this is an old thread, and almighty yaml is the utmost, but I'd like to share another solution.

It's all because of the collisions of separator 'comma'.

So, if you change the separator of --database-flags, can avoid the problem.

--database-flags=^||^ ? changes the separator from ',' to '||'

gcloud sql instances patch ${INSTANCE_NAME} \
--database-flags=^||^\
max_connections=2000||\
log_bin_trust_function_creators=on||\
default_time_zone=+09:00||\
group_concat_max_len=10240||\
slow_query_log=on||\
log_output=TABLE||\
transaction_isolation=READ-COMMITTED||\
innodb_ft_enable_stopword=off||\
performance_schema=on||\
innodb_buffer_pool_size=60129542144||\
sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_TRANS_TABLES'