Sunday, June 8, 2008

MySQL with Servlets - Poor uptime

Now that I have my mobile application working on the smartphone I have been quite pleased with the client. Then I started getting strange timeouts and no matter what I was trying to do I was unable to execute queries against the database. From looking in the Tomcat logs I discovered an interesting exception:

Last packet sent to the server was 3 ms ago.
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2579)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2867)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1428)



This led me to research whether this could be a MySQL issue. It turns out, that the connection pool will close all connections after eight hours of inactivity. Their solution of using the "autoReconnect" property apparently will not work under most circumstances. This is covered in section 26.4.5.3.4 on the MySQL reference manual.

The solution? Well it has been suggested the writing a small daemon thread which wakes up every hour and executes some small query should be sufficient to keep the connections open. I have not implemented this yet, but this seems reasonable. In my case I'll probably tie it to one of my servlets in their init() methods.

No comments: