Frequent Connection Close issues in Tessera to MySQL DB

[ghost]

System information

Quorum: v22.4.0
Tessera: v22.1.1 (running 3 replicas of Tessera on AKS cluster)
Database: MySQL v8.0 as external DB for Tessera

Actual behaviour

Frequent connection close issues in Tessera while trying to connect to MySQL DB

Tessera Logs:
2022-07-27 12:52:07.763 [qtp1325269327-31] WARN  com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@1dab34f6 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.2022-07-27 12:52:07.764 [qtp1325269327-31] WARN  com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@5a2ccc25 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.2022-07-27 12:52:07.765 [qtp1325269327-31] WARN  com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@6597e150 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.2022-07-27 12:52:07.766 [qtp1325269327-31] WARN  com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@538134a1 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.2022-07-27 12:52:07.767 [qtp1325269327-31] WARN  com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@17c9a6a3 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.2022-07-27 12:52:07.883 [qtp1325269327-31] INFO  c.q.t.a.c.RawTransactionResource - Exit Request : POST : /storeraw2022-07-27 12:52:07.883 [qtp1325269327-31] INFO  c.q.t.a.c.RawTransactionResource - Response for storeraw : 200 OK2022-07-27 12:52:10.390 [pool-4-thread-1] INFO  c.q.t.p.p.PartyInfoBroadcaster - Started PartyInfo polling round2022-07-27 12:52:10.391 [pool-4-thread-1] INFO  c.q.t.p.p.PartyInfoBroadcaster - Finished PartyInfo polling round

Client (web3js-quorum) error logs while initiating transactions:

Returned error: 404 status: Recipient not found for key: <<counter party tessera public key>>

Note: After few retries, transactions are successfully posted to Quorum, once Tessera to MySQL DB connection is established.

Expected behaviour

There should not be any connection drops/close issues at Tessera's end. We should get successful response in the first attempt itself when client posts the transactions.

Please suggest, how to keep connections alive as logs suggests to consider using a shorter maxLifetime?

[macfarla]

A similar issue here -
https://stackoverflow.com/questions/60310858/possibly-consider-using-a-shorter-maxlifetime-value-hikari-connection-pool-spr
Could it be that the db timeout is lower than the hikari maxLifetime property

The problem is that the default value of the spring.datasource.hikari.maxLifetime property (default of 30 minutes, https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby) is higher than the database's wait_timeout, 10 minutes in my case.
So you have two options, either decrease the hikari.maxLifetime below 10 minutes, or increase the database's wait_timeout property.

[bipeen-kumar]

@macfarla We already have the database's wait_timeout property set as 30 min & 30 sec