tldr: If you see a "last packet sent to the server was XXX ms ago" error, you may want to upgrade your version of the mysql-connector-java library.
The Symptoms
I had a simple Java app in production that was using JDBC to talk to a MySQL DB. Everything was running great: DB calls were taking 2 ms on average and 8 ms in the 99th percentile. However, once every 4-8 hours, a strange error would pop up that looked something like this:
I can understand the occasionally slow query, but 28800126 ms? EOFException? What's going on here?
Lots of ineffective options
As usual, I turned to a programmer's two best friends: Google and StackOverflow. I quickly found my way to the MySQL docs and found out that MySQL has two timeout settings that will close a connection if it is idle for too long: interactive_timeout and wait_timeout. The default value for these two settings is 28800000 ms or 8 hours.
The general advice online was to make sure that your connection management library was sending periodic "keep-alive" queries to prevent connections from going idle. I was using BoneCP, so I tried everything I could to make it behave properly, including a few configuration tweaks as well as workarounds for a connection leak bug and a releaseHelperThreads bug. Nothing worked.
Eventually, I swapped out BoneCP entirely for a different connection management library. Nevertheless, after a few hours, the dreaded "last packet sent to the server was XXX ms ago" error would pop up on the production box.
The solution at last
For a while, I was at a loss. I couldn't see how two entirely different DB connection management libraries could have the same bug. I began digging for what the two had in common and realized that, under the hood, both would be using the same JDBC driver. For MySQL, this is Connector/J.
It's at this point that I noticed that I was, for some reason, using Connector/J version 3.1.12, which is quite old. In fact, it is officially obsolete and only compatible with MySQL 5.0 and below. This is unfortunate, as I was using MySQL 5.5 in production.
I figured it was a long shot that this was the cause of the errors I was seeing, but I figured that using the "recommended" connector version was a good idea anyway. I updated from mysql-connector-java version 3.1.12 to version 5.1.22.
And just like that, all the errors were gone.
The final word
So, there you have it. If you see a "last packet sent to the server was XXX ms ago", it's likely one of two things:
- Your DB connection management library is leaving idle connections open too long
- You're hitting an incompatibility bug between the Connector/J version and the MySQL DB version
