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
c3p0.testConnectionOnCheckout=true

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…..





103 comments:

  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.

    ReplyDelete
  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

    ReplyDelete
  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. :)

    ReplyDelete
  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...

    ReplyDelete
  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.

    ReplyDelete
  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.

    ReplyDelete
  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:

    https://forum.hibernate.org/viewtopic.php?p=2399313

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

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

    Thanks all!

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

    ReplyDelete
  10. c3p0.idleConnectionTestPeriod = 300

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

    Internet Dissertation

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

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

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

    ReplyDelete
  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

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

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


    true
    org.hibernate.connection.C3P0ConnectionProvider
    5
    20
    1800
    50

    SELECT 1;
    true

    ReplyDelete
  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>

    ReplyDelete
  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

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

    ReplyDelete
  20. org.hibernate.connection.C3P0ConnectionProvider

    1
    10
    60
    0
    100
    5
    20
    10
    6
    60
    false
    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
    ?

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

    ReplyDelete
  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

    ReplyDelete
  23. Still helping people!

    Thanks for the tips

    ReplyDelete
  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

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

    ReplyDelete
  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

      Delete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete

  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

    ReplyDelete
  32. Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
    Thanks & Regards,
    VRIT Professionals,
    No.1 Leading Web Designing Training Institute In Chennai.

    And also those who are looking for
    Web Designing Training Institute in Chennai
    SEO Training Institute in Chennai
    Photoshop Training Institute in Chennai
    PHP & Mysql Training Institute in Chennai
    Android Training Institute in Chennai

    ReplyDelete
  33. Very informative and well written post! Quite interesting and nice topic chosen for the post.
    thanks for sharing this nice post,
    tally course in hyderabad

    ReplyDelete
    Replies
    1. It should be noted that whilst ordering papers for sale at paper writing service, you can get unkind attitude. In case you feel that the bureau is trying to cheat you, don't buy term paper from it.

      Delete
  34. Nice blog. Can't be written much better. You’re doing a great job. Keep continuing.
    Selenium Training in Chennai | SeleniumTraining Institute in Chennai

    ReplyDelete
  35. Hey Nice Blog!! Thanks For Sharing!!!Wonderful blog & good post.Its really helpful for me, waiting for a more new post. Keep Blogging!
    SEO company in coimbatore
    SEO Service in Coimbatore
    web design company in coimbatore

    ReplyDelete
  36. It is a great post. Keep sharing such kind of useful information.

    Technology
    karnatakapucresult

    ReplyDelete
  37. If you are serious about a career pertaining to Trending Technologies.Found one website that’s gives more information about various Technologies.

    ReplyDelete
    Replies
    1. It should be noted that whilst ordering papers for sale at paper writing service, you can get unkind attitude. In case you feel that the bureau is trying to cheat you, don't buy term paper from it.

      Delete
  38. Thank you for your post, I look for such article along time, today i find it finally. this post give me lots of advise it is very useful for me.

    ReplyDelete
  39. Attend the Best Machine learning training Courses in Bangalore From ExcelR. Practical Machine learningTraining Sessions with Assured Placement From Excelr Solutions.

    machine learning course

    ReplyDelete
  40. Thanks for sharing.Really nice information.
    Attend python course in hyderabad from ExcelR

    ReplyDelete
  41. Attend the Best Python training Courses in Bangalore From ExcelR. Practical Python Training Sessions with Assured Placement From Excelr Solutions.

    Python Online Course

    ReplyDelete
  42. It should be noted that whilst ordering papers for sale at paper writing service, you can get unkind attitude. In case you feel that the bureau is trying to cheat you, don't buy term paper from it.

    ReplyDelete
  43. Thank you for your post, I look for such article along time, today i find it finally, this post give me lots of information and it is very useful for me.

    Data Science Courses in Bangalore

    ReplyDelete
  44. I really enjoy simply reading all of your weblogs. Simply wanted to inform you that you have people like me who appreciate your work. Definitely a great post. Hats off to you! The information that you have provided is very helpful.
    https://www.excelr.com/blogs/

    ReplyDelete
  45. I like this i requemend you to read this

    ReplyDelete
  46. Attend The lean six sigma training in bangalore From ExcelR. Practical lean six sigma training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The lean six sigma training in bangalore.
    lean six sigma training in bangalore

    ReplyDelete
  47. Attend The lean six sigma training in bangalore From ExcelR. Practical lean six sigma training in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The lean six sigma training in bangalore.
    lean six sigma training in bangalore

    ReplyDelete
  48. Here at this site really the fastidious material collection so that everybody can enjoy a lot.
    Data Science Course in Pune

    ReplyDelete
  49. Hi, i have read about this article it was very useful. everything is explained in detail and its awesome. want to learn more about data science then follow this given link :
    https://www.excelr.com/data-science-course-training-in-bangalore/


    ReplyDelete
  50. I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts.
    machine learning course malaysia

    ReplyDelete
  51. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
    what are solar panel how to identify best solar

    ReplyDelete
  52. Hi, i have read this article , it was very useful, everything is explained in detail about. if you want to learn more about this particular topic then follow this given link :
    https://www.excelr.com/data-science-course-training-in-bangalore/

    ReplyDelete
  53. Language is the primary way to strengthen your roots and preserve the culture, heritage, and identity. Tamil is the oldest, the ancient language in the world with a rich literature. Aaranju.com is a self-learning platform to learn Tamil very easy and effective way.
    Aaranju.com is a well-structured, elementary school curriculum from Kindergarten to Grade 5. Students will be awarded the grade equivalency certificate after passing the exams. Very engaging and fun learning experience.
    Now you can learn Tamil from your home or anywhere in the world.

    You can knows more:

    Learn Tamil thru English

    Tamil School online

    Easy way to learn Tamil

    Learn Tamil from Home

    Facebook

    YouTube

    twitter

    ReplyDelete

  54. Thanks for sharing this post.
    Interested in Graphic Designing Course!!!
    Contact ACIL for the best Graphic designing Institute in Gurgaon.
    GRAPHIC DESIGNING INSTITUTE IN GURGAON

    ReplyDelete
  55. Attend The Python course in bangalore From ExcelR. Practical Python course in bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Python course in bangalore.
    python course in bangalore

    ReplyDelete
  56. Thanks for sharing this post.
    Want to make your own website!!!
    Contact ACIL the best web design company in Delhi.
    WEB DESIGN COMPANY IN DELHI



    Interested in Graphic Designing Course!!!
    Contact ACIL for the best Graphic designing Institute in Gurgaon.
    GRAPHIC DESIGNING INSTITUTE IN GURGAON



    Interested in Python training!!!
    Contact ACIL for the best Python Training Institute in Gurgaon.
    PYTHON TRAINING INSTITUTE IN GURGAON



    Interested in Web Designing Course!!!
    Contact ACIL for the best Web Designing Institute in Gurgaon.
    WEB DESIGNING INSTITUTE IN GURGAON

    ReplyDelete
  57. This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website
    ExcelR Solutions Offer Data Analytics Course Training Partnered With UNIMAS In Malaysia

    ReplyDelete
  58. Here at this site really the fastidious material collection so that everybody can enjoy a lot.

    web designer

    ReplyDelete
  59. Attend The Lean Six Sigma Training in Bangalore From ExcelR. Practical Lean Six Sigma Training in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Lean Six Sigma Training in Bangalore.
    Lean Six Sigma Training in Bangalore

    ReplyDelete
  60. Attend The Lean Six Sigma Training in Bangalore From ExcelR. Practical Lean Six Sigma Training in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Lean Six Sigma Training in Bangalore.
    Lean Six Sigma Training in Bangalore

    ReplyDelete
  61. Thanks for sharing ! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites! keep it up

    ReplyDelete
  62. Find a local DJ, DJ wanted London
    https://www.djrequired.co.uk/
    Dj Required has been setup by a mixed group of London’s finest Dj’s, a top photographer and cameraman. Together we take on Dj’s, Photographers and Cameramen with skills and the ability required to entertain and provide the best quality service and end product. We supply Bars, Clubs and Pubs with Dj’s, Photographers, and Cameramen. We also supply for private hire and other Occasions. Our Dj’s, Photographers and Cameramen of your choice, we have handpicked the people we work with

    ReplyDelete
  63. Attend The Machine Learning course Hyderabad From ExcelR. Practical Machine Learning course Hyderabad Sessions With Assured

    Placement Support From Experienced Faculty. ExcelR Offers The Machine Learning course Hyderabad.
    <a href="https://www.excelr.com/machine-learning-course-training-in-hyderabad/> Machine Learning Course in Bangalore</a>

    ReplyDelete
  64. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us

    You will get an introduction to the Python programming language and understand the importance of it. How to download and work with Python along with all the basics of Anaconda will be taught. You will also get a clear idea of downloading the various Python libraries and how to use them.
    Topics
    About ExcelR Solutions and Innodatatics
    Do's and Don’ts as a participant
    Introduction to Python
    Installation of Anaconda Python
    Difference between Python2 and Python3
    Python Environment
    Operators
    Identifiers
    Exception Handling (Error Handling)
    Data Analytics

    ReplyDelete
  65. Thank you for excellent article.You made an article that is interesting.
    Tavera car for rent in coimbatore|Indica car for rent in coimbatore|innova car for rent in coimbatore|mini bus for rent in coimbatore|tempo traveller for rent in coimbatore|kodaikanal tour package from chennai

    Keep on the good work and write more article like this...

    Great work !!!!Congratulations for this blog

    ReplyDelete
  66. Thank you for excellent article.You made an article that is interesting.
    Tavera car for rent in chennai|Indica car for rent in chennai|innova car for rent in chennai|mini bus for rent in chennai|tempo traveller for rent in chennai
    Keep on the good work and write more article like this...

    Great work !!!!Congratulations for this blog


    ReplyDelete
  67. The article is so informative. This is more helpful for our
    software testing training institute in chennai
    selenium training Thanks for sharing

    ReplyDelete
  68. Your content is very impressive and thanks for sharing this article. its very useful.
    servicenow online training

    ReplyDelete
  69. This comment has been removed by the author.

    ReplyDelete
  70. i feel beter to read your blog.
    To read more please visit: https://www.airlinesbooking.net/easyclicktravel-reviews-find-best-cheap-flight-deals/

    ReplyDelete
  71. This is also a very good post which I really enjoyed reading. It is not every day that I have the possibility to see something like this,
    Great website

    ReplyDelete