Connecting to 2nd Generation MySQL from a Tomcat webapp context running in load-balanced Compute instances?

[James Lampert]

As I get closer to getting a pilot system operational, I'm now running into something I'm having trouble making sense of.

Given:

The application is in Java, a webapp context running in a Tomcat server, on what is intended to be a load-balanced cluster of GCE instances.

And all of these instances share several common storage buckets, and a common Second Generation MySQL instance for their database.

Now, the developer who's in charge of the webapp ran an experimental cluster in his own private Google Cloud project, in which he just opened up the SQL server to the world, and didn't bother with any SSL. But obviously that's not going to fly as we get closer to the real thing.

I'm trying to get closer to the real thing. But how am I supposed to set up the database connections, when I don't know what IP addresses the GCE instances will have?

Looking at the "Authorization" tab for my MySQL instance, I see:

App Engine authorization

All apps in this project are authorized by default. To authorize apps in other projects, follow the steps below.

 Apps in this project: All authorized.

But that's "App Engine." Not Google Compute Engine.

And I see something called "JDBC Socket Factory," but it's on a page about connecting from applications running outside of Google Cloud.

And I see a page on connecting the MySQL Client on a Google Compute instance (which begs the question, "why would I want to do that, when I can connect from my desktop, via something a bit more user-friendly?"). Not connecting a Tomcat webapp context running on a dynamically created instance.

What am I missing here?

[Kamran (Google Cloud Support)]

Hello James,

Please visit this page which describes Cloud SQL Proxy and the proxy options. Running this proxy tool inside your GCE instances you won't need to use static IP addresses for the VMs or whitelist IP addresses on Cloud SQL instance.

I hope this helps.

[Pia Chamberlain]

You can use the Compute Engine page to understand how to use the proxy from Compute Engine. Then, instead of invoking the mysql client, use the same host address for your application.

[James Lampert]

Hmm. I'd seen something about the proxy. But in my experience, "proxy" has always equated with "slow." Is that not the case here?

I heard back from the developer, and while he is already aware of the proxy, he didn't particularly like it, because of local testing difficulties. I think if I can figure out the "Quickstart for Using the Proxy for Local Testing" well enough to at least get a Sequel Pro session connecting through it, I may be able to teach him a few new tricks (which could be pretty satisfying, given that I'm probably twice his age).

[Pia Chamberlain]

Let me know your experience with that Quickstart! I created it specifically for that use case.

On Thu, Mar 15, 2018 at 4:22 PM James Lampert <jam...@touchtonecorp.com> wrote:

Hmm. I'd seen something about the proxy. But in my experience, "proxy" has always equated with "slow." Is that not the case here?

I heard back from the developer, and while he is already aware of the proxy, he didn't particularly like it, because of local testing difficulties. I think if I can figure out the "Quickstart for Using the Proxy for Local Testing" well enough to at least get a Sequel Pro session connecting through it, I may be able to teach him a few new tricks (which could be pretty satisfying, given that I'm probably twice his age).

--
You received this message because you are subscribed to a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/zwF178iv638/unsubscribe.
To unsubscribe from this group and all its topics, 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/4569136a-389f-499a-b56f-c8ca1dcc7259%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Pia Chamberlain |

Senior Technical Writer |

pi...@google.com |

Cloud SQL

[James Lampert]

Well, it seems that the API that I was having trouble enabling appears to have already been enabled. At least, if what shows up in the list of enabled APIs on the "APIs & Services Dashboard" as "Google Cloud SQL" is the same as the " Cloud SQL Admin API" mentioned in the quickstart at https://cloud.google.com/sql/docs/mysql/quickstart-proxy-test.

But while the quickstart mentioned making sure gcloud is installed, and signing on to it, it did NOT say anything about setting application default credentials.

Without doing  anything, I tried:

Jamess-Mac-mini:~ jamesl$ ./cloud_sql_proxy -instances=REDACTED=tcp:3306

2018/03/19 13:47:59 google: could not find default credentials. See https://developers.google.com/accounts/docs/application-default-credentials for more information.

I eventually found something about setting the application default, and tried:

Jamess-Mac-mini:~ jamesl$ gcloud auth application-default login
Your browser has been opened to visit:
    https://accounts.google.com/REDACTED


Credentials saved to file: [REDACTED]
These credentials will be used by any library that requests
Application Default Credentials. 

. . . and then, I tried the proxy again, and got:

Jamess-Mac-mini:~ jamesl$ ./cloud_sql_proxy -instances=REDACTED=tcp:3306

2018/03/19 13:56:30 Listening on 127.0.0.1:3306 for REDACTED
2018/03/19 13:56:30 Ready for new connections

But then, when I tried to connect to 127.0.0.1:3306, whether from the Sequel Pro or from the default command line MySQL client, using SQL user accounts that are globally usable, I get

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

I used my own user ID when I did the "gcloud auth application-default login"; do I instead need to set the application default to the SQL server's service account? I see instructions for creating a service account, at https://cloud.google.com/docs/authentication/getting-started but I'm not entirely sure I understand it. With a service account already in existence, do I just skip directly to step 3 of "Creating a service account"? I tried that, and got:

ERROR: (gcloud.iam.service-accounts.keys.create) PERMISSION_DENIED: Permission iam.serviceAccountKeys.create is required to perform this operation on service account projects/-/serviceAccounts/REDACTED.iam.gserviceaccount.com.

Something else I noticed, and don't understand: much of what the docs say about service accounts say that, in fully qualified form, they should be of the form "<name>@<projectname>.iam.gserviceaccount.com"; the already-in-existence service account that came with the SQL instance (which I have systematically redacted above) is in the form "<name>@<random word>-<random word>-<random number>.iam.gserviceaccount.com"

[James Lampert]

Oh, and in the terminal session where the proxy is running, I get:

2018/03/19 14:03:59 couldn't connect to "REDACTED": ensure that the account has access to "REDACTED" (and make sure there's no typo in that name). Error during createEphemeral for REDACTED: googleapi: Error 403: The client is not authorized to make this request., notAuthorized

[Pia Chamberlain]

James, I'm so sorry you're having trouble with the proxy quickstart. I'm the writer for this page, and I want to make sure it guides you to success. 

I've created an external bug for this issue-- if you have a Google account, you can access it:

https://issuetracker.google.com/issues/76011694

Maybe we could continue the conversation there?

[James Lampert]

On Tuesday, March 20, 2018 at 10:36:05 AM UTC-7, Pia Chamberlain wrote:

. . .

Maybe we could continue the conversation there?

I've just posted a comment to that Issuetracker ticket, including a couple of hypotheses that occurred to me this morning about why this isn't working yet.