Troubleshooting Cloud SQL

This page includes tips for troubleshooting Cloud SQL issues for supported database engines. Some of these tips apply only to specific database engines, while others are common to all.

For troubleshooting tips for specific database engines, see their individual pages:

Check if your question or problem has already been addressed on one of the following pages:

Topics in this page include:

Backup and recovery

Issue Troubleshooting
You can't see the current operation's status. The Google Cloud console reports only success or failure when the operation is done. It isn't designed to show warnings or other updates.

Run the gcloud sql operations list command to list all operations for the given Cloud SQL instance.
You want to find out who issued an on-demand backup operation. The user interface doesn't show the user who started an operation.

Look in the logs and filter by text to find the user. You may need to use audit logs for private information. Relevant log files include:

  • If Cloud Audit Logs is enabled and you have the required permissions to view them, cloudaudit.googleapis.com/activity may also be available.
After an instance is deleted, you can't take a backup of the instance.

If you delete an instance without taking a final backup of the data, then no data recovery is possible. However, if you restore the instance, then Cloud SQL also restores the backups. For more information on recovering a deleted instance, see Retain backups after instance deletion.

If you have done an export operation, create a new instance and then do an import operation to recreate the database. Exports are written to Cloud Storage and imports are read from there.
An automated backup is stuck for many hours and can't be canceled. Backups can take a long time depending on the database size.

If you really need to cancel the operation, you can ask customer support to force restart the instance.
A restore operation can fail when one or more users referenced in the SQL dump file don't exist. Before restoring a SQL dump, all the database users who own objects or were granted permissions on objects in the dumped database must exist in the target database. If they don't, the restore operation fails to recreate the objects with the original ownership or permissions.

Create the database users before restoring the SQL dump.
You want to increase the number of days that you can keep automatic backups from seven to 30 days, or longer. You can configure the number of automated backups to retain. Automated backups get pruned regularly based on the retention value configured. Unfortunately, this means that the currently visible backups are the only automated backups you can restore from.

To keep backups indefinitely, you can create an on-demand backup, as they are not deleted in the same way as automated backups. On-demand backups remain indefinitely. That is, they remain until they're deleted or the instance they belong to is deleted. Because that type of backup is not deleted automatically, it can affect billing.
An automated backup failed and you didn't receive an email notification. To have Cloud SQL notify you of the backup's status, configure a log-based alert.
An instance is repeatedly failing because it is cycling between the failure and backup restore states. Attempts to connect to and use the database following restore fail.
  • There could be too many open connections. Too many connections can result from errors that occur in the middle of a connection where there are no autovacuum settings to clean up dead connections.
  • Cycling can occur if any custom code is using retry logic that doesn't stop after a few failures.
  • There could be too much traffic. Use connection pooling and other best practices for connectivity.

Things to try:

  1. Verify that the database is set up for autovacuum.
  2. Check if there is any connection retry logic set up in custom code.
  3. Turn down traffic until the database recovers and then slowly turn traffic back up.
You find you are missing data when performing a backup/restore operation. Tables were created as unlogged. For example:

CREATE UNLOGGED TABLE ....

These tables are not included in a restore from a backup:

  • The contents of unlogged tables doesn't survive failover on an HA instance.
  • Unlogged tables don't survive postgres crashes.
  • Unlogged tables are not replicated to read replicas.
  • Unlogged tables are automatically wiped during backup restore.

The solution is to avoid using unlogged tables if you want to restore those tables through a backup. If you're restoring from a database that already has unlogged tables, then you can dump the database to a file, and reload the data after modifying the dumped file to ALTER TABLE to SET LOGGED on those tables.
Unable to delete an instance when you elect to take a final backup at instance deletion. When you delete an instance, you're required to confirm whether you want to take a final backup for your instance prior to deleting it. If you enabled final backup using the final-backup instance setting, then the selection you make when you delete your instance must match the final backup instance configuration you set when you enabled final backup for your instance. To mitigate this issue, do one of the following:
  • Set the final backup value to match the instance's existing backup configuration.
  • Leave the final backup field empty when you delete your instance. If you leave the field empty, then Cloud SQL takes the final backup configuration set in instance settings to take a final backup and define its retention.
To view your instance's final backup instance configuration, see View instance information.
Unable to create a replica instance after successfully creating a primary instance with the final backup setting. If you create a new instance with the final backup instance setting enabled, then you must update the final backup organization policy to apply the backup configurations to the primary instance only. Final backups aren't supported for replica instances.
For more information, see Cloud SQL organization policies.

Clone

Issue Troubleshooting
Cloning fails with constraints/sql.restrictAuthorizedNetworks error. The cloning operation is blocked by the Authorized Networks configuration. Authorized Networks are configured for public IP addresses in the Connectivity section of the Google Cloud console, and cloning is not permitted due to security considerations.

Remove all Authorized Networks entries from the Cloud SQL instance if you can. Otherwise, create a replica without any Authorized Networks entries.
Error message: Failed to create subnetwork. Couldn't find free blocks in allocated IP ranges. Please allocate new ranges for this service provider. Help Token: [help-token-id].

You're trying to use the Google Cloud console to clone an instance with a private IP address, but you didn't specify the allocated IP range that you want to use and the source instance isn't created with the specified range. As a result, the cloned instance is created in a random range.

Use gcloud to clone the instance and provide a value for the
--allocated-ip-range-name parameter. For more information, see Cloning an instance with a private IP.

Connect

Issue Troubleshooting
Aborted connection. The issue might be:
  • Networking instability.
  • No response to TCP keep-alive commands (either the client or the server isn't responsive, possibly overloaded)
  • The database engine connection lifetime was exceeded and the server ends the connection.

Applications must tolerate network failures and follow best practices such as connection pooling and retrying. Most connection poolers catch these errors where possible. Otherwise the application must either retry or fail gracefully.

For connection retry, we recommend the following methods:

  1. Exponential backoff. Increase the time interval between each retry, exponentially.
  2. Add randomized backoff also.

Combining these methods helps reduce throttling.
Error message: Login failed for user "" You might encounter this login error during Microsoft Entra ID authentication. To resolve this issue, make sure that a SQL Server login exists for this Microsoft Entra ID user.
Network connectivity issues with private IP instances You might experience some of the following issues during integration setup:
  • Slow operations to create Microsoft Entra ID logins
  • Unable to create Microsoft Entra ID logins
  • Unable to connect to the instance using Microsoft Entra ID authentication

For more information on how to help address these issues, see Troubleshooting Microsoft Entra ID integration.
FATAL: database 'user' does not exist. gcloud sql connect --user only works with the default postgres user.

Connect with the default user, then change users.
You want to find out who is connected. Log into the database and run this command: 

SELECT datname,
usename,
application_name as appname,
client_addr,
state,
now() - backend_start as conn_age,
now() - state_change as last_activity_age
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY 6 DESC
LIMIT 20

Create instances

Issue Troubleshooting
Error message: The zone or region does not have sufficient resources to handle the request at the moment.

The selected zone lacks capacity for the requested resources or the VM type at the time of the instance creation request. There might be simultaneous high operational demand in that specific regional location at the time of request.

To resolve this issue, retry creating the instance in another zone or retry creating the instance in the same zone that received the error at a different time of day.
Error message: Failed to create subnetwork. Couldn't find free blocks in allocated IP ranges. Please allocate new ranges for this service provider. There are no more available addresses in the allocated IP range. There can be several possible scenarios:
  • The size of the allocated IP range for the private service connection is smaller than /24.
  • The size of the allocated IP range for the private service connection is too small for the number of Cloud SQL instances.
  • The requirement on the size of allocated IP range will be larger if instances are created in multiple regions. See allocated range size

To resolve this issue, you can either expand the existing allocated IP range or allocate an additional IP range to the private service connection. For more information, see Allocate an IP address range.

If you used the --allocated-ip-range-name flag while creating the Cloud SQL instance, you may only expand the specified IP range.

If you're allocating a new range, take care that the allocation doesn't overlap with any existing allocations.

After creating a new IP range, update the vpc peering with the following command:

gcloud services vpc-peerings update \
--service=servicenetworking.googleapis.com \
--ranges=OLD_RESERVED_RANGE_NAME,NEW_RESERVED_RANGE_NAME \
--network=VPC_NETWORK \
--project=PROJECT_ID \
--force

If you're expanding an existing allocation, take care to increase only the allocation range and not decrease it. For example, if the original allocation was 10.0.10.0/24, then make the new allocation at least 10.0.10.0/23.

In general, if starting from a /24 allocation, decrementing the /mask by 1 for each condition (additional instance type group, additional region) is a good rule of thumb. For example, if trying to create both instance type groups on the same allocation, going from /24 to /23 is enough.

After expanding an existing IP range, update the vpc peering with following command:

gcloud services vpc-peerings update \
--service=servicenetworking.googleapis.com \
--ranges=RESERVED_RANGE_NAME \
--network=VPC_NETWORK \
--project=PROJECT_ID
Error message: Failed to create subnetwork. Router status is temporarily unavailable. Please try again later. Help Token: [token-ID]. Try to create the Cloud SQL instance again.
Error message: HTTPError 400: Invalid request: Incorrect Service Networking config for instance: PROJECT_ID:INSTANCE_NAME:SERVICE_NETWORKING_NOT_ENABLED.

Enable the Service Networking API using the following command and try to create the Cloud SQL instance again.

gcloud services enable servicenetworking.googleapis.com \
--project=PROJECT_ID
Error message: Failed to create subnetwork. Required 'compute.projects.get' permission for PROJECT_ID. When you create an instance using with a Private IP address, a service account is created just-in-time using the Service Networking API. If you have only recently enabled the Service Networking API, then the service account might not get created and the instance creation fails. In this case, you must wait for the service account to propagate throughout the system or manually add it with the required permissions.
Error message: More than 3 subject alternative names are not allowed. You're trying to use a custom SAN to add more than three DNS names to the server certificate of a Cloud SQL instance. You can't add more than three DNS names to the instance.
Error message: Subject alternative names %s is too long. The maximum length is 253 characters. Make sure that any DNS names that you want to add to the server certificate of a Cloud SQL instance don't have more than 253 characters.
Error message: Subject alternative name %s is invalid.

Verify that the DNS names that you want to add to the server certificate of a Cloud SQL instance meet the following criteria:

  • They don't have wildcard characters.
  • They don't have trailing dots.
  • They meet RFC 1034 specifications.

Export

Issue Troubleshooting
HTTP Error 409: Operation failed because another operation was already in progress. There is already a pending operation for your instance. Only one operation is allowed at a time. Try your request after the current operation is complete.
HTTP Error 403: The service account does not have the required permissions for the bucket. Ensure that the bucket exists and the service account for the Cloud SQL instance (which is performing the export) has the Storage Object Creator role (roles/storage.objectCreator) to allow export to the bucket. See IAM roles for Cloud Storage.
CSV export worked but SQL export failed. CSV and SQL formats do export differently. The SQL format exports the entire database, and likely takes longer to complete. The CSV format lets you define which elements of the database to include in the export.

Use CSV exports to export only what you need.
Export is taking too long. Cloud SQL does not support concurrent synchronous operations.

Use export offloading. At a high level, in export offloading, instead of issuing an export on the source instance, Cloud SQL spins up an offload instance to perform the export. Export offloading has several advantages, including increased performance on the source instance and the unblocking of administrative operations while the export is running. With export offloading, total latency can increase by the amount of time it takes to bring up the offload instance. Generally, for reasonably sized exports, latency is not significant. However, if your export is small enough, then you may notice the increase in latency.
Create Extension error. The dump file contains references to unsupported extension.

Edit the dump file to remove the references.
Error using pg_dumpall. Using the pg_dumpall utility with the --global flag requires the superuser role, but this role isn't supported in Cloud SQL. To prevent errors from occurring while performing export operations that include user names, also use the --no-role-passwords flag.
The export operation times out before exporting anything, and you see the error message Could not receive data from client: Connection reset by peer. If Cloud Storage does not receive any data within a certain time frame, typically around seven minutes, the connection resets. It's possible the initial export query is taking too long to run.

Do a manual export using the pg_dump tool.
You want exports to be automated. Cloud SQL does not provide a way to automate exports.

You could build your own automated export system using Google Cloud products such as Cloud Scheduler, Pub/Sub, and Cloud Run functions, similar to this article on automating backups.

External primary

Issue Troubleshooting
Lost connection to MySQL server during query when dumping table. The source may have become unavailable, or the dump contained packets too large.

Make sure the external primary is available to connect. You can also modify the values of the net_read_timeout and net_write_timeout flags on the source instance to stop the error. For more information on the allowable values for these flags, see Configure database flags.

To learn more about using mysqldump flags for managed import migration, see Allowed and default initial sync flags
Initial dump fails with timeout or lost connection errors (for example, Dump timeout or Lost connection to MySQL server). This error can occur if one or more DDL statements interact with the parallel dump process, causing the process to wait indefinitely.

Resolution: Don't execute DDL statements on the source database during the initial dump phase. Before you execute any DDL statements, restart the migration and wait for the initial dump phase to complete.
The initial data migration was successful, but no data is being replicated.

One possible root cause could be your source database has defined replication flags which result in some or all database changes not being replicated over.

Make sure the replication flags such as binlog-do-db, binlog-ignore-db, replicate-do-db or replicate-ignore-db are not set in a conflicting way.

Run the command show master status on the primary instance to see the current settings.
The initial data migration was successful but data replication stops working after a while.

Things to try:

  • Check the replication metrics for your replica instance in the Cloud Monitoring section of the Google Cloud console.
  • The errors from the MySQL IO thread or SQL thread can be found in Cloud Logging in the mysql.err log files.
  • The error can also be found when connecting to the replica instance. Run the command SHOW REPLICA STATUS, and check for the following fields in the output:
    • Replica_IO_Running
    • Replica_SQL_Running
    • Last_IO_Error
    • Last_SQL_Error

If you find the error Unknown database DATABASE_NAME on query. Error_code: MY-001049, then replication has failed due to a replicated SQL statement attempting to reference a database that wasn't selected for the migration. To recover replication, determine whether the error based on the message is due to DDL on one of the following objects:

  • An event or routine. You can run CALL mysql.skipReplicationError() on the replica, which will prevent the event or routine from being replicated and resume replication.
  • A foreign key constraint or view. After you determine on which database the object modified by the statement resides, you have two options for recovery. You can either remove the database from the selected databases that are being migrated, or you can run CALL mysql.skipReplicationError() on the replica to resume replication while skipping the constraint or propagation of the view to the replica.
mysqld check failed: data disk is full. The data disk of the replica instance is full.

Increase the disk size of the replica instance. You can either manually increase the disk size or enable auto storage increase.

External replica

Issue Troubleshooting