Troubleshooting Cloud SQL

Run the gcloud sql operations list command to list all operations for the given Cloud SQL instance.

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.

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.

If you really need to cancel the operation, you can ask customer support to force restart the instance.

Create the database users before restoring the SQL dump.

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.

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

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.

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

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

Connect with the default user, then change users.

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 
  

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.

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

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.

• 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 SLAVE STATUS , and check for the following fields in the output:
  • Slave_IO_Running
  • Slave_SQL_Running
  • Last_IO_Error
  • Last_SQL_Error

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

Create a new dump file using the correct flag settings, and configure the external replica using that file

1. Connect to your mysql client through a Compute Engine instance .
2. Run mysqldump and use the --master-data=1 and --flush-privileges flags.

   Important: Do not include the --set-gtid-purged=OFF flag .

   Learn more .

3. Ensure that the dump file just created contains the SET @@GLOBAL.GTID_PURGED='...' line.
4. Upload the dump file to a Cloud Storage bucket and configure the replica using the dump file .

Close unused operations. Check the CPU and memory usage of your Cloud SQL instance to make sure there are plenty of resources available. The best way to ensure maximum resources for the import is to restart the instance before beginning the operation.

A restart:

• Closes all connections.
• Ends any tasks that may be consuming resources.

Create the database users before importing.

Things to try:

Add the following line at the start of the dump file:

SET  
 FOREIGN_KEY_CHECKS 
 = 
 0 
 ; 
  

Additionally, add this line at the end of the dump file:

SET  
 FOREIGN_KEY_CHECKS 
 = 
 1 
 ; 
  

These settings deactivate data integrity checks while the import operation is in progress, and reactivate them after the data is loaded. This doesn't affect the integrity of the data on the database, because the data was already validated during the creation of the dump file.

EXEC  
sp_serveroption  
@server = 
 ' LINKED_SERVER_NAME 
' 
,  
@optname = 
 'data access' 
,  
@optvalue = 
 'TRUE' 

Replace LINKED_SERVER_NAME with the name of the linked server.

EXEC  
master.dbo.sp_addlinkedserver  
@server  
 = 
  
N ' LINKED_SERVER_NAME 
' 
,  
@srvproduct = 
  
N '' 
,  
@provider = 
  
N 'SQLNCLI' 
,  
@datasrc = 
  
N ' TARGET_SERVER_ID 
' 
,  
@provstr = 
  
N 'Encrypt=yes;TrustServerCertificate=yes;User ID= USER_ID 
' 

Replace the following:

• LINKED_SERVER_NAME with the name of the linked server.
• TARGET_SERVER_ID with the name of the target server, or the IP address and port number for the target server.
• USER_ID with the user logging in.

For example, a user was deleted but you can't find out who did it. The logs show the operation started but don't provide any more information. You must enable audit logging for detailed and personal identifying information (PII) like this to be logged.

Connect to the database and run these commands for details on each type:

 SHOW 
  
 VARIABLES 
  
 LIKE 
  
 'innodb_log_file%' 
 ; 
 SELECT 
  
 ROUND 
 ( 
 SUM 
 ( 
 LENGTH 
 ( 
 argument 
 ) 
 / 
 POW 
 ( 
 1024 
 , 
 2 
 ), 
 2 
 ) 
 AS 
  
 GB 
  
 from 
  
 mysql 
 . 
 general_log 
 ; 
 SHOW 
  
 BINARY 
  
 LOGS 
 ; 
  

To download the logs as JSON:

gcloud  
logging  
 read 
  
 \ 
 "resource.type=cloudsql_database \ 
 AND logName=projects/ PROJECT_ID 
\ 
 /logs/cloudsql.googleapis.com%2F LOG_NAME 
" 
  
 \ 
--format  
json  
 \ 
--project = 
 PROJECT_ID 
  
 \ 
--freshness = 
 "1d" 
  
 \ 
>  
downloaded-log.json  

To download the logs as TEXT:

gcloud  
logging  
 read 
  
 \ 
 "resource.type=cloudsql_database \ 
 AND logName=projects/ PROJECT_ID 
\ 
 /logs/cloudsql.googleapis.com%2F LOG_NAME 
" 
  
 \ 
--format  
json  
 \ 
--project = 
 PROJECT_ID 
  
 \ 
--freshness = 
 "1d" 
 | 
  
jq  
-rnc  
--stream  
 'fromstream(1|truncate_stream(inputs)) \ 
 | .textPayload' 
  
 \ 
--order = 
asc
>  
downloaded-log.txt  

1. From a terminal, connect to your database:

   gcloud  
   sql  
   connect  
    INSTANCE_NAME 
     
   

2. Run this command to create the extension:

   CREATE  
   EXTENSION  
   pgaudit ; 
     
   

3. Exit the database, and from a terminal run the following command:

   gcloud  
   sql  
   instances  
   patch  
    INSTANCE_NAME 
     
    \ 
   --database-flags = 
   cloudsql.enable_pgaudit = 
   on,pgaudit.log = 
   all  
   

You can find out the size of the general logs by connecting to the database and running this query:

Things to try:

• You can check the storage occupied by binary logs using the following command in the MySQL command line interface: SHOW BINARY LOGS;
• Temporary tables may also be occupying a significant amount of storage space. To check the temporary space usage, use the following command: SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G .
• The following command lets you check the redo log size: SHOW VARIABLES LIKE 'innodb_log_file%';
• You can check the size of general_log , if it is enabled, with the help of this command: SELECT ROUND(SUM(LENGTH(argument)/POW(1024,2)),2) AS GB from mysql.general_log;
• If needed, you can truncate your log tables by using the API. For more information, see the instances.truncateLog reference page .
• Learn more about setting and configuring slow-query logs.

Connect to the database and execute this query:

SHOW PROCESSLIST .

The first item in the list may be the one holding the lock, which the subsequent items are waiting on.

The SHOW INNODB STATUS query can also be helpful.

SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G

Connect to the database and execute the following query:

SELECT TABLE_SCHEMA, TABLE_NAME, sum(DATA_LENGTH+INDEX_LENGTH)/pow(1024,2) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('PERFORMANCE_SCHEMA','INFORMATION_SCHEMA','SYS','MYSQL') GROUP BY TABLE_SCHEMA, TABLE_NAME;

Shard the instance if possible. Using many smaller Cloud SQL instances is better than one large instance.

SELECT datname, usename, application_name as appname, client_addr, state, now() - backend_start as conn_age, now() - xact_start as xact_age, now() - query_start as query_age, now() - state_change as last_activity_age, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> 'idle' ORDER BY 8 DESC LIMIT 20;

SELECT name, setting, unit FROM pg_settings WHERE name = ' FIELD_NAME ' .

SHOW SETTING_NAME ;

Run SHOW ALL; to see all settings.

Run the following commands:

1.   
    GRANT  
   pg_signal_backend  
   TO  
    USERNAME 
    ; 
    
     
   

2. Find the process ID of the blocked or stuck process:

     
    SELECT  
   pid,  
   usename,  
   state,  
   query  
   FROM  
   pg_stat_activity ; 
    
     
   

3. Stop a running or idle process using these commands:

     
    SELECT  
   pg_cancel_backend ( 
   pid ) 
     
   FROM  
   pg_stat_activity  
   WHERE  
    usename 
     
    = 
     
    ' USERNAME 
   ' 
    ; 
     
    
     
   

     
    SELECT  
   pg_terminate_backend ( 
   pid ) 
     
   FROM  
   pg_stat_activity  
   WHERE  
    usename 
     
    = 
     
    ' USERNAME 
   ' 
    ; 
     
    
     
   

The autovacuum job might be blocked, or might not be reclaiming the transaction IDs fast enough to keep up with the workload.

In order to avoid any outages due to transaction wraparound problem, you can review these self-servicing tips for dealing with TXID wraparound.

For general tuning advice, see Optimizing, monitoring, and troubleshooting vacuum operations in PostgreSQL .

Restart deletes the temporary files but not reduce the storage. Only customer support can reset the instance size.

Look in the logs around the time of the deletion and see if there's a rogue script running from a dashboard or another automated process.

Some possible explanations include:

• Another operation is in progress. Cloud SQL operations do not run concurrently. Wait for the other operation to complete.
• The INSTANCE_RISKY_FLAG_CONFIG warning is triggered whenever at least one beta flag is being used. Remove the risky flag settings and restart the instance

Unfortunately, you can't shrink the ibtmp1 file by any method other than restarting the service.

One mitigation option is to create the temporary table with ROW_FORMAT=COMPRESSED , so it is stored in file-per-table tablespaces in the temporary file directory. However, the downside is performance costs associated with creating and removing a file-per-table tablespace for each temporary table.

If your instance runs out of storage, and the automatic storage increase capability isn't enabled, your instance goes offline. To avoid this issue, you can edit the instance to enable automatic storage increase.

By having connections that last less than 60 seconds, most unclean shutdowns can be avoided, including connections from the database command prompt. If you keep these connections open for hours or days, shutdowns can be unclean.

Find out which objects are dependent on the user, then drop or reassign those objects to a different user.

Refer to general performance tips in particular.

For slow database inserts, updates, or deletes, consider the following actions:

• Check the locations of the writer and database; sending data a long distance introduces latency.
• Check the location of the reader and database; latency affects read performance even more than write performance

To reduce the latency the recommendation is to locate both the source and destination resources in the same region.

There are other factors beside your workload that can impact memory usage, such as the number of active connections and internal overhead processes. These aren't always reflected in the monitoring charts.

Ensure that the instance has enough overhead to account for your workload plus some additional overhead.

To preserve your data, export it to Cloud Storage before you delete an instance .

The Cloud SQL Admin role includes the permission to delete the instance. To prevent accidental deletion, grant this role only as needed.

There are other ways to accomplish the goal by creating a new instance.

• You can clone the instance you want to rename and set a new name for the cloned instance. This allows you to create the new instance without having to import data manually. Just as when creating a new instance, the cloned instance has a new IP address.
• You can export data from your instance into a Cloud Storage bucket, create a new instance with the new name you want, and then import the data into the new instance.

In both cases, you can delete your old instance after the operation is done. We recommend going with the cloning route since it has no impact on performance and doesn't require you to redo any instance configuration settings such as flags, machine type, storage size and memory.

1. Check the endpoint's status.

   gcloud

   To check the status, use the
   gcloud compute forwarding-rules describe command.

   gcloud  
   compute  
   forwarding-rules  
   describe  
    ENDPOINT_NAME 
     
    \ 
   --project = 
    PROJECT_ID 
     
    \ 
   --region = 
    REGION_NAME 
     
    \ 
    | 
     
   grep  
   pscConnectionStatus

   Make the following replacements:

   • ENDPOINT_NAME : the name of the endpoint
   • PROJECT_ID : the ID or project number of the Google Cloud project that contains the endpoint
   • REGION_NAME : the region name for the endpoint

   REST

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

   • PROJECT_ID : the ID or project number of the Google Cloud project that contains the Private Service Connect endpoint
   • REGION_NAME : the name of the region
   • ENDPOINT_NAME : the name of the endpoint

   HTTP method and URL:

   GET https://compute.googleapis.com/compute/v1/projects/ PROJECT_ID 
   /regions/ REGION_NAME 
   /forwardingRules/ ENDPOINT_NAME 
   

   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://compute.googleapis.com/compute/v1/projects/ PROJECT_ID 
   /regions/ REGION_NAME 
   /forwardingRules/ ENDPOINT_NAME 
   "

   PowerShell (Windows)

   Execute the following command:

   $cred = gcloud auth print-access-token
   $headers = @{ "Authorization" = "Bearer $cred" }
   
   Invoke-WebRequest `
   -Method GET `
   -Headers $headers `
   -Uri "https://compute.googleapis.com/compute/v1/projects/ PROJECT_ID 
   /regions/ REGION_NAME 
   /forwardingRules/ ENDPOINT_NAME 
   " | Select-Object -Expand Content

   You should receive a JSON response similar to the following:

   {
     "kind": "compute#forwardingRule",
     "id": " ENDPOINT_ID 
   ",
     "creationTimestamp": "2024-05-09T12:03:21.383-07:00",
     "name": " ENDPOINT_NAME 
   ",
     "region": "https://www.googleapis.com/compute/v1/projects/ PROJECT_ID 
   /regions/ REGION_NAME 
   ",
     "IPAddress": " IP_ADDRESS 
   ",
     "target": "https://www.googleapis.com/compute/v1/projects/ PROJECT_ID 
   /regions/ REGION_NAME 
   /serviceAttachments/ SERVICE_ATTACHMENT_NAME 
   ",
     "selfLink": "https://www.googleapis.com/compute/v1/projects/ PROJECT_ID 
   /regions/ REGION_NAME 
   /forwardingRules/ ENDPOINT_NAME 
   ",
     "network": "https://www.googleapis.com/compute/v1/projects/ PROJECT_ID 
   /global/networks/default",
     "serviceDirectoryRegistrations": [
       {
         "namespace": "goog-psc-default"
       }
     ],
     "networkTier": "PREMIUM",
     "labelFingerprint": " LABEL_FINGERPRINT_ID 
   ",
     "fingerprint": " FINGERPRINT_ID 
   ",
     "pscConnectionId": " CONNECTION_ID 
   ", "pscConnectionStatus": "ACCEPTED","allowPscGlobalAccess": true
   }

2. Verify that the status of the endpoint is ACCEPTED . If the status is PENDING , then the instance isn't allowing the Google Cloud project that contains the endpoint. Make sure that the network project in which the endpoint is created is allowed. For more information, see Edit an instance with Private Service Connect enabled .

If your external network is unable to accept connections from the Private Service Connect interface, then the connection policies on your network attachment may not be configured correctly.

Network attachments need to be configured to either accept all connections automatically, or configured manually with a list of accepted connections. For more information, see Connection policies .

Use the following command to verify the accepted connections in your network attachment:

  
gcloud  
compute  
network-attachments  
describe  
default  
--region = 
 REGION_ID 

First, check that the value of the max_connections flag is greater than or equal to the value on the primary.

If the max_connections flag is set appropriately, inspect the logs in Cloud Logging to find the actual error.

If the error is: set Service Networking service account as servicenetworking.serviceAgent role on consumer project , then disable and re-enable the Service Networking API . This action creates the service account necessary to continue with the process.

Restart the replica instance to reclaim the temporary memory space.

Edit the instance to enable automatic storage increase .

• Slow queries on the replica. Find and fix them.
• All tables must have a unique/primary key. Every update on such a table without a unique/primary key causes full table scans on th replica.
• Queries like DELETE ... WHERE field < 50000000 cause replication lag with row-based replication since a huge number of updates are piled up on the replica.

Some possible solutions include:

• Edit the instance to increase the size of the replica.
• Reduce the load on the database.
• Send read traffic to the read replica.
• Index the tables.
• Identify and fix slow write queries.
• Recreate the replica.

Recreate the replica after stopping all running queries.