Debugging connection issues

Introduction

Generally, connection issues fall into one of the following three areas:

  • Connecting - are you able to reach your instance over the network?
  • Authorizing - are you authorized to connect to the instance?
  • Authenticating - does the database accept your database credentials?

Each of those can be further broken down into different paths for investigation. The following section includes examples of questions you can ask yourself to help further narrow down the issue:

Connection issues checklist

Error messages

For specific API error messages, see the Error messages reference page.

Additional connectivity troubleshooting

For other issues, see the Connectivity section in the troubleshooting page.

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. You are also encouraged to run tcpdump to inspect the packets to track down the source of the problem.

For examples of best practices for connection management, see Managing database 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 Google Cloud console and open the instance. Open its Connections page, select the Security tab and make sure that your server certificate is valid. If it has expired, you must add a new certificate and rotate to it.

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 Cloud SQL Auth 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_ID

  • Here's your current IP address.

  • Try the gcloud sql connect command to connect to your instance. This command authorizes your IP address for a short time. You can run this command in an environment with gcloud CLI and mysql client installed. You can also run this command in Cloud Shell, which is available in the Google Cloud console and has gcloud CLI 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 authorize 0.0.0.0/0 (for IPv6, authorize ::/0.

Verify how you connect

If you get an error message like:

ERROR 1045 (28000): Access denied for user 'root'@'1.2.3.4' (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'@'1.2.3.4' (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.

Determine how connections are being initiated

You can see information about your current connections by connecting to your database and running the following command:

SHOW PROCESSLIST;

Connections that show an IP address, such as 1.2.3.4, are connecting using IP. Connections with cloudsqlproxy~1.2.3.4 are using the Cloud SQL Auth Proxy, or else they originated from App Engine. Connections from localhost may be used by some internal Cloud SQL processes.

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 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 with SHOW PROCESSLIST.

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 timeout (from Compute Engine)

Connections with a Compute Engine instance timeout 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.

cat /proc/sys/net/ipv4/tcp_keepalive_time

Set tcp_keepalive_time to 60 seconds and make it permanent across reboots.

echo