Cloudsql-proxy: Problems trying to connect from within GCE — getsockopt: connection timed out

[isoubelet]

Hi there,

I am having problems trying to connect to a MySQL second generation instance through the proxy from within GCE. The machine where the proxy docker container is running has a service account with role Cloud SQL Client. Ports are open for ingress and egress.
When I try to reach the MySQL with the following command:
mysql -u something -p --host 127.0.0.1 --port 3306

... it simply hangs forever. However, when I check the container's log it says:

Listening on 0.0.0.0:3306 for xxxxx
Ready for new connections
New connection for xxxxx
couldn't connect to xxxxx: dial tcp xxxxx:3307: getsockopt: connection timed out

I have replaced names and ip addressed, but it resolves the ip address of the MySQL instance correctly.

I've been trying different combinations and possible solutions I read from other users but none of them worked for me.

Any clue?

Many thanks!

[Carrotman42]

It's possible there's a firewall between you and the database. Can you
verify in your settings that your VM is allowed to access port 3307?

On Tue, May 15, 2018, 7:29 PM isoubelet [e-mail] wrote:

Hi there,

I am having problems trying to connect to a MySQL second generation
instance through the proxy from within GCE. The machine where the proxy
docker container is running has a service account with role Cloud SQL
Client. Ports are open for ingress and egress.
When I try to reach the MySQL with the following command:
mysql -u something -p --host 127.0.0.1 --port 3306

... it simply hangs forever. However, when I check the container's log it
says:

Listening on 0.0.0.0:3306 for xxxxx
Ready for new connections
New connection for xxxxx
couldn't connect to xxxxx: dial tcp xxxxx:3307: getsockopt: connection
timed out

I have replaced names and ip addressed, but it resolves the ip address of
the MySQL instance correctly.

I've been trying different combinations and possible solutions I read from
other users but none of them worked for me.

Any clue?

Many thanks!

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/164, or mute
the thread
https://github.com/notifications/unsubscribe-auth/AAiy72Ug5VXuPbos4QLX5iNjsnHmQ6Trks5ty2TMgaJpZM4UAbL3
.

[isoubelet]

Hello, Kevin. Thanks for answering.
In fact, as I comment in the previous description, there're firewall rules that allow all traffic and apply for both ingress and egress. Those rules are associated to the GCE instance running the proxy.

However there's something that I am not understanding, why the proxy's trying to connect to the MySQL instance using the port 3307.

I see the following log message:
couldn't connect to xxxxx: dial tcp xxxxx:3307: getsockopt: connection timed out

Running the proxy with the following command:
./cloud_sql_proxy -instances=projectxxxx:regionxxxx:instancexxxxx=tcp:3306

I was able to connect to the instance from a local computer (outside GC) using a standard JDBC client and certificates.

Thanks!

[kurtisvg]

@isoubelet Were you able to get connected? Port 3307 is used by the proxy to connect to the service - the tcp:3306 refers to the local side of the proxy, the port the proxy listens to for connections.

[isoubelet]

Hi @kurtisvg ,
Actually I couldn't connect through the proxy. I have deployed a MySQL instance in GCE by my own. It's a bit curious why it is easier to connect to Cloud SQL from outside GCP's infra than from within.

Anyways, I am planning to migrate to Cloud SQL sooner or later but would need more time to test and properly configure it.

Thanks!

[hfwang]

I think this issue is resolved. The Cloud SQL proxy uses port 3307 instead of the database default ports (3306 or 5432 for mysql and postgres respectively).

This is because they can not share the same port as the proxy isn't using the native database wire protocol.

[crlane]

I think this is still a problem. If you use a VPC with private GKE cluster and NATing, even with correct firewall rules, the proxy seems to make an initial connection but then times out. My cloud sql instance was postgres, but the behavior was the same as described above.

[kurtisvg]

@crlane Can you provide us with a log of your proxy and/or your application when this is happening?

[crlane]

@kurtisvg My proxy log is identical to the one above with the exception of the port:

Listening on 0.0.0.0:5432 for xxxxx
Ready for new connections
New connection for xxxxx
couldn't connect to xxxxx: dial tcp xxxxx:3307: getsockopt: connection timed out

However, I may have jumped the gun on blaming the proxy. I ended up standing up a database vm inside my private subnet. I couldn't get that to work either until I added my secondary IP ranges to my firewall rules.

My guess is that if I do that with cloudsql proxy, it'll work too. I would very much like to use the hosted version of the database, so I'll try it again sometime. Probably next weekend.

[thertzelle]

@kurtisvg My proxy log is identical to the one above with the exception of the port:

Listening on 0.0.0.0:5432 for xxxxx
Ready for new connections
New connection for xxxxx
couldn't connect to xxxxx: dial tcp xxxxx:3307: getsockopt: connection timed out

However, I may have jumped the gun on blaming the proxy. I ended up standing up a database vm inside my private subnet. I couldn't get that to work either until I added my secondary IP ranges to my firewall rules.

My guess is that if I do that with cloudsql proxy, it'll work too. I would very much like to use the hosted version of the database, so I'll try it again sometime. Probably next weekend.

Did you ever get this figured out? I'm having the same issue.

[kurtisvg]

@thertzelle Make sure your firewall allows out on 3307 - this is the port the proxy connects to on Cloud SQL's side.

[thertzelle]

@thertzelle Make sure your firewall allows out on 3307 - this is the port the proxy connects to on Cloud SQL's side.

When I create an Egress Firewall Rule how do I target the Cloud SQL instance? Here is an example:

gcloud compute --project=OUR-AWESOME-PROJECT firewall-rules create test-firewall-rule --direction=EGRESS --priority=1000 --network=default --action=ALLOW --rules=tcp:3307 --destination-ranges=10.99.111.1/32 10.99.111.1 is a fake ip address, but the full IP address. Is this right?

[kurtisvg]

@thertzelle Looks correct to me.

[crlane]

@thertzelle I did get it to work eventually. IIRC The problem was the priority of my firewall rules. Here's the terraform that I ended up using:

// for cloud sql
resource "google_compute_firewall" "fw-db-egress" {
  name      = "${var.name}-fw-db-egress"
  network   = "${google_compute_network.vpc.name}"
  direction = "EGRESS"

  allow {
    protocol = "tcp"
    ports    = ["5432", "3307"]
  }

  destination_ranges = [
    "${google_sql_database_instance.crlane-cloudsql-instance.ip_address.0.ip_address}/32",
  ]

  priority = 600
}

I think the only difference is the priority. Choose something lower so that the rule to go to the cloudsql IP is first.

[thertzelle]

I added the similar firewall rules but haven't gotten it to work. I'm not sure how to target the cloud sql instance, I've tried all the methods; by tag, by service and by all instances.

[kurtisvg]

@thertzelle Have you tried just opening the port to everywhere to verify that is your problem?

Also be sure to note the priority of your rules, as mentioned above.

[isoubelet]

@thertzelle I never figured out how to solve this problem.
From the networking perspective everything was properly configured and I tried several combinations like the aforementioned priority but nothing worked for me, so I ended up moving to a containerized mysql on Kubernetes.
What surprised me of Cloudsql is that it's easier to connect to your instance from outside GCP than from within.

However, if you manage to solve this please share the solution, at least to solve the mystery.

[thertzelle]

@thertzelle Have you tried just opening the port to everywhere to verify that is your problem?

Also be sure to note the priority of your rules, as mentioned above.

We tried 0.0.0.0/0 and that still had the same affect: couldn't connect to xxxxx: dial tcp xxxxx:3307: getsockopt: connection timed out

We assume this has to deal with our CloudSQL being marked as Private IP address, but we haven't found a work around yet.

It connects to the master node, or at least the LB; But then when it connects and attempts to connect to one of the instances (i assume) on 3307, thats when it dies.

For the record, our Kubernetes instance can connect to the CloudSQL instance just fine using the Private IP address. We just can't use cloud proxy from local to connect to the GCP CloudSQL Private remotely.

[kurtisvg]

If you are using a Cloud SQL instance that only has a private IP address, then here are some additional suggestions:

1. Private IPs are only accessible to machines on the same VPC Network. This includes Compute VMs and GKE clusters. For more info on configuring VPCs, check out these pages: Private IP, Configuring Private IP Connectivity

2. The Cloud SQL proxy is not required if you are using Private IP and the machines you are connecting from are on the same VPC. You should be able to connect directly to then IP.

3. If you do want to use the proxy, you can connect with either Private or Public IP. The ip_address_types takes a comma separated list of IP types (-ip_address-types PUBLIC will force the proxy to use the Public IP). The default is PUBLIC,PRIVATE.

4. From my experience, the default firewall rules for a Compute VM or a GKE cluster allow access to Cloud SQL. If more aggressive rules are put in place, this could inadvertently block the connections to the instance. A reasonable way to confirm that this is the problem is by rolling back your firewall rules, verifying connectivity to the instance, then reapplying rules, checking after each one that connections are still possible. You can also enable Firewall Rules Logging, and check the logs for connections to your instance that are being blocked.

[crlane]

I've spun most of the interesting parts of this environment down, but I can try to stand it back up briefly to see what other information I can provide. AFAIK there's no way to assign a private IP to a cloud sql instance (though I see now it's marked as a BETA feature in the cloud console - haven't tried it). I accessed it through the public IP and the firewall rule as listed above. Make sure you have a route from your VPC to the cloudsql IP. Do you have a NAT gateway set up or something? Make sure there aren't any VPC routes or firewall rules that are taking precedence.

[mahsanamin]

Having similar difficulty and read this thread completely, also again saw the documentation about Private IP mentioned by @kurtisvg. I need clarity in below statement.

(Doc Note) Resources that use private services access to connect to Cloud SQL instances, and their subnet, must be in the same region as the Cloud SQL instances.

Which to me means that if I have mysql with private IP, in asia(singapore) but Cloud engine in US-west(oregon) for the sake of Free Tier. Then I can not ever connect my Private Cloud SQL instance (Just confirming)

My Question: Why Connection Peering can not allow this ? I can see a Peering Rule in my Firewall which allows the IP range of my Private Cloud SQL to default vpc and my oregon's instance is in default vpc but I am unable to connect because its not possible a (Doc Note) right ?

Note: I tried also proxy method and direct private IP connection and to me it looks what I am trying to do is not possible as mentioned in Docs.

[luhkevin]

I'm also experiencing this issue. I double checked and there are no firewall rules blocking egress on port 3307. I'm able to ping external IPs just fine, even I'm connecting from a private GKE cluster. Also getting logs such as the one below.

From sqlproxy:

root@my-container:~# ./cloud_sql_proxy.linux.amd64  -verbose -instances=<INSTANCE_CONNECTION_STR>
2018/11/27 06:40:48 current FDs rlimit set to 1048576, wanted limit is 8500. Nothing to do here.
2018/11/27 06:40:48 Listening on 127.0.0.1:3306 for <INSTANCE>
2018/11/27 06:40:48 Ready for new connections
2018/11/27 06:40:50 New connection for "<INSTANCE>"
2018/11/27 06:42:58 couldn't connect to "<INSTANCE>": dial tcp xxxx:3307: connect: connection timed out

And from connecting mysql client in the same container:

root@my-container:~# mysql -h 127.0.0.1 -u root
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Now, the strange thing is, I cloned the db instance above, reran the same commands from the same container with the same credentials, and it worked fine. So I feel like it's something on your end.

I am using sqlproxy 1.13

Also, if it helps, I did a password reset on the instance. Maybe that caused something to break?

[dev-mansonthomas]

Hi,
I'm also affected by this issue.
I'm using could SQL with Google App Engine, and I'm using cloud_sql_proxy to connect remotely to my DB instances to manage it.

I don't want to use a public ip because it cost money (I'm on a very low budget project where the project is running 9 days a year in production mode), and it's rather silly to use a public ipv4 address for a DB that must not be directly accessible on internet. (I know it's not, but then, why use an ipv4 address, when it's so rare).

I am using sqlproxy 1.13

I've checked if I can change the default mysql server password to workaround this issue, but it's not possible. So i'm stuck here and I need to fully delete my DB because a stopped mysql instance won't release the ipv4 address (and then cost me money), and the private ip address doesn't work with cloud_sql_proxy.

I don't get why this issue is closed, there's definitively an issue here. The tool just doesn't work with private IP feature when it's the only connectivity option enabled on the mysql server.

[kurtisvg]

I don't get why this issue is closed, there's definitively an issue here.

This error (getsockopt: connection timed out) is a networking issue. It is caused when the attempt to reach the server times out. If the proxy cannot reach the Cloud SQL instance, it cannot authenticate connections with it. There is nothing the proxy itself can do to resolve this issue.

using cloud_sql_proxy to connect remotely to my DB instances

Can you please elaborate? Private IPs are only accessible within the VPC network that they are assigned on. The proxy will be unable to connect via a Private IP if it is not running in the VPC network.

[dev-mansonthomas]

Well, it tries to reach MySQL on the wrong port, 3307 instead of 3306, that seems the root cause of the issue at first glance.

If I'm within the VPC network, I don't need the cloud_sql_proxy at all according to a post on this thread

The Cloud SQL proxy is not required if you are using Private IP and the machines you are connecting from are on the same VPC. You should be able to connect directly to then IP.

I'm outside of GCP network and used to use cloud_sql_proxy to connect to my MySQL instance that had a public IP.
Now I want to save money and get rid of this public ip, and switch to private IP.

You seems to say, that I need to use a VPC network.
That seems odd, but why not. I thought it was the job of the cloud_sql_proxy to do that (and also what Google Support point out:

Private IP address is not listed in the Cloud SQL pricing document [1]. So I think you will not be charged for using Private IP.
Upon checking your requirement, I would also like to introduce sql-proxy [2] to you. Maybe it could be an option for you.

Also the documentation here seems to indicate that you just have to add the option so that you can connect to the private ip instead of the public ip. No mention of VPC :

sql proxy doc

IP address selection
By default, the proxy attempts to connect using a public IPv4 address. If the instance is configured to use private IP, and does not have a public IPv4 address, the proxy uses the private IP address to connect. If the instance has both public and private IP configured, and you want the proxy to use the private IP address, you must provide the following option when you start the proxy:

--ip_address_types=PRIVATE

Do you have any pointer on how to setup a VPC?
Also I wonder if it's interesting because it sounds more costly than 6€/months, (I'll not be using it constantly, my project is running about a month in production, and shutdown the rest of the time)

[kurtisvg]

The proxy allows users to conveniently authenticate connections with GCP credentials. This is more convenient and secure because it allows fine grain control over access to the instance using service accounts and IAM roles.

indicate that you just have to add the option so that you can connect to the private ip instead of the public ip. No mention of VPC

That is probably because the docs on the Private IP feature mention it. I've filed an internal bug on my end to clarify our Cloud Docs on the proxy and can update our README here to make this more clear.

Do you have any pointer on how to setup a VPC?

Check out the Using VPC Networks page.

it sounds more costly than 6€/months

I'm not sure if VPC has a cost attached (I don't see any at first glance), but you'll likely need a compute instance to be in that VPC to access it. Compute has an "always free" tier with you could use for this purpose without additional cost.

You could also export your data and delete your Cloud SQL instance, and then recreate and import it to avoid paying for the Public IP for the 11 months it isn't in use.

[kurtisvg]

and also what Google Support point out:

Private IP address is not listed in the Cloud SQL pricing document [1]. So I think you will not be charged for using Private IP.
Upon checking your requirement, I would also like to introduce sql-proxy [2] to you. Maybe it could be an option for you.

@dev-mansonthomas do you have a support case number that I can follow up for this recommendation?

[Carrotman42]

Well, it tries to reach MySQL on the wrong port, 3307 instead of 3306, that seems the root cause of the issue at first glance.

Note that the client-side Proxy intentionally connects on 3307 so that it is isolated from regular MySQL connextions. (It made it easier to tell the difference between the two when the remote host accepts connections, for one thing.) Local connections are still made on 3306 since clients intending to connect to MySQL databases expect 3306, but the data is proxied via another means.

[dev-mansonthomas]

and also what Google Support point out:

Private IP address is not listed in the Cloud SQL pricing document [1]. So I think you will not be charged for using Private IP.
Upon checking your requirement, I would also like to introduce sql-proxy [2] to you. Maybe it could be an option for you.

@dev-mansonthomas do you have a support case number that I can follow up for this recommendation?

It was : #17669358

Thanks for the detailed answer, I've a better understanding of what google cloud proxy does and does not.

Indeed automating the creating of MySQL instance seems the most effective way to go.

[ColOfAbRiX]

Having the same issue and can't figure out how to fix it. I read all the thread but didn't see any proper solution. Or am I missing something?

[kurtisvg]

Having the same issue and can't figure out how to fix it. I read all the thread but didn't see any proper solution. Or am I missing something?

This error occurs when the proxy doesn't have connectivity access to the instance. Make sure you verify network connectivity to the Cloud SQL instance IP on port 3307.

[ColOfAbRiX]

As I read in this thread, I cloned my instance and the proxy started to work.
So I did some side-to-side tests and I confirm that cloud sql proxy always times out with the original cloud sql instance, while it works on the clone. As far as I'm aware there's no difference in configuration or firewall rules between the two.

[pedropregueiro]

I've read all of this thread and in the end, I was still having some difficulties using the cloud proxy. However, I just read the following on the private ip documentation:

To access a Cloud SQL instance on its private IP addresses, you must use a GCP resource in the same region.

I'm guessing this is a big no no @kurtisvg? I have my kubernetes cluster in a region and my cloudsql instance on another one, so I'm guessing the VPC doesn't work so well in that case?

[jesusch]

@hfwang this issue is still open. It's not about port overlap at all
cloud_sql_proxy does not work on a local, external host when the cloudSQL instance only has an internal IP
This should either get's documented or "fixed"
Technically I'd like to have the ability to connect to ClouSQL via cloud_sql_proxy without having external IP

[kurtisvg]

Locking this issue because it has become a "catch-all" for a generic error. This error occurs when the Cloud SQL proxy does not have network access to the instance specified. This can be commonly cause by firewalls, or if the proxy is on a network that doesn't have access to the specified IP type. If any future users encounter this error and feel that it is because of a bug in the proxy

@jesusch As mentioned in detail in the comments above, internals IP's are accessible from internal networks only - by design. If you need to access an instance from an external network, then you need to use an external IP.