Connection with MySql is being aborted automatically. How to configure Connector/J properly?

Question

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

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?)

Saprophyte

Answer 1

The text describes three solutions to prevent connection aborts:

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

   url="jdbc:mysql://localhost:3306/confluence?autoReconnect=true"
   

2. Increase the timeout. This is normally a property of the database. You can increase this value to see if you get less connection abort.

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

   • Connection pooling options with JDBC: DBCP vs C3P0
   • http://snipplr.com/view/14725/c3p0-datasource-config-connectionpool/
   • http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

Additionnal comments:

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

Helotism

The MySQL driver does not properly implement the autoReconnect option. The first request will fail, and the driver attempts to reestablish the connection AFTERWARDS. – Impute

Since this answer seems to be the one favored by the search engine, it would be nice if it came with some additional explanation, on the meaning of wait_timeout for MySQL, and how it should play with a c3p0 configuration. Faced with the issue, I can see the MySQL wait_time is 28800. If that's 8h then I don't feel like setting it to say 72h. I don't even understand how it can be accepted that a connection would hang for so long as 8h. – Tiana

@StephaneEybert I found a real nice explanation of this issue here : hibernatedb.blogspot.in/2009/05/…. This may help someone in future – Patinous

can someone help #47011616 – Valor

What is the advantage of a test table over preferredTestQuery? – Chevrotain

Answer 2

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.

Interlay

So what then, is recommended instead? – Behoof

use c3po with option testConnectionOnCheckout=true – Lighten

@AndersB I use c3po, but error still persist #47011616 – Valor

mysql recommend to increase the time_wait – Biebel

Answer 3

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

c3p0.properties

c3p0.testConnectionOnCheckout=true

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][1] and read in detail


  [1]: http://hibernatedb.blogspot.in/2009/05/automatic-reconnect-from-hibernate-to.html

Terzas

Answer 4

The other answers are better long term solutions. But if you just need the MySQL connection running again right away, you can shutdown then restart Tomcat and everything will work fine for a while. This enables you to avoid system downtime while you figure out a longer term solution.

Navigate to $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.

Vinegary

The question didn't mention anything about using Tomcat. – Jujutsu

@Jujutsu The question is about spring. Tomcat is the primary container used for hosting spring apps. And the method described in this answer resolves the OP problem. – Vinegary

Why are we targeting some solution that does not work for long term? All DB servers and connections need to be designed with long term perspective in mind – Patinous

@Patinous Because sometimes this problem happens on a devbox whose user needs to focus on a different deliverable right at that moment. The answer I gave is an immediate solution that gives the developer the ability to wait until they have time to invest in a longer term solution. – Vinegary