Datastream to Cloud SQL private IP not working over private connectivity.

[jg0]

I'm having issues understanding what is blocking here.

I have configured Cloud SQL instance in us-east1 with new datastream user and password. Added Private address IP with Google private connectivity. I can connect directly from GKE pods to it (without Cloud SQL proxy over private IP address)

Issue starts here:
Datastream private connectivity created in us-east1 with ip range 10.124.0.0/20. I'm using default network with auto-subnets that are starting from 10.128.0.0/20-10.196.0.0/20. No issues creating this profile.

VPC peering showing properly and active. 

Only one subnet in this peering: 10.124.0.0/20 - accepted

Exported routes for peering - all default subnets.

Peering routes:

Cloud SQL private address is within 10.34.176.0/24.

And it looks like it should work but:
CloudSQL returns this error - 

[Note] Aborted connection 5328800 to db: 'unconnected' user: 'datastream' host: '35.237.45.20' (Got an error reading communication packets)",

I tried to change flags to Cloud SQL instance:

I'm already without options left to debug this issue.

[christianpaula]

Hi @jg0,

Welcome to Google Cloud Community.

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

Thank you

[etaim]

*EDIT* When using private connectivity with Cloud SQL, you must also set up a reverse proxy. See this page for more details.

[gilgtz]

Hi Etai, I think the part of using a CloudSQL Auth proxy is not well documented, is there any way we could improve this part on our docs?, or docs only mention reverse ssh proxy which is different than CloudSQL Auth proxy.

[etaim]

Hi Gilberto, we intentionally removed the Cloud SQL Auth Proxy solution from our documentation, as the way it was being used in combination with Datastream exposed a security gap. 

[cyrille115]

Hi, etaim!

As you mentioned, Cloud SQL Auth Proxy solution was removed from the documentation. Hence, Shall we setup a reverse proxy, then? 😊 If so, can you edit your answer from this thread last 12-09-2022 which suggests to use the SQL Auth Proxy.

Thank you.

Cy

[cyrille115]

Hi, @etaim.

Is it possible to point the reverse proxy to a read replica cloud sql instance? 

Thanks.

Cy

[etaim]

Connectivity-wise, yes. And for Cloud SQL for MySQL this will work fine.

However, for PostgreSQL (any Postgres, not just Cloud SQL) pglogical replication isn't supported on a read-replica.

[cyrille115]

I see. Hence, Datastream from Cloud SQL (PostgreSQL) Instance (via reverse proxy) works. But Datastreaming from a PostgreSQL read-replica instance (via reverse proxy) won't work.

[cyrille115]

Hi, @etaim.

Is it possible to setup a multiple private connectivity to a single VPC?
For example, we have a dev and qa environments, and they are both sharing the same VPC. 

Do you think we can setup two private connectivity? (from dev, and another one from qa)

Thanks.

[etaim]

Hi @cyrille115 ,

Indeed, it shouldn't be a problem to have multiple private connectivity configurations to a single VPC. Just need to make sure each one has its own separate (non-overlapping) IP range.

 

cyrille115Participant I

• 10 Posts
• 3 Likes given
• 1 Solutions
• 2 Likes received

View profile

[qyuand456]

@etaim is it possible to use the reverse proxy approach when the postgres has SSL connection enforced?

[etaim]

Hi,

SSL/TLS support is a high priority and on our immediate roadmap. Unfortunately, Datastream will not be able to establish the connection if SSL is enforced.