Re: Problem with oracle db and connection pools

> l doing try to configure c3p0 in grails. What l know now is:
>
> The hybernate pool isn´t work good in the production environments
> Try the instrution autoreconnect = true
> The c3p0 is the best way to work with connection pool
>
> But I really don´t know if this is true
>
> Good Luck
> 2008/5/5 <pompl@... <mailto:pompl@...>>:
>
>
>     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
>