Tuesday, May 5, 2009

Automatic reconnect from Hibernate to MySQL

Yesterday I spent the entire day getting the following amazing state-of-the-art not-ever-done-before feature to work:

  • Executing a SQL statement from my program

Because, as everyone knows, I don’t suffer from NIHS, I used standard object-relational mapping software Hibernate, with a standard programming language Java, using the standard web-application server Tomcat, and now I am using the standard connection pooling software C3P0 (which I didn’t know I needed to execute a SQL statement, see below..)

The program is, in fact, already completed, and is nearly deployed. On the test server it works fine and even on the (future) live server it worked fine. But the customer noticed that if one installed it one day, the next day it didn’t work. I’ve had such symptoms many times before, so I know immediately what was going on:

  • MySQL drops a connection after 8 hours (configurable)

  • The software is used during the day, but isn’t used during the night, therefore the connection times out in the night

  • Therefore in the morning, the program one installed the day before no longer works

Perhaps I exaggerated the simplicity above of what I was really trying to achieve. It should really be expressed as the following:

  • Executing a SQL statement from my program, even if a long time has passed since the last one was executed

But that amounts to the same thing in my opinion! It isn’t rocket science! (But in fact is, see below..)

A obvious non-solution is to increase the “connection drop after” time on the MySQL server from 8 hours to e.g. “2 weeks” (”wait_timeout” in “mysql.cnf”). But software has got to be capable of reconnecting after a connection drops. The database server may need to be reset, it may crash, it may suffer hardware failure, etc. If, every time one restarts one particular service, one has to restart a thousand dependent services (maybe some Java, some Perl, some PHP, some robots, ..) and then maybe restart services which are dependent on them - that’s a maintenance nightmare. So the software has to be altered to be able to handle connection drops automatically, by reconnecting. Once the software has been so altered, one no longer needs to alter the “wait_timeout” on the server.

The error was:

org.hibernate.util.JDBCExceptionReporter: The last packet successfully received from the server was 56697 seconds ago. The last packet sent successfully to the server was 56697 seconds ago, which 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.

Quite a helpful error message, don’t you think? But

  • I’m not going to increase “wait_timeout” as discussed above,

  • testing validity” in the application - well I was using standard software Hibernate which should take care of this sort of thing automatically, but evidently wasn’t

  • and we were already using ?autoReconnect=true in the JDBC URL (this evidently wasn’t working).

I figured I really needed to get to the bottom of this. Googling just showed (many) people with the same problem, but no solutions. The only way to get to the bottom of software is to read the source. (It has been the way to resolve issues of simple things simply not working in MySQL before.)

I stopped looking in the MySQL source for why “autoReconnect=true” didn’t work when I saw the following text in the source describing the autoReconnect parameter:

The use of this feature is not recommended, because it has side effects related to session state and data consistency

I have no idea what particular side-effects are meant here? I guess that’s left as an exercise for the reader, to test their imagination.

And anyway, I figure that a reconnect-facility belongs in the “application” (Hibernate in my case) as opposed to in database-vendor specific code. I mean the exactly the same logic would be necessary if one were connecting to PostgreSQL or Oracle, so it doesn’t make sense to build it in to the database driver.

So then I looked in the Hibernate code. To cut a long story short, the basic connection mechanism of Hibernate (as specified in all the introductory books and websites, which is probably how most people learn Hibernate) doesn’t support reconnecting, one has to use H3C0 connection pool (which itself didn't always support reconnecting)

(I don’t want to use container/Tomcat-managed connections, as I have some command-line robots which do some work, and I don’t want to use different code for the robots as the web application. Although another company defined Servlets which did “robot work”, and the robot was just a “wget” entered into Tomcat - to get the user of container-managed connections - but this seems a too-complex solution to my taste..

But once one’s used H3C0, the default behavior seems to be that to process a request, if the connection is dead then the user sees and error - but at least it reconnects for the next request. I suppose one error is better than infinite errors, but still not as good as zero errors. It turns out one needs the optiontestConnectionOnCheckout- which the documentation doesn’t recommend because testing the connection before a request might lead to lower performance. Surely the software firstly has to work, only secondly does it have to work fast.

So, to summarize, to get a connection to “work” (which I define as including handling dropped connections by reconnecting without error): In “hibernate.cfg.xml”:

<!-- hibernate.cfg.xml -->
<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">1800</property>
<property name="c3p0.max_statements">50</property>
<!-- no "connection.pool_size" entry! -->

Then create a file “c3p0.properties” which must be in the root of the classpath (i.e. no way to override it for particular parts of the application):

# c3p0.properties

That was an amazing amount of effort and research to get the simplest thing to work. Now if only this project had been paid by the hour…..


  1. Like soooo many other suggestions: I've tried all these and ... it doesn't work. I did get one great tip: wait_timeout. I set wait_timeout=60 in my.cnf and now I can replicate the problem in minutes and not have to wait until next morning. I'll post an update if I ever get to the bottom of it.

  2. I think I did got to the bottom of it thanks to this post: https://www.hibernate.org/214.967.html

    Seems 3cp0 wasn't actually enabled. To hibernate.properties I added:

    hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider

  3. You are right. Hibernate3 will take the c3p0 in effect only of the provider class is configured. I struggled a lot without configuring that.

    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

    Also dont forget to place the c3p0-0.9.1.jar in the classpath.

    Its working fine for me after 3days of exploring thus stuff. :)

  4. This didn't fix my issue at all. Hibernate basically refused to work when I used c3p0, but I'm probably doing something wrong...

    I'm getting deadlocks while trying to start up. My hibernate config looks just like the one above...

  5. In response to my earlier post, I have figured out my issue. I'm binding the datasource using JNDI as opposed to defining it in the config file. The method described above ONLY works for connections that are hard-coded in the config.

  6. Thanks for posting this. I couldn't for the life of me figure out how to reconnect if MySQL dropped the connection. I found another detail that throws an error if overlooked:

    If you have <mapping> tags in your hibernate.cfg.xml file, apparently they must appear AFTER the c3p0 properties you outline above. Otherwise you get a very cryptical error that makes it very difficult to identify the problem.

    Why, oh why are things this simple and mundane such a pain to deal with in Java. I'll take PHP any day for rapid web development.

  7. Instead of using that c3p0.properties file, couldn't you just use this property in your hibernate.cfg.xml:

    <property name="hibernate.c3p0.validate">true</property>

    Also checkout the last post on this page:


    It says you need to use hibernate.c3p0.* instead of just c3p0.* in the hibernate.cfg.xml

  8. Great, this worked, taking several comments into account (e.g. connection.provider_class and the c3p0.* changed into hibernate.c3p0.*)

    Thanks all!

  9. c3p0.testConnectionOnCheckout=true
    not very smart, use

  10. c3p0.idleConnectionTestPeriod = 300

  11. Blogs are so informative where we get lots of information on any topic. Nice job keep it up!!

    Internet Dissertation

  12. Very helpful man, we had this exact problem and had no idea what was going on. Thanks for the lead.

  13. Thanks a lot for this Blog. Exact year after your post I ran into this issue. :) Thanks for the solution.

  14. how to use JNDI data source with c3p0 in hibernate

  15. This kind of information is very limited on internet. Nice to find the post related to my searching criteria. Your updated and informative post will be appreciated by blog loving people.

    MBA Dissertation Writing

  16. Sangith spring31 calicut kerala indiaSeptember 2, 2010 at 1:09 AM

    The following hibernate configuration helped me to get rid of that error


    SELECT 1;

  17. Sangith spring31 calicut kerala indiaSeptember 2, 2010 at 1:23 AM

    use the following hibernate configuration

    <property name="hibernate.c3p0.validate">true</property>
    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">60</property>
    <property name="hibernate.c3p0.max_statements">50</property>

    <property name="hibernate.c3p0.preferredTestQuery">SELECT 1;</property>
    <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>

  18. hi, i ve used the same configuration and didn't know what goes wrong with. i ve also added the c3po jar fire on my class path.
    but i still have a probleme on my jboss when i want to access to webservice.

    java.lang.ClassNotFoundException: org.hibernate.connection.C3P0ConnectionProvider from BaseClassLoader

    i ve also added the c3po jar file on jboss_home\common\lib and jboss_home\server\default\lib

    i still have this problem

    thx for help

  19. Really good stuff,
    Can you describe How the exactly hibernate will handle the connection/conn provider.

  20. org.hibernate.connection.C3P0ConnectionProvider

    select Mandar-->

    I am using above config of c3p0.But on update,insert and delete task,each time c3p0 is initializing(actually we are using AJAX Req).So, when connections reaches to 100 ,we get error message as "max_connections_exceeded". How to reuse connections or processes?
    How to destry them?
    How to prevent c3p0 from multiple initializing

  21. Make sure connection timeout of your DB server as well.
    In case of MySQL, set wait_timeout to appropriate values.

  22. Hibernate Online Training Hibernate Online Training Hibernate Training in Chennai Hibernate Training in Chennai Java Online Training Java Online Training Hibernate Training Institutes in ChennaiHibernate Training Institutes in Chennai

  23. Still helping people!

    Thanks for the tips

  24. Usually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man, Keep it up.
    Hibernate Training in Noida

  25. Thank you for your post. This is superb information. It is amazing and great to visit your site.
    Hibernate Training in Gurgaon

  26. Replies
    1. Such a nice and awesome institute. I have done my Website Designing and PHP Programming Language training from there and got a nice job from Stucorner Job assistance. Thanks again

  27. Nice tutorial. Thanks for sharing the valuable information. it’s really helpful. Who want to learn this blog most helpful. Keep sharing on updated tutorials…
    Best Devops Training in pune
    Data science training in Bangalore

  28. Very nice post here and thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.
    Python Online training
    python Training in Chennai
    Python training in Bangalore

  29. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    Selenium training in Chennai
    Selenium training in Bangalore
    Selenium training in Pune
    Selenium Online training

  30. Good job in presenting the correct content with the clear explanation. The content looks real with valid information. Good Work

    DevOps is currently a popular model currently organizations all over the world moving towards to it. Your post gave a clear idea about knowing the DevOps model and its importance.

    Good to learn about DevOps at this time.

    devops training in chennai | devops training in chennai with placement | devops training in chennai omr | devops training in velachery | devops training in chennai tambaram | devops institutes in chennai | devops certification in chennai | trending technologies list 2018


  31. Such a wonderful blog on Machine learning . Your blog have almost full information about Machine learning .Your content covered full topics of Machine learning that it cover from basic to higher level content of Machine learning . Requesting you to please keep updating the data about Machine learning in upcoming time if there is some addition.
    Thanks and Regards,
    Machine learning tuition in chennai
    Machine learning workshops in chennai
    Machine learning training with certification in chennai