Problem with oracle db and connection pools

by pompl :: Rate this Message:

Hi,

I resend this messaage to developers list since no one on users list was able to help me. :(

We use connection pools (dataSource.pooled = true) on an oracle db.
If the application is idle for "some" time (somewhere between 30 minutes and 2 hours), the connection pool seems to be out of date.
Acesses to entities lead to an exception (see below). If I repeat the access two or three times, the entity can be read---the connection pool seems to be alive/refreshed, again.

Is it a bug in grails? Or is it a bug in hibernate? Or in oracle driver? Is there any workaround?
I suppose, the connection has timed out and this information got lost somewhere.

In production this only happens very seldom---since the application is "under heavy use". But the first user at a day runs into this exception---and she is very angry because she is the first one at office every day. ;)

I already know the fact that our oracle db is configured to close connections if they are idle for a configured period. But this must not affect the application.
For the application the db access should be transparent.
Some layer between the application and the final layer of the db has to hide the access of timed out connection pools and re-establish one if needed.

So, is there a way to re-establish the connection pools automatically if needed?

Best regards
Marc Pompl

Stacktrace:

org.codehaus.groovy.runtime.InvokerInvocationException: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not load an entity: [MyEntity#7836798]; uncategorized SQLException for SQL [...]; SQL state [null]; error code [17410]; Keine weiteren Daten aus Socket zu lesen; nested exception is java.sql.SQLException: Keine weiteren Daten aus Socket zu lesen
at org.jsecurity.web.servlet.WebInterceptorFilter.doFilterInternal(WebInterceptorFilter.java:106)
at org.jsecurity.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:124)
Caused by: org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not load an entity: [MyEntity#7836798]; uncategorized SQLException for SQL [...]; SQL state [null]; error code [17410]; Keine weiteren Daten aus Socket zu lesen; nested exception is java.sql.SQLException: Keine weiteren Daten aus Socket zu lesen
at BaseController.getCachedMyEntity(BaseController.groovy:106)
at MyEntityController$_closure2.doCall(MyEntityController.groovy:49)
at MyEntityController$_closure2.doCall(MyEntityController.groovy)
... 2 more
Caused by: java.sql.SQLException: Keine weiteren Daten aus Socket zu lesen
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1123)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1075)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:480)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:810)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1039)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:850)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1134)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3339)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3384)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
... 5 more

Mit freundlichen Grüßen

Dipl. Inform. Marc Pompl PGP-Schlüssel 0x82D7A544

ComNetMedia AG
Enterprise Development

Emil-Figge-Str. 86
D-44227 Dortmund

Tel.: + 49 (0) 231/7 25 93-569
Fax.: + 49 (0) 231/7 25 93-590
pompl@...
http://www.comnetmedia.de

Registergericht Dortmund HRB 14084

Aufsichtratsvorsitzender: Klaus Günzel
Vorstand: René Scheer

« Return to Thread: Problem with oracle db and connection pools