[google-cloud-sql-discuss] Re: Cannot connect to Cloud SQL with Java SocketFactory Library

Thanks Emerson, 
Unfortunately, I've tried changing the library, but I still can't get the Dataflow job to connect to Cloud SQL with plain JDBC in the main function.

I've tried to use gcloud auth application-default login command to make sure I'm authenticated, but that still wasn't enough.
I'm also using GOOGLE_APPLICATION_CREDENTIALS environment variable to use a service account key that would let me connect to the Dataflow/Cloud SQL service. 
I still have no idea why I'm receiving connection refused errors. Have you done anything beyond that?

Below is the full stack trace:

org.postgresql.util.PSQLException: Connection to google:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:265)

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)

at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)

at org.postgresql.Driver.makeConnection(Driver.java:450)

at org.postgresql.Driver.connect(Driver.java:252)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:270)

at org.eldorado.sigma.pipeline.PrevisaoJsonPipeline.postProcessingPipeline(PrevisaoJsonPipeline.java:124)

at org.eldorado.sigma.pipeline.PrevisaoJsonPipeline.main(PrevisaoJsonPipeline.java:138)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:282)

at java.lang.Thread.run(Thread.java:748)

Caused by: java.net.ConnectException: Connection refused: connect

at java.net.DualStackPlainSocketImpl.connect0(Native Method)

at java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79)

at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)

at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206)

at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)

at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:172)

at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)

at java.net.Socket.connect(Socket.java:589)

at sun.security.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:673)

at sun.security.ssl.SSLSocketImpl.<init>(SSLSocketImpl.java:432)

at sun.security.ssl.SSLSocketFactoryImpl.createSocket(SSLSocketFactoryImpl.java:88)

at com.google.cloud.sql.core.SslSocketFactory.createAndConfigureSocket(SslSocketFactory.java:190)

at com.google.cloud.sql.core.SslSocketFactory.create(SslSocketFactory.java:152)

at com.google.cloud.sql.postgres.SocketFactory.createSocket(SocketFactory.java:50)

at org.postgresql.core.PGStream.<init>(PGStream.java:60)

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:144)

... 14 more

On Wednesday, November 14, 2018 at 6:59:49 PM UTC-2, Emerson Moraes wrote:

Hi, I do not know what is happening exactly, but I have a Dataflow streaming job that uses Postgres Cloud SQL with JdbcIO and with plain Jdbc inside a custom function. The only diference between you and me is that I'm using postgres-socket-factory on 1.0.10 version and consequently my url connection use diferent params. For example: jdbc:postgresql://google/<database_name>?useSSL=false&socketFactoryArg=<instance_name>&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<user_login>&password=<user_password>.

This URL make connection with my Cloud SQL both locally and in Google Cloud Dataflow environments. The job is running now. I think that you can try to use this version os postgres socket to make a test.

And why is not possible to use delete with JdbcIO on your case? It's a project limitation? Because I'm using JdbcIO to make delete statements.

Example:

movementsToDelete.apply("DeleteMovimentacao", JdbcIO.<Movimentacao> write()

            .withDataSourceConfiguration(

                    JdbcIO.DataSourceConfiguration

                            .create("org.postgresql.Driver", Constants.POSTGRES_URL)

            )

            .withStatement(DELETE_MOVIMENTACAO_SQL)

            .withPreparedStatementSetter(new MovimentacaoDeletePreparedStatementSetter()));

My Apache Beam version is 2.5.0.

Att.,

Emerson Leite de Moraes

terça-feira, 13 de Novembro de 2018 às 15:09:56 UTC-2, Daniel Franco escreveu:

Hello, I'm using Cloud Dataflow to run some pipelines using Cloud SQL as a sink in my personal account. After the pipeline is executed, I want to make a connection to Cloud SQL to run a delete statement. I can't run deletes with JdbcIO (only selects and inserts). Even though I can connect to Cloud SQL with JdbcIO, I cannot connect to it using plain Jdbc. 

Below is the code I'm using to connect to my instance

static void cleanUpPipeline() {

String jdbcUrl = String.format(

    "jdbc:postgresql://google/%s?cloudSqlInstance=%s"

        + "&socketFactory=com.google.cloud.sql.postgres.SocketFactory",

    "xxxxxx",

    "xxxxxx");

try {

Class.forName("org.postgresql.Driver");

Connection connection = DriverManager.getConnection(jdbcUrl, "xxxxxx", "xxxxxx");

} catch (SQLException | ClassNotFoundException e) {

e.printStackTrace();

}

}

My pom.xml has the following dependencies:

<dependency>

    <groupId>com.google.cloud.sql</groupId>

    <artifactId>postgres-socket-factory</artifactId>

    <version>1.0.11</version>

</dependency>

<dependency>

    <groupId>org.postgresql</groupId>

    <artifactId>postgresql</artifactId>

    <version>42.2.5</version>

</dependency>

The error I receive is: 

INFO: Connecting to Cloud SQL instance [xxxxxxxxx] via ssl socket.

nov 13, 2018 2:53:49 PM com.google.cloud.sql.core.SslSocketFactory getInstance

INFO: First Cloud SQL connection, generating RSA key pair.

nov 13, 2018 2:53:51 PM com.google.cloud.sql.core.SslSocketFactory fetchInstanceSslInfo

INFO: Obtaining ephemeral certificate for Cloud SQL instance [xxxxxx].

nov 13, 2018 2:53:54 PM com.google.cloud.sql.core.SslSocketFactory createAndConfigureSocket

INFO: Connecting to Cloud SQL instance [xxxxxxxx] on IP [xxxxxxx]

org.postgresql.util.PSQLException: Connection to google:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

I believe I'm doing everything right here. Can anyone help me? 

--
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+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/81019a42-dadb-44db-87d4-cea260c89dcd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.