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





22 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