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?

Pelpel

Take out the spaces between your sql modes. – Levitical

@BillKarwin Thanks for the reply, I've updated and re-ran the command. The output is the same. I did see some old posts from a few years back indicating that it was not possible (#39081889). The post is from 3 years ago, so I'm not sure if anything has changed. – Pelpel

Okay it was worth a try. I don't use Google Cloud, so I don't know if there's any limitation specific to Google. I just thought of removing the spaces because standard MySQL throws an error if you have spaces in the sql_mode. – Levitical

Answer 1

So I just solved this with the help of GCP Support. Turns out - surprise! - there's a bug in the gcloud sql instances create command that prevents it from parsing SQL_MODE if it's set to a comma-separated list.

This bug has existed since 2016 but has not been a priority for them to fix. <rage emoji here> Would have been nice if they'd at least documented it in the docs, but this is our life right now.

Here's the workaround: a flags file.

Your flags file is just pure YAML, but it looks a little funky. Mind the first dash to indicate a list item, followed by a space, followed by the actual flag syntax (--my-attribute: yaddayadda). Also of note is that integers need to be quoted so that they're cast as strings for parsing:

# sql-flags-file.yaml

- --availability-type: zonal
- --backup-start-time: 05:00 
- --database-flags:
    explicit_defaults_for_timestamp: on
    lower_case_table_names: '1'
    group_concat_max_len: '67108864'
    sql_mode: NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    default_time_zone: -07:00
    log_bin_trust_function_creators: on
    interactive_timeout: '600'
- --database-version: MYSQL_5_7

You can use this flags file in conjunction with inline flags, like so:

gcloud --project=${project_name} beta sql instances create ${instance_name} \
--network="${MY_VPC_NAME}" \
--no-assign-ip \
--flags-file=sql-flags-file.yaml && echo "...done."

This allows you to parameterize dynamic variables (like the names of the Instance and the VPC network in the sample above) while also standardizing configs across instances.

You can even use multiple flag files together, if that's a thing you're into. That's addressed in the link I provided above.

I REALLY hope this helps you. Nobody else should have to swear as much as I did while troubleshooting this yesterday.

Anent

wow, I wasn't expecting a solution to this. I'll need to try this later today, but this looks good if it works! Currently I have to add the SQL_MODE flags manually though the UI every time I create a replica. This should allow for automating my whole process. – Pelpel

@Pelpel that's the idea! GCP's biggest Achilles' Heel is that they put all their energy into their UIs and not into their APIs early on. it's been painful for us devops folks. – Anent

BTW this issue is being tracked as a "feature request" by Google, which is complete malarkey. Feel free to star the issue in their issue tracker and add your thoughts on the matter: issuetracker.google.com/issues/35904292 – Anent

Many thanks, I was finally able to verify this. My Replication process is now fully automated since I do not need to enter SQL_MODE flags separately. Marking this as solved! – Pelpel

Sweet, congratulations, @blueether! – Anent

Answer 2

--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.

Nugent

Thanks for the reply and catching my syntax error. I updated my command and re-ran it. I updated the original post with the new command and the new error. – Pelpel

Have a look at my edit, that should help you definitely solve your issue, let me know if it doesn't and we will continue looking into it. – Nugent

That is correct, what you said about the default flags. The reason for setting this is specifically I do not want the first flag in that list ONLY_FULL_GROUP_BY. I couldn't really tell you why, I didn't create the original database so I don't know why it's not set in the original database. But I encountered this issue because some of the queries I was attempting to do through Grafana complained about ONLY_FULL_GROUP_BY. Does this help? Is there any other info I can provide? – Pelpel

My issue can be solved by manually setting the flags I want through the web interface, but spinning up the slave is part of our deployment process so I try to run everything I can from scripts to avoid errors, so making manual changes through the interface is doable but less desirable than being able to specify the flags through the commandline – Pelpel

Hey blueether, take a loot at my edit, that is the only way I found in which you can have your way of not having ONLY_FULL_GROUP_BY, hope this helps you. – Nugent

Hey Stefan - Very interesting! The other difference between the flags I want and TRADITIONAL is STRICT_ALL_TABLES vs STRICT_TRANS_TABLES. This page noelherrick.com/blog/… indicates that there's 3 criteria that have to be met for these flags to behave differently, the first being I am using some storage engine other than InnoDB. In my case, I am using InnoDB for all tables, so I think it's fine if use TRADITIONAL. Thanks, this has been very helpful! – Pelpel

Also, one thing i was confused about - you said my set of flags is not allowed in Google Cloud Platform, but I was about to set them through the web console imgur.com/a/VMjEkBv – Pelpel

Hey, I assume you managed to find a combination that fits you best, what I meant by the flags not being available, it was due to them not being represented in the list of available flags in the documentation that I linked. Glad we managed to solve it. If you so consider please mark my answer as accepted. Let me know if you need anything else. – Nugent

I don't know if anyone else is getting this, but passing in a string that contains a CSV list for the sql_mode parameter yields a "Bad syntax for dict arg" error for whatever the second value in the list is. It doesn't matter that the values are all part of the TRADITIONAL collection; sql_mode says it wants a string, and it doesn't seem to accept anything other than a single value with no commas in it. I have a support ticket open on this and will follow up when I know what's what, since it's been 6 months since this answer, and things shift regularly with GCP. – Anent

Answer 3

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'

Yehudit