Configuring Database Flags

This page describes how to configure database flags for Cloud SQL, and lists which flags you can set for your instance. You use database 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 database flags

Setting a database flag

Console

  1. In the Google Cloud Platform Console, create a new GCP 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 GCP Console project, or open an existing project by selecting the project name.
  2. Open the instance and click Edit.
  3. Open the Database 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 database 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 database 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 GCP Console project, or open an existing project by selecting the project name.
  2. Select the instance to open its Instance Overview page.

    The database flags that have been set are listed under the Database 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 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 Restart Required
auto_increment_increment integer 1 .. 65535 No
auto_increment_offset integer 1 .. 65535 No
binlog_row_image enumeration full (default), minimal, or noblob. No
character_set_server string utf8 or utf8mb4 No
default_time_zone string Specify values as from-12:59 to +13:00. Leading zeros required. Yes
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.

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

See the Tips section for more information about general logs.

No
group_concat_max_len integer 4 .. 17179869184 No
innodb_autoinc_lock_mode integer 0 .. 2 Yes
innodb_file_per_table boolean on | off

See the Tips section for more information about this flag.

No
innodb_ft_max_token_size integer 10 .. 252

Supported only in MySQL 5.6 and later versions.

Yes
innodb_ft_min_token_size integer 0 .. 16

Supported only in MySQL 5.6 and later versions.

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

Supported only in MySQL 5.5 and 5.6.

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

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

No
lower_case_table_names integer 0 .. 2 Yes
max_allowed_packet integer 16384 .. 1073741824 No
max_heap_table_size integer 16384 .. 67108864

See the Tips section for more information about this flag.

No
max_prepared_stmt_count integer 0 .. 1048576 No
optimizer_search_depth integer 0 .. 62 No
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.

Yes
query_cache_limit integer 0 .. 223338299392 No
query_cache_size integer 0 .. 223338299392 No
query_cache_type enumeration 0 .. 2 Yes
read_only boolean on | off

Has no effect for replicas.

No
show_compatibility_56 boolean on | off

Supported only in MySQL 5.7.

No
skip_show_database flag on | off Yes
slow_query_log boolean on | off

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

No
sql_mode string See the Server SQL Modes in the MySQL documentation for allowed values, including combined modes such as ANSI. Only one mode can be selected. NO_DIR_IN_CREATE and NO_ENGINE_SUBSTITUTION are not supported. No
tmp_table_size integer 1024 .. 67108864

See the Tips section for more information about this flag.

No
wait_timeout integer 1 .. 31536000 No

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