MySQL connection and hibernate c3p0 settings, timeout after 8 hours?

Question

I am using Amazon EC2 to run my Java Wicket app and I have Amazon MySQL instance running for the application. All works fine, but after 8 hours my database connection is lost. I have tried to configure c3p0 settings so that this would not happen. I have also tried to update MySQL settings, but no help.

Here is my persitence.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="xyz">
<persistence-unit name="mysqldb-ds" transaction-type="RESOURCE_LOCAL">

  <description>Persistence Unit</description>
  <provider>org.hibernate.ejb.HibernatePersistence</provider>

  <properties>
    <property name="hibernate.hbm2ddl.auto" value="update"/>
    <property name="hibernate.show_sql" value="true"/>
    <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
    <property name="hibernate.connection.username" value="XXXXX"/>
    <property name="hibernate.connection.password" value="YYYYY"/>
    <property name="hibernate.connection.url" value="jdbc:mysql://xxxx.yyyyy.us-east-1.rds.amazonaws.com:3306/paaluttaja"/>
    <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
    <property name="connection.pool_size" value="1" />
    <property name="cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />
    <property name="hibernate.c3p0.min_size" value="5" />
    <property name="hibernate.c3p0.max_size" value="20" />
    <property name="hibernate.c3p0.timeout" value="300" />
    <property name="hibernate.c3p0.max_statements" value="50" />
    <property name="hibernate.c3p0.idle_test_period" value="3000" />
  </properties>

</persistence-unit>
</persistence>

I am making queries like this:

@Service
public class LoginServiceImpl implements LoginService{

@Override
public Customer login(String username, String password) throws LSGeneralException {
    EntityManager em = EntityManagerUtil.getEm();

    TypedQuery<Customer> query = em.createQuery("SELECT c FROM Customer c "
            + "WHERE c.username = '" + username + "' "
            + "AND c.password = '" + password + "'", Customer.class);

    Customer customer = null;

    try {
        customer = (Customer) query.getSingleResult();
    }catch(Exception e){
        if(e instanceof NoResultException){
            throw new LSGeneralException("login.failed.wrong.credentials", e);
        }else{
            throw new LSGeneralException("failure", e);
        }
    }

    customer.setLogged(true);

    return customer;

}

}

All help would be appreciated.

Answer 1

First, you might want to check your logs for c3p0's dump of its config; your 5-minute timeout should prevent the MySQL connections from going stale after 8 hours, but for some reason that seems not to be happening for you. You want to see if the c3p0 property 'maxIdleTime' is actually 300 as expected. Anyway, you might try adding

hibernate.c3p0.preferredTestQuery=SELECT 1
hibernate.c3p0.testConnectionOnCheckout=true

this is the simplest way to ensure the validity of Connections -- test them (with an efficient query) on every checkout. it is also relatively expensive; if you find that's a problem, you can go to something savvier. But it'd be good to make sure you can get a reliable set-up, and then optimize from there. See here.

Note that your hibernate.c3p0.idle_test_period=3000 is not useful, if your pool is configured as you think it is. Idle connections will be timed out long before the 3000-second test interval has passed.