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, 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.
  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 ("=").

REST

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:

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

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:

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

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:

You should receive a JSON response similar to the following:

In the output, look for the databaseFlags field.

Supported flags

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

What's next