I read this advice from error message:
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
I’m using Spring and JPA. Where should I configure Connector/J? (in
persistence.xml, or in
entityManagerFactory spring configuration, or in
dateSource spring configuration, or somewhere else?)
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
The text describes three solutions to prevent connection aborts:
Configure the connection string with
autoReconnect=true. This is a property of the URL connection string, which works at the driver level. You need to change the connection string in the data source configuration.
- Increase the timeout. This is normally a property of the database. You can increase this value to see if you get less connection abort.
- Configure the connection pool to test the connection validatiy. This is done at the pool, not a the driver level. This will depend on the data source implementation that you use. But it should be configurable in the property of the data source, if you use a pooled one, e.g. c3p0.
- The datasource/pool can also have a timeout, which corresponds to the time an idle connection remains in the pool. To not confused with the db timeout.
- There are several way to test the validity of a connection. One common way is to have dummy test table. The pool will issue a select on the dummy test table to see if the connection is still OK.
AutoReconnect is not recommended. From MySQL here
Should the driver try to re-establish stale and/or dead connections?
If enabled the driver will throw an exception for a queries issued on
a stale or dead connection, which belong to the current transaction,
but will attempt reconnect before the next query issued on the
connection in a new transaction. The use of this feature is not
recommended, because it has side effects related to session state and
data consistency when applications don’t handle SQLExceptions
properly, and is only designed to be used when you are unable to
configure your application to handle SQLExceptions resulting from dead
and stale connections properly. Alternatively, as a last option,
investigate setting the MySQL server variable “wait_timeout” to a high
value, rather than the default of 8 hours.
I was go through many solutions and my problem was solved but after some time the connection is timeout or disconnected.After 2 3 days I got a solution that solve my problem.
many solution suggest to use autoReconnect=true but when I was go through the docs. 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
When I looked in the Hibernate code. The basic connection mechanism of Hibernate doesn’t support reconnecting, one has to use H3C0 connection pool (which itself didn’t always support reconnecting).
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):
If this solution don’t work than there are more possible solutions:-
1. Add <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property> Also dont forget to place the c3p0-0.9.1.jar in the classpath. 2. 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 If all these not work than go [more] and read in detail : http://hibernatedb.blogspot.in/2009/05/automatic-reconnect-from-hibernate-to.html
This is for people like me who find this old posting through the search engines.
The other answers are better long term solutions. But if you just need the
mysql connection running again right away, you can
tomcat and everything will work fine for a while. This enables you to avoid system downtime while you figure out a longer term solution.
$CATALINA_HOME in the
terminal, then type
shutdown.sh, then type
startup.sh. Wait a few moments for the startup sequence to complete, then your apps will work again for a while.