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 batch jobs, and I don’t want to use different code for the batch jobs than the web application. Although another company defined Servlets which were “batch job servlets”, and these were executed via a “wget” entered into crontab – 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 option testConnectionOnCheckout – 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>
<property name="connection.provider_class">
   org.hibernate.connection.C3P0ConnectionProvider</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

Amazing, that that stuff doesn’t just work out of the box. Programming the solution myself in Uboot took, I think, 1 line, and I’m sure it’s not more in WebTek either.

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

[Update 28 May 2009] More Java hate today. Starting a new application, deployed it, and it didn’t work. In the morning, the application was down. Reason: The new project used Hibernate 3.3, and upgrade from 3.2 to 3.3 requires the “connection.provider_class” property to be set. Previously the presence of “c3p0.max_size” was enough.

49 Responses to “Automatic reconnect from Hibernate to MySQL”

  1. sabyasachi roy Says:

    i’m using c3p0 and configuring it in hibernate-cfg.xml itself.
    <property name=”c3p0.testConnectionOnCheckout”>true</property>…But still i’m getting the same exception

    ERROR org.hibernate.util.JDBCExceptionReporter – The last packet successfully received from the server was86384 seconds ago.The last packet sent successfully to the server was 86384 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.

    You are lucky that your problem is resolved.
    I doubt about three things..ie
    <property name=”hibernate.c3p0.timeout”>18000</property>
    <property name=”c3p0.maxIdleTime”>18000</property>
    <property name=”c3p0.testConnectionOnCheckout”>true</property>…..is this property available to c3p0…

    i don’t see the xml code in my earlier post so i’m putting a comment in those.

  2. adrian Says:

    You can’t configure the “testConnectionOnCheckout” using the hibernate-cfg.xml, unfortunately. You have to create the “c3p0.properties” file and put it in the root of the classpath. This wasn’t too convenient for me, but it’s what worked…

  3. sebastian Says:

    Thanks adrian for posting this solution.
    I was dealing with this problem for a long time without finding any solution online.
    This helped me a lot.

  4. Ollie Says:

    Dude,

    Welcome to my world of pain. We had this issue on our production machine some months back. The socket would close because it had become idle after X hours. So now my *really simple query*, that never changes, requires a connection pool to get around the issue! Crazy.

    Ollie

  5. Olivier Says:

    Hi and thanks so much for that fix!
    That was such a hassle to make it work.

    Just a comment: I had to change all the hibernate.cfg.xml config that you set with the “hibernate.” equivalent.

    That means instead of:
    5
    20
    1800
    50

    I put:
    5
    20
    1800
    50

    This because I had warnings at startup.

    Olivier

  6. Olivier Says:

    Arg. Seems like my code was stripped out!! :-)

    What I meant is: for instance, change “c3p0.min_size” to “hibernate.c3p0.min_size”

    Oiliver

  7. Saso Says:

    wow, great and fast solution, just what I needed. thanks so much

  8. Andris Dobrosi Says:

    Thx. Very usefull article!

  9. Asif Says:

    Unless any of you guys are performance conscious use

    c3p0.idleConnectionTestPeriod = 5

    Since c3p0.testConnectionOnCheckout runs very frequently.

    Thanks for the excellent solution. Although autoReconnect=true works with tomcat/JNDI.

    Surprising also works on a my local windows machine for a stand alone application, but has issues when it comes to a stand alone application on linux.

  10. simonxy Says:

    “root of the classpath”, can you explain how may I locate it? Can I just simply put it in the same folder as hibernate.cfg.xml?

    [Answer from Adrian - Yes, put it in the same directory as the hibernate.cfg.xml]

  11. Thomas D. Says:

    Adrian, it looks like you’ve solved my problem! Great! :-)

  12. Zac Morris Says:

    Also wanted to add to this dicussion with a great URL that I found that breaks down many of the C3P0 options:

    http://www.mchange.com/projects/c3p0/index.html

  13. Zac Morris Says:

    Also, it looks like you can configure this directly in the hibernate.cfg.xml file via:

    <property name=”c3p0.validate”>true</property><!– Validate the connection on checkout –>

    Source:
    http://www.informit.com/articles/article.aspx?p=353736&seqNum=4

  14. Tim Says:

    Thanks a lot, saves me another afternoon of work! Great work.

  15. Marcin Says:

    We use:
    hibernate.c3p0.idle_test_period = 100
    in hibernate.properties

    along with
    c3p0.preferredTestQuery = SELECT 1;
    in c3p0.properties and it works just fine.

    Regards,
    Marcin

  16. Felix Says:

    Thank you so much for sharing your insights!

    I was confused about whether the property names have to start with “hibernate.” or not. It seems that if you configure Hibernate using JPA (typically in “persistence.xml”), property names have to include the “hibernate.” prefix. If you use Hibernate’s own configuration mechanism (“hibernate.xml”), you have to omit the prefix.

    A lot of sources do not recommend the use of c3p0.testConnectionOnCheckout or c3p0.validate but using c3p0.idle_test_period instead.

    Another good and short article about C3P0 configuration:
    http://blog.hpxn.net/2009/02/05/using-c3p0-and-hibernate-3/

    This configuration seems to work fine in my Hibernate config file:

    <property name=”connection.provider_class”>org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name=”c3p0.min_size”>0</property>
    <property name=”c3p0.max_size”>30</property>
    <property name=”c3p0.timeout”>600</property>
    <property name=”c3p0.max_statements”>0</property>
    <property name=”c3p0.acquire_increment”>1</property>
    <property name=”c3p0.idle_test_period”>60</property>

    Additionally, I have set autoReconnect=true in my JDBC URL for MySQL (just to be safe).

  17. Lavanya Says:

    Hai,
    Iam also getting the same error.
    I configured my hibernate_conf.xml with these parameters:
    org.hibernate.connection.C3P0ConnectionProvider
    100
    1800
    3600

    as well as added c3p0-0.9.1.jar.
    Then also iam getting same error is coming if i run my application for morethan hours..
    Can you please tell me is there anything i have to add to work if without exception.

    Thanks in Advance,
    Lavanya

  18. Jyoti Ranjan Mishra Says:

    Thanks Adrian.

    I am facing this problem and found no suitable way to solve.

    But yours solution, help me a great.

    Thanks Again

  19. Martin Says:

    Great help, thanks alot!

  20. kuki Says:

    org.hibernate.connection.C3P0ConnectionProvider
    thx a lot :D

  21. Tiko Says:

    Thanks great post, save a lot of my time :)))

  22. lilymere » Blog Archive » MySQL Breakfast Error Says:

    [...] breakfast, and changing breakfast, without luck. Problem is connections timing out (explained here). MySQL drops database connections after 8 hours by [...]

  23. Sash Says:

    Thanks everybody for posts.
    Anyway i set up felix’s configuration and now mysql behaviour is perfect.

    Regards.

  24. Asok Lokuge Says:

    Thanks. Great Post :)

  25. :))) Says:

    Hi. Your post really helps. I have fixed two databases. Thanks You very much :)

  26. natoine Says:

    It seems to be the solution to my problem.
    Except that i don’t find where in my classpath i should put the c3po.properties file.
    I’m doing portlets for a jboss-portal server.
    So i deploy .war
    But it seems i cannot put this file in my WEB-INF or META-INF folder.
    Where should i put it ?

  27. adrian Says:

    Hi Natoine, I’ve never used this particular setup myself, but the c3p0.properties (that’s a zero 0 not a letter o) in the root of the classpath. That either means directly inside a JAR (not inside a directory in the JAR), or in the “classes” directory in the “WEB-INF” directory. Hope this helps! Cheers, Adrian

  28. The 8472 Says:

    or… you do what you should do anyway: execute transactions in a loop and re-do them if they fail. with autoreconnect=true and transactions in a loop no connection pool should be necessary

  29. Luan Says:

    Thank you very much! I had been searching this problem for 1 day, then I found this web site. You helped me to solve my problem. But I don’t use
    # c3p0.properties
    c3p0.testConnectionOnCheckout=true

    I use:
    # c3p0.properties
    testConnectionOnCheckin=true
    preferredTestQuery=SELECT 1;

  30. Nitya Says:

    Thanks very much – this issue has been driving me nuts this week and it was good to finally understand what caused it. Do you happen to know if the configuration is set similarly for the myBatis persistence distribution? I plan to look into it myself but if anyone here has already resolved it, that would be good to know.

  31. Alec Says:

    I had the same described issue, when autoreconnect=true did not work. When i have done it, like described:

    hibernate.c3p0.min_size=5
    hibernate.c3p0.max_size=20
    hibernate.c3p0.timeout=1800
    hibernate.c3p0.max_statements=50
    hibernate.connection.provider_class=org.hibernate.connection.C3P0ConnectionProvider
    hibernate.c3p0.validate=true

    I immediately found out, that things became to work slower.
    I have one integration test in my application, which performs a lot of operations (and of course a lot of database queries) – as a result, time of execution exceeded from 40-45 sec, to 80-85 seconds, after these changes…

    But, yeah, connection is always available :)

  32. Silveirinha Says:

    you save my life!!!

  33. Jorge Cordero Says:

    Awesome post, helped me a lot! Cheers!

  34. mozcelik Says:

    Thanks for the past, it solved my problem too. For the configurations I used only persistence.xml to configure c3p0. (no need to c3p0.properties file)

    in persistence.xml I added properties

    hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider
    hibernate.c3p0.idle_test_period = 5000
    hibernate.c3p0.preferredTestQuery = SELECT 1;

    That’s it.

  35. Hibernate mySQL connection lost overnight « Stumbling into Java Says:

    [...] Automatic reconnect from Hibernate to MySQL [...]

  36. Rao Says:

    Hi,

    I am getting following error in the JBOSS log.

    [com.mchange.v2.resourcepool.BasicResourcePool] (C3P0PooledConnectionPoolManager-Helper Thread-#1) com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@15b2f35 — Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
    java.lang.NullPointerException.

    Thye persistence.xml is as follows

    org.hibernate.ejb.HibernatePersistence
    java:/testDS


    Please let me know what am I doing wrong.

    I am using hibernate-c3p0-3.3.2.GA for C3P0ConnectionProvider.class and c3p0-0.9.2.jar with JBOSS,Hibernate

  37. Jacomo Says:

    Great post, love the humor as well. Definitely pointed me in the right direction.

    BTW, for those of you who’ve set hibernate.c3p0.validate=true, be aware that this is a Hibernate 2.x setting only. It does not work in Hibernate 3.x.

    In 3.x you have to create the separate c3p0.properties and set c3p0.testConnectionsOnCheckout=true in there.

    See this page for Hibernate-specific settings in c3p0: <http://www.mchange.com/projects/c3p0/index.html#hibernate-specific&lt;

  38. Dejan Vukmirović Says:

    Thanks!! You just saved me :)

  39. Rodrigo Says:

    I was looking for the solution to this problem, but never finding the correct reference to fix it. Your explanation points directly to solve it! And here it works fine!!!! Thanks a lot!!!

  40. Roger Chen Says:

    Thanks! it works!

  41. AlberTaker Says:

    Hi.

    I was looking for a solution to this problem. I tried all the things you say with no lucky. But I found the fix I need.

    I have to configure and specified this property in my hibernate.cfg.xml (up to date version of Hibernate 3.x):
    <property name="hibernate.connection.release_mode">after_transaction</property>

    With this property set up several solutions works for me (the “testConnectionsOnCheckout” for example). I use the “idle_test_period” with 180 seconds and the problem is solved ;)

    Thanks a lot! :)

  42. Indu Mary Says:

    Adrian ,

    Thank you very much. This post helped me to solve related issues I faced.

    The post is written in an interesting way. After fixing the issues I came back and read the post again!!:)

  43. naveen kumar Says:

    Adrian ,

    thank you so much for solution and research you helped us a lot

  44. Tom Says:

    Lovely and very articulate post on a very frustrating subject. Thanks!

  45. Despot Says:

    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

    Why would I also use idleConnectionTestPeriod?

    If tomcat’s wait_timeout is 28800 sec, and maxIdleTime is 25200, it means that c3p0 will close the idle connection in 3600sec (1h) earlier, before tomcat throws a “broken pipe” exception. Isn’t that right?!

    As you can see I have no issues with providing only maxIdleTime.

    Unfortunately, these:
    <a href=”http://www.mchange.com/projects/c3p0/#maxIdleTime” title=”maxIdleTime”>maxIdleTime</a>
    <a href=”http://www.mchange.com/projects/c3p0/#idleConnectionTestPeriod” title=”idleConnectionTestPeriod”>idleConnectionTestPeriod</a>
    <a href=”http://www.mchange.com/projects/c3p0/#configuring_connection_testing” title=”configuring_connection_testing”>configuring_connection_testing</a>
    <a href=”http://www.mchange.com/projects/c3p0/#testConnectionOnCheckin” title=”testConnectionOnCheckin”>testConnectionOnCheckin</a>
    don’t explain too much the corner cases. So, its gonna be awesome if anyone can explain how these correlate to each other.

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

    Cheers!

  46. Kasthoori Says:

    I have same error in my application. I use hibernate.cfg.xml and also added autoReconnect=”true” Please help me
    Kasthoori

  47. Sandeep Says:

    Thanks a ton!!!!!!!!!!!! it saves my life :)

  48. Nayan Says:

    I configured c3p0 connection provider exactly similar to given example above. But it seems still not working.
    I am using MySQL Server 5.1, hibernate-core-3.6.1.Final.jar, hibernate-c3p0-3.3.1.GA.jar.
    Following in my configuration

    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
      <property name="dataSource" ref="dataSource"/>
      <property name="configLocation" value="classpath:hibernate.cfg.xml"/>
      <property name="hibernateProperties">
        <value>
          hibernate.dialect=${hibernate.dialect}
          hibernate.query.substitutions=true 'Y', false 'N'
          hibernate.cache.use_second_level_cache=true
          hibernate.cache.provider_class=org.hibernate.cache.EhCacheProvider
          hibernate.show.sql=true
          hibernate.connection.provider_class=org.hibernate.connection.C3P0ConnectionProvider
          hibernate.c3p0.min_size=5
          hibernate.c3p0.max_size=20
          hibernate.c3p0.timeout=1800
          hibernate.c3p0.max_statements=50
          hibernate.c3p0.idle_test_period=10
          hibernate.c3p0.acquire_increment=5
          hibernate.connection.release_mode=after_transaction
        </value>
      </property>
    </bean>

    Just to add..am in missing anything.

    Please help, i am stuck up in this issue since last two days.

  49. Jax-ws on Tomcat: reconnect from Hibernate to MySQL using c3p0 connection pool | How To Says:

    […] http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/ […]

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

For inserting HTML or XML please remember to use &lt; instead of <