"Lost connection to MySQL server" in Cloud Proxy

4,458 views
Skip to first unread message

小川純平

unread,
Mar 14, 2016, 2:51:31 AM3/14/16
to Google Cloud SQL discuss
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

unread,
Mar 14, 2016, 3:00:59 AM3/14/16
to Google Cloud SQL discuss
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.

小川純平

unread,
Mar 14, 2016, 3:21:00 AM3/14/16
to Google Cloud SQL discuss

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 >

小川純平

unread,
Mar 14, 2016, 11:37:58 PM3/14/16
to Google Cloud SQL discuss
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

小川純平

unread,
Mar 15, 2016, 5:59:26 AM3/15/16
to Google Cloud SQL discuss
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?

小川純平

unread,
Mar 15, 2016, 8:14:14 AM3/15/16
to Google Cloud SQL discuss
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

unread,
Mar 15, 2016, 3:56:56 PM3/15/16
to google-cloud...@googlegroups.com
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.

小川純平

unread,
Mar 16, 2016, 12:12:37 AM3/16/16
to Google Cloud SQL discuss
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

小川純平

unread,
Mar 16, 2016, 12:31:50 AM3/16/16
to Google Cloud SQL discuss
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

unread,
Mar 16, 2016, 12:35:22 AM3/16/16
to Google Cloud SQL discuss
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.
Message has been deleted

小川純平

unread,
Mar 16, 2016, 1:12:56 AM3/16/16
to Google Cloud SQL discuss
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.

小川純平

unread,
Mar 16, 2016, 1:16:09 AM3/16/16
to Google Cloud SQL discuss
I will confirm API authorization. Thank you!

小川純平

unread,
Mar 16, 2016, 5:06:42 AM3/16/16
to Google Cloud SQL discuss
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

unread,
Mar 16, 2016, 5:02:12 PM3/16/16
to Google Cloud SQL discuss
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.

Patrick Dougall

unread,
Jun 26, 2016, 4:21:33 PM6/26/16
to Google Cloud SQL discuss
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

`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

unread,
Jun 26, 2016, 8:06:54 PM6/26/16
to Google Cloud SQL discuss
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:
Reply all
Reply to author
Forward
0 new messages