c3p0 config

Communications link failure due to underlying exception and Mysql error

Hello,

I got those error problems with the following configuration:
- Hibernate 3.2.x
- Mysql 5.0.x
- Mysql JConnection 5.0x
- Spring 2.0.x
- Tomcat 5.0.28

Reading this forum it gave me some ideas, but none worked out.

In fact my problem, as exposed in an early post, was that after 8 hours, the MySQL connexions got “closed” or “destroyed” by the data base, but unfortunately my Pool in my Java Code was not aware of that. So, every morning, or 8h with no activities I got that annoying stack trace, and the user of the application had to retry what it did.
Of course an easy solution is to extend the default MySQL server time, but I could not do that since the MySQL DB was used by other applications, so it was not possible to change MySQL configuration.

So this is what completely solved my problem:
- First, I changed from the DBCP Jakarta pool to c3p0 pool
- Second: I tried to understand the Hibernate way of using this pool. Yes, because unlike the Hibernate documentation says it is not as easy as that to make Spring+Hibernate+c3p0 work together.
- Configuring c3p0 as if using pure hibernate will not work properly
- Name of variable are not the same when you are c3p0 alone or c3p0+hibernate
So the idea was to configure c3p0 alone using Spring, and then link it with hibernate using the “correct” properties names (I had to read the documentation of c3p0 twice because the hibernate documentation was not up to date)

This is what it gives:

I put here all properties values, it gives you an idea, the most important thing is to force the pool to recreate connexion before the 8hours, so I took 4hours (maxConnectionAge)
hibernate.c3p0.initialPoolSize=10
hibernate.c3p0.minPoolSize=1
hibernate.c3p0.maxPoolSize=25
hibernate.c3p0.acquireRetryAttempts=10
hibernate.c3p0.acquireIncrement=5
hibernate.c3p0.idleConnectionTestPeriod=3600
hibernate.c3p0.preferredTestQuery=SELECT 1;
hibernate.c3p0.testConnectionOnCheckin=false
hibernate.c3p0.maxConnectionAge=14400
hibernate.c3p0.maxIdleTime=10800

# Keep in mind that For Hibernate
# c3p0.acquireIncrement<=>hibernate.c3p0.acquire_increment
# c3p0.idleConnectionTestPeriod<=>hibernate.c3p0.idle_test_period
# c3p0.initialPoolSize not available -- uses minimum size
# c3p0.maxIdleTime<=>hibernate.c3p0.timeout
# c3p0.maxPoolSize<=>hibernate.c3p0.max_size
# c3p0.maxStatements<=>hibernate.c3p0.max_statements
# c3p0.minPoolSize<=>hibernate.c3p0.min_size

Now, the Spring DataSource initialization, as you can see I use all my properties here (and not in the hibernate initialisation) :
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${jdbc.driverClassName}"/>
<property name="jdbcUrl" value="${jdbc.url}"/>
<property name="user" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>

<property name="initialPoolSize"><value>${hibernate.c3p0.initialPoolSize</value></property>
<property name="minPoolSize"><value>${hibernate.c3p0.minPoolSize}</value></property>
<property name="maxPoolSize"><value>${hibernate.c3p0.maxPoolSize}</value></property>
<property name="acquireRetryAttempts"><value>${hibernate.c3p0.acquireRetryAttempts}</value></property>
<property name="acquireIncrement"><value>${hibernate.c3p0.acquireIncrement}</value></property>
<property name="idleConnectionTestPeriod"><value>${hibernate.c3p0.idleConnectionTestPeriod}</value></property>
<property name="maxIdleTime"><value>${hibernate.c3p0.maxIdleTime}</value></property>
<property name="maxConnectionAge"><value>${hibernate.c3p0.maxConnectionAge}</value></property>
<property name="preferredTestQuery"><value>${hibernate.c3p0.preferredTestQuery}</value></property>
<property name="testConnectionOnCheckin"><value>${hibernate.c3p0.testConnectionOnCheckin}</value></property>
</bean>

And finally the Spring configuration for Hibernate, here I want to be sure that hibernate+spring+c3p0 will work well together so I enforce the available properties.

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="hibernateProperties">
<props>
…
<!-- Those settings are for the connections obl with MySQL -->
<prop key="hibernate.c3p0.acquire_increment">${hibernate.c3p0.acquireIncrement}</prop>
<prop key="hibernate.c3p0.idle_test_period">${hibernate.c3p0.idleConnectionTestPeriod}</prop>
<prop key="hibernate.c3p0.timeout">${hibernate.c3p0.maxIdleTime}</prop>
<prop key="hibernate.c3p0.max_size">${hibernate.c3p0.maxPoolSize}</prop>
<prop key="hibernate.c3p0.min_size">${hibernate.c3p0.minPoolSize}</prop>

</props>
</property>

As said in C3p0 documentation, an other way to properly initialize c3p0 is by creating a property file with the correct name and values, I did not try it, but it should work for those using Hibernate alone without Spring.

Hope, this will help, since now I do not have any more problems with MySQL and the connexion pool.

Best regards,
Renaud.