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 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.
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.
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…
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.
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
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
Arg. Seems like my code was stripped out!! :-)
What I meant is: for instance, change “c3p0.min_size” to “hibernate.c3p0.min_size”
Oiliver
wow, great and fast solution, just what I needed. thanks so much
Thx. Very usefull article!
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.
“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]
Adrian, it looks like you’ve solved my problem! Great! :-)
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
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
Thanks a lot, saves me another afternoon of work! Great work.
We use:
hibernate.c3p0.idle_test_period = 100in hibernate.properties
along with
c3p0.preferredTestQuery = SELECT 1;in c3p0.properties and it works just fine.
Regards,
Marcin
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).
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
Thanks Adrian.
I am facing this problem and found no suitable way to solve.
But yours solution, help me a great.
Thanks Again
Great help, thanks alot!
org.hibernate.connection.C3P0ConnectionProvider
thx a lot :D
Thanks great post, save a lot of my time :)))
[...] breakfast, and changing breakfast, without luck. Problem is connections timing out (explained here). MySQL drops database connections after 8 hours by [...]
Thanks everybody for posts.
Anyway i set up felix’s configuration and now mysql behaviour is perfect.
Regards.
Thanks. Great Post :)
Hi. Your post really helps. I have fixed two databases. Thanks You very much :)
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 ?
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
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
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;
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.
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 :)
you save my life!!!
Awesome post, helped me a lot! Cheers!
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.
[...] Automatic reconnect from Hibernate to MySQL [...]
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
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<
Thanks!! You just saved me :)
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!!!
Thanks! it works!
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! :)