Re: Problem with oracle db and connection pools

Mike Fontenot wrote:

Could one of you post a link to the specific HOWTO configuring C3P0
with Grails?  That is eluding me.

Thanks,

Mike

On Fri, May 23, 2008 at 11:18 AM, Marc Pompl <marc.pompl@lynorics.de> wrote:
> Hi Marcos,
>
> thanks for your information.
>
> I have just tested the instruction you mentioned and it seems the behaviour
> has gone.
> But I will report again in one or two weeks, if I am right.
>
>
> Thanks,
> Marc Pompl
>
>
>
> marcos binelli schrieb:
>>
>> 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@comnetmedia.de <mailto:pompl@comnetmedia.de>>:
>>
>>
>>    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
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>   http://xircles.codehaus.org/manage_email
>
>
>



--
Mike

================================
Mike Fontenot
Apex Consulting & Services LLC
Lakewood, Colorado
303/641.1282
mikef AT apexconsultingservices DOT com
================================

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email