MySQL connection and hibernate c3p0 settings, timeout after 8 hours?
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.
Admin
Updated on June 13, 2022Comments
-
Admin almost 2 years
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.
-
Admin about 11 yearsI made some changes to my configuration and now we wait and hope for the best. Monitoring shows that hibernate.c3p0.min_size = 5 has taken effect, it was only 1 before. Monitoring image: oi49.tinypic.com/35hipee.jpg
-
amphibient over 10 yearshi Steve, can you take a look at my recent question, it is somewhat related: stackoverflow.com/questions/19664878/… ? Thanks