cloud-sql-jdbc-socket-factory: Unable to connect to CloudSQL databases after certificates expire, application can't recover

Bug Description

Some of our applications are unable to connect to their CloudsQL database after running for a few days. They never recover until we restart them. We’ve enabled debug logging and suspect a problem with refreshing certificates.

We see that all applications continuously verify the certificate expiry:

[bolcom-pro-sre-health-538:europe-west4:sre-health] Now = 2024-07-30T09:04:39.959408416Z, Current client certificate expiration = 2024-07-30T10:04:14.941Z

At some point in time we see a refresh operation scheduled:

Jul 30, 2024 @ 11:04:39.553 DEBUG com.google.cloud.sql.core.RefreshAheadStrategy [bolcom-pro-sre-health-538:europe-west4:sre-health] Refresh Operation: Next operation scheduled at 2024-07-30T10:00:14Z.

Most applications then refresh the certificate at the scheduled time, but a few applications fail to do this and break as seen above.

In the logs below it looks like the refresh never happened at 10:00:14Z. Or at least that an old certificate is still lingering. Because at 12:05:17 the applications tries to use the certificate that expired 2 hours ago.

Jul 30, 2024 @ 12:05:17.156 DEBUG com.google.cloud.sql.core.RefreshAheadStrategy [bolcom-pro-sre-health-538:europe-west4:sre-health] Client certificate has expired. Starting next refresh operation immediately.
Jul 30, 2024 @ 12:05:17.156 DEBUG com.google.cloud.sql.core.RefreshAheadStrategy [bolcom-pro-sre-health-538:europe-west4:sre-health] Now = 2024-07-30T10:05:17.156356729Z, Current client certificate expiration = 2024-07-30T10:04:14.941Z

It looks like refreshing the certificate fails, or at least the application is still using the wrong one. As after connections to the database fail.

Jul 30, 2024 @ 12:05:17.157 DEBUG com.google.cloud.sql.core.Connector [10.25.8.40] Connecting to instance.
Jul 30, 2024 @ 12:05:17.172 DEBUG com.google.cloud.sql.core.Connector [10.25.8.40] Connected to instance successfully.
Jul 30, 2024 @ 12:05:17.174 DEBUG com.zaxxer.hikari.pool.HikariPool HikariPool-1 - Cannot acquire connection from data source org.postgresql.util.PSQLException: The connection attempt failed. at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:358) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:273) at org.postgresql.Driver.makeConnection(Driver.java:446) at org.postgresql.Driver.connect(Driver.java:298) at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:137) at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:360) at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202) at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461) at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:724) at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:703) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) at java.base/java.lang.Thread.run(Thread.java:1583) caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: bad_certificate at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:130) at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:117) at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:365) at java.base/sun.security.ssl.Alert$AlertConsumer.consume(Alert.java:287) at java.base/sun.security.ssl.TransportContext.dispatch(TransportContext.java:204) at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:172) at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1509) at java.base/sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1480) at java.base/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:1066) at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:162) at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:129) at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114) at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:74) at org.postgresql.core.PGStream.receiveChar(PGStream.java:467) at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:683) at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:207) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:262) ... 14 common frames elided
Jul 30, 2024 @ 12:05:17.174 DEBUG com.zaxxer.hikari.pool.PoolBase HikariPool-1 - Failed to create/setup connection: The connection attempt failed.

We haven’t been able to identify what is different between applications that work and fail. In some cases even copies of applications (like one for testing and one for production) behave differently. With one working flawlessly for months, while the other one fails every few days. One thing I have noticed is that these applications all have a relatively low load and can idle for hours before a new database interaction happens. But I’m not sure if that is a clue or just an unrelated factor.

We are currently using the following library versions:

  • spring-cloud-gcp 5.4.1
  • spring-boot 3.3.1
  • HikariCP 5.1.0
  • com.google.cloud.sql:jdbc-socket-factory-core:jar:1.18.0

We did not observe the issue when using:

  • spring-cloud-gcp 4.9.4
  • spring-boot 3.1.9
  • HikariCP 5.0.1
  • com.google.cloud.sql:jdbc-socket-factory-core:jar:1.14.1

We started seeing the issue when using:

  • spring-cloud-gcp 5.4.0
  • spring-boot 3.2.6
  • HikariCP 5.0.1
  • com.google.cloud.sql:jdbc-socket-factory-core:jar:1.18.0

I haven’t been able to narrow it down further yet.

We found the following issue: https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/issues/1314 which looks similar, but was fixed in jdbc-socket-factory-core:jar:1.13.1. We are currently using 1.18.0.

Example code (or command)

No response

Stacktrace

om.zaxxer.hikari.pool.HikariPool HikariPool-1 - Cannot acquire connection from data source org.postgresql.util.PSQLException: The connection attempt failed. at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:358) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:273) at org.postgresql.Driver.makeConnection(Driver.java:446) at org.postgresql.Driver.connect(Driver.java:298) at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:137) at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:360) at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202) at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:461) at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:724) at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:703) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642) at java.base/java.lang.Thread.run(Thread.java:1583) caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: bad_certificate at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:130) at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:117) at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:365) at java.base/sun.security.ssl.Alert$AlertConsumer.consume(Alert.java:287) at java.base/sun.security.ssl.TransportContext.dispatch(TransportContext.java:204) at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:172) at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1509) at java.base/sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1480) at java.base/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:1066) at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:162) at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:129) at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:114) at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:74) at org.postgresql.core.PGStream.receiveChar(PGStream.java:467) at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:683) at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:207) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:262) ... 14 common frames elided
Jul 30, 2024 @ 12:05:17.174 DEBUG com.zaxxer.hikari.pool.PoolBase HikariPool-1 - Failed to create/setup connection: The connection attempt failed.

Steps to reproduce?

Unfortunately I don’t have a good reproduction path. The issue seems to be consistently happening with only a few of our applications. Only happens after working well for a couple of days.

Environment

  1. OS: Debian 12, running on Google Kubernetes Engine
  2. JRE: Azul Zulu 21
  3. Cloud SQL Java Socket Factory version: 1.18.0

Additional Details

No response

About this issue

  • Original URL
  • State: closed
  • Created 2 years ago
  • Comments: 15

Commits related to this issue

Most upvoted comments

I think I’ve managed to enable the workaround:

image

In the week from September 2 to September 9 we had 250 JDBC related error messages in our service. (jsonPayload.message=~"JDBC") Since the changes I made on September 9th we’ve only had 3 JDBC related error message.

When investigating the 3 JDBC message that still happened last week, I couldn’t put them near any certificate expiration/refresh, might just be a weird network thing, or something else that . I’ll continue monitoring!

So to me it looks like v1.20.1 does solve the issue!

@julie0005 Can you please open a separate issue on this repo? Your issue seems unrelated to the original one this thread was created for. This will make it easier for us to help individual issues and not cause confusion to future users who may reference these in the future to get help 😄

Perhaps Google Cloud Authentication library has a bug that affects only Cloud Run.

Note that in @roy-t ’s case, he’s running on GKE (kubernetes).