Hikari seems to be using closed connections.

[satyamshanker-glean]

We are using com.zaxxer:HikariCP:5.0.1. We recently switched the driver from com.mysql.cj.jdbc.Driver in mysql:mysql-connector-java:8.0.30 to org.mariadb.jdbc.Driver in org.mariadb.jdbc:mariadb-java-client:3.4.1 We use the above configuration in Java Beam pipelines running in GCP Dataflow. In this case, we see cases where Hikari seems to be using connections where the remote MySql server (version 8.0.31) has already closed the connection.
The exact stack trace we see is given below.

Somethings we set on the Hikari Config

• We set the setMaxLifetime to 30 minutes while the wait_timeout is set to 8 hours
• We also use the connection validation query setConnectionTestQuery to "SELECT 1"

Error message from worker: java.sql.BatchUpdateException: java.sql.SQLNonTransientConnectionException: (conn=824651) Socket error org.mariadb.jdbc.export.ExceptionFactory.createBatchUpdate(ExceptionFactory.java:204) org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:1000) org.mariadb.jdbc.ClientPreparedStatement.executeBatchPipeline(ClientPreparedStatement.java:165) org.mariadb.jdbc.ClientPreparedStatement.executeInternalPreparedBatch(ClientPreparedStatement.java:146) org.mariadb.jdbc.BasePreparedStatement.executeBatch(BasePreparedStatement.java:1623) com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127) com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) com.askscio.beam.core.UpsertToSqlBatched.finishBundle(UpsertToSqlBatched.java:119) com.askscio.beam.core.ScioDoFn.finishBundleWithErrorHandling(ScioDoFn.java:268) Caused by: java.sql.SQLNonTransientConnectionException: (conn=824651) Socket error org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:300) org.mariadb.jdbc.export.ExceptionFactory.create(ExceptionFactory.java:366) org.mariadb.jdbc.client.impl.StandardClient.sendQuery(StandardClient.java:868) org.mariadb.jdbc.client.impl.StandardClient.executePipeline(StandardClient.java:927) org.mariadb.jdbc.ClientPreparedStatement.executeBatchPipeline(ClientPreparedStatement.java:165) org.mariadb.jdbc.ClientPreparedStatement.executeInternalPreparedBatch(ClientPreparedStatement.java:146) org.mariadb.jdbc.BasePreparedStatement.executeBatch(BasePreparedStatement.java:1623) com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127) com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java) com.askscio.beam.core.UpsertToSqlBatched.finishBundle(UpsertToSqlBatched.java:119) com.askscio.beam.core.ScioDoFn.finishBundleWithErrorHandling(ScioDoFn.java:268) com.askscio.beam.core.UpsertToSqlBatched$DoFnInvoker.invokeFinishBundle(Unknown Source) org.apache.beam.runners.dataflow.worker.repackaged.org.apache.beam.runners.core.SimpleDoFnRunner.finishBundle(SimpleDoFnRunner.java:222) org.apache.beam.runners.dataflow.worker.SimpleParDoFn.finishBundle(SimpleParDoFn.java:433) org.apache.beam.runners.dataflow.worker.util.common.worker.ParDoOperation.finish(ParDoOperation.java:56) org.apache.beam.runners.dataflow.worker.util.common.worker.MapTaskExecutor.execute(MapTaskExecutor.java:94) org.apache.beam.runners.dataflow.worker.BatchDataflowWorker.executeWork(BatchDataflowWorker.java:304) org.apache.beam.runners.dataflow.worker.BatchDataflowWorker.doWork(BatchDataflowWorker.java:276) org.apache.beam.runners.dataflow.worker.BatchDataflowWorker.getAndPerformWork(BatchDataflowWorker.java:206) org.apache.beam.runners.dataflow.worker.DataflowBatchWorkerHarness$WorkerThread.doWork(DataflowBatchWorkerHarness.java:150) org.apache.beam.runners.dataflow.worker.DataflowBatchWorkerHarness$WorkerThread.call(DataflowBatchWorkerHarness.java:130) org.apache.beam.runners.dataflow.worker.DataflowBatchWorkerHarness$WorkerThread.call(DataflowBatchWorkerHarness.java:117) java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) org.apache.beam.sdk.util.UnboundedScheduledExecutorService$ScheduledFutureTask.run(UnboundedScheduledExecutorService.java:163) java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) java.base/java.lang.Thread.run(Thread.java:833) Caused by: java.net.SocketException: Connection reset by peer java.base/sun.nio.ch.NioSocketImpl.implWrite(NioSocketImpl.java:425) java.base/sun.nio.ch.NioSocketImpl.write(NioSocketImpl.java:445) java.base/sun.nio.ch.NioSocketImpl$2.write(NioSocketImpl.java:831) java.base/java.net.Socket$SocketOutputStream.write(Socket.java:1035) java.base/sun.security.ssl.SSLSocketOutputRecord.deliver(SSLSocketOutputRecord.java:345) java.base/sun.security.ssl.SSLSocketImpl$AppOutputStream.write(SSLSocketImpl.java:1308) org.mariadb.jdbc.client.socket.impl.PacketWriter.writeSocket(PacketWriter.java:861) org.mariadb.jdbc.client.socket.impl.PacketWriter.flush(PacketWriter.java:717) org.mariadb.jdbc.message.client.QueryWithParametersPacket.encode(QueryWithParametersPacket.java:81) org.mariadb.jdbc.client.impl.StandardClient.sendQuery(StandardClient.java:848) ... 24 more

[svendiedrichsen]

Sounds like you might need to set tcpKeepAlive to avoid abandoned connections. The HikariCP Github main page has an important note about it and some interesting links you might want to read through.

[svendiedrichsen]

The mariadb-driver you are using is a JDBC4 driver and the HikariCP doc strongly advises against using the connectionTestQuery config.

[satyamshanker-glean]

Thanks @svendiedrichsen for your help. We will update these settings a try.