Dropwizard default connection pooling does not expire connections properly

Question

I am using Dropwizard with the Hibernate bundle. After letting it run for some hours/days, any attempt to access the database will result in a

com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 33.304.281 milliseconds ago. Th
e last packet sent successfully to the server was 33.304.283 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should
 consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeout
s, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

[...]
Causing: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.

The config looks something like this:

database:
  # the name of your JDBC driver
  driverClass: com.mysql.cj.jdbc.Driver

  # the username
  user: ...

  # the password
  password: ...

  # the JDBC URL
  url: jdbc:mysql://localhost/my_database

  # any properties specific to your JDBC driver:
  properties:
    charSet: utf8mb4
    hibernate.hbm2ddl.auto: update

  # the maximum amount of time to wait on an empty pool before throwing an exception
  maxWaitForConnection: 1s

  # the SQL query to run when validating a connection s liveness
  validationQuery: "/* MyService Health Check */ SELECT 1"

  # the timeout before a connection validation queries fail
  validationQueryTimeout: 3s

  # the minimum number of connections to keep open
  minSize: 8

  # the maximum number of connections to keep open
  maxSize: 32

  # whether or not idle connections should be validated
  checkConnectionWhileIdle: true

  checkConnectionOnBorrow: true

  # the amount of time to sleep between runs of the idle connection validation, abandoned cleaner and idle pool resizing
  evictionInterval: 10s

  # the minimum amount of time an connection must sit idle in the pool before it is eligible for eviction
  minIdleTime: 1 minute

  maxConnectionAge: 14400s

The hibernate bundle is instantiated from the configuration just like in the DW docs: https://www.dropwizard.io/en/latest/manual/hibernate.html#configuration

The DB wait_timeout is 28800s. I don't understand how the connections do not get either removed due to old age (the time since last package suggested by the error messages is greater than 14400s) or after validation because of

checkConnectionOnBorrow: true

dropwizard Version: 2.0.18
mysql-connector-java Version: 8.0.23

I am out of ideas on how to approach this please help.

score 0 · Answer 1 · answered Oct 07 '22 at 08:35

The problem should be fixed if you change the database.url property to contain autoReconnect=true.

So in your configuration put:

database:
   url: jdbc:mysql://localhost/my_database?autoReconnect=true

In documentation of Connector/J:

autoReconnect

Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, as a last option, investigate setting the MySQL server variable 'wait_timeout' to a high value, rather than the default of 8 hours.

Reference: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-high-availability-and-clustering.html

Dropwizard default connection pooling does not expire connections properly

1 Answers1