"Lost connection to MySQL server" in Cloud Proxy

[小川純平]

Hi, I'm trying to connect to Cloud SQL instance using Cloud SQL Proxy, but I experienced "Lost connection to MySQL server" error.
I have no idea what the cause is. Any idea?

I tried following from CentOS 6 instance on GCE:

$ sudo /usr/local/bin/cloud_sql_proxy -dir=/tmp/cloudsql -fuse -credential_file=./gcp.json &
[1] 3139
2016/03/14 06:23:35 Mounting "/tmp/cloudsql"...
2016/03/14 06:23:35 Mounted "/tmp/cloudsql"
2016/03/14 06:23:35 Socket prefix: /tmp/cloudsql

$ mysql -u DB_USER_NAME -S /tmp/cloudsql/PROJECT_NAME:asia-east1:INSTANCE_NAME
2016/03/14 06:14:25 couldn't connect to "PROJECT_NAME:asia-east1:INSTANCE_NAME": dial tcp CLOUD_SQL_INSTANCE_IP:3307: getsockopt: connection timed out
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0


I also tried without -credential_file, and I got another error:

$ sudo /usr/local/bin/cloud_sql_proxy -dir=/tmp/cloudsql -fuse &
[1] 3481
2016/03/14 06:32:10 Mounting "/tmp/cloudsql"...
2016/03/14 06:32:10 Mounted "/tmp/cloudsql"
2016/03/14 06:32:10 Socket prefix: /tmp/cloudsql

$ mysql -u DB_USER_NAME -S /tmp/cloudsql/PROJECT_NAME:asia-east1:INSTANCE_NAME
2016/03/14 06:32:19 couldn't connect to "PROJECT_NAME:asia-east1:INSTANCE_NAME": POST "https://www.googleapis.com/sql/v1beta4/projects/PROJECT_NAME/instances/INSTANCE_NAME/createEphemeral": 403 Forbidden; Body="{\n \"error\": {\n  \"errors\": [\n   {\n    \"domain\": \"global\",\n    \"reason\": \"insufficientPermissions\",\n    \"message\": \"Insufficient Permission\"\n   }\n  ],\n  \"code\": 403,\n  \"message\": \"Insufficient Permission\"\n }\n}\n"; read error: <nil>
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0


Permission of the socket directory is:

$ ll -R /tmp/
/tmp/:
dr-xr-xr-x. 0 root  root     0 Aug 30  1754 cloudsql
drwxr-xr-x. 2 root  root  4096 Mar 14 06:32 cloudsql-proxy-tmp

/tmp/cloudsql:
-r--r--r--. 0 root root 404 Aug 30  1754 README

/tmp/cloudsql-proxy-tmp:
srwxrwxrwx. 1 root root 0 Mar 14 06:13 PROJECT_NAME:asia-east1:INSTANCE_NAME
srwxrwxrwx. 1 root root 0 Mar 14 05:48 PROJECT_NAME:asia-east1:ANOTHER_INSTANCE_NAME


If this may be a bug and need my project and instance name for investigation,
please refer email I've sent to clou...@google.com on Mar 1st (UTC), titled "Cannot change root password".
If you could't find it, I will gladly resend you.

Thanks,
Jumpei

[Vadim Berezniker]

Hi Jumpei,

Your instance is currently turned off (activation policy is set to 'Never'), so you will not be able to connect to it until it's turned back on by editing the instance and changing the activation policy to 'always'.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/04462528-a789-4e36-8caf-1caf20794bd0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[小川純平]

Oops, got it.
Thanks.

And there is another problem that I cannot change activation policy to Always On from Edit page.
I choose Always On radio button and press Save button, but then I reload the page and still Off radio button is chosen.

(If you don't understand the situation, I will write more details when I can use PC. I only have tablet now.)

Thanks,

Jumpei Ogawa
Software Engineer, GrowAsPeople < http://growaspeople.org >

[小川純平]

Hi Vadim,

I just retried to choose Always On, and Cloud SQL instance successfully started.
Maybe you fixed it?

Anyway thank you for your support!

Jumpei

[小川純平]

Hi,

I successfully started instance, but still the same error is happening.
I also tried on another DB instance, and the same results.

I confirmed External IP of GCE instances which mysql client runs on are registered in Authorized Networks of Cloud SQL.

Any idea to solve this?

[小川純平]

And I also confirmed Cloud SQL proxy is running in GCE instance.

$ ps aux | grep cloud
root      1708  0.0  4.6  47300 27912 ?        Sl   11:26   0:00 /usr/local/bin/cloud_sql_proxy -dir=/tmp/cloudsql -fuse
ogawa     3298  0.0  0.1 103300   884 pts/0    S+   12:11   0:00 grep cloud

[Vadim Berezniker]

Can you verify that you can connect to port 3307 on your instance from your GCE VM?

You can do a simple check using telnet on your GCE VM:

$ telnet <INSTANCE IP> 3307

If it gets stuck on "Trying IP..." then we know it's not able to connect.

I have verified that everything is running properly so if you are not able to connect via telnet it's likely the connection is being blocked somewhere (for example if you have GCE firewall rules or inside your VM)

By the way, when using proxy connectivity, you don't need to worry about updating "Authorized Networks". 

--

You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/c47488d9-74b4-487e-ad17-3f04d3fa970c%40googlegroups.com.

[小川純平]

Thanks Vadim.

I've run telnet on GCE instance and it seems successfully connected to Cloud SQL instance.

$ telnet <INSTANCE IP> 3307

Trying <INSTANCE IP>...
Connected to <INSTANCE IP>.
Escape character is '^]'.

Just after running above command, I retried to connect from mysql, but no luck.

$ mysql -u DB_USER_NAME -S /tmp/cloudsql/PROJECT_NAME:asia-east1:INSTANCE_NAME

[小川純平]

I run Cloud SQL Proxy client via cron (@reboot) but when I tried to kill proxy client process and re-run it, additional error message is shown (maybe by Cloud SQL Proxy client)

$ sudo /usr/local/bin/cloud_sql_proxy -dir=/tmp/cloudsql -fuse &

2016/03/16 04:20:22 Mounting "/tmp/cloudsql"...
2016/03/16 04:20:22 Mounted "/tmp/cloudsql"
2016/03/16 04:20:22 Socket prefix: /tmp/cloudsql

$ mysql -u notel -S /tmp/cloudsql/notelcrms:asia-east1:productions
2016/03/16 04:20:57 couldn't connect to "notelcrms:asia-east1:productions": POST "https://www.googleapis.com/sql/v1beta4/projects/notelcrms/instances/productions/createEphemeral": 403 Forbidden; Body="{\n \"error\": {\n \"errors\": [\n {\n \"domain\": \"global\",\n \"reason\": \"insufficientPermissions\",\n \"message\": \"Insufficient Permission\"\n }\n ],\n \"code\": 403,\n \"message\": \"Insufficient Permission\"\n }\n}\n"; read error: <nil>

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

In the first post of this thread, I thought this error message is shown when -credential_file is not specified, but actually it wasn't shown on my first trial because Proxy client runs from cron.

I will re-check MySQL users registration.

[Vadim Berezniker]

Can you clarify which error you are getting from the proxy right now?

Is it this one:

$ mysql -u DB_USER_NAME -S /tmp/cloudsql/PROJECT_NAME:asia-east1:INSTANCE_NAME

2016/03/14 06:32:19 couldn't connect to "PROJECT_NAME:asia-east1:INSTANCE_NAME": POST "https://www.googleapis.com/sql/v1beta4/projects/PROJECT_NAME/instances/INSTANCE_NAME/createEphemeral": 403 Forbidden; Body="{\n \"error\": {\n  \"errors\": [\n   {\n    \"domain\": \"global\",\n    \"reason\": \"insufficientPermissions\",\n    \"message\": \"Insufficient Permission\"\n   }\n  ],\n  \"code\": 403,\n  \"message\": \"Insufficient Permission\"\n }\n}\n"; read error: <nil>

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

or this one:

2016/03/14 06:14:25 couldn't connect to "PROJECT_NAME:asia-east1:INSTANCE_NAME": dial tcp CLOUD_SQL_INSTANCE_IP:3307: getsockopt: connection timed out

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

For (1), I think the problem is that the service account doesn't have the necessary scopes to call the Cloud SQL API. When creating a Compute Engine VM, there's a setting to select "Identity and API access". The default setting does not include Cloud SQL so the service account will not be able to communicate with the Cloud SQL API.

This is something we need to document better and provide a better error message for.

Unfortunately it is not possible to change the scopes on an existing VMs. The option is either to re-create the VM, if that's possible, or use credentials for a different service account.

--

You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/307e30bc-a668-4e90-a738-68685ff2862e%40googlegroups.com.

[小川純平]

Oh, sorry for unclear explanation.

2016/03/16 04:20:57 couldn't connect to "notelcrms:asia-east1:

productions": POST "https://www.googleapis.com/sql/v1beta4/projects/notelcrms/instances/productions/createEphemeral": 403 Forbidden; Body="{\n \"error\": {\n \"errors\": [\n {\n \"domain\": \"global\",\n \"reason\": \"insufficientPermissions\",\n \"message\": \"Insufficient Permission\"\n }\n ],\n \"code\": 403,\n \"message\": \"Insufficient Permission\"\n }\n}\n"; read error: <nil>

is from Cloud Proxy.

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

should be from MySQL client.

[小川純平]

I will confirm API authorization. Thank you!

[小川純平]

I created instance with --scopes sql,sql-admin option, then successfully connected from MySQL client on the created instance.

$ gcloud compute instances create "$instance_to_create" … --scopes sql,sql-admin

My web app also successfully connected to DB.

Thank you so much for your kind support, Vadim!

Jumpei

[Vadim Berezniker]

Glad to hear it's working.

I have filed an issue to improve the error message: https://github.com/GoogleCloudPlatform/cloudsql-proxy/issues/6

--

You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/452595c6-6b46-4818-a2d4-3c0f9fb34424%40googlegroups.com.

[Patrick Dougall]

Hi Vadim Berexniker, I am actually having the same problem trying to get the sql-proxy to work.  I've got as basic of setup as I can figure out.  A stock compute engine and a stock sql instance.  I'm following https://cloud.google.com/sql/docs/mysql-connect-docker as closely as possible (perfectly as far as I can tell).  I can even enter mysql form the mysql command line passing the IP of the sql instance

`mysql host=<ip address> -u root -p`

And I can start the proxy just fine.  But when I try to enter mysql from the proxy socket I get the dredded error 

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I've tried this with two different engine instances now following the advice you've already given, but I'm still not able to get past that error.

`gcloud compute instances create gce-proxy-5 --scopes sql,sql-admin,storage-rw` to create the instance,

`gcloud compute instances describe gce-proxy-5` validates the scopes are present

serviceAccounts:

  scopes:

  - https://www.googleapis.com/auth/devstorage.read_write

  - https://www.googleapis.com/auth/sqlservice

  - https://www.googleapis.com/auth/sqlservice.admin

`gcloud compute ssh gce-proxy-5` to ssh in

follow that ^ link to set up mysql client and docker and the proxy image

`docker run -d -v /cloudsql:/cloudsql -v /etc/ssl/certs:/etc/ssl/certs b.gcr.io/cloudsql-docker/gce-proxy /cloud_sql_proxy -dir=/cloudsql -instances=gce-testing-1354:us-central1:proxy-test-db`

to run the image

`mysql -u root -p -S /cloudsql/gce-testing-1354:us-central1:proxy-test-db` to start mysql through the proxy

That prompts for a passwork, and I give the one I provided here: https://console.cloud.google.com/sql/instances/proxy-test-db/access-control/users

and boom, same error, every time.

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I would love to hear if you've got any ideas!?!?!

Thanks

Patrick

[Patrick Dougall]

I figured out what the problem I was having was :tada:  

After trying to work with just the proxy without docker I was getting the full error message finally:

```

patrickdougall@hello-rails-1:~$ mysql -u root -p -S /cloudsql/gce-testing-1354:us-central1:proxy-test-db

Enter password:

2016/06/26 23:50:02 New connection for "gce-testing-1354:us-central1:proxy-test-db"

2016/06/26 23:50:02 couldn't connect to "gce-testing-1354:us-central1:proxy-test-db": ensure that the account has access to "gce-testing-1354:us-central1:proxy-test-db" (and make sure there's no typo in that name). Error during createEphemeral for gce-testing-1354:us-central1:proxy-test-db: googleapi: Error 403: Access Not Configured. Cloud SQL Administration API has not been used in project 795050566156 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/sqladmin/overview?project=795050566156 then retry. If you enabled this API recently, wait a few minutes for the action to propagate to our systems and retry., accessNotConfigured

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

```

So I went to that page and clicked enable and both the app bare and the one inside of docker both work now.  It's too bad that full error gets swallowed up in docker though, it makes it pretty hard to work with.

Also, is there a way to programatially (through gcloud preferrably or through the API) handle this problem?

"Cloud SQL Administration API has not been used in project 795050566156 before or it is disabled"

On Monday, March 14, 2016 at 2:51:31 AM UTC-4, 小川純平 wrote: