I’ve been developing a DropWizard application which uses JDBI to connect to a MySQL database. Everything seemed to be going great, just following along with the example configuration in the DropWizard documentation.
So, I decided to take it out for a spin onto some dev and then test servers and things still seemed great. But after awhile, MySQL just didn’t want to return my calls anymore and would drop the connection.
Why? It turns out to be a fairly common issue, due to MySQL dropping connections without notifying the client (in this case, my DropWizard app). Connections can get stale, even in a connection pool, and timeout. When your application tries to contact the database it expects a response, and since the connection is already dropped on the other end, you get none.
In my case, I got some errors like this when the application would try to hit with a stale connection:
ERROR io.dropwizard.jersey.errors.LoggingExceptionMapper: Error handling a request: cd0e5e20cac433c4 ! java.net.SocketException: Broken pipe ! at java.net.SocketOutputStream.socketWrite0(Native Method)
~[na:1.8.0_45] ! at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
~[na:1.8.0_45] ! at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
~[na:1.8.0_45] ! at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
~[na:1.8.0_45] ! at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
~[na:1.8.0_45] ! ... ! ... 71 common frames omitted ! Causing: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 10,915,162 milliseconds ago. The last packet sent successfully to the server was 10,915,164 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 timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem. ! at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
~[na:1.8.0_45] ! at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
~[na:1.8.0_45] ! at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
~[na:1.8.0_45] ! at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
~[na:1.8.0_45] ! ... ! ... 65 common frames omitted ! Causing: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was ...Luckily even the error message gives you suggestions to fix this. I decided to do all the things to get some sleep and threw the following configurations at it. Seems to work, but I’d like to tweak things a little bit to clean it up. In particular, some testing with/without autoReconnect, since that can mask some other issues.
In the database url I added autoReconnect=true like so:
url: jdbc:mysql://{database_host}:{database_port}/{database_name}?autoReconnect=trueAnd then threw some configurations to validate when a connection is taken and alleviate stale connections by giving a max age.
validationQuery: '/* Database Health Check */ SELECT 1'
validationQueryTimeout: 3s
minSize: 8
maxSize: 32
maxWaitForConnection: 1s
evictionInterval: 10s
checkConnectionWhileIdle: false
minIdleTime: 1 minute
// Above configs from DropWizard example, below was added
checkConnectionOnBorrow: true
maxConnectionAge: 14400s