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

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

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.

• https://stackoverflow.com/questions/520585/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.

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

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

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