This page contains a list of the most frequent issues you might run into when working with Cloud SQL instances and steps you can take to address them. Also review the Known issues, Troubleshooting, and Support page pages.
If your instance stops responding to connections or performance is degraded, make sure it conforms to the Operational Guidelines. If it does not conform to these guidelines, it is not covered by the Cloud SQL SLA.
Common connection issues
Verify that your application is closing connections properly
If you see errors containing "
Aborted connection nnnn to db:", it usually
indicates that your application is not stopping connections properly. Network
issues can also cause this error. The error does not mean that
there are problems with your Cloud SQL instance.
For examples of best practices for connection management, see Managing connections.
Verify that your certificates have not expired
If your instance is configured to use SSL, go to the Cloud SQL Instances page in the Cloud Console and open the instance. Open its Connections page and make sure that your server certificate is valid. If it has expired, you must add a new certificate and rotate to it. Learn more.
Verify that you are authorized to connect
If your connections are failing, check that you are authorized to connect:
- If you are having trouble connecting using an IP address, for example,
you are connecting from your on-premises environment with the mysql
client, then make sure that the IP address you are connecting from
is authorized to connect
to the Cloud SQL instance.
Connections to a Cloud SQL instance using a private IP address are automatically authorized for RFC 1918 address ranges. This way, all private clients can access the database without going through the proxy. Non-RFC 1918 address ranges must be configured as authorized networks.
Cloud SQL doesn't learn Non-RFC 1918 subnet routes from your VPC by default. You need to update the network peering to Cloud SQL to export any Non-RFC 1918 routes. For example:
gcloud compute networks peerings update cloudsql-mysql-googleapis-com --network=NETWORK --export-subnet-routes-with-public-ip --project=PROJECT
Here's your current IP address.
- Try the
gcloud sql connectcommand to connect to your instance. This command authorizes your IP address for a short time. You can run this command in an environment with Cloud SDK and mysql client installed. You can also run this command in Cloud Shell, which is available in the Google Cloud Console and has Cloud SDK and the mysql client pre-installed. Cloud Shell provides a Compute Engine instance that you can use to connect to Cloud SQL.
- Temporarily allow all IP addresses to connect to an instance. For IPv4
0.0.0.0/0(for IPv6, authorize
Verify how you connect
If you get an error message like:
ERROR 1045 (28000): Access denied for user 'root'@'22.214.171.124' (using password: NO)
when you connect, verify that you are providing a password.
If you get an error message like:
ERROR 1045 (28000): Access denied for user 'root'@'126.96.36.199' (using password: YES)
when you connect, verify that you are using the correct password and that you are connecting over SSL if the instance requires it.
Understanding connection limits
There are no QPS limits for Cloud SQL instances. However, there are connection, size, and App Engine specific limits in place. See Quotas and Limits.
Database connections consume resources on the server and the connecting application. Always use good connection management practices to minimize your application's footprint and reduce the likelihood of exceeding Cloud SQL connection limits. For more information, see Managing database connections.
Show connections, processes and threads
If you get the "too many connections" error message, or want to find out what is
happening on an instance, you can show the number of connections and threads
From a MySQL client, run:
mysql> SHOW PROCESSLIST;
You get output similar to the following:
+----+-----------+--------------+-----------+---------+------+-------+----------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------+--------------+-----------+---------+------+-------+----------------------+ | 3 | user-name | client-IP | NULL | Query | 0 | NULL | SHOW processlist | | 5 | user-name | client-IP | guestbook | Sleep | 1 | | SELECT * from titles | | 17 | user-name | client-IP | employees | Query | 0 | NULL | SHOW processlist | +----+-----------+--------------+-----------+---------+------+-------+----------------------+ 3 rows in set (0.09 sec)
For information about how to interpret the columns returned from
PROCESSLIST, see the MySQL reference.
To get a thread count, you can use:
mysql> SHOW STATUS WHERE Variable_name = 'Threads_connected';
You get output similar to the following:
+-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_connected | 7 | +-------------------+-------+ 1 row in set (0.08 sec)
Connections that show an IP address, such as
188.8.131.52, are connecting using IP.
cloudsqlproxy~184.108.40.206 are using the Cloud SQL Proxy, or else they
originated from App Engine. Connections from
localhost may be
used by some internal Cloud SQL processes.
Connections timeout (from Compute Engine)
Connections with a Compute Engine instance time out after 10 minutes of inactivity, which can affect long-lived unused connections between your Compute Engine instance and your Cloud SQL instance. For more information, see Networking and Firewalls in the Compute Engine documentation.
To keep long-lived unused connections alive, you can set the TCP keepalive. The following commands set the TCP keepalive value to one minute and make the configuration permanent across instance reboots.
Display the current tcp_keepalive_time value.
Set tcp_keepalive_time to 60 seconds and make it permanent across reboots.
echo 'net.ipv4.tcp_keepalive_time = 60' | sudo tee -a /etc/sysctl.conf
Apply the change.
sudo /sbin/sysctl --load=/etc/sysctl.conf
Display the tcp_keepalive_time value to verify the change was applied.
Connecting with IPv6
If you get either of the error messages
Can't connect to MySQL server on '2001:1234::4321' (10051) Can't connect to MySQL server on '2001:1234::4321' (101)
when you connect it is likely that you are attempting to connect to the IPv6 address of your instance but do not have IPv6 available on your workstation. You can verify whether IPv6 is functional on your workstation by going to ipv6.google.com. If it does not load then you do not have IPv6 available. Connect to the IPv4 address or your Cloud SQL instance instead. You may need to add an IPv4 address to your instance first.
Occasional connection failures (Legacy HA)
When Cloud SQL restarts an instance due to maintenance events, connections might be routed to the failover replica. When connecting to the failover replica:
- Read requests from clients using unencrypted connections succeed as normal. However, write requests fail and return an error message, such as 'Error 1290: The MySQL server is running with the --read-only option so it cannot execute this statement.'
- Read and write requests from clients using encrypted connections fail and return an error message, such as 'x509: certificate is valid for master-instance, not failover-instance.'
After the event is over, Cloud SQL resets the connection. Retry the connection. We recommend that you design your applications to handle occasional connection failures by implementing an error handling strategy like exponential backoff. See Application implementation for more information.
Additional connectivity troubleshooting
For other connection issues, see the Connectivity section in the troubleshooting page.
For the best performance for backups, keep the number of tables to a reasonable number.
For other backups issues, see the Backups section in the troubleshooting page.
Import and export
Imports and exports in Cloud SQL are the
same as using the
mysqldump utility, except that with the Cloud SQL
import/export feature, you transfer data using a Cloud Storage bucket.
Imports and exports into Cloud SQL using the import functionality (with a Cloud Storage bucket) can take a long time to complete, depending on the size of the database. This can have the following impacts:
- You cannot stop a long-running Cloud SQL instance operation.
- You can perform only one import or export operation at a time for each instance.
You can decrease the amount of time it takes to complete each operation by using the Cloud SQL import or export functionality with smaller batches of data.
For exports, you can use serverless export to minimize the impact on database performance and allow other operations to run on your instance while an export is running.
Other points to keep in mind when importing:
- If your import is crashing, it could be due to an out-of-memory (OOM) error.
If this is the case, you can try using MySQL commands directly to add the
--extended-insert=FALSE --complete-insertparameters. These parameters reduce the speed of your import, but also reduce the amount of memory the import requires.
For other import and export issues, see the Import and export section in the troubleshooting page.
Disk spaceIf your instance reaches the maximum storage amount allowed, writes to the database fail. If you delete data, for example, by dropping a table, the space freed is not reflected in the reported Storage Used of the instance. See the FAQ How can I reclaim the space from a dropped table? for an explanation of this behavior.
Reaching the maximum storage limit can also cause the instance to get stuck in restart.
Avoid data corruption
Avoid generated columns
Due to an issue in MySQL, using generated columns might result in data corruption. For more information, see MySQL bug #82736.
When Cloud SQL shuts down an instance (e.g, for maintenance), no new connections are sent to the instance and existing connections are ended. The amount of time mysqld has to shutdown is capped to 1 minute. If the shutdown does not complete in that time, the mysqld process is forcefully stopped. This can result in disk writes being aborted mid-way through.
InnoDB is the only supported storage engine for MySQL instances because it is more resistant to table corruption than other MySQL storage engines, such as MyISAM.
By default, Cloud SQL database tables are created using the InnoDB storage
engine. If your
CREATE TABLE syntax includes an
option specifying a storage engine other than InnoDB, for example
ENGINE = MyISAM, the table is not created and you see error messages
like the following example:
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).
You can avoid this error by removing the
ENGINE = MyISAM option from the
CREATE TABLE command. Doing so creates the table with the InnoDB storage
Changes to system tables
MySQL system tables use the MyISAM storage engine, including all tables in the
mysql database, for example
mysql.db. These tables are
vulnerable to unclean shutdowns; issue the
FLUSH CHANGES command
after making changes to these tables. If MyISAM corruption
CHECK TABLE and
REPAIR TABLE can get you back to good state
(but not save data).
Global Transaction Identifiers (GTID)
All MySQL instances have GTID enabled automatically. Having GTID enabled protects against data loss during replica creation and failover, and makes replication more robust. However, GTID comes with some limitations imposed by MySQL, as documented in the MySQL manual. The following transactionally unsafe operations cannot be used with a GTID-enabled MySQL server:
CREATE TABLE ... SELECTstatements;
CREATE TEMPORARY TABLEstatements inside transactions;
- Transactions or statements that affect both transactional and non-transactional tables.
If you use a transactionally unsafe transaction, you see an error message like the following example:
Exception: SQLSTATE[HY000]: General error: 1786 CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.
Working with triggers and stored functions
There are various reasons why Cloud SQL may suspend an instance, including:
For example, if the credit card for the project's billing account has expired, the instance may be suspended. You can check the billing information for a project by going to the Google Cloud Console billing page, selecting the project, and viewing the billing account information used for the project. After you resolve the billing issue, the instance returns to runnable status within a few hours.
KMS key issues
For example, if the KMS key version used to encrypt the user data in the Cloud SQL instance is not present, or if it has been disabled or destroyed. See Using customer-managed encryption keys (CMEK).
For example, if an instance is stuck in a crash loop (it crashes while starting or just after starting), Cloud SQL may suspend it.
While an instance is suspended, you can continue to view information about it or you can delete it, if billing issues triggered the suspension.
Cloud SQL users with Platinum, Gold, or Silver support packages can contact our support team directly about suspended instances. All users can use the earlier guidance along with the google-cloud-sql forum.
Enable query logs
To tune the performance of your queries, you can configure
Cloud SQL to log slow queries by
adding the database flags
--slow_query_log=on to your instance.
This makes the log output available using the
Logs Viewer in the Google Cloud Console.
Note that Google Cloud's operations suite logging charges apply.
Do not set log_output to
TABLE. Doing so can cause connection issues as
Tips for working with flags.
Enable lock monitoring
InnoDB monitors provide information about the InnoDB storage engine's internal state, which you can use in performance tuning.
Access the instance using MySQL Client and obtain on-demand monitor output:
SHOW ENGINE INNODB STATUS\G
For explanations of the sections in the monitor output, see InnoDB Standard Monitor and Lock Monitor Output.
You can enable InnoDB monitors so that output is generated periodically to a file or a table, with performance degradation. For more information, see Enabling InnoDB Monitors.
Use performance schema
The MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. The most accessible way to consume the stats generated in performance_schema is through MySQL Workbench Performance Reports functionality.
Keep a reasonable number of database tables
Database tables consume system resources. A large number can affect instance performance and availability, and cause the instance to lose its SLA coverage. Learn more.
General performance tips
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.
For slow database selects, consider the following:
- Caching is important for read performance. Compare the size of your dataset to the size of RAM of your instance. Ideally, the entire dataset fits within 70% of the instance's RAM, in which case queries are not constrained to IO performance. If not, consider increasing the size of your instance's RAM.
- If your workload consists of CPU intensive queries (sorting, regular expressions, other complex functions), your instance might be throttled; increase the vCPUs.
- Check the location of the reader and database - latency affects read performance even more than write performance.
- Investigate non-Cloud SQL specific performance improvements, such as adding appropriate indexing, reducing data scanned, and avoiding extra round trips.
If you observe poor performance executing queries, use
EXPLAIN. EXPLAIN is a statement you add to
other statements, like SELECT, and it returns information about how MySQL
executes the statement. It works with SELECT, DELETE, INSERT, REPLACE, and
UPDATE. For example,
EXPLAIN SELECT * FROM myTable;.
EXPLAIN to identify where you can:
Add indexes to tables to improve query performance. For example, make sure every field that you use as a JOIN key has an index on both tables.
ORDER BYoperations. If
EXPLAINshows "Using temporary; Using filesort" in the Extra column of the output, then intermediate results are stored in a file that is then sorted, which usually results in poor performance. In this case, take one of the following steps:
Additional Cloud SQL instance troubleshooting
For other Cloud SQL instance issues, see the Managing instances section in the troubleshooting page.
For specific API error messages, see the Error messages reference page.
Troubleshooting customer-managed encryption keys (CMEK)
Cloud SQL administrator operations, such as create, clone, or update, might fail due to Cloud KMS errors, and missing roles or permissions. Common reasons for failure include a missing Cloud KMS key version, a disabled or destroyed Cloud KMS key version, insufficient IAM permissions to access the Cloud KMS key version, or the Cloud KMS key version is in a different region than the Cloud SQL instance. Use the following troubleshooting table to diagnose and resolve common problems.
Customer-managed encryption keys troubleshooting table
|For this error...||The issue might be...||Try this...|
|Per-product, per-project service account not found||The service account name is incorrect.||Make sure you created a service account for the correct user project.
|Cannot grant access to the service account||The user account does not have permission to grant access to this key version.||Add the Organization Administrator role on your user or service account.
|Cloud KMS key version is destroyed||The key version is destroyed.||If the key version is destroyed, you cannot use it to encrypt or decrypt data.|
|Cloud KMS key version is disabled||The key version is disabled.||Re-enable the Cloud KMS key version.
|Insufficient permission to use the Cloud KMS key||The
If the role is already on your account, see Creating a key to learn how to create a new key version. See note.
|Cloud KMS key is not found||The key version does not exist.||Create a new key version. See Creating a key. See note.|
|Cloud SQL instance and Cloud KMS key version are in different regions||The Cloud KMS key version and Cloud SQL instance must be in the same region. It does not work if the Cloud KMS key version is in a global region or multi-region.||Create a key version in the same region where you want to create instances. See Creating a key. See note.|