Stale Connection Error

[bshah]

Hey All,

I am being faced with a Stale Connection Error when one of my
application is trying to Connect to a Oracle Database. The Websphere
version is 5.0 (Base) on Solaris.
The Database Connection Pool Setting Currently are as below:
Connection timeout - 30 sec
Max Connection - 5
Min Connection - 1
Reap Time - 1800
Unused Time - 7200
Aged Timeout -0
Purge Policy - Entire Pool.

Please let me know how should i get rid of this error. Right now if
start and stop the App server where this application resides it starts
to work fine for some days and then throws this error.

Thanks
bshah

[1/29/07 13:59:03:756 CST] 7510e7 StaleConnecti A CONM7007I: Mapping
the following SQLException, with ErrorCode 17,410 and SQLState <null>,
to a StaleConnectionException: java.sql.SQLException: No more data to
read from socket
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:933)
at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:739)
at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:698)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:373)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.executeFetch(TTC7Protocol.java:729)
at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:
1904)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:
2015)
at
oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:
395)
at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:
339)
at
com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPreparedStatement.java:
506)
at
com.fujisawa.common.util.sql.ConnectionFactory.isValidConnection(ConnectionFactory.java:
210)
at
com.fujisawa.common.util.sql.ConnectionFactory.getConnection(ConnectionFactory.java:
127)
at
com.fujisawa.common.util.sql.ConnectionFactory.getConnection(ConnectionFactory.java:
90)
at
com.fujisawa.common.util.sql.GenericDAO.getConnection(GenericDAO.java:
146)
at
com.astellas.starpass.app.dao.StarPassDAO.insertTransaction(StarPassDAO.java:
141)
at
com.astellas.starpass.web.datahandler.StarPassHelper.insertTransaction(StarPassHelper.java:
48)
at
com.astellas.starpass.web.action.RequestResetAction.createErrorTransaction(RequestResetAction.java:
141)
at
com.astellas.starpass.web.action.RequestResetAction.isValidResetRequest(RequestResetAction.java:
54)
at
com.astellas.starpass.web.action.RequestResetAction.save(RequestResetAction.java:
255)
at java.lang.reflect.Method.invoke(Native Method)
at
com.astellas.starpass.web.action.ApplicationActionProcessor.invokePageAction(ApplicationActionProcessor.java:
106)
at
com.fujisawa.common.web.util.struts.FujisawaActionProcessor.execute(FujisawaActionProcessor.java:
575)
at
com.astellas.starpass.web.action.ApplicationActionProcessor.execute(ApplicationActionProcessor.java:
88)
at
com.fujisawa.common.web.util.struts.FujisawaAction.execute(FujisawaAction.java:
57)
at
org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:
484)
at
com.astellas.starpass.web.ApplicationRequestProcessor.processActionPerform(ApplicationRequestProcessor.java:
82)
at
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:
1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:
525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:
110)
at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:
174)
at
com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:
313)
at
com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:
116)
at
com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:
258)
at
com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:
42)
at
com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:
40)
at
com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:
76)
at
com.fujisawa.common.web.util.struts.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:
63)
at
com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:
132)
at
com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:
71)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:
863)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:
491)
at
com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:
173)
at
com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:
79)
at
com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:
199)
at
com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:
71)
at
com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:
114)
at
com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:
187)
at
com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:
331)
at
com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:
56)
at
com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:
432)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)

[1/29/07 13:59:03:777 CST] 7510e7 ConnectionEve A J2CA0056I: The
Connection Manager received a fatal connection error from the Resource
Adaptor for resource jdbc/oraDS. The exception which was received is
No more data to read from socket
2007-01-29 13:59:03,795 [Servlet.Engine.Transports : 3] ERROR
com.fujisawa.systemlogger - ConnectionFactory.getConnection() Error:
connection was stale, trying again...

[1/29/07 13:59:03:795 CST] 7510e7 SharedPool I J2CA0086W:
Shareable connection MCWrapper id 6d3ca2 Managed connection
com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl@27bd3b
State:STATE_TRAN_WRAPPER_INUSE
from resource jdbc/oraDS was used within a local transaction
containment boundary.

[smit...@netzero.com]

> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.jav­a:
> 2015)
> at
> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStat­ement.java:
> 395)
> at
> oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedState­ment.java:
> 339)
> at
> com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeQuery(WSJdbcPrepar­edStatement.java:
> 506)
> at
> com.fujisawa.common.util.sql.ConnectionFactory.isValidConnection(Connection­Factory.java:
> 210)
> at
> com.fujisawa.common.util.sql.ConnectionFactory.getConnection(ConnectionFact­ory.java:
> 127)
> at
> com.fujisawa.common.util.sql.ConnectionFactory.getConnection(ConnectionFact­ory.java:

> 90)
> at
> com.fujisawa.common.util.sql.GenericDAO.getConnection(GenericDAO.java:
> 146)
> at

> com.astellas.starpass.app.dao.StarPassDAO.insertTransaction(StarPassDAO.jav­a:
> 141)
> at
> com.astellas.starpass.web.datahandler.StarPassHelper.insertTransaction(Star­PassHelper.java:
> 48)
> at
> com.astellas.starpass.web.action.RequestResetAction.createErrorTransaction(­RequestResetAction.java:
> 141)
> at
> com.astellas.starpass.web.action.RequestResetAction.isValidResetRequest(Req­uestResetAction.java:
> 54)
> at
> com.astellas.starpass.web.action.RequestResetAction.save(RequestResetAction­.java:

> 255)
> at java.lang.reflect.Method.invoke(Native Method)
> at

> com.astellas.starpass.web.action.ApplicationActionProcessor.invokePageActio­n(ApplicationActionProcessor.java:
> 106)
> at
> com.fujisawa.common.web.util.struts.FujisawaActionProcessor.execute(Fujisaw­aActionProcessor.java:
> 575)
> at
> com.astellas.starpass.web.action.ApplicationActionProcessor.execute(Applica­tionActionProcessor.java:
> 88)
> at
> com.fujisawa.common.web.util.struts.FujisawaAction.execute(FujisawaAction.j­ava:
> 57)
> at
> org.apache.struts.action.RequestProcessor.processActionPerform(RequestProce­ssor.java:
> 484)
> at
> com.astellas.starpass.web.ApplicationRequestProcessor.processActionPerform(­ApplicationRequestProcessor.java:

> 82)
> at
> org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:
> 274)
> at org.apache.struts.action.ActionServlet.process(ActionServlet.java:
> 1482)
> at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:
> 525)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at

> com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServl­etInstance.java:
> 110)
> at
> com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifec­ycleServlet.java:
> 174)
> at
> com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleSer­vlet.java:
> 313)
> at
> com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecy­cleServlet.java:
> 116)
> at
> com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.jav­a:
> 258)
> at
> com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidSe­rvletReferenceState.java:
> 42)
> at
> com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletIn­stanceReference.java:
> 40)
> at
> com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain­.java:
> 76)
> at
> com.fujisawa.common.web.util.struts.SetCharacterEncodingFilter.doFilter(Set­CharacterEncodingFilter.java:
> 63)
> at
> com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanc­eWrapper.java:
> 132)
> at
> com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain­.java:
> 71)
> at
> com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch­(WebAppRequestDispatcher.java:
> 863)
> at
> com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppReque­stDispatcher.java:
> 491)
> at
> com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppReques­tDispatcher.java:

> 173)
> at
> com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:
> 79)
> at

> com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoke­r.java:
> 199)
> at
> com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(­CachedInvocation.java:
> 71)
> at
> com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(­CacheableInvocationContext.java:
> 114)
> at
> com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRe­questProcessor.java:
> 187)
> at
> com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListen­er.java:
> 331)
> at
> com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.ja­va:

> 56)
> at
> com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:
> 432)
> at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
> at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)
>
> [1/29/07 13:59:03:777 CST] 7510e7 ConnectionEve A J2CA0056I: The
> Connection Manager received a fatal connection error from the Resource
> Adaptor for resource jdbc/oraDS. The exception which was received is
> No more data to read from socket
> 2007-01-29 13:59:03,795 [Servlet.Engine.Transports : 3] ERROR
> com.fujisawa.systemlogger - ConnectionFactory.getConnection() Error:
> connection was stale, trying again...
>
> [1/29/07 13:59:03:795 CST] 7510e7 SharedPool I J2CA0086W:
> Shareable connection MCWrapper id 6d3ca2 Managed connection
> com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl@27bd3b
> State:STATE_TRAN_WRAPPER_INUSE
> from resource jdbc/oraDS was used within a local transaction
> containment boundary.

Do you have a firewall any place in between that could be killing the
connection to oracle? Also is there a timeout on the oracle end that
could be killing the connection? In my experience there is something
external to WAS killing the connection out from under WAS. Once you
ask for a connection, WAS realizes that it does not have any
connections in the pool and attempts to reestablish the pool, thus
throwing the stale connection error. You could put some retry code
into your application to attempt to open a connection X number of
times before actually returning an error to the user. This has worked
for me in the past when dealing with firewall killing the connection
without WAS knowledge of it. Hope this helps.

[Ray]

You can set up to retry when that exception is thrown. You can also
set the min connects to zero in the pool. That way if it is a DBA
"cleaning up" connections it will be much less likely the pool will be
sitting there keeping an idle connection open.

[bshah]

Good thought, I will check with the network guys to find out if there
are any firewall in between and if there is a firewall what is the
timeout for the firewall.
I had my DBA look into Oracle and we do not have any timeouts there.
It is so set that, only the client can kill the connections.

Maybe i will put this thought across to my Developers and see wht they
say.

Thanks a lot
bshah

>
> Do you have a firewall any place in between that could be killing the
> connection to oracle? Also is there a timeout on the oracle end that
> could be killing the connection? In my experience there is something
> external to WAS killing the connection out from under WAS. Once you
> ask for a connection, WAS realizes that it does not have any
> connections in the pool and attempts to reestablish the pool, thus
> throwing the stale connection error. You could put some retry code
> into your application to attempt to open a connection X number of
> times before actually returning an error to the user. This has worked
> for me in the past when dealing with firewall killing the connection

> without WAS knowledge of it. Hope this helps.- Hide quoted text -
>
> - Show quoted text -

[bshah]

I will try that..Thanks
bshah