CODEWITHSUNDEEP
hibernate
user135605
user135605

Reputation: 21

Issues with reconnecting to hibernate

I am using C3P0 Connection pool. I have added a class TestConnectionProvider which extends C3P0ConnectionProvider. What I want is that after the exception is caught, Hibernate should still try to reconnect. However after catching the exception the application is stopped.

The sequence is as follows 1) MySQL Connection is stopped 2) Hibernate tries to reconnect a number of times. 3) I start MySQL connection. 4) The Hibernate obtains the connection and starts inserting data. 5) I stopped the connection again. 6) Now Hibernate throws exception which is not caught by my application.

What I want to achieve is Whenever Hibernate looses connection it should be handled by the exception handling mechanism of the application and still try to reconnect whether the connection is not available at start or in between.

Upvotes: 0

Views: 931

Answers (1)

djb
djb

Reputation: 668

I am assuming that you are not referring to an error that occurs while you are performing a long running Hibernate transaction.

If you are, the solution is not directly related to your connection pool. You need to catch the exception (exactly where is for you to say, but perhaps at the level you started the transaction), rollback the transaction and keep trying until you succeed (i.e. start a fresh transaction). The fact that you mention your connection pool seems to indicate to me that you are having issues with your c3p0 reconnection settings.

Here are my hibernate connection parameters (from a .properties file, yours might be in xml)...

...
hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=jdbc:mysql://localhost/db
hibernate.connection.username=user
hibernate.connection.password=password
hibernate.connection.provider_class=org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider
hibernate.c3p0.minPoolSize=3
hibernate.c3p0.maxPoolSize=200
hibernate.c3p0.maxStatements=100
hibernate.c3p0.testConnectionOnCheckout=true
hibernate.c3p0.idleConnectionTestPeriod=300
...

Note the c3p0 parameters testConnectionOnCheckout and idleConnectionTestPeriod. More information on them here. This post is related too.

Provided you are closing your Hibernate sessions properly (and thus releasing the connection back into the connection pool), these settings will allow you to handle lost database connectivity while your application is running.

If, after you add these config settings in and database connectivity is still not resilient, do a double check in the logs to ensure you are actually initialising a c3p0 pool. You should see this (after you set log4j.logger.com.mchange.v2=INFO)...

INFO 2013-01-17 11:14:18,832 [main] com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource: Initializing c3p0 pool...

Also check that you are closing your Hibernate sessions properly when you're done with them. You can check this by using a "performance" test that opens many hibernate sessions at once (by perhaps sending many HTTP requests at once, if you are building a web app), combined with using show processlist from within the MySQL console (see here for more info).

If the list of processes keeps growing, you are not releasing connections properly and no connection pool on earth can save you.

Upvotes: 1

Related Questions