Error connecting with MySQL Google Cloud

[blecua84]

Hello,

I'm facing problems with my microservice built with spring-data-r2dbc and jasync-r2dbc-mysql. I'm following the proposed nomenclature for creating a ConnectionFactory object:

import com.github.jasync.r2dbc.mysql.JasyncConnectionFactory;
import com.github.jasync.sql.db.mysql.pool.MySQLConnectionFactory;
import com.github.jasync.sql.db.mysql.util.URLParser;
import io.r2dbc.spi.ConnectionFactory;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.r2dbc.config.AbstractR2dbcConfiguration;

import static java.nio.charset.StandardCharsets.UTF_8;

@Configuration
public class DBConfiguration extends AbstractR2dbcConfiguration {

@Override
    public ConnectionFactory connectionFactory() {
        String url = "mysql://[user]:[password]@[host]/[schema]";
        return new JasyncConnectionFactory(new MySQLConnectionFactory(URLParser.INSTANCE.parseOrDie(url, UTF_8)));
    }
}

Deploying this configuration in my local, hitting my SQL Instance in Google Cloud works perfectly, but when I deploy it in my Google Cloud project, trying to hit the same SQL Instance in the same Google Cloud project, I've got is this error:

Failed to obtain R2DBC Connection; nested exception is io.netty.channel.ConnectTimeoutException: connection timed out: XX.XXX.XXX.XX:3306

I haven't found yet any other documentation about this error and I was wondering if I'm doing something wrong and if you guys have more information about.

Thanks in advance.

Jose

[mp911de]

Thanks for report. The issue doesn't look like a Spring Data issue but rather either an environmental issue or a driver issue. Reporting it (after making sure it's a driver issue) at https://github.com/jasync-sql/jasync-sql makes more sense. I assume using R2DBC MySQL (https://github.com/mirromutth/r2dbc-mysql) will result in the same issue.

Out of curiosity, what's required for a regular MySQL Connector/J to connect to MySQL? Do you need to include any specific parameters to make it work?

[blecua84]

Hello Mark,

Thank you for your quick response.

Actually, not really, you don't need anything else to make it work. It seems working in my local environment. In Google Cloud, I followed the official documentation(https://cloud.google.com/sql/docs/mysql/connect-app-engine).

So, should I have to raise a ticket in https://github.com/jasync-sql/jasync-sql? I can do a test using https://github.com/mirromutth/r2dbc-mysql as well just for checking if it produces the same problem.

Kind regards,

Jose

[mp911de]

Thanks for the pointer. These are definitively issues between the driver and Google Cloud.
I suppose that both, driver projects and GCP have some work here to do in order to make this work.

As GCP SQL also touches Postgres, the R2DBC Postgres driver is affected, too. Do you have a contact to GCP folks so we could elaborate on a potential approach? Paging @mirromutth @oshai @meltsufin

Frankly, I have no idea what happens inside of https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory so we would require some details from GCP folks to understand what happens there and what a driver would need to consider. Also, we need to consider that all this needs to happen in a non-blocking way.

Related ticket in R2DBC Postgres: pgjdbc/r2dbc-postgresql#43

Because we have currently no better place to discuss matters, I'm leaving this ticket open.

[meltsufin]

@blecua84
There are several ways to connect to Cloud SQL instances.

1. The most basic one is to just use the instance public IP, but it's least secure. Have you tried it?
2. Using a Cloud SQL Proxy which gives a local IP address and tunnels the connection to the actual Cloud SQL instance. This is fine to use locally or on Google Cloud Compute, but is not available in the serverless environment like App Engine Standard.
3. Use a SocketFactory with the JDBC Driver. This is the recommended way because it's secure and doesn't require exposing a public IP for your Cloud SQL server. However, AFAIK there is no support for it in the reactive drivers. See: Support custom connection SocketFactory pgjdbc/r2dbc-postgresql#43 that Mark has already pointed to.
4. Private IP. VPC is the new option that is currently in Beta for App Engine Standard. It should work with all the drivers and still be secure without the need for SocketFactory. I would give this one a try.

cc/ @saturnism

[blecua84]

Hi @meltsufin

I haven't tried with a private IP because I didn't know I could. I will try it.

Are you sure this option is working with reactive drivers?

Thank you!

Jose

[mp911de]

@meltsufin I'd like to explore the SocketFactory approach, what it is doing and where R2DBC drivers could integrate with it. Probably a good topic to discuss during S1P and I'll add the outcome to this ticket.

[blecua84]

@mp911de does R2DBC not work with the Private IP option?

[meltsufin]

Last I heard from @saturnism, Private IP works, at least with the postgresql R2DBC driver.

[blecua84]

Ok @meltsufin I will check it for mysql R2DBC driver.

Thank you!

[mp911de]

/cc @saturnism

[saturnism]

afaik, the socket factory does this:

1. discover gcp credentials via well-known methods
2. use that credential to connect to cloudsql API to retrieve an ephemeral cert
3. create a socket factory that has the cert so that a ssl connection can be established w/ the cert
4. optionally, it auto discovers environments (e.g., App Engine) that has unix socket domain files, so that connections are made to the unix socket domain file.

It seems like most of the common logic is captured in https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/tree/master/core/src/main/java/com/google/cloud/sql/core

And produces a java.net.Socket: https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/blob/master/core/src/main/java/com/google/cloud/sql/core/CoreSocketFactory.java#L218

[mirromutth]

Hi @saturnism ,

I found the sample of Node.js and Python in the Connecting to Cloud SQL from App Engine.

See https://github.com/GoogleCloudPlatform/python-docs-samples/blob/master/cloud-sql/mysql/sqlalchemy/main.py , and https://github.com/GoogleCloudPlatform/nodejs-docs-samples/blob/master/cloud-sql/mysql/mysql/server.js .

Looks like they just need set a unix socket path in connection options to work properly, isn't it? If that so, we should use DomainSocketAddress in Netty.

BTW, what is the difference between Java implementation and Node.js/Python implementations? Sure, maybe the implementations of Node.js/Python can't be called "implementation" because they're just an option parameter in the sample code.

Thanks for your time.

[mp911de]

Independently of the Google integration, it would make sense to provide unix domain socket connectivity for MySQL and Postgres drivers.

[mp911de]

See also pgjdbc/r2dbc-postgresql#191.

[mp911de]

Unix Domain Socket and SSL Context customization are now in place for R2DBC Postgres. I was trying to make the integration work using https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory. It turns out that we require access to KeyManagerFactory and TrustManagerFactory. All these details are encapsulated and it's not possible from the outside to get hold of these.

R2DBC uses netty so we cannot use a regular SocketFactory as we require non-blocking Channels.

It would make sense to extend the GCP support, ideally by wrapping Postgres and MySQL drivers in an R2DBC facade (ConnectionFactory) to encapsulate the key management and endpoint management.

Happy to guide whoever wants to pick up on that end to enable R2DBC Postgres with GCP. I assume that we will see similar support in the R2DBC MySQL driver (/cc @mirromutth).

[masoudparvari]

@mp911de So does it mean even after the release of r2dbc-postgresql 0.8.0.RELEASE and support of Unix Domain Socket, we still can not connect to gcp postgres ? It's really disapponting as it will stop us and many other from using r2dbc in production

[mp911de]

R2DBC Postgres exposes all features so that the GCP team can provide an extension. All integration happens in the scope of GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#164 and will likely be shipped as add-on to R2DBC Postgres.

GCP folks outlined several access methods to connect even without the add-on to GCP database instances.

You might want to leave a comment in the linked ticket so they know about your use case.

[masoudparvari]

R2DBC Postgres exposes all features so that the GCP team can provide an extension. All integration happens in the scope of GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#164 and will likely be shipped as add-on to R2DBC Postgres.

GCP folks outlined several access methods to connect even without the add-on to GCP database instances.

You might want to leave a comment in the linked ticket so they know about your use case.

Thanks for the info. I did.

[mp911de]

Closing this ticket as the concern is being handled in the scope of the GCP ticket.

[saturnism]

Thanks @mp911de for helping w/ the hooks!

[mp911de]

R2DBC is GA so you can consume Postgres and MySQL drivers directly from Maven Central.