Re: DBCP abandoned trace - unable to understand the leak
On 10/11/2010 06:51, sasidhar prabhakar wrote: After changing time out value now I am getting this problem org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object Shall we guess what you set it to? My guess is 7. Am I right? What else did you change? p On Tue, Nov 9, 2010 at 5:22 PM, Christopher Schultz ch...@christopherschultz.net wrote: Sasidhar, On 11/8/2010 12:31 AM, sasidhar prabhakar wrote: On Thu, Nov 4, 2010 at 9:10 PM, Christopher Schultz ch...@christopherschultz.net wrote: I have found that these exceptions can occur even when there is no leak. Specifically, if your SQL query takes a long time to run (that is, more than the ababdonedTimeout), another request to the connection pool complains about the connection and calls it abandoned. I think your right. Timeout I mentioned 30sec deafault is 300sec. This is my context.xml ?xml version=1.0 encoding=UTF-8? Context path= path is not allowed in context.xml: remove it. validationQuery=SELECT * from dual SELECT *? Wow. How about SELECT 1 FROM dual? testOnBorrow=true removeAbandoned=true removeAbandonedTimeout=30 That's a 30-second abandoned timeout. username=scott password=*** tiger, right? Technically speaking, the connection hasn't been leaked, but the connection pool can't really guess the reason why the connection hasn't been returned. Can you time your queries to see how long they take? Could you post your Resource configuration for your DataSource? For some queries it took more than 30 seconds, from getting data from ip_to_geo table, which has 3 million rows in it. That could be your problem: you should probably increase your removeAbandonedTimeout value to something more appropriate for your application. You might also want a dba to check out your queries and your database structure. 3 million rows isn't that much, even for Oracle :) Another note: I notice that you are using a DataSource object that survives for the life of the DAO object, and is even created by the object in its constructor. Every DAO has only one instance for the entire life of application. Is this correct approach. So Every thread accessing the same datasource object to get connection. It was just a recommendation which gives you flexibility: your webapp (or the container, etc.) has the freedom to discard and completely re-build the DataSource for your webapp if you always go to the JNDI context to get the DataSource. Otherwise, you will force a webapp restart just to get a new DataSource. -chris - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org 0x62590808.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Re: DBCP abandoned trace - unable to understand the leak
Sorry for that. I changed it 300 seconds. On Wed, Nov 10, 2010 at 2:12 AM, Pid p...@pidster.com wrote: On 10/11/2010 06:51, sasidhar prabhakar wrote: After changing time out value now I am getting this problem org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object Shall we guess what you set it to? My guess is 7. Am I right? What else did you change? p On Tue, Nov 9, 2010 at 5:22 PM, Christopher Schultz ch...@christopherschultz.net wrote: Sasidhar, On 11/8/2010 12:31 AM, sasidhar prabhakar wrote: On Thu, Nov 4, 2010 at 9:10 PM, Christopher Schultz ch...@christopherschultz.net wrote: I have found that these exceptions can occur even when there is no leak. Specifically, if your SQL query takes a long time to run (that is, more than the ababdonedTimeout), another request to the connection pool complains about the connection and calls it abandoned. I think your right. Timeout I mentioned 30sec deafault is 300sec. This is my context.xml ?xml version=1.0 encoding=UTF-8? Context path= path is not allowed in context.xml: remove it. validationQuery=SELECT * from dual SELECT *? Wow. How about SELECT 1 FROM dual? testOnBorrow=true removeAbandoned=true removeAbandonedTimeout=30 That's a 30-second abandoned timeout. username=scott password=*** tiger, right? Technically speaking, the connection hasn't been leaked, but the connection pool can't really guess the reason why the connection hasn't been returned. Can you time your queries to see how long they take? Could you post your Resource configuration for your DataSource? For some queries it took more than 30 seconds, from getting data from ip_to_geo table, which has 3 million rows in it. That could be your problem: you should probably increase your removeAbandonedTimeout value to something more appropriate for your application. You might also want a dba to check out your queries and your database structure. 3 million rows isn't that much, even for Oracle :) Another note: I notice that you are using a DataSource object that survives for the life of the DAO object, and is even created by the object in its constructor. Every DAO has only one instance for the entire life of application. Is this correct approach. So Every thread accessing the same datasource object to get connection. It was just a recommendation which gives you flexibility: your webapp (or the container, etc.) has the freedom to discard and completely re-build the DataSource for your webapp if you always go to the JNDI context to get the DataSource. Otherwise, you will force a webapp restart just to get a new DataSource. -chris - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
On 10/11/2010 08:29, sasidhar prabhakar wrote: Sorry for that. I changed it 300 seconds. OK What else did you change? [hint hint] p 0x62590808.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Re: DBCP abandoned trace - unable to understand the leak
On 10/11/2010 09:41, sasidhar prabhakar wrote: private static DataSource dataSource; Getting the DataSource shouldn't be an expensive operation, so 'optimising' by retaining a static reference to it doesn't make much sense. Try just getting a fresh DataSource every time - your DB queries are taking far longer to process than any theoretical performance improvement gained here. p 0x62590808.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Re: [OT] Re: DBCP abandoned trace - unable to understand the leak
On 04/11/2010 11:09, Peter Crowther wrote: On 4 November 2010 10:54, Mark Thomas ma...@apache.org wrote: On 04/11/2010 05:01, sasidhar prabhakar wrote: I have one doubt. You have a question not a doubt I see this on many forums, and have come to realise it's associated with speakers of at least one of the widely-used languages in India. I've just come to accept that doubt is the most obvious English translation of the concept - though I agree with you that question is more understandable to most English speakers. To sasidhar prabhakar: if you don't mind me asking, what's your native language and what's the word or phrase that you're translating as doubt? When this comes up in forums, I'd like to be able to tell the poster that question is probably a better English translation than doubt, and I would be able to do that more easily if I knew the original word or phrase that you're translating. I like it when my questions go unanswered too. That's why I lurk on mailing lists, looking for an opportunity to throw a question out there that'll sit waiting for an answer for weeks, or ideally indefinitely. ;) p 0x62590808.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Re: DBCP abandoned trace - unable to understand the leak
On 04/11/2010 15:41, Mikolaj Rydzewski wrote:
On Thu, 4 Nov 2010 10:37:25 -0500, Propes, Barry L
barry.l.pro...@citi.com wrote:
Not sure if it matters or not, but in your SponserSummaryDAO
method, it appears you establish the rs as null, but don't ever close
it? You might specifically try that.
And is it necessary to reassign all those variables (connection, rs,
pstmt) to null again in those catch blocks?
One more reason to use well designed utilities like commons-dbutils or
jdbc-template.
Or at least to use a static method to close the db objects stop
putting the same boiler plate in all of the finally blocks.
public class DB {
public static void close(ResultSet obj) {
if (obj == null)
return;
try {
obj.close();
}
catch (SQLException e) {
// catch or log
e.printStackTrace();
}
}
public static void close(Statement obj) {
if (obj == null)
return;
try {
obj.close();
}
catch (SQLException e) {
// catch or log
e.printStackTrace();
}
}
public static void close(Connection obj) {
if (obj == null)
return;
try {
obj.close();
}
catch (SQLException e) {
// catch or log
e.printStackTrace();
}
}
}
ResultSet rs = null;
PreparedStatement ps = null;
Connection cn = null;
try {
// ... do DB stuff
}
catch (SQLException e) {
// ... log or something
}
finally {
DB.close(rs);
DB.close(ps);
DB.close(cn);
}
p
0x62590808.asc
Description: application/pgp-keys
signature.asc
Description: OpenPGP digital signature
Re: DBCP abandoned trace - unable to understand the leak
On 10/11/2010 09:02, Pid wrote: On 04/11/2010 12:04, sasidhar prabhakar wrote: dataSource = ConnectionUtil.getDataSource(); } Is the class you posted the only DAO? Could the leak be from another class? Can you post ConnectionUtil.java? Given the SQL seen so far and that some queries take longer than 30s to complete, my money is on the the app trying to process more long running queries in parallel then the pool has connections available. With a low time-out (30s), the pool was abandoning the connections. With a long time-out (300s), the pool was becoming exhausted. If this analysis is correct, the fix is to address the root cause of the long running queries. Unless you are lucky and there is one poorly performing query, the chances are the application and/or database have architectural issues that will require significant work to put right. Web applications should not routinely be running queries as part of request processing that take in excess of a second or so to run. Mark - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
On 04/11/2010 07:50, sasidhar prabhakar wrote:
We are using struts and following DAO pattern.
This is the code
public String getCountryName(long ipSum){
String name = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(select country_name from
ip_to_geo where ? between ip_from and ip_to);
That query looks like a candidate for being slow to me.
pstmt.setString(1, +ipSum);
Minor: but pstmt.setString(1, String.valueOf(ipSum)) would be better.
Slightly less minor, is the question, why are you converting a long to a
string and then attempting to conduct a range operation on it?
There's another method: pstmt.setLong(1, ipSum) which might work, unless
your DB tables are strangely constructed.
p
rs = pstmt.executeQuery();
if( rs.next() ){
name = rs.getString(1);
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{if( rs!=null)rs.close();}catch(SQLException
ex){ex.printStackTrace();}
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch
(SQLException ex) {ex.printStackTrace();}
connection = null;
pstmt = null;
rs = null;
}
return name;
}
0x62590808.asc
Description: application/pgp-keys
signature.asc
Description: OpenPGP digital signature
Re: DBCP abandoned trace - unable to understand the leak
On 04/11/2010 12:04, sasidhar prabhakar wrote: dataSource = ConnectionUtil.getDataSource(); } Is the class you posted the only DAO? Could the leak be from another class? Can you post ConnectionUtil.java? p 0x62590808.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Re: DBCP abandoned trace - unable to understand the leak
On Wed, Nov 10, 2010 at 3:02 AM, Pid p...@pidster.com wrote:
On 04/11/2010 12:04, sasidhar prabhakar wrote:
dataSource = ConnectionUtil.getDataSource();
}
Is the class you posted the only DAO? Could the leak be from another
class?
Some other DAOs are there. Which takes more than removeAbandonedTimeout.
Except timeout I didn't find any unclosed connections, statements and
results.
Can you post ConnectionUtil.java?
This is DAO class
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class ConnectionUtil {
private static Log log = LogFactory.getLog(ConnectionUtil.class);
private static String dataSourceJNDIName = java:comp/env/jdbc/ds;
private static DataSource dataSource;
public static DataSource getDataSource() {
if(dataSource == null) {
try {
log.info(In the ConnectionUtil:getDataSource():Befor calling lookup on
context);
Context ctx = new InitialContext();
dataSource = (DataSource)ctx.lookup(dataSourceJNDIName);
ctx.close();
} catch(Exception e) {
log.error(Can not create DataSource +e.getMessage());
throw new IllegalStateException(e.getMessage());
}
}
return dataSource;
}
public static void setDataSource(DataSource ds) {
dataSource = ds;
}
public static Connection getConnection() throws SQLException {
return getDataSource().getConnection();
}
public static void beginTransaction(Connection conn) throws SQLException {
conn.setAutoCommit(false);
}
public static void endTransaction(Connection conn) throws SQLException {
conn.setAutoCommit(true);
}
public static void commit(Connection conn) throws SQLException {
conn.commit();
}
public static void rollback(Connection conn) throws SQLException {
conn.rollback();
}
public static void close(Connection conn) {
if (conn != null) {
try {
if( !conn.isClosed() )
conn.close();
} catch(SQLException e) {
log.error(SQL Exception caught while closing Connection :+
e.getMessage());
}
}
}
public static void close(Statement stm) {
if (stm != null) {
try {
stm.close();
} catch(SQLException e) {
log.error(SQL Exception caught while closing Statement :+ e.getMessage());
}
}
}
public static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch(SQLException e) {
log.error(SQL Exception caught while closing ResultSet :+ e.getMessage());
}
}
}
}
p
Re: DBCP abandoned trace - unable to understand the leak
When I get this problem, I tried the query in DB manually by this query *select count(*) from v$process;* * * The count some times very less, like if total connections are 200 it shows * * some times 60,40,162 like this. On Wed, Nov 10, 2010 at 3:20 AM, Mark Thomas ma...@apache.org wrote: On 10/11/2010 09:02, Pid wrote: On 04/11/2010 12:04, sasidhar prabhakar wrote: dataSource = ConnectionUtil.getDataSource(); } Is the class you posted the only DAO? Could the leak be from another class? Can you post ConnectionUtil.java? Given the SQL seen so far and that some queries take longer than 30s to complete, my money is on the the app trying to process more long running queries in parallel then the pool has connections available. With a low time-out (30s), the pool was abandoning the connections. With a long time-out (300s), the pool was becoming exhausted. If this analysis is correct, the fix is to address the root cause of the long running queries. Unless you are lucky and there is one poorly performing query, the chances are the application and/or database have architectural issues that will require significant work to put right. Web applications should not routinely be running queries as part of request processing that take in excess of a second or so to run. Mark - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sasidhar, On 11/10/2010 3:29 AM, sasidhar prabhakar wrote: Sorry for that. I changed it 300 seconds. Perhaps you could post your entire configuration. It stops us from asking too many questions, and generally gets right to the problem. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzaz00ACgkQ9CaO5/Lv0PCvBgCeP6H0UewerbUbLyLs/f7Ud3Tk zJ4An2qjPJl+8KtKF7e0Q84b9MYjnBTR =dNVW -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Pid,
On 11/10/2010 3:51 AM, Pid wrote:
finally {
DB.close(rs);
DB.close(ps);
DB.close(cn);
}
I've gone further in our code:
DB.close(cn, ps, rs);
- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAkzbBuQACgkQ9CaO5/Lv0PAyfQCfa1L4FPpWHDZ6Yido3v2HtIo0
OKcAn2MG1rL+h7rcNSzmuzhEF2h3czON
=ShLV
-END PGP SIGNATURE-
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sasidhar, On 11/8/2010 12:31 AM, sasidhar prabhakar wrote: On Thu, Nov 4, 2010 at 9:10 PM, Christopher Schultz ch...@christopherschultz.net wrote: I have found that these exceptions can occur even when there is no leak. Specifically, if your SQL query takes a long time to run (that is, more than the ababdonedTimeout), another request to the connection pool complains about the connection and calls it abandoned. I think your right. Timeout I mentioned 30sec deafault is 300sec. This is my context.xml ?xml version=1.0 encoding=UTF-8? Context path= path is not allowed in context.xml: remove it. validationQuery=SELECT * from dual SELECT *? Wow. How about SELECT 1 FROM dual? testOnBorrow=true removeAbandoned=true removeAbandonedTimeout=30 That's a 30-second abandoned timeout. username=scott password=*** tiger, right? Technically speaking, the connection hasn't been leaked, but the connection pool can't really guess the reason why the connection hasn't been returned. Can you time your queries to see how long they take? Could you post your Resource configuration for your DataSource? For some queries it took more than 30 seconds, from getting data from ip_to_geo table, which has 3 million rows in it. That could be your problem: you should probably increase your removeAbandonedTimeout value to something more appropriate for your application. You might also want a dba to check out your queries and your database structure. 3 million rows isn't that much, even for Oracle :) Another note: I notice that you are using a DataSource object that survives for the life of the DAO object, and is even created by the object in its constructor. Every DAO has only one instance for the entire life of application. Is this correct approach. So Every thread accessing the same datasource object to get connection. It was just a recommendation which gives you flexibility: your webapp (or the container, etc.) has the freedom to discard and completely re-build the DataSource for your webapp if you always go to the JNDI context to get the DataSource. Otherwise, you will force a webapp restart just to get a new DataSource. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzZ180ACgkQ9CaO5/Lv0PDicACfZ/rv+FN8cT8JATK2ZlGYgWUW CPoAn2/j0NO6af4RuL9t7j4yH9wXP+bW =l181 -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
After changing time out value now I am getting this problem org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object On Tue, Nov 9, 2010 at 5:22 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sasidhar, On 11/8/2010 12:31 AM, sasidhar prabhakar wrote: On Thu, Nov 4, 2010 at 9:10 PM, Christopher Schultz ch...@christopherschultz.net wrote: I have found that these exceptions can occur even when there is no leak. Specifically, if your SQL query takes a long time to run (that is, more than the ababdonedTimeout), another request to the connection pool complains about the connection and calls it abandoned. I think your right. Timeout I mentioned 30sec deafault is 300sec. This is my context.xml ?xml version=1.0 encoding=UTF-8? Context path= path is not allowed in context.xml: remove it. validationQuery=SELECT * from dual SELECT *? Wow. How about SELECT 1 FROM dual? testOnBorrow=true removeAbandoned=true removeAbandonedTimeout=30 That's a 30-second abandoned timeout. username=scott password=*** tiger, right? Technically speaking, the connection hasn't been leaked, but the connection pool can't really guess the reason why the connection hasn't been returned. Can you time your queries to see how long they take? Could you post your Resource configuration for your DataSource? For some queries it took more than 30 seconds, from getting data from ip_to_geo table, which has 3 million rows in it. That could be your problem: you should probably increase your removeAbandonedTimeout value to something more appropriate for your application. You might also want a dba to check out your queries and your database structure. 3 million rows isn't that much, even for Oracle :) Another note: I notice that you are using a DataSource object that survives for the life of the DAO object, and is even created by the object in its constructor. Every DAO has only one instance for the entire life of application. Is this correct approach. So Every thread accessing the same datasource object to get connection. It was just a recommendation which gives you flexibility: your webapp (or the container, etc.) has the freedom to discard and completely re-build the DataSource for your webapp if you always go to the JNDI context to get the DataSource. Otherwise, you will force a webapp restart just to get a new DataSource. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzZ180ACgkQ9CaO5/Lv0PDicACfZ/rv+FN8cT8JATK2ZlGYgWUW CPoAn2/j0NO6af4RuL9t7j4yH9wXP+bW =l181 -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
On Thu, Nov 4, 2010 at 9:10 PM, Christopher Schultz ch...@christopherschultz.net wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sasidhar, On 11/4/2010 8:34 AM, sasidhar prabhakar wrote: The class is fine but in log it is showing this one. Here everything closed fine. Then why it is showing like this DBCP object created 2010-11-04 11:07:59 by the following code was never closed: I have found that these exceptions can occur even when there is no leak. Specifically, if your SQL query takes a long time to run (that is, more than the ababdonedTimeout), another request to the connection pool complains about the connection and calls it abandoned. I think your right. Timeout I mentioned 30sec deafault is 300sec. This is my context.xml ?xml version=1.0 encoding=UTF-8? Context path= Resource accessToUnderlyingConnectionAllowed=true auth=Container driverClassName=oracle.jdbc.OracleDriver maxActive=200 maxIdle=10 maxWait=8000 validationQuery=SELECT * from dual testOnBorrow=true removeAbandoned=true removeAbandonedTimeout=30 logAbandoned=true name=jdbc/ds password=*** type=javax.sql.DataSource url=jdbc:oracle:thin:@localhost.localdomain:1521:amulyam username=scott/ Resource auth=Container name=mail/Session type=javax.mail.Session mail.smtp.host=localhost/ /Context Technically speaking, the connection hasn't been leaked, but the connection pool can't really guess the reason why the connection hasn't been returned. Can you time your queries to see how long they take? Could you post your Resource configuration for your DataSource? For some queries it took more than 30 seconds, from getting data from ip_to_geo table, which has 3 million rows in it. Another note: I notice that you are using a DataSource object that survives for the life of the DAO object, and is even created by the object in its constructor. Every DAO has only one instance for the entire life of application. Is this correct approach. So Every thread accessing the same datasource object to get connection.
Re: DBCP abandoned trace - unable to understand the leak
On Thu, 4 Nov 2010 11:08:07 +0530, sasidhar prabhakar sasidhar1...@gmail.com wrote: I didn't understand below, in DAO class everything fine. Connection,PreparedStatement,ResultSet are all declared method local, and closed properly. Within finally clause? Please guide me to solve the problem. Please show us your code. For small projects I found commons-dbutil very convenient. For larger ones, spring's JdbcTemplate is my choice. -- Mikolaj Rydzewski m...@ceti.pl - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
We are using struts and following DAO pattern.
This is the code
public String getCountryName(long ipSum){
String name = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(select country_name from
ip_to_geo where ? between ip_from and ip_to);
pstmt.setString(1, +ipSum);
rs = pstmt.executeQuery();
if( rs.next() ){
name = rs.getString(1);
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{if( rs!=null)rs.close();}catch(SQLException
ex){ex.printStackTrace();}
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch
(SQLException ex) {ex.printStackTrace();}
connection = null;
pstmt = null;
rs = null;
}
return name;
}
Re: DBCP abandoned trace - unable to understand the leak
On Thu, 4 Nov 2010 13:20:48 +0530, sasidhar prabhakar sasidhar1...@gmail.com wrote: We are using struts and following DAO pattern. Looks fine. Does the problem occur everytime? -- Mikolaj Rydzewski m...@ceti.pl - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
Yes it is. I have one doubt. Is abandoned trace really shows the code where the connection established and did not close it. Is remove abandoned, will close the connection after time out and places it back to pool. Is it really closes the connection? for example I configured pool with 200 connections. If 50 connections are leaked, now remove abandoned will places these 50 connections back to pool or pool left with 150 connections. please clarify my doubts.
Re: DBCP abandoned trace - unable to understand the leak
On 04/11/2010 05:01, sasidhar prabhakar wrote: Yes it is. I have one doubt. You have a question not a doubt and you have more than one of them. Is abandoned trace really shows the code where the connection established and did not close it. Yes. Is remove abandoned, will close the connection after time out Yes. and places it back to pool. No. A brand new connection is created to replace the abandoned one. Is it really closes the connection? Yes. Really. for example I configured pool with 200 connections. If 50 connections are leaked, now remove abandoned will places these 50 connections back to pool or pool left with 150 connections. Neither. You will have a pool with 150 of the original connections plus 50 new connections to replace the ones that were abandoned. Mark - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
[OT] Re: DBCP abandoned trace - unable to understand the leak
On 4 November 2010 10:54, Mark Thomas ma...@apache.org wrote: On 04/11/2010 05:01, sasidhar prabhakar wrote: I have one doubt. You have a question not a doubt I see this on many forums, and have come to realise it's associated with speakers of at least one of the widely-used languages in India. I've just come to accept that doubt is the most obvious English translation of the concept - though I agree with you that question is more understandable to most English speakers. To sasidhar prabhakar: if you don't mind me asking, what's your native language and what's the word or phrase that you're translating as doubt? When this comes up in forums, I'd like to be able to tell the poster that question is probably a better English translation than doubt, and I would be able to do that more easily if I knew the original word or phrase that you're translating. - Peter
Re: DBCP abandoned trace - unable to understand the leak
On Thu, Nov 4, 2010 at 4:24 PM, Mark Thomas ma...@apache.org wrote: On 04/11/2010 05:01, sasidhar prabhakar wrote: Is abandoned trace really shows the code where the connection established and did not close it. Yes. The code I posted above is clean and properly closed all of resources. Is there any problem with the code shown above. anybody help me to solve this problem.
RE: DBCP abandoned trace - unable to understand the leak
The code you posted looks fine but without the complete class it is hard to say 100% your class is fine. -Original Message- From: sasidhar prabhakar [mailto:sasidhar1...@gmail.com] Sent: 04 November 2010 11:36 To: Tomcat Users List Subject: Re: DBCP abandoned trace - unable to understand the leak On Thu, Nov 4, 2010 at 4:24 PM, Mark Thomas ma...@apache.org wrote: On 04/11/2010 05:01, sasidhar prabhakar wrote: Is abandoned trace really shows the code where the connection established and did not close it. Yes. The code I posted above is clean and properly closed all of resources. Is there any problem with the code shown above. anybody help me to solve this problem. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
The complete class has only two methods. And class is
import connection.ConnectionUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
*
* @author oracle
*/
public class SponserSummaryDAO {
private Log log = LogFactory.getLog(SponserSummaryDAO.class);
private static final String updateClicksQuery = update sponser_summary set
sp_sum_clicks = sp_sum_clicks + 1 where sp_sum_sid = ? and sp_sum_date =
trunc(sysdate);
private static final String getCityIdQuery = select c_id from cities where
lower(c_name) like lower(?);
private static final String updateImpByCityQuery = update
sponser_summ_by_cities set sp_sum_c_imp = sp_sum_c_imp + 1 where
sp_sum_c_sid = ? and lower(sp_sum_c_city) = ?;
private DataSource dataSource;
public SponserSummaryDAO(){
log.info(^Cretion of SponserSummaryDAO :
+Calendar.getInstance().getTime().toString());
dataSource = ConnectionUtil.getDataSource();
}
public void updateClicks(int sid){
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(updateClicksQuery);
pstmt.setInt(1, sid );
int updated = pstmt.executeUpdate();
log.info( sponser clicks updated val : +updated);
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}finally{
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
}
}
public void updateImpByCity(int sid, String city){
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(updateImpByCityQuery);
pstmt.setInt(1, sid );
pstmt.setString(2, city.toLowerCase());
int updated = pstmt.executeUpdate();
log.info( sponser imp by city updated val : +updated);
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}finally{
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
}
}
public String getCityId(String city){
String cityID = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(getCityIdQuery);
pstmt.setString(1, %+city+%);
rs = pstmt.executeQuery();
if( rs.next() ){
cityID = rs.getString(1);
}else{
cityID = -1;
}
log.info( city ID : +cityID);
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}finally{
try{if( rs!=null)rs.close();}catch(SQLException ex){ex.printStackTrace();}
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
rs = null;
}
return cityID;
}
public String getCountryName(long ipSum){
String name = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(select country_name from ip_to_geo
where ? between ip_from and ip_to);
pstmt.setString(1, +ipSum);
rs = pstmt.executeQuery();
if( rs.next() ){
name = rs.getString(1);
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{if( rs!=null)rs.close();}catch(SQLException ex){ex.printStackTrace();}
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
rs = null;
}
return name;
}
protected void finalize() throws Throwable {
log.info(^Finalize of SponserSummaryDAO :
+Calendar.getInstance().getTime().toString());
}
}
RE: DBCP abandoned trace - unable to understand the leak
The full class looks ok to me. Your issues must be elsewhere.
-Original Message-
From: sasidhar prabhakar [mailto:sasidhar1...@gmail.com]
Sent: 04 November 2010 12:05
To: Tomcat Users List
Subject: Re: DBCP abandoned trace - unable to understand the leak
The complete class has only two methods. And class is
import connection.ConnectionUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
*
* @author oracle
*/
public class SponserSummaryDAO {
private Log log = LogFactory.getLog(SponserSummaryDAO.class);
private static final String updateClicksQuery = update
sponser_summary set
sp_sum_clicks = sp_sum_clicks + 1 where sp_sum_sid = ? and sp_sum_date
=
trunc(sysdate);
private static final String getCityIdQuery = select c_id from cities
where
lower(c_name) like lower(?);
private static final String updateImpByCityQuery = update
sponser_summ_by_cities set sp_sum_c_imp = sp_sum_c_imp + 1 where
sp_sum_c_sid = ? and lower(sp_sum_c_city) = ?;
private DataSource dataSource;
public SponserSummaryDAO(){
log.info(^Cretion of SponserSummaryDAO :
+Calendar.getInstance().getTime().toString());
dataSource = ConnectionUtil.getDataSource();
}
public void updateClicks(int sid){
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(updateClicksQuery);
pstmt.setInt(1, sid );
int updated = pstmt.executeUpdate();
log.info( sponser clicks updated val : +updated);
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}finally{
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException
ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
}
}
public void updateImpByCity(int sid, String city){
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(updateImpByCityQuery);
pstmt.setInt(1, sid );
pstmt.setString(2, city.toLowerCase());
int updated = pstmt.executeUpdate();
log.info( sponser imp by city updated val : +updated);
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}finally{
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException
ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
}
}
public String getCityId(String city){
String cityID = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(getCityIdQuery);
pstmt.setString(1, %+city+%);
rs = pstmt.executeQuery();
if( rs.next() ){
cityID = rs.getString(1);
}else{
cityID = -1;
}
log.info( city ID : +cityID);
}catch(Exception ex){
ex.printStackTrace();
log.error(ex.getMessage());
}finally{
try{if( rs!=null)rs.close();}catch(SQLException
ex){ex.printStackTrace();}
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException
ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
rs = null;
}
return cityID;
}
public String getCountryName(long ipSum){
String name = null;
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(select country_name from
ip_to_geo
where ? between ip_from and ip_to);
pstmt.setString(1, +ipSum);
rs = pstmt.executeQuery();
if( rs.next() ){
name = rs.getString(1);
}
}catch(Exception ex){
ex.printStackTrace();
}finally{
try{if( rs!=null)rs.close();}catch(SQLException
ex){ex.printStackTrace();}
try {if( pstmt != null)pstmt.close();} catch (SQLException ex)
{ex.printStackTrace();}
try {if( connection != null)connection.close();} catch (SQLException
ex)
{ex.printStackTrace();}
connection = null;
pstmt = null;
rs = null;
}
return name;
}
protected void finalize() throws Throwable {
log.info(^Finalize of SponserSummaryDAO :
+Calendar.getInstance().getTime().toString());
}
}
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
The class is fine but in log it is showing this one. Here everything closed fine. Then why it is showing like this DBCP object created 2010-11-04 11:07:59 by the following code was never closed: java.lang.Exception at org.apache.tomcat.dbcp.dbcp.AbandonedTrace.setStackTrace(AbandonedTrace.java:160) at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:86) at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96) at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880) *at SponserSummaryDAO.getCountryName(SponserSummaryDAO.java:304) at SponserSummaryBO.getCountryName(SponserSummaryBO.java:61) at SignUpAction.execute(SignUpAction.java:52)* at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913) at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449) at javax.servlet.http.HttpServlet.service(HttpServlet.java:617) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at RedirectFilter.doFilter(RedirectFilter.java:56) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454) at java.lang.Thread.run(Thread.java:595) If anything else, what are the possible connection leaks may occur in java.
RE: DBCP abandoned trace - unable to understand the leak
Are you sure it is running against the code you think it is running against. Try changing something that is output to the log files and make sure you see this change before the abandoned stack trace is thrown. -Original Message- From: sasidhar prabhakar [mailto:sasidhar1...@gmail.com] Sent: 04 November 2010 12:35 To: Tomcat Users List Subject: Re: DBCP abandoned trace - unable to understand the leak The class is fine but in log it is showing this one. Here everything closed fine. Then why it is showing like this DBCP object created 2010-11-04 11:07:59 by the following code was never closed: java.lang.Exception at org.apache.tomcat.dbcp.dbcp.AbandonedTrace.setStackTrace(AbandonedTrace. java:1 60) at org.apache.tomcat.dbcp.dbcp.AbandonedObjectPool.borrowObject(AbandonedOb jectPo ol.java:86) at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataS ource. java:96) at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSourc e.java :880) *at SponserSummaryDAO.getCountryName(SponserSummaryDAO.java:304) at SponserSummaryBO.getCountryName(SponserSummaryBO.java:61) at SignUpAction.execute(SignUpAction.java:52)* at org.apache.struts.action.RequestProcessor.processActionPerform(RequestPr ocesso r.java:425) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java: 228) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913) at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:449) at javax.servlet.http.HttpServlet.service(HttpServlet.java:617) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applica tionFi lterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt erChai n.java:206) at RedirectFilter.doFilter(RedirectFilter.java:56) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applica tionFi lterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilt erChai n.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValv e.java :233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValv e.java :191) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authenticator Base.j ava:433) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java :128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java :102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve. java:1 09) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:2 93) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:84 9) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process( Http11 Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454) at java.lang.Thread.run(Thread.java:595) If anything else, what are the possible connection leaks may occur in java. - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: DBCP abandoned trace - unable to understand the leak
Not sure if it matters or not, but in your SponserSummaryDAO method, it
appears you establish the rs as null, but don't ever close it? You might
specifically try that.
And is it necessary to reassign all those variables (connection, rs, pstmt) to
null again in those catch blocks?
-Original Message-
From: sasidhar prabhakar [mailto:sasidhar1...@gmail.com]
Sent: Thursday, November 04, 2010 7:05 AM
To: Tomcat Users List
Subject: Re: DBCP abandoned trace - unable to understand the leak
*
* @author oracle
*/
public class SponserSummaryDAO {
...
public SponserSummaryDAO(){
log.info(^Cretion of SponserSummaryDAO :
+Calendar.getInstance().getTime().toString());
dataSource = ConnectionUtil.getDataSource(); }
public void updateClicks(int sid){
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
connection = dataSource.getConnection();
pstmt = connection.prepareStatement(updateClicksQuery);
pstmt.setInt(1, sid );
int updated = pstmt.executeUpdate();
log.info( sponser clicks updated val : +updated); }catch(Exception ex){
ex.printStackTrace(); log.error(ex.getMessage()); }finally{ try {if( pstmt !=
null)pstmt.close();} catch (SQLException ex) {ex.printStackTrace();} try {if(
connection != null)connection.close();} catch (SQLException ex)
{ex.printStackTrace();} connection = null; pstmt = null; } }
}
-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org
Re: DBCP abandoned trace - unable to understand the leak
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Sasidhar, On 11/4/2010 8:34 AM, sasidhar prabhakar wrote: The class is fine but in log it is showing this one. Here everything closed fine. Then why it is showing like this DBCP object created 2010-11-04 11:07:59 by the following code was never closed: I have found that these exceptions can occur even when there is no leak. Specifically, if your SQL query takes a long time to run (that is, more than the ababdonedTimeout), another request to the connection pool complains about the connection and calls it abandoned. Technically speaking, the connection hasn't been leaked, but the connection pool can't really guess the reason why the connection hasn't been returned. Can you time your queries to see how long they take? Could you post your Resource configuration for your DataSource? Another note: I notice that you are using a DataSource object that survives for the life of the DAO object, and is even created by the object in its constructor. I usually recommend that your code obtain the DataSource from the JNDI context each time it needs it to improve flexibility. You could also use dependency injection if that it your preferred mechanism for obtaining (or being provided with) a DataSource. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzS09gACgkQ9CaO5/Lv0PCxQACdFcCek6dGip51q06YtaY6tJSF PT8An32Xxfy+d63TkjEs5tPr8a+KT0BZ =4smp -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: DBCP abandoned trace - unable to understand the leak
On Thu, 4 Nov 2010 10:37:25 -0500, Propes, Barry L barry.l.pro...@citi.com wrote: Not sure if it matters or not, but in your SponserSummaryDAO method, it appears you establish the rs as null, but don't ever close it? You might specifically try that. And is it necessary to reassign all those variables (connection, rs, pstmt) to null again in those catch blocks? One more reason to use well designed utilities like commons-dbutils or jdbc-template. -- Mikolaj Rydzewski m...@ceti.pl - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
