Debug connection issues

MySQL   |  PostgreSQL   |  SQL Server

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

• Connecting
• Private IP
• Have you enabled the Service Networking API for your project?
• Are you using a Shared VPC?
• Does your user or service account have the required IAM permissions to manage a private services access connection?
• Is private services access connection configured for your project?
• Did you allocate an IP address range for the private connection?
• Did your allocated IP address ranges contain at least a /24 space for every region where you are planning to create sqlserver instances?
• If you are specifying an allocated IP address range for your sqlserver instances, does the range contain at least a /24 space for every region where you are planning to create sqlserver instances in this range?
• Is the private connection created?
• If the private connection was changed, were the vpc-peerings updated?
• Do the VPC logs indicate any errors?
• Is your source machine's IP a non-RFC 1918 address?
• Public IP
• Is your source IP listed as an authorized network?
• Are SSL/TLS certificates required?
• Does your user or service account have the required IAM permissions to connect to a Cloud SQL instance?
• Authorizing
• Cloud SQL Auth Proxy
• Is the Cloud SQL Auth Proxy up to date?
• Is the Cloud SQL Auth Proxy running?
• Is the instance connection name formed correctly in the Cloud SQL Auth Proxy connection command?
• Have you checked the Cloud SQL Auth Proxy output? Pipe the output to a file, or watch the Cloud Shell terminal where you started the Cloud SQL Auth Proxy.
• Does your user or service account have the required IAM permissions to connect to a Cloud SQL instance?
• Have you enabled the Cloud SQL Admin API for your project?
• If you have an outbound firewall policy, make sure it allows connections to port 3307 on the target Cloud SQL instance.
• If you are connecting using UNIX domain sockets, confirm that the sockets were created by listing the directory specified with the -dir when you started the Cloud SQL Auth Proxy.
• Cloud SQL connectors and language-specific code
• Is the connection string formed correctly?
• Have you compared your code with the sample code for your programming language?
• Are you using a runtime or framework for which we don't have sample code?
• If so, have you looked to the community for relevant reference material?
• Self-managed SSL/TLS certificates
• Is the server certificate still valid?
• Authorized networks
• Is the source IP address included?
• Are you using a non-RFC 1918 IP address?
• Are you using an unsupported IP address?
• Connection failures
• Are you authorized to connect?
• Are you seeing connection limit errors?
• Is your application closing connections properly?
• Authenticating
• Native database authentication (username/password)
• Are you seeing access denied errors?
• Are the username and password correct?

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

Note: Authorizing all IP addresses opens your database to any client that tries to connect. If you have sensitive or proprietary data in your database, don't use this method to investigate connectivity issues.

Determine how connections are being initiated

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

sp_who
go

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

To see the processes that are running on your database, connect to your database and run the following command:

sp_who
go

For information about how to interpret the columns returned from sp_who, see the SQL Server reference.

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

cat /proc/sys/net/ipv4/tcp_keepalive_time

Tools for debugging connectivity

Note: Tools that are based on the Internet Control Message Protocol (ICMP), such as ping and traceroute, do not work with Cloud SQL. Do not use these tools for troubleshooting because they can return false negatives.

tcpdump

The tcpdump is a tool to capture packets. It's highly encouraged to run tcpdump to capture and inspect the packets between your host and the Cloud SQL instances when you are debugging the connectivity problems.

Locate your local IP address

If you don't know the local address of your host, then run the ip -br address show command. On Linux, this shows the network interface, the status of the interface, the local IP, and MAC addresses. For example: eth0 UP 10.128.0.7/32 fe80::4001:aff:fe80:7/64.

Alternatively, you can run ipconfig or ifconfig to see the status of your network interfaces.

Test with Connectivity Test

Connectivity Test is a diagnostics tool that lets you check connectivity between endpoints in your network. It analyzes your configuration and in some cases performs run-time verification. It supports Cloud SQL now. Follow these instructions to run tests with your Cloud SQL instances.

Test your connection

You can use the sqlcmd client to test your ability to connect from your local environment. For more information, see Connecting the sqlcmd client using IP addresses and Connecting the sqlcmd client using the Cloud SQL Auth Proxy.

Determine the IP address for your application

To determine the IP address of a computer running your application so you can authorize access to your Cloud SQL instance from that address, use one of the following options:

• If the computer is not behind a proxy or firewall, log in to the computer and use the What is my IP? site to determine its IP address.
• If the computer is behind a proxy or firewall, log in to the computer and use a tool or service like whatismyipaddress.com to determine its true IP address.

Open local ports

To verify that your host is listening on the ports you think it is, run the ss -tunlp4 command. This tells you what ports are open and listening.

All local port activity

Use the netstat command to see all the local port activity. For example, netstat -lt shows all the currently active ports.

Connect to your Cloud SQL instance using telnet

To verify that you can connect to your Cloud SQL instance using TCP, run the telnet command. Telnet attempts to connect to the IP address and port you give it.

On success, you see the following:

Trying 35.193.198.159...

Connected to 35.193.198.159. .

On failure, you see telnet hangs until you force-close the attempt:

Trying 35.193.198.159...

^C. .

Cloud Logging

Cloud SQL and Cloud SQL use Cloud Logging. See the Cloud Logging documentation for complete information and review the Cloud SQL sample queries.

View logs

You can view logs for Cloud SQL instances and other Google Cloud projects such as Cloud VPN or Compute Engine instances. To view logs for your Cloud SQL instance log entries:

Console

1. In the Google Cloud console, go to the Cloud Logging page.

   Go to Cloud Logging

2. Select an existing Cloud SQL project at the top of the page.
3. In the Query builder, add the following:
• Resource: Select Cloud SQL Database. In the dialog, select a Cloud SQL instance.
• Log names: Scroll to the Cloud SQL section and select appropriate log files for your instance. For example:
• Severity: Select a log level.
• Time range: Select a preset or create a custom range.

gcloud

Use the gcloud logging command to view log entries. In the example below, replace PROJECT_ID. The limit flag is an optional parameter that indicates the maximum number of entries to return.

Private IP addresses

Connections to a Cloud SQL instance using a private IP address are automatically authorized for RFC 1918 address ranges. Non-RFC 1918 address ranges must be configured in Cloud SQL as authorized networks. You also need to update the network peering to Cloud SQL to export any Non-RFC 1918 routes. For example:

gcloud compute networks peerings update cloudsql-sqlserver-googleapis-com 

--network=NETWORK 

--export-subnet-routes-with-public-ip 

--project=PROJECT_ID

VPN troubleshooting

See the Cloud VPN troubleshooting page.