Got an error reading communication packets in Google Cloud SQL

Question

From 31th March I've got following error in Google Cloud SQL:

Got an error reading communication packets.

I have been using Google Cloud SQL for 2 years, but never faced with such problem. I'm very worried about it.

This is detail error message:

textPayload:  "2019-04-29T17:21:26.007574Z 203385 [Note] Aborted connection 203385 to db: {db_name} user: {db_username} host: 'cloudsqlproxy~{private ip}' (Got an error reading communication packets)"

Answer 1

While it is true that this error message often occurs after a maintenance period, it isn't necessarily a cause for concern as this is a known behavior by MySQL.

Possible explanations about why this issue is happening are :

1. The large increase of connection requests to the instance, with the number of active connections increasing over a short period of time. The freezing / unavailability of the instance can also occur due to the burst of connections happening in a very short time interval. It is observed that this freezing always happens with an increase of connection requests. This increase in connections causes the instance to be overloaded and hence unavailable to respond to further connection requests until the number of connections decreases or the instance stabilizes.
2. The server was too busy to accept new connections.
3. There were high rates of previous connections that were not closed correctly.
4. The client terminated it abnormally.
5. readTimeout setting being set too low in the MySQL driver.
6. In an excerpt from the documentation, it is stated that:

There are many reasons why a connection attempt might not succeed. Network communication is never guaranteed, and the database might be temporarily unable to respond. Make sure your application handles broken or unsuccessful connections gracefully.

7. Also a low Cloud SQL Proxy version can be the reason for such incident issues. Possible upgrade to the latest version of (v1.23.0) can be a troubleshooting solution.
8. IP from where you are trying to connect, may not be added to the Authorized Networks in the Cloud SQL instance.

Some possible workaround for this issue, depending which is your case could be one of the following:

1. In the case that the issue is related to a high load, you could retry the connection, using an exponential backoff to prevent from sending too many simultaneous connection requests. The best practice here is to exponentially back off your connection requests and add randomized backoffsto avoid throttling, and potentially overloading the instance. As a way to mitigate this issue in the future, it is recommended that connection requests should be spaced-out to prevent overloading. Although, depending on how you are connecting to Cloud SQL, exponential backoffs may already be in use by default with certain ORM packages.

2. If the issue could be related to an accumulation of long-running inactive connections, you would be able to know if it is your case using show full processliston your database looking for the connections with high Time or connections where Command is Sleep.
   

   If this is your case you would have a few possible options:
   

   If you are not using a connection pool you could try to update the client application logic to properly close connections immediately at the end of an operation or use a connection pool to limit your connections lifetime. In particular, it is ideal to manage the connection count by using a connection pool. This way unused connections are recycled and also the number of simultaneous connection requests can be limited through the use of the maximum pool size parameter.

   If you are using a connecting pool, you could return the idle connections to the pool immediately at the end of an operation and set a shorter timeout by adjusting wait_timeout or interactive_timeoutflag values. Set CloudSQL wait_timeout flag to 600 seconds to force refreshing connections.

3. To check the network and port connectivity once -

Step 1. Confirm TCP connectivity on port 3306 with tcptraceroute or netcat.

Step 2. If [Step 1] succeeded then try to check if there are any errors in using mysql client to check timeout/error.

4. When the client might be terminating the connection abruptly you could check for:

   If the MySQL client or mysqld server are receiving a packet bigger than max_allowed_packet bytes, or the client receiving a packet too large message,if it so you could send smaller packets or increase the max_allowed_packet flag value on both client and server. If there are transactions that are not being properly committed using both "begin" and "commit", there is the need to update the client application logic to properly commit the transaction.

5. There are several utilities that I think will be helpful here, if you can install mtr and the tcpdump utilities to monitor the packets during these connection-increasing events.

6. It is strongly recommended to enable the general_log in the database flags. Another suggestion is to also enable the slow_query database flag and output to a file. Also have a look at this GitHub issue comment and go through the list of additional solutions proposed for this issue here

Answer 2

This error message indicates a connection issue, either because your application doesn't terminate connections properly or because of a network issue.

As suggested in these troubleshooting steps for MySQL or PostgreSQL instances from the GCP docs, you can start debugging by checking that you follow best practices for managing database connections.