My application is in idle state since yesterday night. Today its giving me JDBCConnectionException with SQL state 08S01. In this situtaion, I'd like to reconnect the session.

Below is the code I use to get current session. Multiple sessionfactory are maintained outside since the application deals with multiple databases. Before returning the Session handle, it calls isConnected() and reconnect if required. But when I was debugging the code, it showed me that session is connected(even underlying connection may have closed) and later on when that session was used, it threw JDBCConnectionException.

Now my question is how do I make sure that database connection is established before using Session object and if the connection is not present, reconnect. This will gurantee that at any point of time, any JDBCConnectionException won't occur(unless database is down or change in password)



Please help !

I am having a similar problem.

A session object is created. The connection associated with the session is closed by mysql due to connection timeout. If I try to use this session after the connection is closed, I get a JDBCConnectionException. I am expecting the session object to realize that the connection is dead and to get another one from the connection pool, but it doesn't.

I am using c3p0 with mysql, using the following configuration in hibernate.cfg.xml:
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">50</property>
<property name="hibernate.c3p0.timeout">60</property>

As far as my db settings, mysql closes any idle connections that are 300 seconds old. Using hibernate 3.1 andy c3p0 8.5.

Any advice is appreciated.

I've tried 'hibernate.connection.release_mode=after_statement' without any results.

I am not sure if this helps, but you may want to give hibernate a reference to your transaction manager if hibernate actions happen in the context a transaction. This helps hibernate get notified when transaction is over, and hibernate will close its connections after a transaction finishes.

Just a suggestion, but you might try using a BasicDataSource from Apache DBCP (DataBase Connection Pooling). It's what I use, and I never have to worry about such cases because Apache DBCP handles everything for me.

Thanks for the replies, but:

I don't want to close the connection after each transaction, the session should be long lived.

Also, I am stuck using c3p0 because of some 3rd party software that is being utilized in the system.

Closing a connection will return it to your pool. I does not necessarily close the connection to your database. To quickly check if you have this problem test your application with JBoss and look in your log for "Closing a connection for you....."

[quote="farzad"]I am not sure if this helps, but you may want to give hibernate a reference to your transaction manager if hibernate actions happen in the context a transaction. This helps hibernate get notified when transaction is over, and hibernate will close its connections after a transaction finishes.[/quote]

I am not sure how to do what you're suggesting above.


Here's what I did try, added a check before using the session to see if it's connected:
if(session.getConnection.isClosed())
{
//create new session
}

but that didn't work. The session connection thinks it's still open, but the database has closed the connection already... very strange.

What I really would like is some configuration parameters in hibernate and/or c3p0, such that the Session object itself realizes that the connection is dead and gets a new connection from the pool. Is this even possible?

This should help you through configuration steps:

http://www.hibernate.org/hib_docs/v3/re ... onstrategy

IMHO you shouldn't need to deal with these things. Are you running your application in a J2EE container?

I am using hibernate's default: org.hibernate.transaction.JDBCTransactionFactory
And using the Hibernate Transaction API to open and close transactions, and running a standalone application, not in J2EE environment.

Here is my test: I create a hibernate Session object when the application starts, I wait 5 mins (to let mysql close the idle connection), then I try to use the session object and here's the exception I get:

org.hibernate.exception.JDBCConnectionException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:72)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2150)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
at org.hibernate.loader.Loader.list(Loader.java:2021)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:296)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:992)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at jpl.mipl.pgs.perstore.Database.createSession(Database.java:180)
at jpl.mipl.pgs.perstore.PersistentStore.createSession(PersistentStore.java:205)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
at sun.rmi.transport.Transport$1.run(Transport.java:153)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
at java.lang.Thread.run(Thread.java:595)
at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:247)
at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:223)
at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:126)
at sun.rmi.server.ActivatableRef.invoke(ActivatableRef.java:124)
at jpl.mipl.pgs.perstore.PersistentStore_Stub.createSession(Unknown Source)
at jpl.mipl.pgs.client.sf.init(sf.java:48)
at jpl.mipl.pgs.client.sf.run(sf.java:126)
at jpl.mipl.pgs.client.sf.main(sf.java:158)
Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:

** BEGIN NESTED EXCEPTION **

java.io.EOFException

STACKTRACE:

java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2351)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1124)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:676)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:75)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:137)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1676)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
at org.hibernate.loader.Loader.list(Loader.java:2021)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:296)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:992)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at jpl.mipl.pgs.perstore.Database.createSession(Database.java:180)
at jpl.mipl.pgs.perstore.PersistentStore.createSession(PersistentStore.java:205)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
at sun.rmi.transport.Transport$1.run(Transport.java:153)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
at java.lang.Thread.run(Thread.java:595)


** END NESTED EXCEPTION **



Last packet sent to the server was 52 ms ago.
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2563)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1124)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:676)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:75)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:137)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1676)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
at org.hibernate.loader.Loader.doList(Loader.java:2147)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
at org.hibernate.loader.Loader.list(Loader.java:2021)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:296)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:992)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at jpl.mipl.pgs.perstore.Database.createSession(Database.java:180)
at jpl.mipl.pgs.perstore.PersistentStore.createSession(PersistentStore.java:205)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
at sun.rmi.transport.Transport$1.run(Transport.java:153)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
at java.lang.Thread.run(Thread.java:595)

Do you really need to use the same session instance in the entire timeout period for a connection? My understanding is that you need to open a new session for each unit of work and close it after you are done. This is an example from Session class's JavaDoc:



However, you will need to change your database settings if it closes connections after 5 minutes and your unit of work takes longer than 5 minutes.