Sunday, May 13, 2007

Hibernate and MySQL Connection Timeouts

Several weeks ago, while trying to finish up a portion of my senior project, my team was having some issues with our persistence layer. The issue had to do with the connection time out in MySQL, and the default C3P0 setup that Hibernate uses. As it took MUCH more research than I think it should have to solve the issue, I thought I would write a blog entry about it. Besides, I haven't set out to give anything back in quite a while. Note that while none of this information is new in any way, I have not been able to find it all gathered together in one place.

Complaint:
After my Spring/Hibernate/MySQL soltuion has been running but inactive for more than 8 hours, I get a broken pipe exception.

Root Cause:
MySQL automatically times out, and closes unused connections after 8 hours, and out of the box, Hibernate does not set up C3P0 to appropriately test/refresh its connection pool when connections go stale.

Solution:
It seemed that as we searched the forums for an answer it wasn't immediately obvious what the right answer would be. We noticed that the C3P0 properties had different names, depending on how your project was configured. The hibernate documentation has some suggestions, but it turns out that the names of the properties are not stated correctly. It is also important to note that if properties are not set in the hibernate configuration they will be overridden by hibernate defaults. Here's a snippet from my hibernate.cfg.xml:


<session-configuration>
<!-- driver/connection info removed --!>
<!-- C3P0 Stuff -->
<property name=“hibernate.c3p0.acquire_increment”>3</property>
<property name=“hibernate.c3p0.idle_test_period”>14400</property>
<property name=“hibernate.c3p0.timeout”>25200</property>
<property name=“hibernate.c3p0.max_size”>15</property>
<property name=“hibernate.c3p0.min_size”>3</property>
<property name=“hibernate.c3p0.max_statements”>0</property>
<property name=“hibernate.c3p0.preferredTestQuery”>select 1;</property>
</session-configuration>

The important properties to note above are idle_test_period, and timeout. You want to make sure that C3P0 is configured to test for closed connections and time out unused connections at some rate beneath the threshold set on your MySQL server. With these properties in place you should be good to go.

Testing:
Waiting 8 hours to conduct a test like this would be lame, so let's just change the connection timeout for the MySQL server, re-start the MySQL server, and our application to try it out. You can change the timeout time for MySQL by editing your /ect/my.cnf file (linux) or your my.ini file(windows). You would want to add the following line to the file:

wait_timeout=120

Note that the value after the property is in seconds. Once you're done with your testing, you can remove the property and it will default back to 8 hours.

References:

Configuring C3PO

C3P0 Hibernate Configuration

my_sql options()

Set the MySQL Connection Timeout


26 comments:

Mona said...

Hi:

I noticed that you have "hibernate.c3p0.idle_test". But from my research with c3p0 and hibernate, they said that variable should be "hibernate.c3p0.idle_test_period". You alluded to the fact that the hibernate document incorrected stated the name of the properties, which property names were incorrect? Was it the idle_test?

thanks,
Mona

Joe said...

Mona-

You are totally correct, I wrote this entry several weeks after I had fixed the problem, and in my writeup, I did mis-state the property for the idle test. It is as you say it should be and I have fixed the writeup to reflect the proper property name.

Thank You for noticing,
Joe

Mona said...

Hi Joe:

Thanks for your fast response!

We are still having the problem with MySQL timeout. We are using MySQL 5.0.41, hibernate 2, c3p0 0.9.2. My MySQL timeout is set to 900 and my hibernate.properties has:

hibernate.show_sql=false
hibernate.connection.pool_size=40
hibernate.connection.autocommit=true
hibernate.connection.shutdown=true
hibernate.cache.provider_class=net.sf.hibernate.cache.EhCacheProvider

###########################
### C3P0 Connection Pool###
###########################

hibernate.c3p0.max_size 50
hibernate.c3p0.min_size 2
hibernate.c3p0.timeout 600
hibernate.c3p0.max_statements 0
hibernate.c3p0.idle_test_period 100
hibernate.c3p0.idle_test 450
hibernate.c3p0.acquire_increment 2

Do you have any suggestion for us?

Unfortunately, the code is not ours. I do have the source but I don't even know what to look for in the code to see if it is doing the right thing with using the DB/hibernate. Can you suggest something I should check in the code?

thanks,
Mona

Joe said...

Is it just a transcription error in the properties that you pasted in your last response, or are the equals (=) signs missing from all of your C3P0 Connection Pool properties?

Shouldn't it look like this:
hibernate.c3p0.max_size=50

Give that a try.

Cristian said...

I think that you shouldn't prefix the property names with the "hibernate" keyword when you are using an xml configuration file.
So <property name="hibernate.c3p0.timeout"> should read <property name="c3p0.timeout">

Matt said...

isn't the property in question wait_timeout, not connect_timeout? looks like wait_timeout is the 8-hour default mysql property? real curious about this.

Joe said...

Matt,

You're right!
I was writing this up a month or so after I fixed the issue, and it appears that when I went to write it up I used the wrong system variable. My apologies. I'll fix the post.

Andy Medlicott said...

Thanks very much! This saved me a lot of time fixing this problem on our system.

Anonymous said...

Very good tip, thank you

Anonymous said...

Hi,

I have a probleme, i lost my connection.

I use hibernate2.jar, c3p0-0.9.1.2.jar, mysql5, java 1.5

My hibernate.cfg.xml :

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://mysql-2.univ-rennes1.fr:3307/igUR1prod10</property>
<property name="connection.username">dev_igUR1prod10</property>
<property name="connection.password">6sQxbPl9c</property>

<property name="dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<property name="show_sql">false</property>
<property name="use_outer_join">true</property>

<property name="connection.autoReconnectForPools">true</property>
<property name="connection.autoReconnect">true</property>

<property name="c3p0.min_size">3</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">10</property>
<property name="c3p0.acquireRetryAttempts">30</property>
<property name="c3p0.acquireIncrement">5</property>
<property name="c3p0.idleConnectionTestPeriod">9</property>
<property name="c3p0.initialPoolSize">20</property>
<property name="c3p0.maxPoolSize">100</property>
<property name="c3p0.maxIdleTime">300</property>
<property name="c3p0.maxStatements">50</property>
<property name="c3p0.minPoolSize">10</property>

Do you have any suggestion for us?

Thanks Linda

Ridvan said...

This didn't work for me,
but this context connection pool solved my problem:
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/dbname"

maxActive="30"
maxIdle="5"
validationQuery="SELECT 1"
username="username"
password="password"

/>

henko said...

Great post! Been struggling with this one for I don't know how long.

Andrew Dailey said...

Yes! Thank you.
If anyone wants to know how to fix this in a spring context, the property names look like this:

bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"

property name="idleConnectionTestPeriod" value="14400"
property name="maxIdleTime" value="25200"

Santhosh said...

It worked for me! Thanks a lot buddy.

mr_t said...

Thanks so much - finally i'm able to fix this annoyance!

My blog

luns said...

hi,
After putting the c3p0 properties in hibernate.cfg.xml , i am still getting a problem...

Please help me out of this. thanks.
here's the log...
1919815 29-Jan 11:46:52,282 [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2] WARN com.mchange.v2.resourcepool.BasicResourcePool - BasicResourcePool: An idle resource is broken and will be purged.
java.sql.SQLException: Connection is invalid
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.testPooledConnection(C3P0PooledConnectionPool.java:196)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.refurbishIdleResource(C3P0PooledConnectionPool.java:136)
at com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask.run(BasicResourcePool.java:1394)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:368)

Janaki Ram Chaganti said...

Hi Joe,

Thank you very much for providing this info at a central place. I too encountered this problem and I made changes to hibernate.cfg.xml based on your suggestions. This didn't work for me initially. But later some more research of few hours indicated that we require the following line as well which tells hibernate to use connection pooling:

<property name="hibernate.connection.pool_size">0</property>


The properties hibernate.c3p0.* perhaps suggest hibernate to use c3p0 connection pooling specifically. Surprisingly if we have non-zero value for pool_size, it does not work. At least my experimentation made me to infer this way. You can perhaps try out removing the line and confirm. If this line does matter, you can add this info also to your blog.


Thanks.
Janaki Ram

Bob said...

Hi,

It didn't work for me.
I really don't what to do now.

here my hibernate.cfg.xml:

----------------------------------
?xml version='1.0' encoding='utf-8'?
!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"

hibernate-configuration
session-factory

!-- Database connection settings --
property name="connection.driver_class"com.mysql.jdbc.Driver/property
property name="connection.url"jdbc:mysql://localhost:3306/prod_infoWeb/property
property name="connection.username"infoweb/property
property name="connection.password"qwe123/property

!-- JDBC connection pool (use the built-in) --
property name="connection.pool_size"1/property

!-- SQL dialect --
property name="dialect"org.hibernate.dialect.MySQLDialect/property

!-- Enable Hibernate's automatic session context management --
property name="current_session_context_class"thread/property

!-- Disable the second-level cache --
property name="cache.provider_class"org.hibernate.cache.NoCacheProvider/property

!-- Echo all executed SQL to stdout --
property name="show_sql"true/property

!-- Drop and re-create the database schema on startup --
!-- property name="hbm2ddl.auto"create/property --

!-- C3P0 Stuff to avoid disconnect after 8 hours--
property name="hibernate.c3p0.acquire_increment"3/property
property name="hibernate.c3p0.idle_test_period"14400/property
property name="hibernate.c3p0.timeout"25200/property
property name="hibernate.c3p0.max_size"15/property
property name="hibernate.c3p0.min_size"3/property
property name="hibernate.c3p0.max_statements"0/property
property name="hibernate.c3p0.preferredTestQuery"select 1;/property

mapping resource="actifs/Actif.hbm.xml"/
mapping resource="bonDeTravails/BonDeTravail.hbm.xml"/
mapping resource="demandeurs/Demandeur.hbm.xml"/
mapping resource="employes/Employe.hbm.xml"/
mapping resource="evenements/Evenement.hbm.xml"/
mapping resource="fabriquants/Fabriquant.hbm.xml"/
mapping resource="groupes/Groupe.hbm.xml"/
mapping resource="heuresEffectuees/HeuresEffectuees.hbm.xml"/
mapping resource="mnemoniques/Mnemonique.hbm.xml"/
mapping resource="repondants/Repondant.hbm.xml"/
mapping resource="repondantsActifs/RepondantsActif.hbm.xml"/
mapping resource="typeExperts/TypeExpert.hbm.xml"/

mapping resource="bonDeTravails/BonDeTravailDTO.hbm.xml"/

/session-factory
/hibernate-configuration

Janaki Ram Chaganti said...

Bob,

Please try with connection.pool_size as 0. As I mentioned in my comments, non-zero value wasn't working for me.

Thanks,
Janaki

amir said...

perfect post...My great problem solved with your post in few minutes...
thanks a lot.

Debs_za said...

brilliant help, thank you

Kyle Braak said...

Thanks for taking the time to write this!

Alois Reitbauer said...

A great post and saved me a lot of work!

Amandeep said...

Hi,
I am using hibernate 3 and mysql 5.1.Referring to your post I used c3p0 and the configuration file has these parameters:


org.hibernate.connection.C3P0ConnectionProvider

5
20
15

I set Mysql wait_timout=30.But still once it crossed the 30 seconds I am getting the same exception.Please tell me is there anything else to be included in the configuration file.

Despot said...

I've changed the local tomcat wait_timeout via my.ini file to 120sec (2 min). And I placed the following:
maxIdleTime=100
idleConnectionTestPeriod=0 (same as default/as if it didn't exist)
other:
acquireIncrement=2
minPoolSize=2
maxPoolSize=5
maxIdleTimeExcessConnections=10

I had no problems with this setup.

Previously, though I had issues with the following values:
tomcat's wait_timeout=8h (default)
maxIdleTime=0

@Andrew Dailey http://www.blogger.com/profile/12043393939412314891
why would I also use idleConnectionTestPeriod?

If tomcat's wait_timeout is 28800 sec, and maxIdleTime is 25200, doesn't this mean that it will close the idle connection in 3600sec (1h) earlier, before tomcat throws a "broken pipe" exception?

As you can see I have no issues with providing only maxIdleTime.
Unfortunately, these:
http://www.mchange.com/projects/c3p0/#maxIdleTime
http://www.mchange.com/projects/c3p0/#idleConnectionTestPeriod
http://www.mchange.com/projects/c3p0/#configuring_connection_testing
http://www.mchange.com/projects/c3p0/#testConnectionOnCheckin
don't explain too much the corner cases.

And, btw, here is how to open the my.ini file with Notepad++:
http://drupal.org/node/32715#comment-4907440

Cheers,
Despot

eguimerans said...

The solution does not work if you don'n include the reference to the c3p0 provider on your cfg file:

org.hibernate.connection.C3P0ConnectionProvider

And of course, don't forget to place the c3p0-X.X.X.jar in the classpath.

Cheers!