使用 App Engine 在本地测试云 SQL 时出现问题

Question

我一直在尝试使用本地App Engine的HikariCP连接到Cloud SQL实例，以便进行查询。每次使用./gradlew appengineRun命令运行 App Engine 时，都会收到java.net.SocketException: already connected错误。当我将其部署到 App Engine 时，这工作正常，但在本地它不起作用。我被难住了。

以下是 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

然后执行查询：

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)
}
}
}
}

我确定这是连接到SQL的正确配置，但是我一直得到这个堆栈跟踪：

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.<init>(HikariPool.java:115)
at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
at appengine.sql.repository.ProfileRepository.<clinit>(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.<init>(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.<init>(PGStream.java:64)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:133)
... 60 more

Answer 1

使用后连接是否正确关闭？应用程序是否有可能在 SQL 实例关闭连接后尝试重用连接？我建议在应用程序中实现一个连接池，以便有效地使用与数据库的连接。一些可能有助于涵盖这些主题的 CloudSQL 文档页面 [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