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:

gcloud auth application-default login
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:

gcloud auth application-default login
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:

gcloud auth application-default login
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 about the representation of the flags in the output, see Instances Resource Representation.

cURL

To list flags configured for an instance:

gcloud auth application-default login
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.

For a given flag, Cloud SQL might support a different range than the corresponding MySQL parameter or option.

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

A | B | C | D | E | F | G | I | L | M | N | O | P | Q | R | S | T | U | W

Cloud SQL Flag Type
Acceptable Values and Notes
Restart
Required?
In
Beta?
auto_increment_increment integer
1 ... 65535
No No
auto_increment_offset integer
1 ... 65535
No No
binlog_cache_size integer
4096 ... 9223372036854775807
No Yes
binlog_row_image enumeration
full (default), minimal, or noblob
No No
binlog_stmt_cache_size 4096 ... 9223372036854775807 No Yes
character_set_server string
utf8 or utf8mb4 (recommended)
Yes No
default_time_zone string
Specify values as from -12:59 to +13:00. Leading zeros required.
Yes No
eq_range_index_dive_limit integer
0 ... 2147483647
No No
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 No
explicit_defaults_for_timestamp boolean
on | off
No No
ft_max_word_len integer
10 ... 252
Yes No
ft_min_word_len integer
1 ... 16
Yes No
ft_query_expansion_limit integer
0 ... 1000
Yes No
general_log boolean
on | off

See the Tips section for more information about general logs.

No No
group_concat_max_len integer
4 ... 17179869184
No No
init_connect string No Yes
innodb_adaptive_hash_index boolean
on | off
No Yes
innodb_adaptive_hash_index_parts integer
1 ... 512
Yes Yes
innodb_adaptive_max_sleep_delay integer
0 ... 1000000
No No
innodb_autoinc_lock_mode integer
0 ... 2
Yes No
innodb_change_buffer_max_size integer
0 ... 50
No Yes
innodb_concurrency_tickets integer
1 ... 4294967295
No No
innodb_file_per_table boolean
on | off

See the Tips section for more information about this flag.

No No
innodb_fill_factor integer
10 ... 100
No No
innodb_ft_aux_table string No No
innodb_ft_cache_size integer
1600000 ... 80000000
Yes Yes
innodb_ft_enable_diag_print boolean
on | off
No No
innodb_ft_enable_stopword boolean
on | off
No No
innodb_ft_max_token_size integer
10 ... 252

Supported only in MySQL 5.6 and later versions.

Yes No
innodb_ft_min_token_size integer
0 ... 16

Supported only in MySQL 5.6 and later versions.

Yes No
innodb_ft_num_word_optimize integer
1000 ... 10000
No No
innodb_ft_result_cache_limit integer
1000000 ... 4294967295
No Yes
innodb_ft_server_stopword_table string

Supported only in MySQL 5.6 and later versions.

No No
innodb_ft_sort_pll_degree integer
1 ... 32
Yes No
innodb_ft_total_cache_size integer
32000000 ... 1600000000
Yes Yes
innodb_ft_user_stopword_table string No No
innodb_large_prefix boolean
on | off

Supported only in MySQL 5.5 and 5.6.

No No
innodb_lock_wait_timeout integer
1 ... 1073741824
No No
innodb_log_buffer_size integer
262144 ... 4294967295
Yes Yes
innodb_lru_scan_depth integer
100 ... 9223372036854775807
No No
innodb_old_blocks_pct integer
5 ... 95
No No
innodb_old_blocks_time integer
0 ... 4294967295
No No
innodb_online_alter_log_max_size integer
65536 ... 9223372036854775807
No No
innodb_optimize_fulltext_only boolean
on | off
No No
innodb_print_all_deadlocks boolean
on | off
No No
innodb_random_read_ahead boolean
on | off
No No
innodb_read_ahead_threshold integer
0 ... 64
No No
innodb_replication_delay integer
0 ... 4294967295
No Yes
innodb_rollback_on_timeout boolean
on | off
Yes No
innodb_rollback_segments integer
1 ... 128
No No
innodb_sort_buffer_size integer
65536 ... 67108864
Yes Yes
innodb_stats_auto_recalc boolean
on | off
No No
innodb_stats_method enumeration
nulls_equal | nulls_unequal | nulls_ignored
No No
innodb_stats_on_metadata boolean
on | off
No No
innodb_stats_persistent boolean
on | off
No No
innodb_stats_persistent_sample_pages integer
1 ... 9223372036854775807
No No
innodb_stats_sample_pages integer
1 ... 9223372036854775807
No No
innodb_stats_transient_sample_pages integer
1 ... 9223372036854775807
No No
innodb_status_output boolean
on | off
No No
innodb_status_output_locks boolean
on | off
No No
innodb_thread_concurrency integer
0 ... 1000
No No
innodb_thread_sleep_delay integer
0 ... 1000000
No No
interactive_timeout integer
1 ... 31536000
No No
join_buffer_size integer
128 ... 9223372036854775807
No Yes
local_infile boolean
on | off
No No
lock_wait_timeout integer
1 ... 31536000
No No
log_bin_trust_function_creators boolean
on | off
No No
log_output set
FILE | TABLE | NONE
No No
log_queries_not_using_indexes boolean
on | off
No No
log_throttle_queries_not_using_indexes integer
0 ... 9223372036854775807
No No
long_query_time float
0 ... 30000000

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

No No
lower_case_table_names integer
0 or 1

In most cases, the default value of 0 should be used, which makes table and database names case sensitive. If you need to change the value of this flag, make sure that you understand how the change will affect your existing tables and databases.

Yes No
max_allowed_packet integer
16384 ... 1073741824

This value should be a multiple of 1024, if sql_mode=STRICT_ALL_TABLES.

No No
max_connections integer
25 ... 100000
No Yes
max_digest_length integer
0 ... 1048576
Yes Yes
max_execution_time integer
0 ... 9223372036854775807
No No
max_heap_table_size integer
16384 ... 67108864

See the Tips section for more information about this flag.

No No
max_join_size integer
16 ... 9223372036854775807
No No
max_length_for_sort_data integer
4 ... 8388608
No No
max_points_in_geometry integer
3 ... 1048576
No No
max_prepared_stmt_count integer
0 ... 1048576
No No
max_seeks_for_key integer
1 ... 9223372036854775807
No No
max_sort_length integer
4 ... 8388608
No Yes
max_sp_recursion_depth integer
0 ... 255
No Yes
max_user_connections integer
0 ... 4294967295
No Yes
max_write_lock_count integer
1 ... 9223372036854775807
No No
net_read_timeout integer
30 ... 4294967295
No No
net_retry_count integer
10 ... 4294967295
No No
net_write_timeout integer
60 ... 4294967295
No No
optimizer_prune_level integer
0 ... 1
No No
optimizer_search_depth integer
0 ... 62
No No
optimizer_trace_max_mem_size integer
0 ... 9223372036854775807
No Yes
optimizer_trace_offset integer
-9223372036854775808 ... 9223372036854775807
No No
parser_max_mem_size integer
10000000 ... 9223372036854775807
No Yes
performance_schema boolean
on | off (default)

See Tips section for more information about performance_schema flags.

Yes No
performance_schema_accounts_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_digests_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_stages_history_long_size integer
-1 ... 1048576
Yes Yes
performance_schema_events_stages_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_statements_history_long_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_statements_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_transactions_history_long_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_transactions_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_waits_history_long_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_events_waits_history_size integer
-1 ... 1024

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_hosts_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_cond_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_cond_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_digest_length integer
0 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_file_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_file_handles integer
0 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_file_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_index_stat integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_memory_classes integer
0 ... 1024

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_metadata_locks integer
-1 ... 104857600

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_mutex_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_mutex_instances integer
-1 ... 104857600

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_prepared_statements_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_program_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_rwlock_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_rwlock_instances integer
-1 ... 104857600

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_socket_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_socket_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_sql_text_length integer
0 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_stage_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_statement_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_statement_stack integer
1 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_table_handles integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_table_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_table_lock_stat integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_thread_classes integer
0 ... 256

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_max_thread_instances integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_session_connect_attrs_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_setup_actors_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_setup_objects_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
performance_schema_users_size integer
-1 ... 1048576

See Tips section for more information about performance_schema flags.

Yes Yes
query_alloc_block_size integer
1024 ... 4294967295
No Yes
query_cache_limit integer
0 ... 223338299392
No No
query_cache_min_res_unit integer
0 ... 9223372036854775807
No Yes
query_cache_size integer
0 ... 223338299392

See the Tips section for more information about this flag.

No No
query_cache_type enumeration
0 ... 2

See the Tips section for more information about this flag.

Yes No
query_cache_wlock_invalidate boolean
on | off
No No
query_prealloc_size integer
8192 ... 9223372036854775807
No Yes
range_alloc_block_size integer
4096 ... 4294967295
No Yes
range_optimizer_max_mem_size integer
0 ... 9223372036854775807
No Yes
read_buffer_size integer
8192 ... 2147483647
No Yes
read_only boolean
on | off

Has no effect for replicas.

No No
read_rnd_buffer_size integer
1 ... 2147483647
No Yes
show_compatibility_56 boolean
on | off

Supported only in MySQL 5.7.

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

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

No No
sort_buffer_size integer
32768 ... 9223372036854775807
No Yes
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.

No No
stored_program_cache integer
16 ... 524288
No Yes
table_definition_cache integer
400 ... 524288
No Yes
table_open_cache integer
1 ... 524288
No Yes
table_open_cache_instances integer
1 ... 64
Yes Yes
thread_cache_size integer
0 ... 16384
No Yes
thread_stack integer
131072 ... 9223372036854775807
Yes Yes
tmp_table_size integer
1024 ... 67108864

See the Tips section for more information about this flag.

No No
transaction_alloc_block_size integer
1024 ... 131072
No Yes
transaction_isolation enumeration
READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE
Yes Yes
transaction_prealloc_size integer
1024 ... 131072
No Yes
updatable_views_with_limit integer
0 ... 1
No No
wait_timeout integer
1 ... 31536000
No 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 or cause the instance to lose its SLA coverage; 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.

Some values for these flags can cause your instance to lose its SLA coverage. Learn more. For more information about working with these flags, see How MySQL Uses Internal Temporary Tables and The MEMORY Storage Engine.

performance_schema*

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_size, query_cache_type

Some values for these flags can cause your instance to lose its SLA coverage. Learn more.

What's next

Was this page helpful? Let us know how we did:

Send feedback about...

Cloud SQL for MySQL