Configuring database flags

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 SQL Server parameters, adjusting options, and configuring and tuning an instance.

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, and the instance is restarted, the replica is also restarted 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.
  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 1204, remote access, and remote query timeout flags, you can use the following command:

gcloud sql instances patch [INSTANCE_NAME] --database-flags  \
    "1204"=on,"remote access"=on,"remote query timeout (s)"=300

REST API 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://sqladmin.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:

You should receive a JSON response similar to the following:

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.
  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 API 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://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

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

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Viewing current values of database flags

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.
  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 API 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://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

In the output, look for the databaseFlags field.

Supported flags

Flags not mentioned below are not supported.

Cloud SQL Flag Type
Acceptable Values and Notes
Restart
Required?
1204 (trace flag) boolean
on | off
No
1222 (trace flag) boolean
on | off
No
1224 (trace flag) boolean
on | off
No
2528 (trace flag) boolean
on | off
No
3205 (trace flag) boolean
on | off
No
3226 (trace flag) boolean
on | off
No
3625 (trace flag) boolean
on | off
Yes
4199 (trace flag) boolean
on | off
No
4616 (trace flag) boolean
on | off
No
7806 (trace flag) boolean
on | off
Yes
access check cache bucket count integer
0 ... 65536
No
access check cache quota integer
0 ... 2147483647
No
affinity mask integer
2147483648 ... 2147483647
No
agent xps boolean
on | off
No
automatic soft-numa disabled boolean
on | off
Yes
cost threshold for parallelism integer
0 ... 32767
No
contained database authentication boolean
on | off
No
cross db ownership chaining boolean
on | off
No
cursor threshold integer
-1 ... 2147483647
No
default full-text language integer
0 ... 2147483647
No
default language integer
0 ... 2147483647
No
default trace enabled boolean
on | off
No
disallow results from triggers boolean
on | off
No
external scripts enabled boolean
on | off
Yes
ft crawl bandwidth (max) integer
0 ... 32767
No
ft crawl bandwidth (min) integer
0 ... 32767
No
ft notify bandwidth (max) integer
0 ... 32767
No
ft notify bandwidth (min) integer
0 ... 32767
No
fill factor (%) integer
0 ... 100
No
index create memory (kb) integer
704 ... 2147483647
No
locks integer
5000 ... 2147483647
Yes
max server memory (mb) integer
128 ... 2147483647
No
max text repl size (b) integer
-1 ... 2147483647
No
max worker threads integer
128 ... 65535
No
nested triggers boolean
on | off
No
optimize for ad hoc workloads boolean
on | off
No
ph timeout (s) integer
1 ... 3600
No
query governor cost limit integer
0 ... 2147483647
No
query wait (s) integer
-1 ... 2147483647
No
recovery interval (min) integer
0 ... 32767
No
remote access boolean
on | off
No
remote login timeout (s) integer
0 ... 2147483647
No
remote query timeout (s) integer
0 ... 2147483647
No
transform noise words boolean
on | off
No
two digit year cutoff integer
1753 ... 9999
No
user connections integer
0, 10 ... 32767
Yes
user options integer
0 ... 32767
No

What's next