You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Anthony Presley <an...@zoraptera.com> on 2003/11/25 00:15:12 UTC
DBCP "Statement already closed .... how?"
Hello all ....
I've been using DBCP to develop an application for the last 12 months,
and it's finally been placed into "active" use. Which is great ...
except that it didn't scale very well. When being used by less than 10
or 15 users, it's fine. However, as soon as the number of users
increases (or I simulate by clicking on a bunch of database links at one
time), I start getting a lot of "'Statement' already closed." error's in
my logs.
As far as I can tell, I'm not doing any double closings. Again, this
works fine and dandy when there's only a few users, but once several
people get on it, it starts with these errors, and they're rather
annoying. Most intruiging is that if you wait a few seconds, then try
again, requests to the database work fine.
I've thrown in a LOT of log() statements in my code to ensure that the
database connections are, in fact, being closed and returned to the pool
-- and they are. From what I can tell, they're being closed somewhere
in the middle of the processing of them.
My Tomcat config looks like (and I'm using DBCP 1.1 and nightly (have
tried both) and Pool 1.1) :
<ResourceParams name="jdbc/ifx" scope="Shareable">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>15</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>3</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>SELECT user.id FROM user WHERE id =
1</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>300</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.informix.jdbc.IfxDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:informix-sqli://ids:1314/development:INFORMIXSERVER=london_net</value>
</parameter>
</ResourceParams>
A chunk of the log is:
2003-11-24 17:04:29 InternalClients: Closing connections
2003-11-24 17:04:29 InternalClients: ERROR: Servlet InternalClients, SQL
Error lookup up General Information: 'Statement' already closed.
RootCause: null: /Error
2003-11-24 17:04:29 InternalClients: ERROR: Servlet InternalClients,
General Error viewing internal client information: null RootCause: null:
/Error
2003-11-24 17:04:29 InternalClients: Closing connections
2003-11-24 17:04:29 InternalClients: Closed
2003-11-24 17:04:29 StandardWrapperValve[InternalClients]:
Servlet.service() for servlet InternalClients threw exception
java.lang.IllegalStateException
at
org.apache.coyote.tomcat4.CoyoteResponseFacade.sendRedirect(CoyoteResponseFacade.java:340)
at azure.servlets.Azure.err(Azure.java:76)
at
azure.servlets.InternalClients.processRequest(InternalClients.java:199)
at azure.servlets.InternalClients.doGet(InternalClients.java:34)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
Again, the link that generated the error .... clicking on it right after
it logged these error's, it works fine. Which obviously (to me?) leads
me right to ... a problem with the DBCP, or the DB, or my code. These
are not simple problems to troubleshoot, it appears.
I can post the code the generates this .... if that would help. At this
point in the game, I'm pretty much at my wits ends.
Any help is much appreciated ....
--Anthony
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by "Craig R. McClanahan" <cr...@apache.org>.
Quoting Anthony Presley <an...@zoraptera.com>:
> Thank you Dirk ..... where do you live? I owe you a Pizza.
>
> This did, in fact, solve almost all of my problems. Subtle [to me].
>
> However, I have a remaining issue, and I've narrowed it down to the
> following lines of code. I'll post in the tomcat user group, but
> thought someone here might have a pearl of wisdom.
>
> Nine times out of ten, multiple requests work perfectly. However, in
> the one time out of ten that it doesn't, my code is throwing an
> Exception with this:
>
> try {
> if (rd != null)
> rd.forward(req, res);
> else {
> log("RD is NULL, MAJOR ERROR");
> }
> } catch (ServletException e) {
> log("5ERROR ServletException: " + e.getMessage());
> e.printStackTrace();
> } catch (java.io.IOException e) {
> log("5ERROR java.io.IOException: " + e.getMessage());
> e.printStackTrace();
> } catch (Exception e) {
> log("5ERROR Exception: " + e.getMessage());
> e.printStackTrace();
> }
>
> Doing a reload on it, it works fine. IE, what is being sent (the URL)
> does start with a "/", and rd is NOT null (neither are req or res).
>
> The Error is:
> 5ERROR Exception: Cannot forward after response has been commit
>
It means you tried to call RequestDispatcher.forward() after the current
response has been committed :-).
To understand what's really happening, you need to be a little bit familiar with
how servlets work. When you start writing to a response page, the output you
write is buffered, up to the point where the buffer size has been exceeded.
Prior to that point, you can call forward() -- which, among other things,
erases any buffered output so that the new resource or page can write the entre
content of the response.
However, if you've already written more characters than the size of the output
buffer, the HTTP headers (and the first part of the response) are actually sent
to the client. After that point, the response is considered to be "committed"
and you can no longer do a RequestDispatcher.forward() call.
So, how big is the page buffer? Ah, there's the rub ... it's not specified, so
it varies by container. However, you can control it by calling
response.setBufferSize() if you are writing directly from a servlet, or using
the "buffer" attribute on the <%@ page %> directive if you're using JSP. You
need to ensure that you haven't already written more characters than the buffer
size before you execute the forward() call.
Note that this kind of problem would never occur if you were using an MVC-based
framework like Struts <http://jakarta.apache.org/struts>, because the business
logic that processed the incoming form submit would never output anything to
the response -- it would merely perform the required processing, then forward()
to a servlet or JSP page to create the actual output.
Craig McClanahan
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by Anthony Presley <an...@zoraptera.com>.
Thank you Dirk ..... where do you live? I owe you a Pizza.
This did, in fact, solve almost all of my problems. Subtle [to me].
However, I have a remaining issue, and I've narrowed it down to the
following lines of code. I'll post in the tomcat user group, but
thought someone here might have a pearl of wisdom.
Nine times out of ten, multiple requests work perfectly. However, in
the one time out of ten that it doesn't, my code is throwing an
Exception with this:
try {
if (rd != null)
rd.forward(req, res);
else {
log("RD is NULL, MAJOR ERROR");
}
} catch (ServletException e) {
log("5ERROR ServletException: " + e.getMessage());
e.printStackTrace();
} catch (java.io.IOException e) {
log("5ERROR java.io.IOException: " + e.getMessage());
e.printStackTrace();
} catch (Exception e) {
log("5ERROR Exception: " + e.getMessage());
e.printStackTrace();
}
Doing a reload on it, it works fine. IE, what is being sent (the URL)
does start with a "/", and rd is NOT null (neither are req or res).
The Error is:
5ERROR Exception: Cannot forward after response has been commit
Googling is again, less than helpful (although it appeared that in
Version 3.3 this error was common, I'm now on 4.1).
On Tue, 2003-11-25 at 15:15, Dirk Verbeeck wrote:
> The problem with the example code you posted is then "conn" variable.
> It should be declared inside the method (local variable) and not as a member
> variable.
>
> Your example works if only one request is processed.
> A second request overwrites the connection of the first one.
> So the first connection is lost and at the end, both requests will try to
> close the same connection two times triggering the exception you see.
>
> -- Dirk
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by Dirk Verbeeck <di...@pandora.be>.
The problem with the example code you posted is then "conn" variable.
It should be declared inside the method (local variable) and not as a member
variable.
Your example works if only one request is processed.
A second request overwrites the connection of the first one.
So the first connection is lost and at the end, both requests will try to
close the same connection two times triggering the exception you see.
-- Dirk
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by John Zoetebier <jo...@transparent.co.nz>.
It trick is to isolate the problem:
There are 3 options available.
1) Jakarta DBCP 1.1
2) Database server
3) Problem in code
I did a test with DBCP 1.1 and McKoi.
This test can be done outside Tomcat, so it is easy to isolate the root
cause.
I fired off rapidly a number of connections to the server.
To my big surprise the process is hanging when the number of connections
exceeds 8.
I will look into this further tomorrow and repeat the same test without
DBCP.
That way I can easily check if the problem is in DBCP or the database
server.
My bet is that there is a problem in DBCP.
Let you know how it went.
--
John Zoetebier
Web site: http://www.transparent.co.nz
On Mon, 24 Nov 2003 22:55:42 -0600, Anthony Presley
<an...@zoraptera.com> wrote:
> On Tue, 2003-11-25 at 08:15, John Zoetebier wrote:
>> Could this be the problem ?
>> > <parameter>
>> > <name>maxActive</name>
>> > <value>15</value>
>> > </parameter>
>>
>> This tells JDBC that the maximum number of connection to be borrowed at
>> any point in time is 15
>> Increase it to 1000 and test again.
>
> I moved it to 1000, and maxIdle to 100, and get the same errors.
>
> --Anthony
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by Anthony Presley <an...@zoraptera.com>.
On Tue, 2003-11-25 at 08:15, John Zoetebier wrote:
> Could this be the problem ?
> > <parameter>
> > <name>maxActive</name>
> > <value>15</value>
> > </parameter>
>
> This tells JDBC that the maximum number of connection to be borrowed at
> any point in time is 15
> Increase it to 1000 and test again.
I moved it to 1000, and maxIdle to 100, and get the same errors.
--Anthony
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by John Zoetebier <jo...@transparent.co.nz>.
Could this be the problem ?
> <parameter>
> <name>maxActive</name>
> <value>15</value>
> </parameter>
This tells JDBC that the maximum number of connection to be borrowed at
any point in time is 15
Increase it to 1000 and test again.
--
John Zoetebier
Web site: http://www.transparent.co.nz
On Mon, 24 Nov 2003 17:15:12 -0600, Anthony Presley
<an...@zoraptera.com> wrote:
> Hello all ....
>
> I've been using DBCP to develop an application for the last 12 months,
> and it's finally been placed into "active" use. Which is great ...
> except that it didn't scale very well. When being used by less than 10
> or 15 users, it's fine. However, as soon as the number of users
> increases (or I simulate by clicking on a bunch of database links at one
> time), I start getting a lot of "'Statement' already closed." error's in
> my logs.
>
> As far as I can tell, I'm not doing any double closings. Again, this
> works fine and dandy when there's only a few users, but once several
> people get on it, it starts with these errors, and they're rather
> annoying. Most intruiging is that if you wait a few seconds, then try
> again, requests to the database work fine.
>
> I've thrown in a LOT of log() statements in my code to ensure that the
> database connections are, in fact, being closed and returned to the pool
> -- and they are. From what I can tell, they're being closed somewhere
> in the middle of the processing of them.
>
> My Tomcat config looks like (and I'm using DBCP 1.1 and nightly (have
> tried both) and Pool 1.1) :
>
> <ResourceParams name="jdbc/ifx" scope="Shareable">
> <parameter>
> <name>factory</name>
>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
>
> <parameter>
> <name>maxActive</name>
> <value>15</value>
> </parameter>
>
> <parameter>
> <name>maxIdle</name>
> <value>3</value>
> </parameter>
>
> <parameter>
> <name>validationQuery</name>
> <value>SELECT user.id FROM user WHERE id =
> 1</value>
> </parameter>
>
> <parameter>
> <name>removeAbandonedTimeout</name>
> <value>300</value>
> </parameter>
>
> <parameter>
> <name>removeAbandoned</name>
> <value>true</value>
> </parameter>
>
> <parameter>
> <name>maxWait</name>
> <value>10000</value>
> </parameter>
>
> <parameter>
> <name>driverClassName</name>
> <value>com.informix.jdbc.IfxDriver</value>
> </parameter>
>
> <parameter>
> <name>url</name>
> <value>jdbc:informix-sqli://ids:1314/development:INFORMIXSERVER=london_net</value>
> </parameter>
> </ResourceParams>
>
> A chunk of the log is:
>
> 2003-11-24 17:04:29 InternalClients: Closing connections
> 2003-11-24 17:04:29 InternalClients: ERROR: Servlet InternalClients, SQL
> Error lookup up General Information: 'Statement' already closed.
> RootCause: null: /Error
> 2003-11-24 17:04:29 InternalClients: ERROR: Servlet InternalClients,
> General Error viewing internal client information: null RootCause: null:
> /Error
> 2003-11-24 17:04:29 InternalClients: Closing connections
> 2003-11-24 17:04:29 InternalClients: Closed
> 2003-11-24 17:04:29 StandardWrapperValve[InternalClients]:
> Servlet.service() for servlet InternalClients threw exception
> java.lang.IllegalStateException
> at
> org.apache.coyote.tomcat4.CoyoteResponseFacade.sendRedirect(CoyoteResponseFacade.java:340)
> at azure.servlets.Azure.err(Azure.java:76)
> at
> azure.servlets.InternalClients.processRequest(InternalClients.java:199)
> at azure.servlets.InternalClients.doGet(InternalClients.java:34)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
>
> Again, the link that generated the error .... clicking on it right after
> it logged these error's, it works fine. Which obviously (to me?) leads
> me right to ... a problem with the DBCP, or the DB, or my code. These
> are not simple problems to troubleshoot, it appears.
>
> I can post the code the generates this .... if that would help. At this
> point in the game, I'm pretty much at my wits ends.
>
> Any help is much appreciated ....
>
> --Anthony
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by Anthony Presley <an...@zoraptera.com>.
Ok ... so I took Ben's advice and decided to (at least for testing) yank
a bunch of code, and see about a slightly more modular approach. Also,
thanks to Antony, I am forwarding AFTER the database close. Here's what
I've found ....
Closing's were extracted into a method, and the while (conn == null) was
pulled out.
I also pulled everything but the following code [below]. In it, I have
yanked the DBCP code and am establishing a single connection in the
init(), which stays open until the destroy() method is called. Not a
great solution, but wanted to see if it was DBCP related.
As John pointed out, it is DBCP related (possibly) ... however, I can
generate the same error's (albeit, fewer of them) when using straight
JDBC Connections. For instance:
Scenario 1 (Using DBCP):
- Call the servlet (1 time)
- Redirected to JSP file(s)
- Display of information correctly [GOOD]
- Call the servlet (5 times)
- Redirected to JSP file(s)
- Display of information correctly 1 of the 5 times [BAD]
Scenario 2 (NOT Using DBCP):
- Call the servlet (1 time)
- Redirected to JSP file(s)
- Display of information correctly [GOOD]
- Call the servlet (5 times)
- Redirected to JSP file(s)
- Display of information correctly 3 of the 5 times [BAD,BETTER]
Scenario 3 (NOT Using DBCP, Simplified JSP file):
- Call the servlet (1 time)
- Redirected to JSP file(s)
- Display of information correctly [GOOD]
- Call the servlet (5 times)
- Redirected to JSP file(s)
- Display of information correctly 4 of the 5 times [BAD,BETTER]
I'm not using a JK connector, or Apache in my testing, just plain Tomcat
on port 8080.
However, what I'm beginning to wonder and note is:
a. Using DBCP IS causing some of the problem.
b. By using more simple JSP files, I can decrease the chance of
errors, but 4/5 is still not good. Is there a limit to the number of
active sessions, or amount of data I can put in the session, or
processing time (less than 1 or 2 seconds) that I'm missing? A workload
of 5 servlets && 5 JSP's (each calling about 3 other JSP's, except in
the simplified test) doesn't seem like a lot.
My modified code is below. Please NOTE ... I can tell by the logs that
it gets done with all JDBC calls, and does the forward, whereby it's
occasionally (but not consistently) dieing in the JSP files. In the
case of the simplified test, the JSP file pulls three session variables
and displays a true / false on the screen. I've checked with a grep
tool, and I'm not closing or accessing the connection in any way in the
JSPs.
This is frustrating ....... Thanks for everyone's help.
protected void processRequest(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, java.io.IOException {
set(request, response);
setServlet("InternalClients");
// Create / validate the current session
session = request.getSession(true);
username = request.getRemoteUser();
try {
if (conn == null || conn.isClosed()) {
// Try again to fetch a new connection?
log("Connection was closed");
init();
}
// Did not make a valid connection for some reason, take to error
screen
if (conn == null) {
setError(DBNULL_ERR);
err();
doDBClose();
return;
} else {
// Should be a valid user
if (username == null) {
setError(NOUSER_ERR);
err();
doDBClose();
return;
}
}
log("Sure of connection ... should NEVER be closed.");
} catch (Exception e) {
setError(CONTEXT_ERR);
err();
doDBClose();
return;
}
// Valid user, valid roles
// Process their data
String action = (String) request.getParameter("action");
String type = (String) request.getParameter("type");
if (action == null) {
// No action to perform, error
setError(ACTION_ERR);
err();
doDBClose();
} else if (action.equalsIgnoreCase("search")) {
// LOOKING for a client or lead
// Search based on lname, fname AND/OR unumber
String lname = request.getParameter("lname");
String fname = request.getParameter("fname");
String unum = request.getParameter("clientnr");
try {
DoSearch(request);
} catch (Exception e) {
setError("Error searching clients for: " + username);
setExtraErr(e.getMessage());
err();
} finally {
doDBClose();
}
} else if (action.equalsIgnoreCase("view")) {
try {
Client c = new Client(conn, true);
String cname = request.getParameter("clientnr");
log("Looking up username: " + username);
if (!c.queryExactUserName(cname)) {
// Should be an error, but instead, set as a message...
req.setAttribute("msg", "Cannot find client with client number
" + username + ", please try again.");
forward("/jsp/clients/internal/index.jsp");
} else {
String phase = "general";
String which = "0";
LookupGeneral(c, which, request);
// Forward along with client (c) and the information in the
Lookup...
request.setAttribute("client", c);
request.setAttribute("phase", phase);
request.setAttribute("action", action);
request.setAttribute("which", which);
request.setAttribute("inventions", c.getInventions());
}
} catch (SQLException e) {
setError("SQLError viewing internal client information");
setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
while ((e = e.getNextException()) != null) {
log("Next exception: " + e.getMessage());
log("Next exception major issue: " + e.getErrorCode());
log("Next exception major issue: " + e.getCause());
}
err();
} catch (Exception e) {
setError("General Error viewing internal client information");
setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
err();
} finally {
doDBClose();
}
forward("/jsp/clients/internal/test.jsp");
}
}
On Tue, 2003-11-25 at 02:24, Ben Walding wrote:
> I suspect a lot of your problems are due to code duplication. How so?
>
> You have the same boilerplate code for closing the connection in several
> places - extract it into a method.
>
> Re: this code.
>
> Context ctx = new InitialContext();
> if (ctx == null) {
> setError(CONTEXT_ERR);
> err();
> return;
> }
>
> I haven't closely read the JLS, but I doubt this could even happen. The constructor either succeeds or throws an exception.
>
>
> while (conn == null || conn.isClosed()) {
> conn = ds.getConnection();
> }
>
> This will thrash your machine while it tries to acquire a connection if the DB is down, at least put a wait state in.
>
>
>
> session = request.getSession(true);
> if (session == null) {
> setErr(SESSION_ERR);
> err();
> return;
> } else {
> username = request.getRemoteUser();
> }
>
> According to the spec, request.getSession() / request.getSession(true) will create a session if none exists. Hence my reading suggests that session will never be null for your if test.
>
>
>
>
> Of course this doesn't really answer your question at hand - why are my connections falling apart. I suspect that if you cleaned things up and made it more modular, you would start to see where your connections would be getting used. I suspect that you are probably throwing away a connection in some other code - eg Client, but not realising this inside your servlet. This will typically be caused by unclear responsibilities of components. eg. Why does Client need a connection? would they be better off with a datasource that they can acquire a connection from and destroy themselves?
>
> Since there is virtually no JDBC code in this example I assume that most of it occurs in LookupGeneral and DoLookup etc, the code for those is probably of more interest.
>
> IllegalStateExceptions typically occur if you acquire the PrintWriter
> and then try to do a sendRedirect or something along those lines,
> because your doGet method is one big if-then-else nest, it is really
> hard to work out where this might be occurring. Better to split out the
> actions out into methods where you can properly see if they doing bad
> things one at a time.
>
>
> Cheers,
>
> Ben
>
>
> Anthony Presley wrote:
>
> >On Mon, 2003-11-24 at 18:18, Vic Cekvenich wrote:
> >
> >
> >>Consider testing the app before deploying, such as OpenSTA.
> >>
> >>
> >
> >Agreed. It was tested "relatively" extensively for about six months
> >amongst the various offices, with no problems. Problems occur only when
> >everyone is on it.
> >
> >
> >
> >>What DAO layer are you using?
> >>(ibatis, hibrenate, ?)
> >>
> >>
> >
> >Not using one, using JDBC directly through the DBCP commons. Using one
> >would require retooling the 100K+ lines of code.
> >
> >Following is some example code, again, it works fine with one request to
> >the servlet (say, I click and load one client). But, if I click on more
> >than one client (say, six one after another), I start getting Error
> >500's, and the logs read "Connection closed" or "Statement already
> >closed". This appears to happen (from what I can tell) most often in
> >the JSP. My theory, currently, is:
> >
> > Servlet is called / created
> > JDBC calls made / JSP called (via forward)
> > Connection is closed
> > Return from forward to JSP
> > Finally{} from try block is called, connection is not null, but not
> >open, error is thrown.
> >
> >Of course, unless the app is grabbing the SAME connection, I don't see
> >why this would be happening (no JDBC calls occur in the forwards).
> >
> >On the other hand, closing the connection prior to the forward() doesn't
> >give the above errors, but does give the error:
> >
> >java.lang.IllegalStateException
> > at
> >org.apache.coyote.tomcat4.CoyoteResponseFacade.sendRedirect(CoyoteResponseFacade.java:340)
> >
> >What does that mean <Google's not helping too much>?
> >
> >Some example code:
> >
> > protected void processRequest(HttpServletRequest request,
> >HttpServletResponse response)
> > throws ServletException, java.io.IOException {
> > // Create / validate the current session
> > session = request.getSession(true);
> > if (session == null) {
> > setErr(SESSION_ERR);
> > err();
> > return;
> > } else {
> > username = request.getRemoteUser();
> > }
> >
> > try {
> > javax.servlet.ServletContext context = getServletContext();
> > String dbInit = context.getInitParameter("dbInit");
> > if (dbInit == null) {
> > setError(DBINIT_ERR);
> > err();
> > return;
> > }
> >
> > Context ctx = new InitialContext();
> > if (ctx == null) {
> > setError(CONTEXT_ERR);
> > err();
> > return;
> > }
> >
> > DataSource ds = (DataSource) ctx.lookup(dbInit);
> > if (ds != null) {
> > conn = ds.getConnection();
> > } else {
> > setError(CONTEXT_ERR);
> > err();
> > return;
> > }
> >
> > if (conn == null || conn.isClosed()) {
> > // Try again to fetch a new connection?
> > log("Connection was closed");
> > while (conn == null || conn.isClosed()) {
> > conn = ds.getConnection();
> > }
> > log("Reopened the connection");
> > }
> >
> > // Did not make a valid connection for some reason, take to error
> >screen
> > if (conn == null) {
> > setError(DBNULL_ERR);
> > err();
> > return;
> > } else {
> > // Should be a valid user
> > if (username == null) {
> > setError(NOUSER_ERR);
> > err();
> > try {
> > if (conn != null) {
> > conn.close();
> > conn = null;
> > }
> > } catch (SQLException e) { ; }
> > return;
> > }
> > }
> >
> > log("Sure of connection ... should NEVER be closed.");
> > } catch (Exception e) {
> > setError(CONTEXT_ERR);
> > err();
> > try {
> > if (conn != null) {
> > conn.close();
> > conn = null;
> > }
> > } catch (SQLException ex) { ; }
> > return;
> > }
> >
> > // Valid user, valid roles
> > // Process their data
> > String action = (String) request.getParameter("action");
> > String type = (String) request.getParameter("type");
> > if (action == null) {
> > // No action to perform, error
> > setError(ACTION_ERR);
> > err();
> > try {
> > if (conn != null) {
> > conn.close();
> > conn = null;
> > }
> > } catch (SQLException e) { ; }
> > } else if (action.equalsIgnoreCase("search")) {
> > // LOOKING for a client or lead
> > // Search based on lname, fname AND/OR unumber
> >
> > String lname = request.getParameter("lname");
> > String fname = request.getParameter("fname");
> > String unum = request.getParameter("clientnr");
> > try {
> > DoSearch(request);
> > } catch (Exception e) {
> > setError("Error searching clients for: " + username);
> > setExtraErr(e.getMessage());
> > err();
> > } finally {
> > try {
> > log("Closing");
> > if (conn != null) {
> > conn.close();
> > conn = null;
> > }
> > log("Closed");
> > } catch (SQLException e) { ; }
> > }
> > } else if (action.equalsIgnoreCase("view")) {
> > try {
> > Client c = new Client(conn, true);
> >
> > String cname = request.getParameter("clientnr");
> > log("Looking up username: " + username);
> >
> > if (!c.queryExactUserName(cname)) {
> > // Should be an error, but instead, set as a message...
> > req.setAttribute("msg", "Cannot find client with client number
> >" + username + ", please try again.");
> > forward("/jsp/clients/internal/index.jsp");
> > } else {
> > String phase = "general";
> > String which = "0";
> > LookupGeneral(c, which, request);
> >
> > // Forward along with client (c) and the information in the
> >Lookup...
> > request.setAttribute("client", c);
> > request.setAttribute("phase", phase);
> > request.setAttribute("action", action);
> > request.setAttribute("which", which);
> >
> > forward("/jsp/clients/internal/index.jsp");
> > }
> > } catch (SQLException e) {
> > setError("SQLError viewing internal client information");
> > setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
> > while ((e = e.getNextException()) != null) {
> > log("Next exception: " + e.getMessage());
> > log("Next exception major issue: " + e.getErrorCode());
> > log("Next exception major issue: " + e.getCause());
> > }
> > err();
> > } catch (Exception e) {
> > setError("General Error viewing internal client information");
> > setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
> > err();
> > } finally {
> > try {
> > log("Closing connections");
> > if (conn != null) {
> > conn.close();
> > conn = null;
> > }
> > log("Closed");
> > } catch (SQLException e) { ; }
> > }
> > }
> > }
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> >For additional commands, e-mail: commons-user-help@jakarta.apache.org
> >
> >
> >
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by Ben Walding <be...@walding.com>.
I suspect a lot of your problems are due to code duplication. How so?
You have the same boilerplate code for closing the connection in several
places - extract it into a method.
Re: this code.
Context ctx = new InitialContext();
if (ctx == null) {
setError(CONTEXT_ERR);
err();
return;
}
I haven't closely read the JLS, but I doubt this could even happen. The constructor either succeeds or throws an exception.
while (conn == null || conn.isClosed()) {
conn = ds.getConnection();
}
This will thrash your machine while it tries to acquire a connection if the DB is down, at least put a wait state in.
session = request.getSession(true);
if (session == null) {
setErr(SESSION_ERR);
err();
return;
} else {
username = request.getRemoteUser();
}
According to the spec, request.getSession() / request.getSession(true) will create a session if none exists. Hence my reading suggests that session will never be null for your if test.
Of course this doesn't really answer your question at hand - why are my connections falling apart. I suspect that if you cleaned things up and made it more modular, you would start to see where your connections would be getting used. I suspect that you are probably throwing away a connection in some other code - eg Client, but not realising this inside your servlet. This will typically be caused by unclear responsibilities of components. eg. Why does Client need a connection? would they be better off with a datasource that they can acquire a connection from and destroy themselves?
Since there is virtually no JDBC code in this example I assume that most of it occurs in LookupGeneral and DoLookup etc, the code for those is probably of more interest.
IllegalStateExceptions typically occur if you acquire the PrintWriter
and then try to do a sendRedirect or something along those lines,
because your doGet method is one big if-then-else nest, it is really
hard to work out where this might be occurring. Better to split out the
actions out into methods where you can properly see if they doing bad
things one at a time.
Cheers,
Ben
Anthony Presley wrote:
>On Mon, 2003-11-24 at 18:18, Vic Cekvenich wrote:
>
>
>>Consider testing the app before deploying, such as OpenSTA.
>>
>>
>
>Agreed. It was tested "relatively" extensively for about six months
>amongst the various offices, with no problems. Problems occur only when
>everyone is on it.
>
>
>
>>What DAO layer are you using?
>>(ibatis, hibrenate, ?)
>>
>>
>
>Not using one, using JDBC directly through the DBCP commons. Using one
>would require retooling the 100K+ lines of code.
>
>Following is some example code, again, it works fine with one request to
>the servlet (say, I click and load one client). But, if I click on more
>than one client (say, six one after another), I start getting Error
>500's, and the logs read "Connection closed" or "Statement already
>closed". This appears to happen (from what I can tell) most often in
>the JSP. My theory, currently, is:
>
> Servlet is called / created
> JDBC calls made / JSP called (via forward)
> Connection is closed
> Return from forward to JSP
> Finally{} from try block is called, connection is not null, but not
>open, error is thrown.
>
>Of course, unless the app is grabbing the SAME connection, I don't see
>why this would be happening (no JDBC calls occur in the forwards).
>
>On the other hand, closing the connection prior to the forward() doesn't
>give the above errors, but does give the error:
>
>java.lang.IllegalStateException
> at
>org.apache.coyote.tomcat4.CoyoteResponseFacade.sendRedirect(CoyoteResponseFacade.java:340)
>
>What does that mean <Google's not helping too much>?
>
>Some example code:
>
> protected void processRequest(HttpServletRequest request,
>HttpServletResponse response)
> throws ServletException, java.io.IOException {
> // Create / validate the current session
> session = request.getSession(true);
> if (session == null) {
> setErr(SESSION_ERR);
> err();
> return;
> } else {
> username = request.getRemoteUser();
> }
>
> try {
> javax.servlet.ServletContext context = getServletContext();
> String dbInit = context.getInitParameter("dbInit");
> if (dbInit == null) {
> setError(DBINIT_ERR);
> err();
> return;
> }
>
> Context ctx = new InitialContext();
> if (ctx == null) {
> setError(CONTEXT_ERR);
> err();
> return;
> }
>
> DataSource ds = (DataSource) ctx.lookup(dbInit);
> if (ds != null) {
> conn = ds.getConnection();
> } else {
> setError(CONTEXT_ERR);
> err();
> return;
> }
>
> if (conn == null || conn.isClosed()) {
> // Try again to fetch a new connection?
> log("Connection was closed");
> while (conn == null || conn.isClosed()) {
> conn = ds.getConnection();
> }
> log("Reopened the connection");
> }
>
> // Did not make a valid connection for some reason, take to error
>screen
> if (conn == null) {
> setError(DBNULL_ERR);
> err();
> return;
> } else {
> // Should be a valid user
> if (username == null) {
> setError(NOUSER_ERR);
> err();
> try {
> if (conn != null) {
> conn.close();
> conn = null;
> }
> } catch (SQLException e) { ; }
> return;
> }
> }
>
> log("Sure of connection ... should NEVER be closed.");
> } catch (Exception e) {
> setError(CONTEXT_ERR);
> err();
> try {
> if (conn != null) {
> conn.close();
> conn = null;
> }
> } catch (SQLException ex) { ; }
> return;
> }
>
> // Valid user, valid roles
> // Process their data
> String action = (String) request.getParameter("action");
> String type = (String) request.getParameter("type");
> if (action == null) {
> // No action to perform, error
> setError(ACTION_ERR);
> err();
> try {
> if (conn != null) {
> conn.close();
> conn = null;
> }
> } catch (SQLException e) { ; }
> } else if (action.equalsIgnoreCase("search")) {
> // LOOKING for a client or lead
> // Search based on lname, fname AND/OR unumber
>
> String lname = request.getParameter("lname");
> String fname = request.getParameter("fname");
> String unum = request.getParameter("clientnr");
> try {
> DoSearch(request);
> } catch (Exception e) {
> setError("Error searching clients for: " + username);
> setExtraErr(e.getMessage());
> err();
> } finally {
> try {
> log("Closing");
> if (conn != null) {
> conn.close();
> conn = null;
> }
> log("Closed");
> } catch (SQLException e) { ; }
> }
> } else if (action.equalsIgnoreCase("view")) {
> try {
> Client c = new Client(conn, true);
>
> String cname = request.getParameter("clientnr");
> log("Looking up username: " + username);
>
> if (!c.queryExactUserName(cname)) {
> // Should be an error, but instead, set as a message...
> req.setAttribute("msg", "Cannot find client with client number
>" + username + ", please try again.");
> forward("/jsp/clients/internal/index.jsp");
> } else {
> String phase = "general";
> String which = "0";
> LookupGeneral(c, which, request);
>
> // Forward along with client (c) and the information in the
>Lookup...
> request.setAttribute("client", c);
> request.setAttribute("phase", phase);
> request.setAttribute("action", action);
> request.setAttribute("which", which);
>
> forward("/jsp/clients/internal/index.jsp");
> }
> } catch (SQLException e) {
> setError("SQLError viewing internal client information");
> setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
> while ((e = e.getNextException()) != null) {
> log("Next exception: " + e.getMessage());
> log("Next exception major issue: " + e.getErrorCode());
> log("Next exception major issue: " + e.getCause());
> }
> err();
> } catch (Exception e) {
> setError("General Error viewing internal client information");
> setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
> err();
> } finally {
> try {
> log("Closing connections");
> if (conn != null) {
> conn.close();
> conn = null;
> }
> log("Closed");
> } catch (SQLException e) { ; }
> }
> }
> }
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by John Zoetebier <jo...@transparent.co.nz>.
There is a method you can check if your code leaves connections open.
From web site
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html#Common%20Problems
==>
There is a solution to this problem. The Jakarta-Commons DBCP can be
configured to track and recover these abandoned dB connections. Not only
can it recover them, but also generate a stack trace for the code which
opened these resources and never closed them.
To configure a DBCP DataSource so that abandoned dB connections are
removed and recycled add the following paramater to the ResourceParams
configuration for your DBCP DataSource Resource:
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
When available db connections run low DBCP will recover and recyle any
abandoned dB connections it finds. The default is false.
Use the removeAbandonedTimeout parameter to set the number of seconds a dB
connection has been idle before it is considered abandoned.
<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>
The default timeout for removing abandoned connections is 300 seconds.
The logAbandoned parameter can be set to true if you want DBCP to log a
stack trace of the code which abandoned the dB connection resources.
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
The default is false.
==>
--
John Zoetebier
Web site: http://www.transparent.co.nz
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by Antony Paul <an...@hotmail.com>.
Check this link
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-how
to.html#Common%20Problems
forward the request only after the connection is closed. ie after finally
block.
Antony Paul.
----- Original Message -----
From: "Anthony Presley" <an...@zoraptera.com>
To: "Jakarta Commons Users List" <co...@jakarta.apache.org>
Sent: Tuesday, November 25, 2003 11:31 AM
Subject: Re: DBCP "Statement already closed .... how?"
> On Mon, 2003-11-24 at 18:18, Vic Cekvenich wrote:
> > Consider testing the app before deploying, such as OpenSTA.
>
> Agreed. It was tested "relatively" extensively for about six months
> amongst the various offices, with no problems. Problems occur only when
> everyone is on it.
>
> > What DAO layer are you using?
> > (ibatis, hibrenate, ?)
>
> Not using one, using JDBC directly through the DBCP commons. Using one
> would require retooling the 100K+ lines of code.
>
> Following is some example code, again, it works fine with one request to
> the servlet (say, I click and load one client). But, if I click on more
> than one client (say, six one after another), I start getting Error
> 500's, and the logs read "Connection closed" or "Statement already
> closed". This appears to happen (from what I can tell) most often in
> the JSP. My theory, currently, is:
>
> Servlet is called / created
> JDBC calls made / JSP called (via forward)
> Connection is closed
> Return from forward to JSP
> Finally{} from try block is called, connection is not null, but not
> open, error is thrown.
>
> Of course, unless the app is grabbing the SAME connection, I don't see
> why this would be happening (no JDBC calls occur in the forwards).
>
> On the other hand, closing the connection prior to the forward() doesn't
> give the above errors, but does give the error:
>
> java.lang.IllegalStateException
> at
>
org.apache.coyote.tomcat4.CoyoteResponseFacade.sendRedirect(CoyoteResponseFa
cade.java:340)
>
> What does that mean <Google's not helping too much>?
>
> Some example code:
>
> protected void processRequest(HttpServletRequest request,
> HttpServletResponse response)
> throws ServletException, java.io.IOException {
> // Create / validate the current session
> session = request.getSession(true);
> if (session == null) {
> setErr(SESSION_ERR);
> err();
> return;
> } else {
> username = request.getRemoteUser();
> }
>
> try {
> javax.servlet.ServletContext context = getServletContext();
> String dbInit = context.getInitParameter("dbInit");
> if (dbInit == null) {
> setError(DBINIT_ERR);
> err();
> return;
> }
>
> Context ctx = new InitialContext();
> if (ctx == null) {
> setError(CONTEXT_ERR);
> err();
> return;
> }
>
> DataSource ds = (DataSource) ctx.lookup(dbInit);
> if (ds != null) {
> conn = ds.getConnection();
> } else {
> setError(CONTEXT_ERR);
> err();
> return;
> }
>
> if (conn == null || conn.isClosed()) {
> // Try again to fetch a new connection?
> log("Connection was closed");
> while (conn == null || conn.isClosed()) {
> conn = ds.getConnection();
> }
> log("Reopened the connection");
> }
>
> // Did not make a valid connection for some reason, take to error
> screen
> if (conn == null) {
> setError(DBNULL_ERR);
> err();
> return;
> } else {
> // Should be a valid user
> if (username == null) {
> setError(NOUSER_ERR);
> err();
> try {
> if (conn != null) {
> conn.close();
> conn = null;
> }
> } catch (SQLException e) { ; }
> return;
> }
> }
>
> log("Sure of connection ... should NEVER be closed.");
> } catch (Exception e) {
> setError(CONTEXT_ERR);
> err();
> try {
> if (conn != null) {
> conn.close();
> conn = null;
> }
> } catch (SQLException ex) { ; }
> return;
> }
>
> // Valid user, valid roles
> // Process their data
> String action = (String) request.getParameter("action");
> String type = (String) request.getParameter("type");
> if (action == null) {
> // No action to perform, error
> setError(ACTION_ERR);
> err();
> try {
> if (conn != null) {
> conn.close();
> conn = null;
> }
> } catch (SQLException e) { ; }
> } else if (action.equalsIgnoreCase("search")) {
> // LOOKING for a client or lead
> // Search based on lname, fname AND/OR unumber
>
> String lname = request.getParameter("lname");
> String fname = request.getParameter("fname");
> String unum = request.getParameter("clientnr");
> try {
> DoSearch(request);
> } catch (Exception e) {
> setError("Error searching clients for: " + username);
> setExtraErr(e.getMessage());
> err();
> } finally {
> try {
> log("Closing");
> if (conn != null) {
> conn.close();
> conn = null;
> }
> log("Closed");
> } catch (SQLException e) { ; }
> }
> } else if (action.equalsIgnoreCase("view")) {
> try {
> Client c = new Client(conn, true);
>
> String cname = request.getParameter("clientnr");
> log("Looking up username: " + username);
>
> if (!c.queryExactUserName(cname)) {
> // Should be an error, but instead, set as a message...
> req.setAttribute("msg", "Cannot find client with client number
> " + username + ", please try again.");
> forward("/jsp/clients/internal/index.jsp");
> } else {
> String phase = "general";
> String which = "0";
> LookupGeneral(c, which, request);
>
> // Forward along with client (c) and the information in the
> Lookup...
> request.setAttribute("client", c);
> request.setAttribute("phase", phase);
> request.setAttribute("action", action);
> request.setAttribute("which", which);
>
> forward("/jsp/clients/internal/index.jsp");
> }
> } catch (SQLException e) {
> setError("SQLError viewing internal client information");
> setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
> while ((e = e.getNextException()) != null) {
> log("Next exception: " + e.getMessage());
> log("Next exception major issue: " + e.getErrorCode());
> log("Next exception major issue: " + e.getCause());
> }
> err();
> } catch (Exception e) {
> setError("General Error viewing internal client information");
> setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
> err();
> } finally {
> try {
> log("Closing connections");
> if (conn != null) {
> conn.close();
> conn = null;
> }
> log("Closed");
> } catch (SQLException e) { ; }
> }
> }
> }
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by John Zoetebier <jo...@transparent.co.nz>.
Results of DBCP 1.1 testing.
My first tests showed that:
- There is no problem with McKoi handling large number of connections.
Meximum number of connections in my test is 50
- DBCP hung on 9th connection
After some further digging I discovered that I had not set MaxIdle in my
tests.
After setting: genericConnectionPool.setmaxIdle(-1) there was not problem
with DBCP anymore.
You have to be aware though of an internal preset number of 8 connections,
in stead of "unlimited" which I would expect to be the default. 8
connections seems rather arbitrary to my opinion.
My tests show without any doubt that you can exclude DBCP 1.1 as a
possible source for your problem.
--
John Zoetebier
Web site: http://www.transparent.co.nz
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by Anthony Presley <an...@zoraptera.com>.
On Mon, 2003-11-24 at 18:18, Vic Cekvenich wrote:
> Consider testing the app before deploying, such as OpenSTA.
Agreed. It was tested "relatively" extensively for about six months
amongst the various offices, with no problems. Problems occur only when
everyone is on it.
> What DAO layer are you using?
> (ibatis, hibrenate, ?)
Not using one, using JDBC directly through the DBCP commons. Using one
would require retooling the 100K+ lines of code.
Following is some example code, again, it works fine with one request to
the servlet (say, I click and load one client). But, if I click on more
than one client (say, six one after another), I start getting Error
500's, and the logs read "Connection closed" or "Statement already
closed". This appears to happen (from what I can tell) most often in
the JSP. My theory, currently, is:
Servlet is called / created
JDBC calls made / JSP called (via forward)
Connection is closed
Return from forward to JSP
Finally{} from try block is called, connection is not null, but not
open, error is thrown.
Of course, unless the app is grabbing the SAME connection, I don't see
why this would be happening (no JDBC calls occur in the forwards).
On the other hand, closing the connection prior to the forward() doesn't
give the above errors, but does give the error:
java.lang.IllegalStateException
at
org.apache.coyote.tomcat4.CoyoteResponseFacade.sendRedirect(CoyoteResponseFacade.java:340)
What does that mean <Google's not helping too much>?
Some example code:
protected void processRequest(HttpServletRequest request,
HttpServletResponse response)
throws ServletException, java.io.IOException {
// Create / validate the current session
session = request.getSession(true);
if (session == null) {
setErr(SESSION_ERR);
err();
return;
} else {
username = request.getRemoteUser();
}
try {
javax.servlet.ServletContext context = getServletContext();
String dbInit = context.getInitParameter("dbInit");
if (dbInit == null) {
setError(DBINIT_ERR);
err();
return;
}
Context ctx = new InitialContext();
if (ctx == null) {
setError(CONTEXT_ERR);
err();
return;
}
DataSource ds = (DataSource) ctx.lookup(dbInit);
if (ds != null) {
conn = ds.getConnection();
} else {
setError(CONTEXT_ERR);
err();
return;
}
if (conn == null || conn.isClosed()) {
// Try again to fetch a new connection?
log("Connection was closed");
while (conn == null || conn.isClosed()) {
conn = ds.getConnection();
}
log("Reopened the connection");
}
// Did not make a valid connection for some reason, take to error
screen
if (conn == null) {
setError(DBNULL_ERR);
err();
return;
} else {
// Should be a valid user
if (username == null) {
setError(NOUSER_ERR);
err();
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) { ; }
return;
}
}
log("Sure of connection ... should NEVER be closed.");
} catch (Exception e) {
setError(CONTEXT_ERR);
err();
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ex) { ; }
return;
}
// Valid user, valid roles
// Process their data
String action = (String) request.getParameter("action");
String type = (String) request.getParameter("type");
if (action == null) {
// No action to perform, error
setError(ACTION_ERR);
err();
try {
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) { ; }
} else if (action.equalsIgnoreCase("search")) {
// LOOKING for a client or lead
// Search based on lname, fname AND/OR unumber
String lname = request.getParameter("lname");
String fname = request.getParameter("fname");
String unum = request.getParameter("clientnr");
try {
DoSearch(request);
} catch (Exception e) {
setError("Error searching clients for: " + username);
setExtraErr(e.getMessage());
err();
} finally {
try {
log("Closing");
if (conn != null) {
conn.close();
conn = null;
}
log("Closed");
} catch (SQLException e) { ; }
}
} else if (action.equalsIgnoreCase("view")) {
try {
Client c = new Client(conn, true);
String cname = request.getParameter("clientnr");
log("Looking up username: " + username);
if (!c.queryExactUserName(cname)) {
// Should be an error, but instead, set as a message...
req.setAttribute("msg", "Cannot find client with client number
" + username + ", please try again.");
forward("/jsp/clients/internal/index.jsp");
} else {
String phase = "general";
String which = "0";
LookupGeneral(c, which, request);
// Forward along with client (c) and the information in the
Lookup...
request.setAttribute("client", c);
request.setAttribute("phase", phase);
request.setAttribute("action", action);
request.setAttribute("which", which);
forward("/jsp/clients/internal/index.jsp");
}
} catch (SQLException e) {
setError("SQLError viewing internal client information");
setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
while ((e = e.getNextException()) != null) {
log("Next exception: " + e.getMessage());
log("Next exception major issue: " + e.getErrorCode());
log("Next exception major issue: " + e.getCause());
}
err();
} catch (Exception e) {
setError("General Error viewing internal client information");
setExtraErr(e.getMessage() + " RootCause: " + e.getCause());
err();
} finally {
try {
log("Closing connections");
if (conn != null) {
conn.close();
conn = null;
}
log("Closed");
} catch (SQLException e) { ; }
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: DBCP "Statement already closed .... how?"
Posted by Vic Cekvenich <ce...@baseBeans.com>.
Consider testing the app before deploying, such as OpenSTA.
What DAO layer are you using?
(ibatis, hibrenate, ?)
.V
Anthony Presley wrote:
> Hello all ....
>
> I've been using DBCP to develop an application for the last 12 months,
> and it's finally been placed into "active" use. Which is great ...
> except that it didn't scale very well. When being used by less than 10
> or 15 users, it's fine. However, as soon as the number of users
> increases (or I simulate by clicking on a bunch of database links at one
> time), I start getting a lot of "'Statement' already closed." error's in
> my logs.
>
> As far as I can tell, I'm not doing any double closings. Again, this
> works fine and dandy when there's only a few users, but once several
> people get on it, it starts with these errors, and they're rather
> annoying. Most intruiging is that if you wait a few seconds, then try
> again, requests to the database work fine.
>
> I've thrown in a LOT of log() statements in my code to ensure that the
> database connections are, in fact, being closed and returned to the pool
> -- and they are. From what I can tell, they're being closed somewhere
> in the middle of the processing of them.
>
> My Tomcat config looks like (and I'm using DBCP 1.1 and nightly (have
> tried both) and Pool 1.1) :
>
> <ResourceParams name="jdbc/ifx" scope="Shareable">
> <parameter>
> <name>factory</name>
>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
>
> <parameter>
> <name>maxActive</name>
> <value>15</value>
> </parameter>
>
> <parameter>
> <name>maxIdle</name>
> <value>3</value>
> </parameter>
>
> <parameter>
> <name>validationQuery</name>
> <value>SELECT user.id FROM user WHERE id =
> 1</value>
> </parameter>
>
> <parameter>
> <name>removeAbandonedTimeout</name>
> <value>300</value>
> </parameter>
>
> <parameter>
> <name>removeAbandoned</name>
> <value>true</value>
> </parameter>
>
> <parameter>
> <name>maxWait</name>
> <value>10000</value>
> </parameter>
>
> <parameter>
> <name>driverClassName</name>
> <value>com.informix.jdbc.IfxDriver</value>
> </parameter>
>
> <parameter>
> <name>url</name>
> <value>jdbc:informix-sqli://ids:1314/development:INFORMIXSERVER=london_net</value>
> </parameter>
> </ResourceParams>
>
> A chunk of the log is:
>
> 2003-11-24 17:04:29 InternalClients: Closing connections
> 2003-11-24 17:04:29 InternalClients: ERROR: Servlet InternalClients, SQL
> Error lookup up General Information: 'Statement' already closed.
> RootCause: null: /Error
> 2003-11-24 17:04:29 InternalClients: ERROR: Servlet InternalClients,
> General Error viewing internal client information: null RootCause: null:
> /Error
> 2003-11-24 17:04:29 InternalClients: Closing connections
> 2003-11-24 17:04:29 InternalClients: Closed
> 2003-11-24 17:04:29 StandardWrapperValve[InternalClients]:
> Servlet.service() for servlet InternalClients threw exception
> java.lang.IllegalStateException
> at
> org.apache.coyote.tomcat4.CoyoteResponseFacade.sendRedirect(CoyoteResponseFacade.java:340)
> at azure.servlets.Azure.err(Azure.java:76)
> at
> azure.servlets.InternalClients.processRequest(InternalClients.java:199)
> at azure.servlets.InternalClients.doGet(InternalClients.java:34)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
> org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
>
> Again, the link that generated the error .... clicking on it right after
> it logged these error's, it works fine. Which obviously (to me?) leads
> me right to ... a problem with the DBCP, or the DB, or my code. These
> are not simple problems to troubleshoot, it appears.
>
> I can post the code the generates this .... if that would help. At this
> point in the game, I'm pretty much at my wits ends.
>
> Any help is much appreciated ....
>
> --Anthony
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org