WSAD 5 / WAS 4: JDBC connection pooling problem - Connection reset by peer
Brian Oster
database using a connection from the WAS controlled connection pool.
I am using Microsofts JDBC driver for SQL server 2K. If the
application server "sits" for an extended period of time w/o anyone
accessing it, when the servlets/jsp's execute they fail. The logs
shows the following exception being thrown (I will put the full stack
trace at the end of this post):
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Connection reset by peer: socket write error
I only get this error once, if I reload the page, everything works
fine. I only get it if the servlet or jsp is not accessed for an
extended period of time.
Application was developed using WSAD 5 targeted at a WAS 4 server.
Brian Oster
Full stack trace:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Connection reset by peer: socket write error
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.submitRequest(Unknown
Source)
at
com.microsoft.jdbc.sqlserver.SQLServerImplDatabaseMetaData.getTypeInfo(Unknown
Source)(Compiled Code)
at
com.microsoft.jdbc.base.BaseDatabaseMetaData.getTypeInfo(Unknown
Source)
at
com.microsoft.jdbcx.base.BaseDatabaseMetaDataWrapper.getTypeInfo(Unknown
Source)
at
com.ibm.db.db.base.DatabaseConnection.getSearchTypeInfo(DatabaseConnection.java(Compiled
Code))
at
com.ibm.db.db.base.DatabaseResultTable.execute(DatabaseResultTable.java)
at
com.ibm.db.db.SelectStatement.createResultTable(SelectStatement.java)
at com.ibm.db.db.SelectStatement.execute(SelectStatement.java)
at com.ibm.db.beans.DBSelect.execute(DBSelect.java)
at
com.brick.util.jde.JdeRptDateGL.setDateFromJdeDb(JdeRptDateGL.java:89)
at com.brick.util.jde.JdeRptDateGL.init(JdeRptDateGL.java:70)
at com.brick.util.jde.JdeRptDateGL.<init>(JdeRptDateGL.java:35)
at reports._ReportsA_jsp_2._jspService(_ReportsA_jsp_2.java:113)
at
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:142)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.jasper.runtime.JspServlet$JspServletWrapper.service(JspServlet.java:323)
at
org.apache.jasper.runtime.JspServlet.serviceJspFile(JspServlet.java:503)
at
org.apache.jasper.runtime.JspServlet.service(JspServlet.java:636)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
com.ibm.servlet.engine.webapp.StrictServletInstance.doService(ServletManager.java:827)
at
com.ibm.servlet.engine.webapp.StrictLifecycleServlet._service(StrictLifecycleServlet.java:167)
at
com.ibm.servlet.engine.webapp.IdleServletState.service(StrictLifecycleServlet.java:297)
at
com.ibm.servlet.engine.webapp.StrictLifecycleServlet.service(StrictLifecycleServlet.java:110)
at
com.ibm.servlet.engine.webapp.ServletInstance.service(ServletManager.java:472)
at
com.ibm.servlet.engine.webapp.ValidServletReferenceState.dispatch(ServletManager.java:1012)
at
com.ibm.servlet.engine.webapp.ServletInstanceReference.dispatch(ServletManager.java:913)
at
com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:721)
at
com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:374)
at
com.ibm.servlet.engine.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:118)
at
com.ibm.servlet.engine.srt.WebAppInvoker.doForward(WebAppInvoker.java:134)
at
com.ibm.servlet.engine.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:239)
at
com.ibm.servlet.engine.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:67)
at
com.ibm.servlet.engine.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:106)
at
com.ibm.servlet.engine.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:154)
at
com.ibm.servlet.engine.oselistener.OSEListenerDispatcher.service(OSEListener.java:315)
at
com.ibm.servlet.engine.http11.HttpConnection.handleRequest(HttpConnection.java:60)
at
com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:323)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:252)
at com.ibm.ws.util.CachedThread.run(ThreadPool.java(Compiled
Code))
Ken Hygh
> I have a couple of servlets and jsp pages that look up info in a
> database using a connection from the WAS controlled connection pool.
> I am using Microsofts JDBC driver for SQL server 2K. If the
> application server "sits" for an extended period of time w/o anyone
> accessing it, when the servlets/jsp's execute they fail. The logs
> shows the following exception being thrown (I will put the full stack
> trace at the end of this post):
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC]Connection reset by peer: socket write error
>
> I only get this error once, if I reload the page, everything works
> fine. I only get it if the servlet or jsp is not accessed for an
> extended period of time.
>
> Application was developed using WSAD 5 targeted at a WAS 4 server.
>
>
> Brian Oster
>
>
>
> Full stack trace:
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> JDBC]Connection reset by peer: socket write error
I've seen this kind of error when there's a firewall between the 2
machines that closes connections after a certain period without activity.
Ken
Brian Oster
and the sql servers. This is completely an intranet application and
all of the development servers are behind any type of firewalls, but I
will check to make sure. I can also test this out by setting up a
couple of servers in the same collision domain just to be 100%
certain. The only problem is that it is a real intermittent problem
so it is very hard to re-create...
Is there any kind of a way to monitor the status or track the status
of connection pools in WAS?
Brian Oster
On Mon, 29 Sep 2003 11:18:51 -0400, Ken Hygh <ken...@nc.rr.com>
wrote:
Sunit Patke
correctly.
Sunit
"Brian Oster" <bgoster@swbell**nosmap**.net> wrote in message
news:lgsgnv0taak67rv92...@4ax.com...
Brian Oster
differently or might be doing to cause this. App server settings,
driver settings, etc. Is there some way to test connections before I
try to use them other than simply executing a statement and catching
the exception?
The driver is from MS and is simply called:
Microsoft SQL Server 2000 Driver for JDBC
Brian Oster
Joris Kuipers
"Brian Oster" <bgoster@swbell**nosmap**.net> wrote in message
> Can you elaborate a little more. Is there something I should be doing
> differently or might be doing to cause this. App server settings,
> driver settings, etc. Is there some way to test connections before I
> try to use them other than simply executing a statement and catching
> the exception?
>
> The driver is from MS and is simply called:
> Microsoft SQL Server 2000 Driver for JDBC
Ken was right, it looks like the socket connection for the pooled
connection has been closed. This doesn't have to caused by be a firewall,
though, it can also be a router or even the database that does this.
Simplest solution would be to lower the idle timeout for your pooled
connections and set the minimum number of connections to 0. This would
ensure that your pooled connections remain valid throughout their
lifetime.
Greetings,
Joris Kuipers
Ray McVay
(including IBM) heve had bugs to work out in that respect. Of course
it could be a "we couldn't care less" on Microsoft's part since they
don't want you using Java against their servers anyway. At any rate, the
drivers can't detect a stale connection until you actually try to use
it, at which time it will create a new one, so you have to catch that
exception and retry the query to recover.
Brian Oster
wrote:
That is actually the work around I put into place. It only happens
after a servlet or jsp has been idle for apx 30 Min. (which is the
default timeout settings for the connection btw - idle & orphan -
correlation??? ) and it only happens on the first request, after which
everything works fine. So basically what I am doing is catching the
exception and re-trying the query a second time (which always works).
I have checked all my code and I can't find any place where I don't
properly release the connection back to the pool when I am done with
it.
I'll try playing around with the settings Joris suggested and see what
happens.
Oh, one other thing. I only seem to be having this problem after I
deploy my app to a WAS4 server, I have not been able to recreate it
during development in WSAD5.
Brian Oster
Joris Kuipers
"Brian Oster" <bgoster@swbell**nosmap**.net> wrote in message
> On Mon, 29 Sep 2003 20:36:41 -0500, Ray McVay <rmcv...@SPAMacm.org>
> wrote:
>
> >It should be throwing a stale connection exception but some JDBC
vendors
> > (including IBM) heve had bugs to work out in that respect. Of course
> >it could be a "we couldn't care less" on Microsoft's part since they
> >don't want you using Java against their servers anyway. At any rate,
the
> >drivers can't detect a stale connection until you actually try to use
> >it, at which time it will create a new one, so you have to catch that
> >exception and retry the query to recover.
> >
>
> That is actually the work around I put into place. It only happens
> after a servlet or jsp has been idle for apx 30 Min. (which is the
> default timeout settings for the connection btw - idle & orphan -
> correlation??? ) and it only happens on the first request, after which
> everything works fine. So basically what I am doing is catching the
> exception and re-trying the query a second time (which always works).
>
> I have checked all my code and I can't find any place where I don't
> properly release the connection back to the pool when I am done with
> it.
First of all, throwing a StaleConnectionException isn't something that a
particular JDBC-vendor should implement in his driver; this is taken care
of by Websphere itself, which has a table that maps particular
DB-vendor-specific error codes to StaleConnectionException. There is no
'standard' exception that a database driver can throw to indicate that the
used connection *might* be stale... So if a particalar failure isn't
reported as a StaleConnectionException, the error reported for this
particular failure simply isn't recognized by WAS as caused by a possible
stale connection. Higher fixpak levels usually have a more complete map
for these sort of DB-specific errors.
Furthermore, your solution isn't really a work around but the preferred
way of handling things. A StaleConnectionException can occur any time, for
example because there was a short network outage, or a DB reboot. It will
trigger WAS to empty its connection pool (if the appropiate fixpaks are in
place, this actually works ;), so a retry will succeed if a new connection
can be made successfully. The StaleConnectionException has nothing to do
with properly releasing connections to the pool, it means that a
connection (that was probably retreived from the pool) has become invalid.
Good luck in solving your problems.
Greetings,
Joris Kuipers
Brian Oster
<jorisk...@xs4all.nl> wrote:
>
>First of all, throwing a StaleConnectionException isn't something that a
>particular JDBC-vendor should implement in his driver; this is taken care
>of by Websphere itself, which has a table that maps particular
>DB-vendor-specific error codes to StaleConnectionException. There is no
>'standard' exception that a database driver can throw to indicate that the
>used connection *might* be stale... So if a particalar failure isn't
>reported as a StaleConnectionException, the error reported for this
>particular failure simply isn't recognized by WAS as caused by a possible
>stale connection. Higher fixpak levels usually have a more complete map
>for these sort of DB-specific errors.
>
>Furthermore, your solution isn't really a work around but the preferred
>way of handling things. A StaleConnectionException can occur any time, for
>example because there was a short network outage, or a DB reboot. It will
>trigger WAS to empty its connection pool (if the appropiate fixpaks are in
>place, this actually works ;), so a retry will succeed if a new connection
>can be made successfully. The StaleConnectionException has nothing to do
>with properly releasing connections to the pool, it means that a
>connection (that was probably retreived from the pool) has become invalid.
>
I'll keep that in mind and always try a query at least twice as a
standard coding technique, it definetly is more robust that way.
BTW, I set the minimum pool size to 0 and it appears to have fixed the
problem. I am going to play around w/ some of the timeout settings
and see if I can find the exact cause of what is going on.
Brian Oster
Ray McVay
> BTW, I set the minimum pool size to 0 and it appears to have fixed the
> problem. I am going to play around w/ some of the timeout settings
> and see if I can find the exact cause of what is going on.
Although that was the way we fixed it on 3.5, I've seen the exception
thrown while working in WSAD5/WTE4 lately. Of course it could be a
different cause, just the same symptom. Also at one point the tools in
WSAD4 would not allow me to set the min pool size to zero.
Ray McVay
> First of all, throwing a StaleConnectionException isn't something that a
> particular JDBC-vendor should implement in his driver; this is taken care
> of by Websphere itself, which has a table that maps particular
> DB-vendor-specific error codes to StaleConnectionException. There is no
> 'standard' exception that a database driver can throw to indicate that the
> used connection *might* be stale... So if a particalar failure isn't
Ahh, ok. The place I battled this was WAS 3.5 and DB2 so it wasn't
really obvious to me where the line was drawn. One way or the other, I
knew which vendor was at fault. 8-)