How to connect from a pool of compute engine instances to Cloud SQL DB in the same project?

[Roshan Dawrani]

Hi,

If I have a pool of Compute Engine instances (behind a load-balancer with a static IP), and I want to connect from Java apps on these instances to a Cloud Sql database in the same project, what's the way to about it?

1) These instances in the load balancer don't have static external IPs, so I can't authorize access to Cloud SQL by IP addresses.

2) "com.mysql.jdbc.GoogleDriver" is not available on Compute Engine, as it is on App Engine.

3) If I try to use the stock MySql JDBC driver, it doesn't understand a connection string like:

    "jdbc:google:mysql://__instance_name__/__db_name__>?user=__user__&password=__password__";

    and fails saying "No suitable driver found for jdbc:google:mysql://__instance_name__/__db_name__>?user=__user__&password=__password__"

I can't believe I can't easily connect from Compute Engine instances to a Cloud SQL database of the same Google Cloud project.

Is my only option to use "Connecting using the Cloud SQL Proxy" approach as described on https://cloud.google.com/sql/docs/compute-engine-access? That seems significant amount of work to be done automatically on instances that will come to the pool and go away at will!

Could someone advise please?

Thanks,

Roshan

[Vadim Berezniker]

Have you looked at https://github.com/GoogleCloudPlatform/cloud-sql-mysql-socket-factory?

--
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/dc37088f-5f15-4e10-8c9f-383cca2dece0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Roshan Dawrani]

Is there any reasonable documentation available for how to use it? I googled and only saw some questions, etc in conjunction with AppEngine Flexible Enviroment.

The github repo README only says that JDBC url should look like "jdbc:mysql://google/mydb?cloudSqlInstance=foo:bar:baz&socketFactory=com.google.cloud.sql.mysql.SocketFactory"

Where do I pass the login credentials for the user I am logging in as to the database server?

On Thursday, 22 September 2016 00:24:50 UTC+5:30, Vadim Berezniker wrote:

Have you looked at https://github.com/GoogleCloudPlatform/cloud-sql-mysql-socket-factory?

On Wed, Sep 21, 2016 at 11:48 AM Roshan Dawrani <roshan...@gmail.com> wrote:

Hi,

If I have a pool of Compute Engine instances (behind a load-balancer with a static IP), and I want to connect from Java apps on these instances to a Cloud Sql database in the same project, what's the way to about it?

1) These instances in the load balancer don't have static external IPs, so I can't authorize access to Cloud SQL by IP addresses.

2) "com.mysql.jdbc.GoogleDriver" is not available on Compute Engine, as it is on App Engine.

3) If I try to use the stock MySql JDBC driver, it doesn't understand a connection string like:

    "jdbc:google:mysql://__instance_name__/__db_name__>?user=__user__&amp;password=__password__";

    and fails saying "No suitable driver found for jdbc:google:mysql://__instance_name__/__db_name__>?user=__user__&amp;password=__password__"

I can't believe I can't easily connect from Compute Engine instances to a Cloud SQL database of the same Google Cloud project.

Is my only option to use "Connecting using the Cloud SQL Proxy" approach as described on https://cloud.google.com/sql/docs/compute-engine-access? That seems significant amount of work to be done automatically on instances that will come to the pool and go away at will!

Could someone advise please?

Thanks,

Roshan

--
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-discuss+unsub...@googlegroups.com.

[Vadim Berezniker]

This uses the regular mysql driver so pass in the credentials in the same way you would for the regular mysql driver (either via the URL or via properties). 

This library only overrides how the connection in established to MySQL.

On Wed, Sep 21, 2016 at 12:18 PM Roshan Dawrani <roshan...@gmail.com> wrote:

Is there any reasonable documentation available for how to use it? I googled and only saw some questions, etc in conjunction with AppEngine Flexible Enviroment.

The github repo README only says that JDBC url should look like "jdbc:mysql://google/mydb?cloudSqlInstance=foo:bar:baz&socketFactory=com.google.cloud.sql.mysql.SocketFactory"

Where do I pass the login credentials for the user I am logging in as to the database server?

On Thursday, 22 September 2016 00:24:50 UTC+5:30, Vadim Berezniker wrote:

Have you looked at https://github.com/GoogleCloudPlatform/cloud-sql-mysql-socket-factory?

On Wed, Sep 21, 2016 at 11:48 AM Roshan Dawrani <roshan...@gmail.com> wrote:

Hi,

If I have a pool of Compute Engine instances (behind a load-balancer with a static IP), and I want to connect from Java apps on these instances to a Cloud Sql database in the same project, what's the way to about it?

1) These instances in the load balancer don't have static external IPs, so I can't authorize access to Cloud SQL by IP addresses.

2) "com.mysql.jdbc.GoogleDriver" is not available on Compute Engine, as it is on App Engine.

3) If I try to use the stock MySql JDBC driver, it doesn't understand a connection string like:

    "jdbc:google:mysql://__instance_name__/__db_name__>?user=__user__&amp;password=__password__";

    and fails saying "No suitable driver found for jdbc:google:mysql://__instance_name__/__db_name__>?user=__user__&amp;password=__password__"

I can't believe I can't easily connect from Compute Engine instances to a Cloud SQL database of the same Google Cloud project.

Is my only option to use "Connecting using the Cloud SQL Proxy" approach as described on https://cloud.google.com/sql/docs/compute-engine-access? That seems significant amount of work to be done automatically on instances that will come to the pool and go away at will!

Could someone advise please?

Thanks,

Roshan

--
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/dc37088f-5f15-4e10-8c9f-383cca2dece0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

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/9e5f50ea-0b8f-4595-aeea-7a5f34f9c1f2%40googlegroups.com.

[Roshan Dawrani]

Just gave it a try, but got the following errors:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Caused by: java.net.UnknownHostException: google: unknown error

A) My JDBC connection string looked like (as per the library's documentation on GitHub):

"jdbc:mysql://google/__my_db__name?cloudSqlInstance=__my_cloud_sql_instance_connection_name_as_foo:bar:baz&socketFactory=com.google.cloud.sql.mysql.SocketFactory"

Is the format ""jdbc:mysql://google/...." correct for a compute engine environment?

B) I also tried switching ""jdbc:mysql://111.222.111.222/__my_db_name?cloudSqlInstance...." format (where 111.222.111.222 is my Cloud SQL DB instance's IP address), but I still get the errors:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Caused by: java.net.ConnectException: Connection timed out

Am I doing something wrong? Both my compute engine instance and Cloud Sql DB instance are under the same project and compute engine's default service account has an Editor role too.

On Thursday, 22 September 2016 00:51:04 UTC+5:30, Vadim Berezniker wrote:

This uses the regular mysql driver so pass in the credentials in the same way you would for the regular mysql driver (either via the URL or via properties). 

This library only overrides how the connection in established to MySQL.

On Wed, Sep 21, 2016 at 12:18 PM Roshan Dawrani <roshan...@gmail.com> wrote:

Is there any reasonable documentation available for how to use it? I googled and only saw some questions, etc in conjunction with AppEngine Flexible Enviroment.

The github repo README only says that JDBC url should look like "jdbc:mysql://google/mydb?cloudSqlInstance=foo:bar:baz&socketFactory=com.google.cloud.sql.mysql.SocketFactory"

Where do I pass the login credentials for the user I am logging in as to the database server?

On Thursday, 22 September 2016 00:24:50 UTC+5:30, Vadim Berezniker wrote:

Have you looked at https://github.com/GoogleCloudPlatform/cloud-sql-mysql-socket-factory?

On Wed, Sep 21, 2016 at 11:48 AM Roshan Dawrani <roshan...@gmail.com> wrote:

Hi,

If I have a pool of Compute Engine instances (behind a load-balancer with a static IP), and I want to connect from Java apps on these instances to a Cloud Sql database in the same project, what's the way to about it?

1) These instances in the load balancer don't have static external IPs, so I can't authorize access to Cloud SQL by IP addresses.

2) "com.mysql.jdbc.GoogleDriver" is not available on Compute Engine, as it is on App Engine.

3) If I try to use the stock MySql JDBC driver, it doesn't understand a connection string like:

    "jdbc:google:mysql://__instance_name__/__db_name__>?user=__user__&amp;password=__password__";

    and fails saying "No suitable driver found for jdbc:google:mysql://__instance_name__/__db_name__>?user=__user__&amp;password=__password__"

I can't believe I can't easily connect from Compute Engine instances to a Cloud SQL database of the same Google Cloud project.

Is my only option to use "Connecting using the Cloud SQL Proxy" approach as described on https://cloud.google.com/sql/docs/compute-engine-access? That seems significant amount of work to be done automatically on instances that will come to the pool and go away at will!

Could someone advise please?

Thanks,

Roshan

--
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-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/dc37088f-5f15-4e10-8c9f-383cca2dece0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsub...@googlegroups.com.

[Roshan Dawrani]

Tried with the port 3306 also, but same errors:

C) I also tried switching ""jdbc:mysql://111.222.111.222:3306/__my_db_name?cloudSqlInstance...." format (where 111.222.111.222 is my Cloud SQL DB instance's IP address), but I still get the errors:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Caused by: java.net.ConnectException: Connection timed out

[Roshan Dawrani]

Went ahead with using Google Cloud Sql proxy. Now able to connect my GCE instance to the Cloud SQL DB. 

Some details captured at https://roshandawrani.wordpress.com/2016/09/22/connecting-from-gce-gae-java-to-google-cloud-sql/, just in case someone is in a similar boat and unclear a bit.

Cheers.

[Vadim Berezniker]

On Wed, Sep 21, 2016 at 12:55 PM Roshan Dawrani <roshan...@gmail.com> wrote:

Just gave it a try, but got the following errors:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Caused by: java.net.UnknownHostException: google: unknown error

This error indicates that the custom socket factory is not being used at all.

Can you paste the full stack trace?

What version of the mysql jdbc driver are you using?

I'd like to understand why it didn't work for you.

 

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/dc37088f-5f15-4e10-8c9f-383cca2dece0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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/9e5f50ea-0b8f-4595-aeea-7a5f34f9c1f2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

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/2109ff2d-1aaa-4c7d-a625-457f5d0f7658%40googlegroups.com.

[Roshan Dawrani]

To return to that error with custom Java socket factory, I'll need to undo some changes my google-cloud-proxy based environment (I linked its setup to start-up scripts of the GCE instance pool members). Also, the GCE + Cloud SQL environment is in heavy, temporary use tonight. So, I'll do the changes to temporarily start using cloud-sql-mysql-socket-factory again tomorrow morning my time (India) and get you the stack trace and other info for analysis.

Cheers.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/dc37088f-5f15-4e10-8c9f-383cca2dece0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/9e5f50ea-0b8f-4595-aeea-7a5f34f9c1f2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsub...@googlegroups.com.

[Vadim Berezniker]

Thanks Roshan. 

In the meantime, can you tell me what version of the mysql driver you are depending on in your project?

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/dc37088f-5f15-4e10-8c9f-383cca2dece0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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/9e5f50ea-0b8f-4595-aeea-7a5f34f9c1f2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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/2109ff2d-1aaa-4c7d-a625-457f5d0f7658%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

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/0721a994-eeee-4dc4-92fd-4c056db7b941%40googlegroups.com.

[Roshan Dawrani]

I have mysql-connector-java-5.1.39-bin.jar connecting to MySQL 5.7 (Cloud SQL) db.

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/dc37088f-5f15-4e10-8c9f-383cca2dece0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/9e5f50ea-0b8f-4595-aeea-7a5f34f9c1f2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/2109ff2d-1aaa-4c7d-a625-457f5d0f7658%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsub...@googlegroups.com.

[Roshan Dawrani]

Hi Vadim,

I tried putting back "cloud-sql-mysql-socket-factory" again just now, and didn't run into any issues this time. I must have made a mistake last time I tried it.

I just had to additionally introduce the socket factory's dependency "google-api-services-sqladmin" jar and then it was fine.

So, finally I used mysql-connector-java-5.1.39-bin.jar, mysql-socket-factory-1.0.1.jar, above-mentioned google-api-services-sqladmin-v1beta4-rev29-1.22.0.jar jar, and the following test JDBC URL:

"jdbc:mysql://google/___my_db_name___?cloudSqlInstance=___my_cloud_db_conn_name_like_foo:bar:baz___&socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=___my_db_user___&password=___my_db_user___"

Thanks for your time on helping on this.

Cheers,

Roshan

To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/dc37088f-5f15-4e10-8c9f-383cca2dece0%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/9e5f50ea-0b8f-4595-aeea-7a5f34f9c1f2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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-discuss+unsub...@googlegroups.com.