Configuring database flags

MySQL   |  PostgreSQL   |  SQL Server

This page describes how to configure database flags for Cloud SQL, and lists the flags that you can set for your instance. You use database flags for many operations, including adjusting MySQL parameters, adjusting options, and configuring and tuning an instance.

Note: Some database flag settings can affect instance availability or stability, and remove the instance from the Cloud SQL SLA. For information about these flags, see Operational Guidelines.

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 Console Logs Viewer.

When you set, remove, or modify a flag for a database instance, the database might be restarted. The flag value is then persisted for the instance until you remove it. If the instance is the source of a replica, the replica will also restart to align with the current configuration of the instance.

Configuring database flags

Setting a database flag

Console

1. In the Google Cloud Console, create a new Cloud Console project, or open an existing project by selecting the project name.
   Important: You should be aware that some resource identifiers (such as project IDs) might be retained beyond the life of your project. For this reason, avoid storing sensitive information in resource identifiers.
   ...see naming guidelines
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]

This command will overwrite all database flags previously set. To keep those and add new ones, include the values for all flags you want set on the instance; any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign ("=").

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=on,wait_timeout=200000

REST v1beta4

To set a flag for an existing database:

Before using any of the request data below, make the following replacements:

• project-id: The project ID
• instance-id: The instance ID

HTTP method and URL:

PATCH https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{
  "settings":
  {
    "databaseFlags":
    [
      {
        "name": "flag_name",
        "value": "flag_value"
      }
    ]
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

curl -X PATCH \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

PowerShell (Windows)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
  -Method PATCH `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -InFile request.json `
  -Uri "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "PENDING",
  "user": "[e-mail]",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

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

Before using any of the request data below, make the following replacements:

• project-id: The project ID
• instance-id: The instance ID

HTTP method and URL:

PATCH https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{
  "settings":
  {
    "databaseFlags":
    [
      {
        "name": "general_log",
        "value": "on"
      }
    ]
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

curl -X PATCH \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

PowerShell (Windows)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
  -Method PATCH `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -InFile request.json `
  -Uri "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "PENDING",
  "user": "[e-mail]",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

If there are existing flags configured for the database, 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 Console, create a new Cloud Console project, or open an existing project by selecting the project name.
   Important: You should be aware that some resource identifiers (such as project IDs) might be retained beyond the life of your project. For this reason, avoid storing sensitive information in resource identifiers.
   ...see naming guidelines
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.

REST v1beta4

To clear all flags for an existing instance:

Before using any of the request data below, make the following replacements:

• project-id: The project ID
• instance-id: The instance ID

HTTP method and URL:

PATCH https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{
  "settings":
  {
    "databaseFlags": []
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

curl -X PATCH \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

PowerShell (Windows)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Save the request body in a file called request.json, and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
  -Method PATCH `
  -Headers $headers `
  -ContentType: "application/json; charset=utf-8" `
  -InFile request.json `
  -Uri "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id",
  "status": "PENDING",
  "user": "[e-mail]",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": "operation-id",
  "targetId": "instance-id",
  "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
  "targetProject": "project-id"
}

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 Console, create a new Cloud Console project, or open an existing project by selecting the project name.
   Important: You should be aware that some resource identifiers (such as project IDs) might be retained beyond the life of your project. For this reason, avoid storing sensitive information in resource identifiers.
   ...see naming guidelines
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.

REST v1beta4

To list flags configured for an instance:

Before using any of the request data below, make the following replacements:

• project-id: The project ID
• instance-id: The instance ID

HTTP method and URL:

GET https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Execute the following command:

curl -X GET \
-H "Authorization: Bearer "$(gcloud auth print-access-token) \
https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

PowerShell (Windows)

Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
  -Method GET `
  -Headers $headers `
  -Uri "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "settings":
  {
    "authorizedGaeApplications": [],
    "tier": "machine-type",
    "kind": "sql#settings",
    "availabilityType": "REGIONAL",
    "pricingPlan": "PER_USE",
    "replicationType": "SYNCHRONOUS",
    "activationPolicy": "ALWAYS",
    "ipConfiguration":
    {
      "privateNetwork": "projects/project-id/global/networks/default",
      "authorizedNetworks": [],
      "ipv4Enabled": false
    },
    "locationPreference":
    {
      "zone": "zone",
      "kind": "sql#locationPreference"
    },
    "databaseFlags": [
      {
        "name": "general_log",
        "value": "on"
      }
    ],
    "dataDiskType": "PD_SSD",
    "maintenanceWindow":
    {
      "kind": "sql#maintenanceWindow",
      "hour": 0,
      "day": 0
    },
    "backupConfiguration":
    {
      "startTime": "03:00",
      "kind": "sql#backupConfiguration",
      "enabled": true,
      "binaryLogEnabled": true
    },
    "settingsVersion": "54",
    "storageAutoResizeLimit": "0",
    "storageAutoResize": true,
    "dataDiskSizeGb": "10"
  }
}

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 from the corresponding MySQL parameter or option.

Note: Some flags in the following table are in beta for Cloud SQL. Acceptable ranges for these flags might change. The Cloud SQL SLA does not cover instances that use non-default settings for these flags.

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?
activate_all_roles_on_login	boolean on | off default: off Note: The flag is supported in MySQL 8.0 for Cloud SQL.	No	No
auto_increment_increment	integer 1 ... 65535	No	No
auto_increment_offset	integer 1 ... 65535	No	No
binlog_cache_size	integer 4096 ... 9223372036854775807	No	No
binlog_row_image	enumeration full (default), minimal, or noblob	No	No
binlog_stmt_cache_size	4096 ... 9223372036854775807	No	No
binlog_transaction_dependency_history_size	integer For information about how to use this flag and its acceptable values, see Configuring parallel replication.	No	No
binlog_transaction_dependency_tracking	enumeration For information about how to use this flag and its acceptable values, see Configuring parallel replication.	No	No
character_set_server	string utf8 or utf8mb4 (recommended)	Yes	No
default_password_lifetime	integer 0...65535 default: 0 Note: The flag is supported in MySQL 8.0 for Cloud SQL.	No	No
default_time_zone	string Specify values as time zone offsets, from -12:59 to +13:00. Leading zeros required. For example, London is in the UTC time zone, which is +00:00 in the supported values for this flag. Named areas, such as 'Europe/London', are not supported by Cloud SQL. Note: Automatic adjustment to daylight saving time is not supported, and you must update this flag manually to account for it.	Yes	No
disconnect_on_expired_password	boolean on | off default: on Note: The flag is supported in MySQL 8.0 for Cloud SQL.	Yes	No
eq_range_index_dive_limit	integer 0 ... 2147483647	No	No
event_scheduler	boolean on | off If you are using the Event Scheduler, 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
generated_random_password_length	integer 5-255 default: 20 Note: The flag is supported in MySQL 8.0 for Cloud SQL.	No	No
group_concat_max_len	integer 4 ... 17179869184	No	No
init_connect	string	No	No
innodb_adaptive_hash_index	boolean on | off	No	No
innodb_adaptive_hash_index_parts	integer 1 ... 512	Yes	No
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	No
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_cache_size	integer 1600000 ... 80000000	Yes	No
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	No
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	No
innodb_ft_user_stopword_table	string	No	No
innodb_large_prefix	boolean on | off Supported only in MySQL 5.6.	No	No
innodb_lock_wait_timeout	integer 1 ... 1073741824	No	No
innodb_log_buffer_size	integer 262144 ... 4294967295	Yes	No
innodb_log_file_size	integer MySQL 5.6: 1048576 ... 274877906944 MySQL 5.7: 4194304 ... 274877906944	Yes	No
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_read_io_threads	integer 1 ... 64	Yes	No
innodb_replication_delay	integer 0 ... 4294967295	No	No
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	No
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_strict_mode	boolean on | off	No	No
innodb_thread_concurrency	integer 0 ... 1000	No	No
innodb_thread_sleep_delay	integer 0 ... 1000000	No	No
innodb_write_io_threads	integer 1 ... 64	Yes	No
interactive_timeout	integer 1 ... 31536000	No	No
join_buffer_size	integer 128 ... 9223372036854775807	No	No
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. If the log_queries_not_using_indexes flag is also enabled, you may see queries with less than the time specified here.	No	No
lower_case_table_names	integer 0 or 1 In most cases, use the default value of 0, 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. This flag is not supported for MySQL 8 for Cloud SQL.	Yes	No
mandatory_roles	string role name default: empty string Note: The flag is supported in MySQL 8.0 for Cloud SQL.	No	No
max_allowed_packet	integer 16384 ... 1073741824 This value must be a multiple of 1024, if sql_mode=TRADITIONAL or sql_mode=STRICT_ALL_TABLES.	No	No
max_binlog_size	integer 4096 ... 1073741824	No	No
max_connections	integer 25 ... 100000	No	No
max_digest_length	integer 0 ... 1048576	Yes	No
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	No
max_sp_recursion_depth	integer 0 ... 255	No	No
max_user_connections	integer 0 ... 4294967295	No	No
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	No
optimizer_trace_offset	integer -9223372036854775808 ... 9223372036854775807	No	No
parser_max_mem_size	integer 10000000 ... 9223372036854775807	No	No
password_history	integer 0-4294967295 default: 0 Note: The flag is supported in MySQL 8.0 for Cloud SQL.	No	No
password_require_current	boolean on | off default: off Note: The flag is supported in MySQL 8.0 for Cloud SQL.	No	No
password_reuse_interval	integer 0-4294967295 default: 0 Note: The flag is supported in MySQL 8.0 for Cloud SQL.	No	No
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	No
performance_schema_digests_size	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_events_stages_history_long_size	integer -1 ... 1048576	Yes	No
performance_schema_events_stages_history_size	integer -1 ... 1024 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_events_statements_history_long_size	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_events_statements_history_size	integer -1 ... 1024 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_events_transactions_history_long_size	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_events_transactions_history_size	integer -1 ... 1024 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_events_waits_history_long_size	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_events_waits_history_size	integer -1 ... 1024 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_hosts_size	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_cond_classes	integer 0 ... 256 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_cond_instances	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_digest_length	integer 0 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_file_classes	integer 0 ... 256 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_file_handles	integer 0 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_file_instances	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_index_stat	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_memory_classes	integer 0 ... 1024 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_metadata_locks	integer -1 ... 104857600 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_mutex_classes	integer 0 ... 256 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_mutex_instances	integer -1 ... 104857600 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_prepared_statements_instances	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_program_instances	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_rwlock_classes	integer 0 ... 256 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_rwlock_instances	integer -1 ... 104857600 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_socket_classes	integer 0 ... 256 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_socket_instances	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_sql_text_length	integer 0 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_stage_classes	integer 0 ... 256 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_statement_classes	integer 0 ... 256 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_statement_stack	integer 1 ... 256 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_table_handles	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_table_instances	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_table_lock_stat	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_thread_classes	integer 0 ... 256 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_max_thread_instances	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_session_connect_attrs_size	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_setup_actors_size	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_setup_objects_size	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
performance_schema_users_size	integer -1 ... 1048576 See Tips section for more information about performance_schema flags.	Yes	No
query_alloc_block_size	integer 1024 ... 4294967295	No	No
query_cache_limit	integer 0 ... 223338299392	No	No
query_cache_min_res_unit	integer 0 ... 9223372036854775807	No	No
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	No
range_alloc_block_size	integer 4096 ... 4294967295	No	No
range_optimizer_max_mem_size	integer 0 ... 9223372036854775807	No	No
read_buffer_size	integer 8192 ... 2147483647	No	No
read_only	boolean on | off Has no effect for replicas.	No	No
read_rnd_buffer_size	integer 1 ... 2147483647	No	No
show_compatibility_56	boolean on | off Supported only in MySQL 5.7.	No	No
skip_show_database	flag on | off	Yes	No
slave_parallel_type	enumeration For information about how to use this flag and its acceptable values, see Configuring parallel replication.	No	No
slave_parallel_workers	integer For information about how to use this flag and its acceptable values, see Configuring parallel replication.	No	No
slave_preserve_commit_order	boolean For information about how to use this flag and its acceptable values, see Configuring parallel replication.	No	No
slave_pending_jobs_size_max	integer For information about how to use this flag and its acceptable values, see Configuring parallel replication.	No	No
slow_query_log	integer 0 | 1 See the Tips section for more information on slow query logs.	No	No
sort_buffer_size	integer 32768 ... 9223372036854775807	No	No
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 is not supported. If specifying a comma-separated list of modes using gcloud commands, use the --flags-file argument.	No	No
stored_program_cache	integer 16 ... 524288	No	No
sync_binlog	integer 0 ... 4294967295 Only supported in MySQL 5.7 and 8.0, and only on replica instances (not supported on legacy HA failover replicas). If a replica using this flag is promoted, the flag is removed. It is not supported on primary, standalone instances. 0 = Disables synchronization of the binary log to disk. 1 (defaullt) = Enables synchronization of the binary log to disk before transactions are committed. This is the safest setting but can have a negative impact on performance due to the increased number of disk writes. N = where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected. This setting can have a negative impact on performance due to the increased number of disk writes. A higher value improves performance, but with an increased risk of data loss. The binlog retention period on replica instances is automatically set to one day, unlike seven days on primary instances.	No	No
table_definition_cache	integer 400 ... 524288	No	No
table_open_cache	integer 1 ... 524288	No	No
table_open_cache_instances	integer 1 ... 64	Yes	No
thread_cache_size	integer 0 ... 16384	No	No
thread_stack	integer 131072 ... 9223372036854775807	Yes	No
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	No
transaction_isolation	enumeration READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE	Yes	No
transaction_prealloc_size	integer 1024 ... 131072	No	No
transaction_write_set_extraction	enumeration For information about how to use this flag and its acceptable values, see Configuring parallel replication.	No	No
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 Console. Note that Google Cloud's operations suite logging charges apply. To minimize instance storage cost, general and slow query logs on the instance disk will be rotated when the log file is older than 24 hours (and no changes have been made within that duration) or greater than 100MB in size. Old log files will be automatically deleted after the rotation.

If log_output is set to NONE, you will be unable 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.

innodb_file_per_table

For all MySQL versions 5.6 and higher, 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.

query_cache_size, query_cache_type

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

Flags managed by Cloud SQL

Cloud SQL adjusts certain system flags depending on the instance tier. You cannot adjust these values.

Cloud SQL Flag	Values and Notes
innodb_buffer_pool_instances	See the buffer pool instances section below for more information about this flag.
innodb_buffer_pool_size	See the buffer pool size section below for more information about this flag.

innodb_buffer_pool_instances

• 1 for f1-micro, g1-small and n1-standard-1.
• 2 for n1-standard-2.
• 4 for n1-standard-4 and n1-highmem-2.
• 8 for all other tiers.

innodb_buffer_pool_size

approximate sizes.

• ~ 1.5 GB for f1-micro, g1-small and n1-standard-1.
• ~ 4 GB for n1-standard-2.
• ~ 11 GB for n1-standard-4.
• ~ 22 GB for n1-standard-8.
• ~ 46 GB for n1-standard-16.
• ~ 92 GB for n1-standard-32.
• ~ 186 GB for n1-standard-64.
• ~ 10 GB for n1-highmem-2.
• ~ 20 GB for n1-highmem-4.
• ~ 40 GB for n1-highmem-8.
• ~ 80 GB for n1-highmem-16.
• ~ 160 GB for n1-highmem-32.
• ~ 320 GB for n1-highmem-64.

Troubleshooting

Click the links in the table for details:

For this problem...	The issue might be...	Try this...
Data with character set utf8mb4.	This character set is not supported.	Filter utf8mb4 strings out of your data.
Enabling a flag crashes the instance.	The max_connections flag value may be set too high.	Contact customer support to request a flag removal.
Can't add the performance_schema flag.	Instance size is too small.	Update to a larger instance.
Time zone doesn't automatically change.	Automated time zone change is not supported.	Time must be changed manually. Learn more.
Bad syntax for dict arg.	Complex parameter values require special treatment.	Learn more.

Data with character set utf8mb4

Failed to import data with character set utf8mb4.

The issue might be

The character set utf8mb4 is not supported even though the documentation has previously indicated it is.

Things to try

Filter utf8mb4 strings out of your data.

---

Enabling a flag crashes the instance

After enabling a flag the instances loop between panicking and crashing.

The issue might be

Setting the max_connections flag value too high causes this error.

Things to try

Contact customer support to request a flag removal followed by a hard drain. This forces the instance to restart on a different host with a fresh configuration without the undesired flag or setting.

---

Can't add the performance_schema flag

You cannot add the performance_schema flag because it isn't in the dropdown menu of supported flags.

The issue might be

The performance_schema and its variants (performance_schema_accounts_size, performance_schema_accounts_size, etc.) cannot be enabled on instances smaller than db-n1-standard-8 or db-n1-highmem-4.

Things to try

Make sure the performance_schema flag is enabled.

Edit the instance to upgrade to a larger size if you need to use this flag.

---

Time zone doesn't automatically change

The time zone didn't automatically change for daylight savings time.

The issue might be

Automated time zone changes are not supported in Cloud SQL and must be done manually, and not by string but by time zone offset value.

Things to try

Edit the instance to change the default_time_zone flag. Named areas are not supported. For example: Europe/LondonLondon is in the UTC time zone, which would be a supported value of +00:00 for the default_time_zone flag.

---

Bad syntax for dict arg

You see the error message Bad syntax for dict arg when trying to set a flag.

The issue might be

Complex parameter values, such as comma-separated lists, require special treatment when used with gcloud commands.

Things to try

Use the gcloud --flags-file parameter, which specifies a YAML or JSON file containing a --flag:value dictionary useful for complex flag values.

What's next

• Learn more about MySQL system variables.
• Learn more about Operational Guidelines.