Spring app losing connection to MySql after 8 hours. How to properly configure?

Question

I've got a Spring app that I believe uses DBCP connection pooling to connect to a MySql database. I say believe because this isn't an area I'm very strong in and I'm not positive if everything is set up correctly. I have no problems running the application and everything is working fine. The problem occurs overnight. The app is not heavily used and overnight it apparently loses it's connection to MySql. I looked into it and found out MySql has an 8 hour window and then it disconnects or whatever. I'm fine with this, but when a user attempts to log on in the morning, they get an error something like:

Communications link failure. The last packet successfully received 60,000,000ms ago. The last packet successfully setn 15ms ago.

This is the problem. I need them to be able to reconnect in the morning without running into this issue. The only way I seem to be able to fix it is by bouncing the Tomcat server. From looking into it, it seems that DBCP pooling should be able to prevent this somehow but I can't find a reliable source of info on how to configure it. I'm hoping someone here can provide me with some insight. Here is my current configuration, all done in a Spring xml file:

app-data.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
    http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/tx 
    http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
    http://www.springframework.org/schema/aop 
    http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context-3.0.xsd">

<context:annotation-config />
<context:component-scan base-package="com.vz.sts.domain" />
<context:component-scan base-package="com.vz.sts.persistence" />
<context:component-scan base-package="com.vz.sts.service" />

<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="database" value="MYSQL" />
            <property name="showSql" value="true" />
        </bean>
    </property>
</bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/app" />
    <property name="username" value="root" />
    <property name="password" value="admin" />
    <property name="initialSize" value="5" />
</bean>

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

<bean id="jdbcUserService" class="org.springframework.security.provisioning.JdbcUserDetailsManager">
    <property name="dataSource" ref="dataSource"/>
    <property name="authenticationManager" ref="authenticationManager"/>
</bean>

<bean id="saltSource" class="org.springframework.security.authentication.dao.ReflectionSaltSource">
    <property name="userPropertyToUse" value="username" />
</bean>

<tx:annotation-driven />
</beans>

I'm not sure what specific properties I need to add in order to allow the app to reconnect to the database. I don't mind if it closes the connection after a number of hours but it should automatically reconnect and not throw errors like this. Nor am I even positive it's actually set up to use connection pooling. So any help would be very much appreciated, thank you.

UPDATE

I found this page and I think that all I need to do is add the ValidationQuery property. Can anyone verify if this will have the desire affect while leaving everything else at default? I believe that will then make use of the testOnBorrow aspect of DBCP. I don't entirely understand what the explanation says testOnBorrow does, but I think this will do what I want. Anyone confirm? Thanks.

Answer 1

The short answer is it should be enough. DBCP supports testing the connection on borrowing from the connection pool (the default), but also supports test on return and test while idle.

It's also worth understanding what may be going wrong here. It sounds like something between your Tomcat server and the database is dropping the idle connection after a timeout (such as a router or firewall). The problem with this is that Tomcat thinks it still has a valid connection, tries to do some work with the connection and fails, but keeps the connection alive and returns it to the pool. Now any further attempt to talk to the database will fail if it is given the same broken connection from the pool.

I think it was Michael Nygard's excellent 'Release It!' book that described this scenario in one of his from-the-trenches stories.

You will also want to look into how MySQL cleans up dead connections as when Tomcat loses the connection after 8 hours the DB will also be unaware of the failed connection.

One final point, if you are using Tomcat 7 switch to their new connection pool as it offers better performance than DBCP.

Answer 2

My friend, DBCP does a promise he can't keep. Hehe. I've found myself with this problem and it got down to some newly firewall recently put in the middle chopping idle connections with idle time longer than X hours. So, the Db couldn't notify my client (and its socket) that the conn was going down and the socket was kept open, hence the pool couldn't know that the conn was not available. Result: first query attempt in the morning failed with timeout while the second worked as expected. Even with the validationQuery, DBCP didn't check an already valid conn (don't ask me why, I just found out that)

Solution 1? Due to the fact that it was a production environment (yeah, lots of sweat), the fast horse was to create a separate thread sending a sure-thing query to the DB using the pool every... X/4 hours. It kept the brand-new firewall/WAF from chopping my socket conn!

Solution 2? Check infrastructure. Check continuity. Check coherence in speed and mode of network interfaces (e.g full duplex, 100M). Check Db server settings (no net card saving energy hehe). And maybe keeping the probe in solution 1 working.

EDIT. testOnBorrow and validationQuery should work under normal circumstances. Imaging the pool with logical channels and a physical socket btw client and server. testOnBorrow checks if a channel is valid before giving it out to your request. It uses validationQuery to do it.