MySql is configured to drop any connection which has been Idle for more than 8 hours. What is the implication of this? After you return to your deployed app after a gap of 8 hours (If default SQL parameters have not been changed), you will be greeted with an exception.
How to solve this issue?
- Increase the wait_time parameter -Not a good Idea, it might unnecessarily hold on to the resources and not might be a sure shot way. Apart from that, being dependent on an “external” configuration for failover is not a very good idea -what if the server itself crashes, what if this configuration is missed in one of the instnaces, and many such issues will pop up against this approach.
- Use the parameter autoReconnect=true with JDBC URL -My SQl itself does not recommend this, have a look at link and people have reported that this does not work as well, refer link.
- Custom handling -have your code identify that connection has been lost and then recover it and try to reconnect, but then it would be a lot of fail over mechanism in code.
- The best way I found was to configure pooling mechanism as c3p0. See this post how to configure c3p0 in JPA for hibernate, it’s simple, easy and reliable.
So how do you test that issue is solved?
- Change wait_timeout in MySql to just 2 minutes, this is how it can be done from MySql workbench admin console
- Keep value of idleTestPeriod less than wait_timeout -A quick recap what idleTestPeriod signifies
- Login after wait_timeout has passed -it should not throw a exception
idleTestPeriod: default value=0; If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds
I added c3p0 for pooling in JPA, but encountered an exception
Unable to load class [org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider]
My configuration looked like
<property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" /> <property name="hibernate.c3p0.max_size" value="10" /> <property name="hibernate.c3p0.min_size" value="0" /> <property name="hibernate.c3p0.acquire_increment" value="5" /> <property name="hibernate.c3p0.idle_test_period" value="60" /> <property name="hibernate.c3p0.max_statements" value="0" /> <property name="hibernate.c3p0.timeout" value="100" />
Details about these properties and other defined at link
Looking at log trace it’s not difficult to figure out that jar is not correct, so first change, upgrade to latest c3p0 artifact.
<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-c3p0</artifactId> <version>3.6.10.Final</version> </dependency>
<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-c3p0</artifactId> <version>4.1.9.Final</version> </dependency>
After changing this, it worked but printed an Warning
WARN - HHH000208: org.hibernate.connection.C3P0ConnectionProvider has been deprecated in favor of org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider; that provider will be used instead.
This indicates that provider_class should be changed to remove the depricated class, so it should be
<property name="hibernate.connection.provider_class" value="org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" />
This cleanly integrated the c3p0 implementation.