MySQL JPA EntityManager Timeout

Few weeks back, I had a MySQL databases connectivity issues in the web application I was working on. The issue was there with daily first login to the application. Initially we thought that it was the LDAP (AD or Active Directory) connectivity issue as it appeared after the authentication with LDAP.

Problem Description: Daily morning first login attempt to my web application built on Spring, JPA and MySQL technologies fails.

Root Cause: After a given period of time (8 hours), MySQL database server automatically closes unused connections. The issues comes because JPA uses the same C3P0 configuration used by Hibernate which does not automatically checks or refreshes it connection pool when a connection is closed by database server.

Solution: I did a lot of google search to find out the solution. Most of the resources on internet suggested configuration changes in Hibernate configuration file (hibernate.cfg.xml). Many sites on Internet suggested adding different properties of c3p0 pool. Mostly the error occurs when we forget to configure and assign the value to one or more important property in the c3p0 connection pool, and the default value is assumed by the framework.

My applicationContext.xml file in the Spring/JPA/MySQL based application (after adding the required property: idleConnectionTestPeriod) has following bean definitions:

<!-- The JPA Transaction Manager --> <bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager"> <property name="entityManagerFactory" ref="entityManagerFactory" /> </bean> <!-- The global EntityManagerFactory Instance --> <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" lazy-init="true"> <property name="dataSource" ref="dataSource" /> <property name="persistenceXmlLocation" value="classpath:META-INF/ec-persistence.xml" /> <property name="jpaVendorAdapter"> <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> <property name="databasePlatform" value="org.hibernate.dialect.MySQL5InnoDBDialect" /> <!-- <property name="generateDdl" value="false" />--> <property name="showSql" value="true" /> </bean> </property> </bean> <!-- The global application DataSource --> <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="${jdbc.driverClassName}" /> <property name="jdbcUrl" value="${jdbc.url}" /> <property name="user" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <!-- C3P0 specific DataSource properties --> <property name="initialPoolSize" value="${c3p0.initialPoolSize}" /> <property name="minPoolSize" value="${c3p0.minPoolSize}" /> <property name="maxPoolSize" value="${c3p0.maxPoolSize}" /> <property name="acquireIncrement" value="${c3p0.acquireIncrement}" /> <property name="idleConnectionTestPeriod" value="$ {c3p0.idle_test_period}" /> </bean> <!-- Configurer that replaces ${...} placeholders with values from a properties file --> <!-- (in this case, JDBC-related settings for the JPA EntityManager and DataSource definitions below) --> <context:property-placeholder location="/WEB-INF/config/jdbc.properties" />

Jdbc.properties

#MySQL Database Settings jdbc.driverClassName=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/erilearning?autoReconnect=true jdbc.username=erilearning jdbc.password=erilearning #Connection Pooling Settings c3p0.initialPoolSize=5 c3p0.minPoolSize=5 c3p0.maxPoolSize=50 c3p0.acquireIncrement=5 # add following property. Value in seconds c3p0.idle_test_period=3600

To identify all the properties available in the com.mchange.v2.c3p0.ComboPooledDataSource class, download the source jar (c3p0-0.9.1-sources.jar) of the c3p0-0.9.1.jar

One of the important property of the c3p0 is idleConnectionTestPeriod. After adding this property value less that the corresponding value at server end (8 hours), we can test the closed connections and the timed out connections by the connection pool at application end with the corresponding reference on database server side.

Testing: To test the changes applied in above step, we need to wait for at least 8 hours, which would be a long time. We can reduce this testing time by changing the connection time out in the MySQL server, restarting MySQL server and restarting application server. Add the following line to MySQL configuration file:

wait_timeout=120

MySQL Configuration File Name on windows: my.ini

MySQL Configuration File Name on Linux: /etc/mysql/my.cnf

Linux users must use the sudo command to edit the file as given below:

sudo vi /etc/mysql/my.cnf

Once you're done with your testing, you can remove the property and it will default back to 8 hours.

References:

C3P0 Hibernate Configuration

Source link corresponding to Hibernate issue