0

I want a Java Appengine Managed VM application to connect to a 2nd Gen Cloud SQL Instance. There are discrepancies in the documentation - I can't figure out if this is actually supported by Google or not!

https://cloud.google.com/appengine/docs/managed-vms/java/using-cloud-sql states:

4: In the console, grant your App Engine application access to the Google Cloud SQL instance.

But I see no way of doing this. In the Cloud SQL management console, under properties of an instance, there is:

Authorized applications: None

and seemingly no way to authorize applications?

Then on this page https://cloud.google.com/sql/docs/dev-access it states:

Java App Engine Applications

Using the Cloud SQL Proxy is not supported for Java.

So you seemingly cant use the Cloud SQL proxy. The only way I have got it all working is to open the SQL port to the world, so that the managed VM instances can connect to it on its public IP address, but that is a horrific solution!

Is there an actual supported way of doing this? Anyone from Google able to answer?

Community
  • 1
  • 1
DaBeeeenster
  • 1,481
  • 3
  • 16
  • 20

2 Answers2

1

April 2016 Update

We have a new Java library for connecting to Cloud SQL instances from Managed VMs and other environments: https://github.com/GoogleCloudPlatform/cloud-sql-mysql-socket-factory

It's still very new so the usual caveats apply, but we haven't found any issues in our testing.

Old answer:

I think the best option right now is to use the junixsocket library as explained in this post: https://stackoverflow.com/a/34820600

If you are using the maven-war-plugin to package your application, then adding the following two dependencies should be enough:

<dependency>
  <groupId>com.kohlschutter.junixsocket</groupId>
  <artifactId>junixsocket-mysql</artifactId>
  <version>2.0.4</version>
</dependency>
<dependency>
  <groupId>com.kohlschutter.junixsocket</groupId>
  <artifactId>junixsocket-native-common</artifactId>
  <version>2.0.4</version>
</dependency>

For Play Framework, add the following dependencies:

libraryDependencies += "com.kohlschutter.junixsocket" % "junixsocket-mysql" % "2.0.4"
libraryDependencies += "com.kohlschutter.junixsocket" % "junixsocket-native-common" % "2.0.4"

Configure your Play application.conf as follows:

db.default.url="jdbc:mysql:///mydb?socketFactory=org.newsclub.net.mysql.AFUNIXDatabaseSocketFactory&junixsocket.file=/cloudsql/PROJECT_ID:REGION:INSTANCE_NAME"

We hope to offer something in the future that does not require the use of junixsocket or a similar library.

We'll review/fix the documentation at https://cloud.google.com/appengine/docs/managed-vms/java/using-cloud-sql as it has some issues. Thanks for bringing it to our attention.

Community
  • 1
  • 1
Vadim
  • 4,996
  • 1
  • 26
  • 30
  • Thanks for the quick reply. Am I right in saying that the junixsocket solution would mean having to download binary blobs onto the Managed VMs and start hacking around at that level? Sounds like the start of a rabbit hole! – DaBeeeenster Feb 28 '16 at 22:06
  • Kind of crazy that you can't connect to Cloud SQL with Managed VMs in Java right now! – DaBeeeenster Feb 28 '16 at 22:06
  • Yes and no, if your application is setup/packaged how the "hello world" application is setup then all you need to do is add junixsocket as a dependency and the native dependencies will be packaged as well and things should "just work". If your setup is more custom, then there might be more you would need to do for the native libraries to be found. We know it's not optimal and we have some ideas on how to make this better, but we have to work with the limitation that Java is one of the few languages that does not support UNIX sockets out of the box. – Vadim Feb 28 '16 at 22:11
  • Understood Vadim - thanks. I cant see any documentation on the google side about this specifically - is that correct? What is it that is packaging the native dependencies? Does the maven plugin coordinate this with the managed VM provisioning? – DaBeeeenster Feb 29 '16 at 00:05
  • maven-war-plugin takes care of including all dependent jars in the war. junixsocket-native-common jar contains the native libraries. junixsocket uses native-lib-loader (https://github.com/scijava/native-lib-loader) to load the native library from the packaged jar. – Vadim Feb 29 '16 at 00:25
  • I'm using play framework which doesn't use Maven...So I should just need to add that jar to my library for things to work? Do all the managed vms come with the cloud SQL proxy set up or do I need to define that somewhere in my project? How do I point the cloud SQL proxy to the cloud SQL instance? – DaBeeeenster Feb 29 '16 at 20:06
  • Yes, it should work (please report back your results). You need to tell the proxy which instances you will be connecting to: https://cloud.google.com/sql/docs/dev-access#gaev2-csqlv2 – Vadim Feb 29 '16 at 20:58
  • Hi Vadim. I cant see any evidence of the SQL Proxy running on a MVM instance. I'm sshd in, but I cant see any obvious process running that could be the proxy, and in /var/run I can't see anything purporting to be the proxy? Is there a simple way to test that the proxy is running and presenting a socket using mysql-client? – DaBeeeenster Mar 01 '16 at 17:33
  • The sockets are created in /cloudsql directory, you can check there. – Vadim Mar 01 '16 at 17:41
  • There is no /cloudsql directory in the root of the VM – DaBeeeenster Mar 01 '16 at 17:41
  • Can you post a new question with excerpts of your app.yaml / appengine-web.xml related to the proxy and any other steps you took? – Vadim Mar 01 '16 at 17:43
  • OK I have done here http://stackoverflow.com/questions/35732071/managed-vm-not-running-cloud-sql-proxy – DaBeeeenster Mar 01 '16 at 19:46
0

I did finally get it running with a managed VM with this xml:

<?xml version="1.0" encoding="utf-8"?>
<appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
    <application>thmadmin-ben</application>
    <version>master</version>

    <threadsafe>true</threadsafe>
    <vm>true</vm>
    <precompilation-enabled>false</precompilation-enabled>

    <manual-scaling>
        <instances>1</instances>
    </manual-scaling>

    <beta-settings>
        <setting name="cloud_sql_instances" value="xxx-ben:us-east1:yyy"/>
    </beta-settings>
</appengine-web-app>

But after following the rabit hole I dont think its going to be easy to swap out the TCP database connector with the socket based one in the framework Im using (play framework).

REALLY would love to be able to define "allowed" AppEngine projects in the Cloud SQL instance settings - without this ability Im going to have to run on AWS...

DaBeeeenster
  • 1,481
  • 3
  • 16
  • 20