Sunday, December 16, 2012

C3P0 connection pool configuration Spring Hibernate

Recently I faced an error with our server, every morning we received an 500 error as follow:

HTTP Status 500 - Request processing failed; nested exception is org.springframework.dao.DataAccessResourceFailureException: could not execute query;

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 54,840,265 milliseconds ago. The last packet sent successfully to the server was 54,840,266 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

 For a while I was browsing and searching in google to solve this issue, and then I figured out what I need, a connection pool, so I started to configure our Java projects with C3P0 (see for completing info).

First step: You should add the c3p0 dependency to your pom.xml


Second step: Set dataSource configuration to some like this:

<bean class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" id="dataSource">
<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 properties -->
<property name="minPoolSize" value="${hibernate.c3p0.min_size}">
<property name="maxPoolSize" value="${hibernate.c3p0.max_size}">
<property name="maxIdleTime" value="${hibernate.c3p0.idle_test_period}">

<bean class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" id="sessionFactory">
<property name="dataSource" ref="dataSource">
<property name="annotatedClasses">
<property name="hibernateProperties">
        <prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="">${}</prop>
<prop key="hibernate.cache.provider_class">${hibernate.cache.provider_class}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
<prop key="hibernate.show_comments">${hibernate.show_comments}</prop>

Third step: Add this to your pom.xml

<jdbc .driverclassname=".driverclassname">com.mysql.jdbc.Driver</jdbc>
<jdbc .url=".url">jdbc:mysql://localhost:3306/vapaya</jdbc>
<jdbc .username=".username">vapayaUser</jdbc>
<jdbc .password=".password">v4p4y4DB</jdbc>

<hibernate .dialect=".dialect">org.hibernate.dialect.MySQLDialect</hibernate>
<hibernate .cache.provider_class=".cache.provider_class">org.hibernate.cache.NoCacheProvider</hibernate>
<hibernate .show_sql=".show_sql">true</hibernate>
<hibernate .format_sql=".format_sql">false</hibernate>
<hibernate .show_comments=".show_comments">true</hibernate>

<hibernate .c3p0.min_size=".c3p0.min_size">5</hibernate>
<hibernate .c3p0.max_size=".c3p0.max_size">20</hibernate>
<hibernate .c3p0.idle_test_period=".c3p0.idle_test_period">3000</hibernate>

Fourth step: Add this to your properties file




That's all, in order to know is everything is set, you should see this output in catalina.out:

Dec 16, 2012 5:35:17 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0- [built 21-May-2007 15:04:56; debug? true; trace: 10]

Some additional information about if it's working is as follow:

1. Connect to mysql, command line.
2. type: show processlist;

This should show you 5 connections (minimum pool size).

I hope you find this helpful.

Monday, December 10, 2012


I refused to end this year without another mix, this one has some favorite dj such as Deadmau5 and Daniel Kandi, includes also a delicious track called Revolution by Rowald Steyn. This sound  mix goes from Progressive House to Uplifting Trance.


1. Deadmau5 - HR 8938 Cephei (Original Mix) (10:48)
2. Electrocker - Enfoire (Original Mix) (6:9)
3. Dominik Eulberg - Bionik (Orginal Edit) (8:32)
4. Solarity - Marsh (Original Mix) (8:18)
5. Deadmau5 - Faxing Berlin (Chris Lake Edit) (8:13)
6. Rowald Steyn - Revolution (6:24)
7. Phillip Alpha & Daniel Kandi - Sticks & Stones (Beat Service Remix) (7:37)
8. Miguel Bose - Por ti (Above & beyond remix) (8:31)

I hope you like it.