DBCP and Hibernate on Spring, doesn't re-open dead connections, why?

Question

I'm using Hibernate and DBCP to manage mySQL connections, all in a Spring project.

Everything is working fine. The only problem is that if the app stays still for a long time, it will throw a an exception because the connection is dead (same thing if I restart mySQLd when the application is up). It's not big deal because the user will get the exception page (or the custom one) and a reload will solve the problem. But I'd like to solve it. Here is part of the exception:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException MESSAGE: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

STACKTRACE:

java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

I googled around and I found that with mysql I should set the dbcp.BasicDataSource property testOnBorrow to true, which I've done in my servlet-context.xml:

<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://${mySQL.host}/${mySQL.db}" />
    <property name="username" value="${mySQL.user}" />
    <property name="password" value="${mySQL.pass}" />
    <property name="testOnBorrow" value="true"></property>
</bean>

But the problem persists. Any clues?

Solution! I used:

<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://${mySQL.host}/${mySQL.db}" />
    <property name="username" value="${mySQL.user}" />
    <property name="password" value="${mySQL.pass}" />
    <property name="testOnBorrow" value="true"></property>
    <property name="validationQuery" value="SELECT 1"></property>
</bean>

score 12 · Accepted Answer · answered Apr 12 '11 at 17:32

12

If you set testOnBorrow you must also set validationQuery -

validationQuery - The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row.

I have also set timeBetweenEvictionRunsMillis so the dead connections will be evicted from the pool.

answered Apr 12 '11 at 17:32

Tarlog

10,024
2
43
67

Does this mean that this query will run every time Hibernate borrows one connection from DBCP??? Isn't this too heavy? – gotch4 Apr 13 '11 at 08:29
It should be a tiny query that runs very fast. Assuming Hibernate runs several not tiny queries for each connection, one more tiny query should not effect performance. – Tarlog Apr 13 '11 at 09:06
hm.... Yeah I see, I used SELECT 1 (it's suggested on the dbcp wiki). Anyway, I think that hibernate will get a connection everytime it opens a session. This means roughly everytime the controller processes a request. I hope this is just a mySQL related issue. – gotch4 Apr 13 '11 at 12:48

DBCP and Hibernate on Spring, doesn't re-open dead connections, why?

1 Answers1