Configuring Cloud SQL Flags

This page describes how to configure Cloud SQL flags, and lists which flags you can set for your Cloud SQL instance. You use Cloud SQL flags to adjust MySQL parameters and options, to configure and tune your instance.

In some cases, setting one flag may require that you set another flag to fully enable the desired functionality. For example, to enable slow query logging, you must set both the slow_query_log flag to on and the log_output flag to FILE to make your logs available using the Google Cloud Platform Console Logs Viewer.

When you set, remove, or modify a flag for an instance, the instance might be restarted. The flag value is then persisted for the instance until you remove it.

Configuring Cloud SQL flags

Setting a Cloud SQL flag

Console

  1. In the Google Cloud Platform Console, create a new Cloud Platform Console project, or open an existing project by selecting the project name.
  2. Open the instance and click Edit.
  3. Scroll down to the Flags section.
  4. To set a flag that has not been set on the instance before, click Add item, choose the flag from the drop-down menu, and set its value.
  5. Click Save to save your changes.
  6. Confirm your changes under Flags on the Overview page.

gcloud

Edit the instance:

gcloud sql instances patch [INSTANCE_NAME] --database-flags [FLAG1=VALUE1,FLAG2=VALUE2]

For flags that do not take a value, specify the flag name followed by an equals sign ("="). Include the values for all flags you want set on the instance; any flag not included is set back to its default value.

For example, to set the general_log, skip_show_database, and wait_timeout flags, you can use the following command:

gcloud sql instances patch [INSTANCE_NAME] --database-flags general_log=on, \
       skip_show_database=, wait_timeout=200000

cURL

To set a flag for an existing instance:

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
       --header 'Content-Type: application/json' \
       --data '{"settings" : {"databaseFlags" : \
                                 [{ "name": "<FLAG-NAME>",  "value": "<FLAG-VALUE>" }]}}' \
       -X PATCH \
       https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>

For example, to set the general_log flag for an existing instance use:

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
       --header 'Content-Type: application/json' \
       --data '{"settings" : {"databaseFlags" : [{ "name": "general_log",  "value": "on" }]}}' \
       -X PATCH \
       https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>

If there are existing flags configured for the instance, modify the previous command to include them. The PATCH command overwrites the existing flags with the ones specified in the request.

Clearing all flags to their default value

Console

  1. In the Google Cloud Platform Console, create a new Cloud Platform Console project, or open an existing project by selecting the project name.
  2. Open the instance and click Edit.
  3. Scroll down to the Flags section.
  4. Click the X next to all of the flags shown.
  5. Click Save to save your changes.

gcloud

Clear all flags to their default values on an instance:

gcloud sql instances patch [INSTANCE_NAME] --clear-database-flags

You are prompted to confirm that the instance will be restarted.

cURL

To clear all flags for an existing instance:

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
       --header 'Content-Type: application/json' \
       --data '{"settings" : {"databaseFlags" : []}}' \
       -X PATCH \
       https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>

Viewing current values of Cloud SQL flags

To view all current values of the MySQL system variables, log into your instance with the mysql client and enter the following statement:

 SHOW VARIABLES;

Note that you can change the value only for supported flags (as listed below).

Determining what flags have been set for an instance

To see what flags have been set for a Cloud SQL instance:

Console

  1. In the Google Cloud Platform Console, create a new Cloud Platform Console project, or open an existing project by selecting the project name.
  2. Select the instance to open its Instance details page.

    The flags that have been set are listed under the Flags section.

gcloud

Get the instance state:

gcloud sql instances describe [INSTANCE_NAME]

In the output, database flags are listed under the settings as the collection databaseFlags. For more information on the representation of the flags in the output, see Instances Resource Representation.

cURL

To list flags configured for an instance:

ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
       --header 'Content-Type: application/json' \
       -X GET \
       https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>?fields=settings

In the output, look for the databaseFlags field.

Supported flags

The flags supported in Google Cloud SQL are the most commonly requested flags for MySQL. Note that for a given flag, Cloud SQL may support a different range than the corresponding MySQL variable or option.

The flags apply to all versions of MySQL supported by Cloud SQL except where noted.

Cloud SQL Flag Type Acceptable Values and Notes
auto_increment_increment integer 1 .. 65535
auto_increment_offset integer 1 .. 65535
binlog_checksum string CRC32 or NONE

Supported only in MySQL 5.6 and later versions.

binlog_row_image enumeration full (default), minimal, or noblob.
character_set_server string utf8 or utf8mb4
default_time_zone string Specify values as from-12:59 to +13:00. Leading zeros required.
event_scheduler boolean on | off

Cannot be set at instance creation time for First Generation instances.

If you are using the Event Scheduler, you should configure your instance with an activation policy of ALWAYS to ensure that scheduled events run.

ft_max_word_len integer 10 .. 252
ft_min_word_len integer 1 .. 16
ft_query_expansion_limit integer 0 .. 1000
general_log boolean on | off

See the Tips section for more information about general logs.

group_concat_max_len integer 4 .. 4294967295
innodb_autoinc_lock_mode integer 0 .. 2
innodb_file_per_table boolean on | off

See the Tips section for more information about this flag.

innodb_flush_log_at_trx_commit enumeration 0 | 1 | 2
innodb_ft_max_token_size integer 10 .. 252

Supported only in MySQL 5.6 and later versions.

innodb_ft_min_token_size integer 0 .. 16

Supported only in MySQL 5.6 and later versions.

innodb_ft_server_stopword_table string Supported only in MySQL 5.6 and later versions.
innodb_large_prefix boolean on | off

Supported only in MySQL 5.5 and 5.6.

innodb_lock_wait_timeout integer 1 .. 1073741824
innodb_print_all_deadlocks boolean on | off
interactive_timeout integer 1 .. 28800
local_infile boolean on | off
lock_wait_timeout integer 1 .. 31536000
log_bin_trust_function_creators boolean on | off
log_output set FILE | TABLE | NONE
log_queries_not_using_indexes boolean on | off
long_query_time float 0 .. 30000000

Cloud SQL provides the ability to set this flag to less than 1 if needed.

lower_case_table_names integer 0 .. 2
max_allowed_packet integer 1024 .. 1073741824
max_heap_table_size integer 16384 .. 1844674407370954752

See the Tips section for more information about this flag.

max_prepared_stmt_count integer 0 .. 1048576
optimizer_search_depth integer 0 .. 62
performance_schema boolean on | off (default)

You must use the API to change the value of this flag. It cannot be enabled on instances with a tier size smaller than db-n1-standard-8 or db-n1-highmem-4. If this flag is enabled, you cannot change your tier to a size that does not support this flag; you must first disable this flag. Not supported for First Generation instances.

query_cache_limit integer 0 .. 18446744073709551615
query_cache_size integer 0 .. 18446744073709551615
query_cache_type enumeration 0 .. 2
read_only boolean on | off

Has no effect for replicas.

show_compatibility_56 boolean on | off

Supported only in MySQL 5.7.

skip_show_database flag on | off
slow_query_log boolean on | off

See the Tips section for more information on slow query logs.

sql_mode string See the Server SQL Modes in the MySQL documentation for allowed values, including combined modes such as ANSI. NO_DIR_IN_CREATE and NO_ENGINE_SUBSTITUTION are not supported.
tmp_table_size integer 1024 .. 18446744073709551615

See the Tips section for more information about this flag.

wait_timeout integer 1 .. 31536000

Tips for working with flags

general_log, slow_query_log

To make your general or slow query logs available, enable the corresponding flag and set the log_output flag to FILE. This makes the log output available using the Logs Viewer in the Google Cloud Platform Console. Note that Stackdriver logging charges apply.

If log_output is set to NONE, you will not be able to access the logs. If you set log_output to TABLE, the log output is placed in a table in your database. If this table becomes large, it can affect instance restart time; for this reason, the TABLE option is not recommended. If needed, you can truncate your log tables by using the API. For more information, see the instances.truncateLog reference page.

For First Generation instances, a very large mysql.general_log or mysql.slow_log table can cause connections to hang after a restart of the instance. To avoid this problem, the general_log and/or slow_query_log settings are ignored on restart if the log size grows to be larger than 128 Mb. For this reason, for First Generation instances, we recommend enabling the flag only temporarily or keeping the table small by periodically truncating it. If needed, INSERT ... SELECT can be used to preserve the contents of the mysql.general_log table.

innodb_file_per_table

This flag is set to OFF by default for MySQL 5.5; for all later MySQL versions, the default value is ON.

max_heap_table_size, tmp_table_size

Exhausting the available instance memory can occur when you set tmp_table_size and max_heap_table_size too high for the number of concurrent queries the instance processes. Exhausting the memory will result in an instance crash and restart.

For more information about working with these flags, see How MySQL Uses Internal Temporary Tables and The MEMORY Storage Engine.

What's next

Learn more about MySQL system variables.

Send feedback about...

Cloud SQL for MySQL