c3p0.properties

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

Answer 1

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.
```
url="jdbc:mysql://localhost:3306/confluence?autoReconnect=true"
```
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.

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.

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.

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

c3p0.properties

友情链接