Basicdatasource connection time out problem (using mysql)

Question

I am using BasicDatasource in my application. This application is processing huge amount of raw data. Sometimes 1 query can take more than 15 minutes. (using mysql as db)

Here is my question, I acquire a connection from pool, then execute several queries on it. But when I use the same connection more than 15 minutes, I get the error below. In the mysql server max_wait is set to 180 hours so it shouldn t be a problem to keep the connection alive and no firewall rule set to kill connections that are alive more than a certain amount of time.

What am I missing here do you think ?

The last packet successfully received from the server was 928,374 milliseconds ago.  The last packet sent successfully to the server was 928,374 milliseconds ago.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3055)
        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2941)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664)
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
        at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
        at com.adsclick.logs.cron.adsclickv261.global.ProcessBase.executeUpdate(ProcessBase.java:766)

Answer 1

Things you can try:

1. setMaxWait(-1) on the BasicDataSource. That tells it to wait indefinitely for a connection.

2. Check that the wait_timeout on your MySQL server is set to the default 8h.

3. Set ?autoReconnect=true on your JDBC URL

4. setTestOnBorrow(true) on the BasicDataSource. This will prevent it from handing out stale connections but will add overhead to your app (though if you've already got such long single queries you probably won't even notice that part).

In general, I find it a bad idea to keep re-using a connection. For me, the point of having a pool is that I don't have to do that.

Are your queries transactional? Is some really long query locking up a major table?