Issues with testing Cloud SQL locally using App Engine

Question

I've been trying to connect to a Cloud SQL instance using HikariCP from App Engine locally so I can make queries. Every time I run App Engine using the ./gradlew appengineRun command, I get a java.net.SocketException: already connected error. This works fine when I deploy it to App Engine, but locally it just won't work. I'm stumped.

Here's the configuration for Hikari:

val config = HikariConfig().apply {
    jdbcUrl = "jdbc:postgresql://google/[DB-NAME]"
    username = "[USERNAME]"
    password = "[PASSWORD]"
    addDataSourceProperty("cloudSqlInstance", "[INSTANCE-CONNECTION-NAME")
    addDataSourceProperty("socketFactory", "com.google.cloud.sql.postgres.SocketFactory")
}

private val dataSource = HikariDataSource(config)

private val connection = dataSource.connection

And then to execute the query:

connection.use { connection ->
    connection.prepareCall("SELECT EXISTS(SELECT 1 FROM profiles WHERE username = '$username')").use { statement ->
        statement.executeQuery().use { resultSet ->
            try {
                val exists = generateSequence {
                    if (resultSet.next()) resultSet.getBoolean(1) else null
                }.toList()
                onComplete(exists.any { it }, null)
            } catch (e: Exception) {
                onComplete(false, e)
            }
        }
    }
}

I was certain this was the correct configuration to connect to SQL, but I keep getting this stacktrace:

Caused by: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: The connection attempt failed.
    at com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException(HikariPool.java:597)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:576)
    at com.zaxxer.hikari.pool.HikariPool.&lt;init&gt;(HikariPool.java:115)
    at com.zaxxer.hikari.HikariDataSource.&lt;init&gt;(HikariDataSource.java:81)
    at appengine.sql.repository.ProfileRepository.&lt;clinit&gt;(ProfileRepository.kt:34)
    ... 48 more
Caused by: org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:262)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:67)
    at org.postgresql.jdbc.PgConnection.&lt;init&gt;(PgConnection.java:216)
    at org.postgresql.Driver.makeConnection(Driver.java:406)
    at org.postgresql.Driver.connect(Driver.java:274)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:353)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:473)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:562)
    ... 51 more
Caused by: java.net.SocketException: already connected
    at java.net.Socket.connect(Socket.java:569)
    at sun.security.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:668)
    at org.postgresql.core.PGStream.&lt;init&gt;(PGStream.java:64)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:133)
    ... 60 more

I've tried to run your code locally and it works fine for me. Libraries are: implementation("com.zaxxer:HikariCP:3.3.1") implementation("org.postgresql:postgresql:42.2.6") implementation("com.google.cloud.sql:postgres-socket-factory:1.0.14") Maybe you have some background process from previous run? Have you tried to reboot system? — Rinat Suleimanov
@RinatSuleimanov yeah I've tried rebooting, but I'm guessing the background processes are still open. How would I check that from IntelliJ? — user2759839
If reboot didn't help, I believe that problem somewhere else. Could you connect to database directly using external IP? Or, I'm guessing, you code is trying to create connection object multiple times? Could you share please whole code example? — Rinat Suleimanov

Harif Velarde Harif Velarde · Accepted Answer · 2019-09-02T20:28:14

Are the connection being properly closed after use? Is it possible the application is attempting to reuse connection after they have been closed by the SQL instance? I'd advise implementing a connection pool within the application for efficient use of connections to the db. Some CloudSQL documentation pages that may help cover these topics [1][2].

[1] https://cloud.google.com/sql/docs/mysql/manage-connections#opening_and_closing_connections

[2] https://cloud.google.com/sql/docs/mysql/diagnose-issues

Issues with testing Cloud SQL locally using App Engine

1 Answers