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 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

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
Open the instance and click Edit.
Scroll down to the Flags section.
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.
Click Save to save your changes.
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 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)

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": "user@example.com",
  "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

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
Open the instance and click Edit.
Open the Database flags section.
Click the X next to all of the flags shown.
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)

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)

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": "user@example.com",
  "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

Determining what database flags have been set for an instance

To see what flags have been set for a Cloud SQL instance:

Console

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
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)

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)

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

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`, `10` ... `32767`	Yes	No
user options	`integer` `0` ... `32767`	No	No

What's next

Learn more about SQL Server configuration options.