MySQL and hibernate c3p0 settings, timeout after 8 hours?

Question

I am using Amazon EC2 to run a Java Wicket application, and I have an Amazon MySQL instance for the application. Everything is working fine, but after 8 hours my connection to the database is lost. I tried setting c3p0 parameters to prevent this from happening. I also tried updating MySQL settings, but didn't 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 make requests as follows:

 @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 can check your logs for dump c3p0 of its configuration; your 5 minute timeout should prevent MySQL connections from becoming obsolete after 8 hours, but for some reason this doesn't seem to be for you. You want to find out if the c3p0 "maxIdleTime" property is 300. In any case, you can try adding

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

this is the easiest way to ensure the reliability of the connections - test them (with an effective request) at each check. it is also relatively expensive; if you find that the problem is, you can go for something more savvier. But it would be nice to make sure that you can get a reliable setup and then optimize from there. See here .

Please note that your hibernate.c3p0.idle_test_period=3000 not useful if your pool is configured as you think. Invalid connections will be synchronized long before the trial interval of 3000 seconds passes.