You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Tyson Lowery <ty...@gmail.com> on 2010/11/09 01:19:26 UTC
DBCP - unclosed connections
I have a JSP page that is getting reported for not closing connections
in catalina.out. I'm running Tomcat 6.0.26, so I believe we are on DBCP
1.2. I've racked my brain trying to figure out how these connections
could possible remain unclosed. Does anyone have any tips or
suggestions on how I can further troubleshoot this?
Here's the latest version of our connection pool settings:
<Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
maxActive="350" maxIdle="40" minIdle="10" maxWait="45"
removeAbandoned="true"
removeAbandonedTimeout="55"
validationQuery="select 1"
testWhileIdle="true"
testOnBorrow="true"
logAbandoned="true"
timeBetweenEvictionRunsMillis="100000"
minEvictableIdleTimeMillis="400000"
numTestsPerEvictionRun="3"
username="user" password="password"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://1.2.3.4/myDB?autoReconnect=true&jdbcCompliantTruncation=false"/>
The JSP page literally has everything enclosed in a try block and all
connections closed in a finally statement. See below:
Connection con = null;
Statement stmt = null;
ResultSet rset = null;
String query = "";
Statement stmt2 = null;
ResultSet rset3 = null;
try {
// page executes
}
catch (SQLException ex) {
out.println ("\n*** SQLException caught ***\n");
while (ex != null) {
out.println ("SQLState: " + ex.getSQLState ());
out.println ("Message: " + ex.getMessage ());
out.println ("Vendor: " + ex.getErrorCode ());
ex = ex.getNextException ();
out.println ("");
}
}
catch (java.lang.Exception ex) { // Got some other type of exception.
Dump it.
ex.printStackTrace ();
}
finally {
if(rset != null) {
try {rset.close();}
catch(Exception e) {
System.out.println("Exception in finally rset");
e.printStackTrace();
}
rset = null;
}
if(rset3 != null) {
try {rset3.close();}
catch(Exception e) {
System.out.println("Exception in finally rset3");
e.printStackTrace();
}
rset3 = null;
}
if(stmt != null) {
try {stmt.close();}
catch(Exception e) {
System.out.println("Exception in finally stmt");
e.printStackTrace();
}
stmt = null;
}
if(stmt2 != null) {
try {stmt2.close();}
catch(Exception e) {
System.out.println("Exception in finally stmt2");
e.printStackTrace();
}
stmt2 = null;
}
if(con != null) {
try {con.close();}
catch(Exception e) {
System.out.println("Exception in finally con");
e.printStackTrace();
}
con = null;
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP - unclosed connections
Posted by Tyson Lowery <ty...@gmail.com>.
Disregard this message. Upon closer inspection, I can see that the
reported unclosed connections are in fact not being closed. I am not
sure if this was fixed by the upgrade or not, but I took a closer look
at the line numbers in the compiled _jsp.java and they are connections
from an imported page that are not being closed.
Tyson
On 11/12/2010 9:15 AM, Tyson Lowery wrote:
> Thanks for the help. Unfortunately, I've made the change to upgrade
> and I'm still seeing the issue. I've gone ahead and added some more
> debugging info to my code. I'm seeing the issue about 1% of the time
> with this test case. I'm outputting debugging information before my
> try block, and at the end of my finally block (among other places). A
> grep count shows that there are an equal number of these statements in
> the catalina.out file over a specific time period, 637. I am checked
> how many times this page appeared in the access_log over the same time
> period, and it also added up to 637. During this time, it was
> reported that 6 connections were not closed by this jsp. I also am
> outputting the result of Connection.isClosed() and
> Statement.isClosed() to catalina.out after I close the connections and
> statements. These always show as true.
>
> Please note that I have other jsps that I know are NOT closing
> connections properly. Is it possible that AbandonedTrace.java has a
> bug and is reporting the wrong jsp page in the log?
>
> Where should I dig next?
>
> Thanks,
> Tyson
>
> On 11/11/2010 1:08 PM, Mark Thomas wrote:
>> On 11/11/2010 18:57, Tyson Lowery wrote:
>>> Thanks Mark for taking the time to reply.
>>>
>>> I've upgraded Tomcat to 6.0.29. Do I still need to explicitly set the
>>> factory to make sure I'm using DBCP 1.3?
>> Maybe. 6.0.29 ships with DBCP 1.3 and Pool 1.5.4. To get the benefits of
>> Pool 1.5.5 you'd need to follow the instructions below.
>>
>> 6.0.30 will include Pool 1.5.5
>>
>> Mark
>>
>>> On 11/11/2010 12:37 AM, Mark Thomas wrote:
>>>> On 11/11/2010 05:11, Phil Steitz wrote:
>>>>> I will check or someone else can confirm DBCP and pool versions.
>>>> http://svn.apache.org/viewvc/tomcat/tc6.0.x/tags/TOMCAT_6_0_26/build.properties.default?view=markup
>>>>
>>>>
>>>>
>>>> DBCP 1.2.2
>>>> Pool 1.5.4
>>>>
>>>>> If not the latest you can upgrade them independently of Tomcat and
>>>>> you should try that. See the Tomcat datasource docs for instructions
>>>>> on how to do this. Ask here or on tomcat-user if you need help.
>>>> 1. Add latest DBCP& Pool JARs to CATALINA_BASE/lib
>>>>
>>>> 2. Modify your Resource to include:
>>>> factory="org.apache.commons.dbcp.BasicDataSourceFactory"
>>>>
>>>> 3. Restart
>>>>
>>>> Mark
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> For additional commands, e-mail: user-help@commons.apache.org
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP - unclosed connections
Posted by Tyson Lowery <ty...@gmail.com>.
Thanks for the help. Unfortunately, I've made the change to upgrade and
I'm still seeing the issue. I've gone ahead and added some more
debugging info to my code. I'm seeing the issue about 1% of the time
with this test case. I'm outputting debugging information before my try
block, and at the end of my finally block (among other places). A grep
count shows that there are an equal number of these statements in the
catalina.out file over a specific time period, 637. I am checked how
many times this page appeared in the access_log over the same time
period, and it also added up to 637. During this time, it was reported
that 6 connections were not closed by this jsp. I also am outputting
the result of Connection.isClosed() and Statement.isClosed() to
catalina.out after I close the connections and statements. These always
show as true.
Please note that I have other jsps that I know are NOT closing
connections properly. Is it possible that AbandonedTrace.java has a bug
and is reporting the wrong jsp page in the log?
Where should I dig next?
Thanks,
Tyson
On 11/11/2010 1:08 PM, Mark Thomas wrote:
> On 11/11/2010 18:57, Tyson Lowery wrote:
>> Thanks Mark for taking the time to reply.
>>
>> I've upgraded Tomcat to 6.0.29. Do I still need to explicitly set the
>> factory to make sure I'm using DBCP 1.3?
> Maybe. 6.0.29 ships with DBCP 1.3 and Pool 1.5.4. To get the benefits of
> Pool 1.5.5 you'd need to follow the instructions below.
>
> 6.0.30 will include Pool 1.5.5
>
> Mark
>
>> On 11/11/2010 12:37 AM, Mark Thomas wrote:
>>> On 11/11/2010 05:11, Phil Steitz wrote:
>>>> I will check or someone else can confirm DBCP and pool versions.
>>> http://svn.apache.org/viewvc/tomcat/tc6.0.x/tags/TOMCAT_6_0_26/build.properties.default?view=markup
>>>
>>>
>>> DBCP 1.2.2
>>> Pool 1.5.4
>>>
>>>> If not the latest you can upgrade them independently of Tomcat and
>>>> you should try that. See the Tomcat datasource docs for instructions
>>>> on how to do this. Ask here or on tomcat-user if you need help.
>>> 1. Add latest DBCP& Pool JARs to CATALINA_BASE/lib
>>>
>>> 2. Modify your Resource to include:
>>> factory="org.apache.commons.dbcp.BasicDataSourceFactory"
>>>
>>> 3. Restart
>>>
>>> Mark
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP - unclosed connections
Posted by Mark Thomas <ma...@apache.org>.
On 11/11/2010 18:57, Tyson Lowery wrote:
> Thanks Mark for taking the time to reply.
>
> I've upgraded Tomcat to 6.0.29. Do I still need to explicitly set the
> factory to make sure I'm using DBCP 1.3?
Maybe. 6.0.29 ships with DBCP 1.3 and Pool 1.5.4. To get the benefits of
Pool 1.5.5 you'd need to follow the instructions below.
6.0.30 will include Pool 1.5.5
Mark
>
> On 11/11/2010 12:37 AM, Mark Thomas wrote:
>> On 11/11/2010 05:11, Phil Steitz wrote:
>>> I will check or someone else can confirm DBCP and pool versions.
>> http://svn.apache.org/viewvc/tomcat/tc6.0.x/tags/TOMCAT_6_0_26/build.properties.default?view=markup
>>
>>
>> DBCP 1.2.2
>> Pool 1.5.4
>>
>>> If not the latest you can upgrade them independently of Tomcat and
>>> you should try that. See the Tomcat datasource docs for instructions
>>> on how to do this. Ask here or on tomcat-user if you need help.
>> 1. Add latest DBCP & Pool JARs to CATALINA_BASE/lib
>>
>> 2. Modify your Resource to include:
>> factory="org.apache.commons.dbcp.BasicDataSourceFactory"
>>
>> 3. Restart
>>
>> Mark
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP - unclosed connections
Posted by Tyson Lowery <ty...@gmail.com>.
Thanks Mark for taking the time to reply.
I've upgraded Tomcat to 6.0.29. Do I still need to explicitly set the
factory to make sure I'm using DBCP 1.3?
On 11/11/2010 12:37 AM, Mark Thomas wrote:
> On 11/11/2010 05:11, Phil Steitz wrote:
>> I will check or someone else can confirm DBCP and pool versions.
> http://svn.apache.org/viewvc/tomcat/tc6.0.x/tags/TOMCAT_6_0_26/build.properties.default?view=markup
>
> DBCP 1.2.2
> Pool 1.5.4
>
>> If not the latest you can upgrade them independently of Tomcat and you should try that. See the Tomcat datasource docs for instructions on how to do this. Ask here or on tomcat-user if you need help.
> 1. Add latest DBCP& Pool JARs to CATALINA_BASE/lib
>
> 2. Modify your Resource to include:
> factory="org.apache.commons.dbcp.BasicDataSourceFactory"
>
> 3. Restart
>
> Mark
>
>> The answer to your question is no, so if this is happening it indicates a DBCP or pool bug or something else happening in your code to hold the connections.
>>
>> Phil
>>
>>
>>
>> On Nov 10, 2010, at 1:39 PM, Tyson Lowery<ty...@gmail.com> wrote:
>>
>>> Thanks Phil for the reply. I've changed maxWait to 1000 and removeAbandonedTimeout to 300. MySQL wait_timeout is set to 500.
>>>
>>> I've put some System.out.printlns in and can see that this page is taking less than 1 second to process, even in the cases where I get the DBCP object created 2010-11-10 12:54:14 by the following code was never closed:
>>> java.lang.Exception errors. So I don't think the timeout is being hit. I can also see that the finally block is being executed, I'm now printing to catalina.out after each object is closed.
>>>
>>> Note that this isn't being reported everytime the page loads, just once in a while.
>>>
>>> Are there cases where this exception is generated even though the connections were successfully closed? Or is there a way to get more info from AbandonedTrace.java?
>>>
>>> Thanks,
>>> Tyson
>>>
>>> On 11/10/2010 2:44 AM, Phil Steitz wrote:
>>>> On 11/8/10 7:19 PM, Tyson Lowery wrote:
>>>>> I have a JSP page that is getting reported for not closing
>>>>> connections in catalina.out. I'm running Tomcat 6.0.26, so I believe
>>>>> we are on DBCP 1.2. I've racked my brain trying to figure out how
>>>>> these connections could possible remain unclosed. Does anyone have
>>>>> any tips or suggestions on how I can further troubleshoot this?
>>>>>
>>>> If your page holds onto a connection for longer than 55 seconds or there are queries taking longer than 55 seconds to execute, DBCP will consider the associated connection abandoned. Try increasing this setting. Note also that maxWait is specified in milliseconds, so below is a pretty low setting.
>>>>
>>>> Phil
>>>>
>>>>> Here's the latest version of our connection pool settings:
>>>>> <Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
>>>>> maxActive="350" maxIdle="40" minIdle="10" maxWait="45"
>>>>> removeAbandoned="true"
>>>>> removeAbandonedTimeout="55"
>>>>> validationQuery="select 1"
>>>>> testWhileIdle="true"
>>>>> testOnBorrow="true"
>>>>> logAbandoned="true"
>>>>> timeBetweenEvictionRunsMillis="100000"
>>>>> minEvictableIdleTimeMillis="400000"
>>>>> numTestsPerEvictionRun="3"
>>>>> username="user" password="password"
>>>>> driverClassName="com.mysql.jdbc.Driver"
>>>>> url="jdbc:mysql://1.2.3.4/myDB?autoReconnect=true&jdbcCompliantTruncation=false"/>
>>>>>
>>>>>
>>>>>
>>>>> The JSP page literally has everything enclosed in a try block and
>>>>> all connections closed in a finally statement. See below:
>>>>>
>>>>> Connection con = null;
>>>>> Statement stmt = null;
>>>>> ResultSet rset = null;
>>>>> String query = "";
>>>>> Statement stmt2 = null;
>>>>> ResultSet rset3 = null;
>>>>> try {
>>>>> // page executes
>>>>> }
>>>>> catch (SQLException ex) {
>>>>> out.println ("\n*** SQLException caught ***\n");
>>>>> while (ex != null) {
>>>>> out.println ("SQLState: " + ex.getSQLState ());
>>>>> out.println ("Message: " + ex.getMessage ());
>>>>> out.println ("Vendor: " + ex.getErrorCode ());
>>>>> ex = ex.getNextException ();
>>>>> out.println ("");
>>>>> }
>>>>> }
>>>>> catch (java.lang.Exception ex) { // Got some other type of
>>>>> exception. Dump it.
>>>>> ex.printStackTrace ();
>>>>> }
>>>>> finally {
>>>>> if(rset != null) {
>>>>> try {rset.close();}
>>>>> catch(Exception e) {
>>>>> System.out.println("Exception in finally rset");
>>>>> e.printStackTrace();
>>>>> }
>>>>> rset = null;
>>>>> }
>>>>> if(rset3 != null) {
>>>>> try {rset3.close();}
>>>>> catch(Exception e) {
>>>>> System.out.println("Exception in finally rset3");
>>>>> e.printStackTrace();
>>>>> }
>>>>> rset3 = null;
>>>>> }
>>>>> if(stmt != null) {
>>>>> try {stmt.close();}
>>>>> catch(Exception e) {
>>>>> System.out.println("Exception in finally stmt");
>>>>> e.printStackTrace();
>>>>> }
>>>>> stmt = null;
>>>>> }
>>>>> if(stmt2 != null) {
>>>>> try {stmt2.close();}
>>>>> catch(Exception e) {
>>>>> System.out.println("Exception in finally stmt2");
>>>>> e.printStackTrace();
>>>>> }
>>>>> stmt2 = null;
>>>>> }
>>>>> if(con != null) {
>>>>> try {con.close();}
>>>>> catch(Exception e) {
>>>>> System.out.println("Exception in finally con");
>>>>> e.printStackTrace();
>>>>> }
>>>>> con = null;
>>>>> }
>>>>> }
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>>>>> For additional commands, e-mail: user-help@commons.apache.org
>>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>>>> For additional commands, e-mail: user-help@commons.apache.org
>>>>
>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>>> For additional commands, e-mail: user-help@commons.apache.org
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> For additional commands, e-mail: user-help@commons.apache.org
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP - unclosed connections
Posted by Mark Thomas <ma...@apache.org>.
On 11/11/2010 05:11, Phil Steitz wrote:
> I will check or someone else can confirm DBCP and pool versions.
http://svn.apache.org/viewvc/tomcat/tc6.0.x/tags/TOMCAT_6_0_26/build.properties.default?view=markup
DBCP 1.2.2
Pool 1.5.4
> If not the latest you can upgrade them independently of Tomcat and you should try that. See the Tomcat datasource docs for instructions on how to do this. Ask here or on tomcat-user if you need help.
1. Add latest DBCP & Pool JARs to CATALINA_BASE/lib
2. Modify your Resource to include:
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
3. Restart
Mark
> The answer to your question is no, so if this is happening it indicates a DBCP or pool bug or something else happening in your code to hold the connections.
>
> Phil
>
>
>
> On Nov 10, 2010, at 1:39 PM, Tyson Lowery <ty...@gmail.com> wrote:
>
>> Thanks Phil for the reply. I've changed maxWait to 1000 and removeAbandonedTimeout to 300. MySQL wait_timeout is set to 500.
>>
>> I've put some System.out.printlns in and can see that this page is taking less than 1 second to process, even in the cases where I get the DBCP object created 2010-11-10 12:54:14 by the following code was never closed:
>> java.lang.Exception errors. So I don't think the timeout is being hit. I can also see that the finally block is being executed, I'm now printing to catalina.out after each object is closed.
>>
>> Note that this isn't being reported everytime the page loads, just once in a while.
>>
>> Are there cases where this exception is generated even though the connections were successfully closed? Or is there a way to get more info from AbandonedTrace.java?
>>
>> Thanks,
>> Tyson
>>
>> On 11/10/2010 2:44 AM, Phil Steitz wrote:
>>> On 11/8/10 7:19 PM, Tyson Lowery wrote:
>>>> I have a JSP page that is getting reported for not closing
>>>> connections in catalina.out. I'm running Tomcat 6.0.26, so I believe
>>>> we are on DBCP 1.2. I've racked my brain trying to figure out how
>>>> these connections could possible remain unclosed. Does anyone have
>>>> any tips or suggestions on how I can further troubleshoot this?
>>>>
>>>
>>> If your page holds onto a connection for longer than 55 seconds or there are queries taking longer than 55 seconds to execute, DBCP will consider the associated connection abandoned. Try increasing this setting. Note also that maxWait is specified in milliseconds, so below is a pretty low setting.
>>>
>>> Phil
>>>
>>>> Here's the latest version of our connection pool settings:
>>>> <Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
>>>> maxActive="350" maxIdle="40" minIdle="10" maxWait="45"
>>>> removeAbandoned="true"
>>>> removeAbandonedTimeout="55"
>>>> validationQuery="select 1"
>>>> testWhileIdle="true"
>>>> testOnBorrow="true"
>>>> logAbandoned="true"
>>>> timeBetweenEvictionRunsMillis="100000"
>>>> minEvictableIdleTimeMillis="400000"
>>>> numTestsPerEvictionRun="3"
>>>> username="user" password="password"
>>>> driverClassName="com.mysql.jdbc.Driver"
>>>> url="jdbc:mysql://1.2.3.4/myDB?autoReconnect=true&jdbcCompliantTruncation=false"/>
>>>>
>>>>
>>>>
>>>> The JSP page literally has everything enclosed in a try block and
>>>> all connections closed in a finally statement. See below:
>>>>
>>>> Connection con = null;
>>>> Statement stmt = null;
>>>> ResultSet rset = null;
>>>> String query = "";
>>>> Statement stmt2 = null;
>>>> ResultSet rset3 = null;
>>>> try {
>>>> // page executes
>>>> }
>>>> catch (SQLException ex) {
>>>> out.println ("\n*** SQLException caught ***\n");
>>>> while (ex != null) {
>>>> out.println ("SQLState: " + ex.getSQLState ());
>>>> out.println ("Message: " + ex.getMessage ());
>>>> out.println ("Vendor: " + ex.getErrorCode ());
>>>> ex = ex.getNextException ();
>>>> out.println ("");
>>>> }
>>>> }
>>>> catch (java.lang.Exception ex) { // Got some other type of
>>>> exception. Dump it.
>>>> ex.printStackTrace ();
>>>> }
>>>> finally {
>>>> if(rset != null) {
>>>> try {rset.close();}
>>>> catch(Exception e) {
>>>> System.out.println("Exception in finally rset");
>>>> e.printStackTrace();
>>>> }
>>>> rset = null;
>>>> }
>>>> if(rset3 != null) {
>>>> try {rset3.close();}
>>>> catch(Exception e) {
>>>> System.out.println("Exception in finally rset3");
>>>> e.printStackTrace();
>>>> }
>>>> rset3 = null;
>>>> }
>>>> if(stmt != null) {
>>>> try {stmt.close();}
>>>> catch(Exception e) {
>>>> System.out.println("Exception in finally stmt");
>>>> e.printStackTrace();
>>>> }
>>>> stmt = null;
>>>> }
>>>> if(stmt2 != null) {
>>>> try {stmt2.close();}
>>>> catch(Exception e) {
>>>> System.out.println("Exception in finally stmt2");
>>>> e.printStackTrace();
>>>> }
>>>> stmt2 = null;
>>>> }
>>>> if(con != null) {
>>>> try {con.close();}
>>>> catch(Exception e) {
>>>> System.out.println("Exception in finally con");
>>>> e.printStackTrace();
>>>> }
>>>> con = null;
>>>> }
>>>> }
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>>>> For additional commands, e-mail: user-help@commons.apache.org
>>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>>> For additional commands, e-mail: user-help@commons.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> For additional commands, e-mail: user-help@commons.apache.org
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP - unclosed connections
Posted by Phil Steitz <ph...@gmail.com>.
I will check or someone else can confirm DBCP and pool versions. If not the latest you can upgrade them independently of Tomcat and you should try that. See the Tomcat datasource docs for instructions on how to do this. Ask here or on tomcat-user if you need help.
The answer to your question is no, so if this is happening it indicates a DBCP or pool bug or something else happening in your code to hold the connections.
Phil
On Nov 10, 2010, at 1:39 PM, Tyson Lowery <ty...@gmail.com> wrote:
> Thanks Phil for the reply. I've changed maxWait to 1000 and removeAbandonedTimeout to 300. MySQL wait_timeout is set to 500.
>
> I've put some System.out.printlns in and can see that this page is taking less than 1 second to process, even in the cases where I get the DBCP object created 2010-11-10 12:54:14 by the following code was never closed:
> java.lang.Exception errors. So I don't think the timeout is being hit. I can also see that the finally block is being executed, I'm now printing to catalina.out after each object is closed.
>
> Note that this isn't being reported everytime the page loads, just once in a while.
>
> Are there cases where this exception is generated even though the connections were successfully closed? Or is there a way to get more info from AbandonedTrace.java?
>
> Thanks,
> Tyson
>
> On 11/10/2010 2:44 AM, Phil Steitz wrote:
>> On 11/8/10 7:19 PM, Tyson Lowery wrote:
>>> I have a JSP page that is getting reported for not closing
>>> connections in catalina.out. I'm running Tomcat 6.0.26, so I believe
>>> we are on DBCP 1.2. I've racked my brain trying to figure out how
>>> these connections could possible remain unclosed. Does anyone have
>>> any tips or suggestions on how I can further troubleshoot this?
>>>
>>
>> If your page holds onto a connection for longer than 55 seconds or there are queries taking longer than 55 seconds to execute, DBCP will consider the associated connection abandoned. Try increasing this setting. Note also that maxWait is specified in milliseconds, so below is a pretty low setting.
>>
>> Phil
>>
>>> Here's the latest version of our connection pool settings:
>>> <Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
>>> maxActive="350" maxIdle="40" minIdle="10" maxWait="45"
>>> removeAbandoned="true"
>>> removeAbandonedTimeout="55"
>>> validationQuery="select 1"
>>> testWhileIdle="true"
>>> testOnBorrow="true"
>>> logAbandoned="true"
>>> timeBetweenEvictionRunsMillis="100000"
>>> minEvictableIdleTimeMillis="400000"
>>> numTestsPerEvictionRun="3"
>>> username="user" password="password"
>>> driverClassName="com.mysql.jdbc.Driver"
>>> url="jdbc:mysql://1.2.3.4/myDB?autoReconnect=true&jdbcCompliantTruncation=false"/>
>>>
>>>
>>>
>>> The JSP page literally has everything enclosed in a try block and
>>> all connections closed in a finally statement. See below:
>>>
>>> Connection con = null;
>>> Statement stmt = null;
>>> ResultSet rset = null;
>>> String query = "";
>>> Statement stmt2 = null;
>>> ResultSet rset3 = null;
>>> try {
>>> // page executes
>>> }
>>> catch (SQLException ex) {
>>> out.println ("\n*** SQLException caught ***\n");
>>> while (ex != null) {
>>> out.println ("SQLState: " + ex.getSQLState ());
>>> out.println ("Message: " + ex.getMessage ());
>>> out.println ("Vendor: " + ex.getErrorCode ());
>>> ex = ex.getNextException ();
>>> out.println ("");
>>> }
>>> }
>>> catch (java.lang.Exception ex) { // Got some other type of
>>> exception. Dump it.
>>> ex.printStackTrace ();
>>> }
>>> finally {
>>> if(rset != null) {
>>> try {rset.close();}
>>> catch(Exception e) {
>>> System.out.println("Exception in finally rset");
>>> e.printStackTrace();
>>> }
>>> rset = null;
>>> }
>>> if(rset3 != null) {
>>> try {rset3.close();}
>>> catch(Exception e) {
>>> System.out.println("Exception in finally rset3");
>>> e.printStackTrace();
>>> }
>>> rset3 = null;
>>> }
>>> if(stmt != null) {
>>> try {stmt.close();}
>>> catch(Exception e) {
>>> System.out.println("Exception in finally stmt");
>>> e.printStackTrace();
>>> }
>>> stmt = null;
>>> }
>>> if(stmt2 != null) {
>>> try {stmt2.close();}
>>> catch(Exception e) {
>>> System.out.println("Exception in finally stmt2");
>>> e.printStackTrace();
>>> }
>>> stmt2 = null;
>>> }
>>> if(con != null) {
>>> try {con.close();}
>>> catch(Exception e) {
>>> System.out.println("Exception in finally con");
>>> e.printStackTrace();
>>> }
>>> con = null;
>>> }
>>> }
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>>> For additional commands, e-mail: user-help@commons.apache.org
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> For additional commands, e-mail: user-help@commons.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP - unclosed connections
Posted by Tyson Lowery <ty...@gmail.com>.
Thanks Phil for the reply. I've changed maxWait to 1000 and
removeAbandonedTimeout to 300. MySQL wait_timeout is set to 500.
I've put some System.out.printlns in and can see that this page is
taking less than 1 second to process, even in the cases where I get the
DBCP object created 2010-11-10 12:54:14 by the following code was never
closed:
java.lang.Exception errors. So I don't think the timeout is being hit.
I can also see that the finally block is being executed, I'm now
printing to catalina.out after each object is closed.
Note that this isn't being reported everytime the page loads, just once
in a while.
Are there cases where this exception is generated even though the
connections were successfully closed? Or is there a way to get more
info from AbandonedTrace.java?
Thanks,
Tyson
On 11/10/2010 2:44 AM, Phil Steitz wrote:
> On 11/8/10 7:19 PM, Tyson Lowery wrote:
>> I have a JSP page that is getting reported for not closing
>> connections in catalina.out. I'm running Tomcat 6.0.26, so I believe
>> we are on DBCP 1.2. I've racked my brain trying to figure out how
>> these connections could possible remain unclosed. Does anyone have
>> any tips or suggestions on how I can further troubleshoot this?
>>
>
> If your page holds onto a connection for longer than 55 seconds or
> there are queries taking longer than 55 seconds to execute, DBCP will
> consider the associated connection abandoned. Try increasing this
> setting. Note also that maxWait is specified in milliseconds, so
> below is a pretty low setting.
>
> Phil
>
>> Here's the latest version of our connection pool settings:
>> <Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
>> maxActive="350" maxIdle="40" minIdle="10" maxWait="45"
>> removeAbandoned="true"
>> removeAbandonedTimeout="55"
>> validationQuery="select 1"
>> testWhileIdle="true"
>> testOnBorrow="true"
>> logAbandoned="true"
>> timeBetweenEvictionRunsMillis="100000"
>> minEvictableIdleTimeMillis="400000"
>> numTestsPerEvictionRun="3"
>> username="user" password="password"
>> driverClassName="com.mysql.jdbc.Driver"
>> url="jdbc:mysql://1.2.3.4/myDB?autoReconnect=true&jdbcCompliantTruncation=false"/>
>>
>>
>>
>>
>> The JSP page literally has everything enclosed in a try block and
>> all connections closed in a finally statement. See below:
>>
>> Connection con = null;
>> Statement stmt = null;
>> ResultSet rset = null;
>> String query = "";
>> Statement stmt2 = null;
>> ResultSet rset3 = null;
>> try {
>> // page executes
>> }
>> catch (SQLException ex) {
>> out.println ("\n*** SQLException caught ***\n");
>> while (ex != null) {
>> out.println ("SQLState: " + ex.getSQLState ());
>> out.println ("Message: " + ex.getMessage ());
>> out.println ("Vendor: " + ex.getErrorCode ());
>> ex = ex.getNextException ();
>> out.println ("");
>> }
>> }
>> catch (java.lang.Exception ex) { // Got some other type of
>> exception. Dump it.
>> ex.printStackTrace ();
>> }
>> finally {
>> if(rset != null) {
>> try {rset.close();}
>> catch(Exception e) {
>> System.out.println("Exception in finally rset");
>> e.printStackTrace();
>> }
>> rset = null;
>> }
>> if(rset3 != null) {
>> try {rset3.close();}
>> catch(Exception e) {
>> System.out.println("Exception in finally rset3");
>> e.printStackTrace();
>> }
>> rset3 = null;
>> }
>> if(stmt != null) {
>> try {stmt.close();}
>> catch(Exception e) {
>> System.out.println("Exception in finally stmt");
>> e.printStackTrace();
>> }
>> stmt = null;
>> }
>> if(stmt2 != null) {
>> try {stmt2.close();}
>> catch(Exception e) {
>> System.out.println("Exception in finally stmt2");
>> e.printStackTrace();
>> }
>> stmt2 = null;
>> }
>> if(con != null) {
>> try {con.close();}
>> catch(Exception e) {
>> System.out.println("Exception in finally con");
>> e.printStackTrace();
>> }
>> con = null;
>> }
>> }
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> For additional commands, e-mail: user-help@commons.apache.org
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: DBCP - unclosed connections
Posted by Phil Steitz <ph...@gmail.com>.
On 11/8/10 7:19 PM, Tyson Lowery wrote:
> I have a JSP page that is getting reported for not closing
> connections in catalina.out. I'm running Tomcat 6.0.26, so I believe
> we are on DBCP 1.2. I've racked my brain trying to figure out how
> these connections could possible remain unclosed. Does anyone have
> any tips or suggestions on how I can further troubleshoot this?
>
If your page holds onto a connection for longer than 55 seconds or
there are queries taking longer than 55 seconds to execute, DBCP
will consider the associated connection abandoned. Try increasing
this setting. Note also that maxWait is specified in milliseconds,
so below is a pretty low setting.
Phil
> Here's the latest version of our connection pool settings:
> <Resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource"
> maxActive="350" maxIdle="40" minIdle="10" maxWait="45"
> removeAbandoned="true"
> removeAbandonedTimeout="55"
> validationQuery="select 1"
> testWhileIdle="true"
> testOnBorrow="true"
> logAbandoned="true"
> timeBetweenEvictionRunsMillis="100000"
> minEvictableIdleTimeMillis="400000"
> numTestsPerEvictionRun="3"
> username="user" password="password"
> driverClassName="com.mysql.jdbc.Driver"
> url="jdbc:mysql://1.2.3.4/myDB?autoReconnect=true&jdbcCompliantTruncation=false"/>
>
>
>
> The JSP page literally has everything enclosed in a try block and
> all connections closed in a finally statement. See below:
>
> Connection con = null;
> Statement stmt = null;
> ResultSet rset = null;
> String query = "";
> Statement stmt2 = null;
> ResultSet rset3 = null;
> try {
> // page executes
> }
> catch (SQLException ex) {
> out.println ("\n*** SQLException caught ***\n");
> while (ex != null) {
> out.println ("SQLState: " + ex.getSQLState ());
> out.println ("Message: " + ex.getMessage ());
> out.println ("Vendor: " + ex.getErrorCode ());
> ex = ex.getNextException ();
> out.println ("");
> }
> }
> catch (java.lang.Exception ex) { // Got some other type of
> exception. Dump it.
> ex.printStackTrace ();
> }
> finally {
> if(rset != null) {
> try {rset.close();}
> catch(Exception e) {
> System.out.println("Exception in finally rset");
> e.printStackTrace();
> }
> rset = null;
> }
> if(rset3 != null) {
> try {rset3.close();}
> catch(Exception e) {
> System.out.println("Exception in finally rset3");
> e.printStackTrace();
> }
> rset3 = null;
> }
> if(stmt != null) {
> try {stmt.close();}
> catch(Exception e) {
> System.out.println("Exception in finally stmt");
> e.printStackTrace();
> }
> stmt = null;
> }
> if(stmt2 != null) {
> try {stmt2.close();}
> catch(Exception e) {
> System.out.println("Exception in finally stmt2");
> e.printStackTrace();
> }
> stmt2 = null;
> }
> if(con != null) {
> try {con.close();}
> catch(Exception e) {
> System.out.println("Exception in finally con");
> e.printStackTrace();
> }
> con = null;
> }
> }
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
> For additional commands, e-mail: user-help@commons.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org