Java BoneCP MySQL connection timing out

Question

I have an application set up to pool mysql connections with BoneCP. Right now, the application isn't getting a ton of use, so the connections aren't used as frequently. After a certain amount of time, queries that once worked, start to fail, and I get messages similar to this:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 2,618,063 milliseconds ago.      The last packet sent successfully to the server was 44,734 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3567)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3456)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1379)
at com.jolbox.bonecp.PreparedStatementHandle.execute(PreparedStatementHandle.java:138)

I am setting up BoneCP like this:

BoneCPConfig config = new BoneCPConfig();
config.setJdbcUrl("jdbc:mysql://" + hostname + "/" + database);
config.setUsername(username); 
config.setPassword(password);
config.setMinConnectionsPerPartition(minPoolSize);
config.setMaxConnectionsPerPartition(maxPoolSize);
config.setIdleConnectionTestPeriodInMinutes(60);
config.setIdleMaxAgeInMinutes(240);
config.setPartitionCount(1);
connectionPool = new BoneCP(config);

I'm unsure of how to find out what the timeout is for the mysql server (it hasn't been changed from whatever the default is), but I get this error after around 5 or 10 mins of no connection pool activity, which seems extremely short.

Aleksander Blomskøld · Accepted Answer · 2012-08-14T05:28:55.647

5

Either you'll have to configure mysqls idle timeout (setting wait_timeout = X / interactive_timeout = X), or you could configure the connection pool to issue keep-alive statements:

config.setIdleConnectionTestPeriodInMinutes(10);
config.setConnectionTestStatement("/* ping */ SELECT 1"):

edited Aug 14 '12 at 05:28

answered Aug 14 '12 at 05:23

Aleksander Blomskøld

18,374
9
76
82

Thanks for the keep-alive statement part, and the variable names for the Mysql idle timeout. I checked both of them, and they are both at default of 8hrs. And right now in the testing I've been doing, program has only been running max 30mins to an hr. I will try out the keep-alive statements and let you know! – Nick Aug 14 '12 at 05:57
This seems to have fixed the problem! Thanks! – Nick Aug 14 '12 at 20:18

Java BoneCP MySQL connection timing out

1 Answers1