Google Cloud SQL on App Engine - Connection String Outdated?

[lennrt]

Issue description

In the readme for this driver, the connection string for connecting to Google Cloud SQL on App Engine is given as:

user@cloudsql(project-id:instance-name)/dbname

While Google's cloudsql package documentation also affirms this for your driver, there are posts on Stack Overflow such as this one which claim that one needs to use projectid:regionname:instancename rather than projectid:instancename.

What is the correct connection string? Neither of these is currently working.

Example code

A more detailed post can be found here: http://stackoverflow.com/questions/39668672/trouble-connecting-to-google-cloud-sql-server-from-deployed-app

Error log

My server is returning a 500 response whenever I make a call to an endpoint that uses the Cloud SQL database. The database connection works fine when I am connecting to the server from a locally served version of my app.

I have tried a variety of connection strings, and here are some errors that were logged in the Google Cloud Console:

5447 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.

5447 [Warning] entry 'root'@'localhost' in mysql.user is ignored because it duplicates entry in mysql.system_user

14409 [Note] Aborted connection 14409 to db: 'User' user: 'root' host: 'xxx.xxx.xxx.xxx' (Got an error reading communication packets)

(No password was specified in the connection string because the documentation does not specify the need for a password. This post mentions that the connection password must be null when the app attempts to connect to the server using root@localhost.)

6170 [Note] Access denied for user 'root'@'cloudsqlproxy~xx.xxx.xxx.xx' (using password: NO) 

I also tried connecting with a user other than the root user (username: newuser):

5447 [Warning] 'user' entry 'newuser@localhost' ignored in --skip-name-resolve mode.

Configuration

Driver version (or git SHA): https://github.com/go-sql-driver/mysql/tree/3654d25ec346ee8ce71a68431025458d52a38ac0

Go version: go version go1.6.2 linux/amd64

Server version: The Google Cloud SQL instance is running MySQL 5.7

Server OS: From the Compute Engine tab, it looks like the server hosting the most recent version of my app is running Debian 7.11 (Wheezy)

[pjebs]

user@cloudsql(project-id:instance-name)/dbname
Is the old one. It still works because I still use it.

Recently gae started categorising the database into regions too hence the new format. If your cloudsql is categorised into a region, you'll need to use the new format

[pjebs]

Perhaps this driver doesn't properly parse the new format. That may be the issue

[lennrt]

In your case, are you running a second generation Cloud SQL server?

[pjebs]

No first gen

[pjebs]

I suspect the driver isn't parsing the new format. The code may need to change so that it takes the content before first colon and the content after first colon irrespective of the new colon present

[pjebs]

There is a pull request currently updating read me

[lennrt]

Thanks for the heads up. I just left a comment on that PR.
As a temporary workaround I will set up a first generation server until the driver issue is fixed.

[lennrt]

Update: It looks like there is an issue with the driver when a deployed Google App Engine application tries to connect to a second generation Google Cloud SQL Server.

I created a first generation Cloud SQL Server and I was able to successfully connect to the server using a deployed Google App Engine application with this connection string:

user@cloudsql(project-id:instance-name)/dbname

No region name was required.

[arnehormann]

Parsing shouldn't be a problem, the driver takes everything between the parens: https://github.com/go-sql-driver/mysql/blob/master/dsn.go#L282
It's only used in https://github.com/go-sql-driver/mysql/blob/master/driver.go#L65

Can someone with a cloudsql account please download the driver, edit https://github.com/go-sql-driver/mysql/blob/master/appengine.go#L14 and replace "appengine/cloudsql" with "google.golang.org/appengine/cloudsql", try it with the old and new version with and without the region and report what works? Thanks!

[benguild]

_Guys, for reference:_
The Google provided solution to connecting to 2nd generation is here:
http://stackoverflow.com/questions/38890022/tls-requested-but-server-does-not-support-tls-error-with-google-cloud-sql-2nd

Google's posted documentation was (and perhaps still is) wrong, but the correct solution is posted there. We're using 2nd generation Cloud SQL in production without issue.

[bagatelli]

Is there a solution to this? Definitely not working with CloudSQL Second Generation.

[benguild]

@bagatelli see my comment above yours 

[bagatelli]

@benguild I've seen your comment in multiple places. However you're assuming that everyone is using SSL in their connections which is not the case to everyone. Certainly it's not mine. The error message I'm getting is "Driver: Bad Connection". The database is not even reached because the driver can't identify the connection parameters properly. I'm thinking the issue lies somewhere in the mess that's been around with the "new appengine" and "old appengine" packages which is causing a lot of things to break and Google is not bothering to document this change properly.

[benguild]

@bagatelli To clarify, make sure you're using the connection string format mentioned in this comment: http://stackoverflow.com/questions/38890022/tls-requested-but-server-does-not-support-tls-error-with-google-cloud-sql-2nd#comment65140499_38890022

Sorry for the prior brevity. We're using 2nd gen in production from Go on App Engine without issue. Just leave out the "tlsConfigName" parameter since the SQL proxy will add that, but either way they report that TLS is now supported anyway.

[bagatelli]

@benguild . Thanks for all your effort in helping me out on all these issues.
Are you using the "new appengine" or "old appengine"? I've just spent the day converting all my code to this new nightmare which is the new appengine packages and as you may know from my other post I can no longer deploy my code and therefore I can't properly test what you had suggested above. I'll try again as soon as I find a solution to deploy my app.
Thank you!

[benguild]

@bagatelli If you're having trouble deploying, try pulling your code onto a VM or use Google Cloud Console.

[benguild]

@bagatelli What I was saying was... to try it on a fresh machine, or use their cloud console (which has the dev tools already) before you write off the fact that it's definitely not environment related.

I can't currently deploy using macOS Sierra due to a bug that's 4 months old, but we can deploy fine from an VM.

I'm sure it's more work for you to move off the environment entirely so I'd at least be sure to rule that out before quitting entirely.

[bagatelli]

@pjebs . Not sure where you've got this assumption from but I'm not using flexible environment.

[benguild]

@pjebs That's incorrect. That's currently how you deploy to standard.

[benguild]

@pjebs appcfg.py update also works.

[benguild]

@bagatelli I hear you— I generally tend to minimize my third-party dependencies and also API dependencies since what you're saying is true... Google is not obligated to provide support, although they'd risk their reputation otherwise.

I'm not sure what's currently the issue for you exactly (besides using old APIs, I think?), but every piece of software I've developed for App Engine I've also developed with the intention of moving off of App Engine if and when needed. For example, for every Google API I interface with, I write a "helper" for that actually calls the methods and provides its own to the rest of the application so that and only that needs to be adjusted to a new environment when necessary and [hopefully] without breaking the rest of the application.

For me, App Engine is more of a convenience as it's an environment that requires little configuration besides for scaling and preferences, and provides some great APIs. It's annoying when something goes wrong, but they do offer premium support if you have issues like what you're talking about?

[bagatelli]

@pjebs. As fas as I understand goapp is just a wrapper for appcfg.py. It will end up calling appcfg.py at some point.

[benguild]

@bagatelli Sure, send me an email. No problem

[bagatelli]

@benguild . I've finally managed to deploy my app, however still no luck connecting to CloudSQL.
I've updated my entire app to use the "new appengine" packages as well as set the import of clousql to google.golang.org/appengine/clousql in "appengine.go" on the mysql-driver package.
I then created a client certificate from the CloudSQL Control Panel and update my code accordingly using RegisterTLSConfig as described in the drivers documentation providing the tls config as a parameter as stated in your posts on stackoverflow. My url connection looks like this:

root:password@cloudsql(instance-connection-name-copied-from-cloud-console)/myDatabase?tls=customTls

Still no luck. Error:

Driver: Bad Connection

@arnehormann . This answers your question from your post above.

[benguild]

@bagatelli Try it without TLS.

[bagatelli]

@benguild . Tried without tls. No luck.

Driver: Bad Connection

@pjebs . Not sure if you fully understand the issue here. The driver is not even reaching the database. I do understand what you've mentioned above may be true on a production environment but I'm not even able to establish a single connection to the database so in my case the above does not apply at all.

[benguild]

@bagatelli What I would do is post on Stack Overflow like I did and tag it with: google-cloud-sql ... Someone from Google should respond, I hope.

If you're having this issue then they'd want to have it indexed for others who have the same issue. Respond back here with a link to the question once resolved.

Most likely it still has to do with your implementation because like I said people are using 2nd generation in production and it's out of beta.

[benguild]

@bagatelli Did you remember to turn off the "TLS Required" setting for the instance in the Cloud Console?

[bagatelli]

Thanks @benguild and @pjebs . Will post on S.O. and give an update here if I find the cause.

[bagatelli]

@benguild . I can't see any mention to TLS in the Cloud Console

[bagatelli]

I think you mean the "Allow only SSL Connection". If this is what you're talking about then yes, it's off.

[bagatelli]

Ok guys, I've found the issue. And it was neither the driver, my app or CloudSQL. The project I'm deploying the app onto was create many years ago through the old developers console.
In the Access Control tab in the Cloud Console, there's a label saying " Apps in this project: All authorized." and when a project is created it also creates the default appengine and compute engine accounts. The problem is that this particular project did not have both default accounts and pretty sure that's the issue. What probably happened is that Google did not create those accounts when they migrated appengine to the cloud console which uses IAM & Admin to manage access accounts.
I'm assuming that because when I noticed the default accounts where missing I created a new project and a new cloud sql database and deployed the exact same code onto this new project and voila...All is working just fine.
@benguild @pjebs Really appreciate all you effort helping me tackle this issue.
@benguild I've deleted all the unrelated comments from our prior conversation as I think we are on the same page related to AppEngine/Google. Will send you an email on that later.
I've posted this question on StackOverflow and will answer it with this discovery so other people with same issue might benefit from it.
http://stackoverflow.com/questions/40020782/connect-to-google-sql-cloud-2nd-generation-with-go-on-appengine

Thanks again!

[benguild]

Yeah, if App Engine doesn't have access to the SQL instance then you're going to have a problem. 👍🏻

[julienschmidt]

Fixed by #485

[osembedded]

All I can say is thanks @benguild ! You helped me stop banging my head against the keyboard after 500 open chrome tabs and 5 hours of googling! Hallelujah!!!