Re: DBCP exception after Tomcat 10.1.13 -> 10.1.14 upgrade

[Konstantin Kolinko]

Hi!

Thank you for the report.
This issue is known and has already been fixed. See
https://bz.apache.org/bugzilla/show_bug.cgi?id=67664

Best regards,
Konstantin Kolinko

вт, 10 окт. 2023 г. в 23:42, Michael Hayes :

>
> I have just upgraded a working Tomcat 10.1.13 installation to Tomcat 10.1.14,
> on Rocky Linux 9.2, PostgreSQL 11.20, JDBC driver 42.6.0, Java 17.0.8.
>
> During startup I get this exception:
>
> java.sql.SQLException
> at 
> org.apache.tomcat.jdbc.pool.ConnectionPool.setupConnection(ConnectionPool.java:351)
> at 
> org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:200)
> at 
> org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:131)
> at fms.lib.Jdbc.open(Jdbc.java:139)
> at fms.lib.Startup.contextInitialized(Startup.java:38)
> at 
> org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4422)
> at 
> org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:4860)
> ... lots more
> Caused by: java.lang.IllegalArgumentException: 
> org.apache.tomcat.jdbc.pool.PooledConnection is not an interface
> at 
> java.base/java.lang.reflect.Proxy$ProxyBuilder.validateProxyInterfaces(Proxy.java:706)
> at 
> java.base/java.lang.reflect.Proxy$ProxyBuilder.(Proxy.java:648)
> at 
> java.base/java.lang.reflect.Proxy.lambda$getProxyConstructor$1(Proxy.java:440)
> at 
> java.base/jdk.internal.loader.AbstractClassLoaderValue$Memoizer.get(AbstractClassLoaderValue.java:329)
> at 
> java.base/jdk.internal.loader.AbstractClassLoaderValue.computeIfAbsent(AbstractClassLoaderValue.java:205)
> at 
> java.base/java.lang.reflect.Proxy.getProxyConstructor(Proxy.java:438)
> at java.base/java.lang.reflect.Proxy.getProxyClass(Proxy.java:398)
> at 
> org.apache.tomcat.jdbc.pool.ConnectionPool.getProxyConstructor(ConnectionPool.java:377)
> at 
> org.apache.tomcat.jdbc.pool.ConnectionPool.setupConnection(ConnectionPool.java:339)
> ... 45 more
>
> Any ideas?
>
> Thanks...
>
> --Mike
>
> -
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP params different for the Tomcat JDBC (not DBCP) implementation in Tomcat 8?

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Bradley,

On 10/7/15 4:04 PM, Bradley Wagner wrote:
>> The general recommendation is to use the default pool
>> (commons-dbcp).
> 
> Great. Thanks!
> 
>> Unless you have narrowed a performance problem to the pool
>> itself,
> there's no reason to use one over the other. I suspect that there
> are only a few companies in the world where the connection pool 
> implementation's overhead makes a noticeable difference in the 
> performance of their web applications.
>> Not doing a million transactions a minute? Don't worry about a
>> thing.
> 
> Nope. Thanks!
> 
>> Of course. No component is going to guess that the number you put
>> in
> configuration (5000) should be applied to some other setting.
> 
> Right, understood. In a previous message int his thread, Mark had 
> speculated that perhaps the factory was mapping the param to the
> right name under the covers. This logging confirmed that the param
> was just being ignored.

Right: for the transition for commons-dbcp1 -> commons-dbcp2, we have
mapped certain common fields to help easy the transition process.
Anything that issues a complaint in the log file is surely being ignored
.

>> I think this has more to do with the tolerance of the Spring
> configuration component relative to the tolerance of the Tomcat 
> configuration component: both are creating a new DataSource and 
> calling setFoo() on them, but only Spring fails when a setter is
> not available, while Tomcat just gives a warning and continues on
> its way.
> 
> Yep, that makes sense.  Was looking for a warning in the Tomcat
> logging but didn't see that either.

I think that's another source of confusion: Tomcat has re-mapped those
fields, but it doesn't magically do it for other components like Spring.

Hope that helps,
- -chris
-BEGIN PGP SIGNATURE-
Comment: GPGTools - http://gpgtools.org

iQIcBAEBCAAGBQJWFqPcAAoJEBzwKT+lPKRYpP4QAMZvB7WLjxoONMYyMQ0tq/no
BuBfq5uk+xd09XIdCdMW66rvC63s9fPqwud6hnBB++yh3HQaFy+iqLHsbO2sLYkp
obiuEw4vVg2QrlHKJW1kvO8w4HoT1rytF5iR99ejxErEWbQRBm+Vkq7TmRE71nXu
ANYhEC08+DdnRaLhFPI7pSTAIOd2JQSeWqdytl4hNlBaiKqs3H1W69Y8qSSatW/P
v0ZNYArz5l1C3o4I07bGqEbWl1NGpmdciYldV3ZdxAXrW13MshLZu9Jw0BOjxTUW
Nt5PaunBbM554YD8cfs9kid+fQ380ZwuKpjAPP/2XHmuoVZt9Wm+kSXJO3b+ASgA
uzB3jYSTYnqURKZNXwwI8xoJaMxMrLd3WvLI5DB9iC/lO9aWwvuFHVNqHDOGXKiz
xemu54LZY2XL6KLgR0I5h4cH/2f2c2eK2ybce7ndNtpuzSrc6VUg/oaW4sJWOceS
v3qchS2kT28DaMWbgV9p5OJKby9yKdZFdLs8glD3CL7+yrFfD0zHnrB1eQKDwrhL
RgHKHTwvS8YlvQ3iroWqwlw9kcoZBv3anu4izvLW/USOiBQ27/y65WGaYmsaG+Xl
/AcwMP/6vvWCqB35atnWu6MrdLM9gI9YZeTfExUlHr9HHYGMH3oRhfr+bAhY/eFl
7exTSYPw24xwUtsMqCr4
=LSZ3
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP params different for the Tomcat JDBC (not DBCP) implementation in Tomcat 8?

[Bradley Wagner]

> The general recommendation is to use the default pool (commons-dbcp).

Great. Thanks!

> Unless you have narrowed a performance problem to the pool itself,
there's no reason to use one over the other. I suspect that there are
only a few companies in the world where the connection pool
implementation's overhead makes a noticeable difference in the
performance of their web applications.
> Not doing a million transactions a minute? Don't worry about a thing.

Nope. Thanks!

> Of course. No component is going to guess that the number you put in
configuration (5000) should be applied to some other setting.

Right, understood. In a previous message int his thread, Mark had
speculated that perhaps the factory was mapping the param to the right name
under the covers. This logging confirmed that the param was just being
ignored.

>  I think this has more to do with the tolerance of the Spring
configuration component relative to the tolerance of the Tomcat
configuration component: both are creating a new DataSource and
calling setFoo() on them, but only Spring fails when a setter is not
available, while Tomcat just gives a warning and continues on its way.

Yep, that makes sense.  Was looking for a warning in the Tomcat logging but
didn't see that either.

On Wed, Oct 7, 2015 at 3:51 PM Christopher Schultz <
ch...@christopherschultz.net> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA256
>
> Bradley,
>
> On 10/7/15 3:21 PM, Bradley Wagner wrote:
> > Ah, I see what you're saying. My apologies for not seeing that
> > sooner.
> >
> > That post was also very helpful in explaining why both exist.
> > Thank you!
>
> It's definitely confusing to those you don't already understand.
> "DBCP" usually just means "database connection pool", but in the
> Apache world, it usually specifically means commons-dbcp.
>
> > Is it your recommendation then to use DBCP 2 over Tomcat JDBC in
> > Tomcat 8? If so, I think it would be helpful to have a page on the
> > public Tomcat website about why both exist and which one is
> > recommended.
>
> The general recommendation is to use the default pool (commons-dbcp).
>
> Unless you have narrowed a performance problem to the pool itself,
> there's no reason to use one over the other. I suspect that there are
> only a few companies in the world where the connection pool
> implementation's overhead makes a noticeable difference in the
> performance of their web applications.
>
> Not doing a million transactions a minute? Don't worry about a thing.
>
> > Also, in my case, after some more digging, I found that Tomcat JDBC
> > was simply ignoring my badly named params. I had specified
> > maxWaitMillis="5000" in my  in context.xml. Yet on
> > initialization, I saw the following in my log:
> >
> > Using DataSource
> > [org.apache.tomcat.jdbc.pool.DataSource@41a91006{ConnectionPool[defaul
> tAutoCommit=null;
> >
> >
> defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=nul
> l;
> > driverClassName=com.mysql.jdbc.Driver; maxActive=100; maxIdle=10;
> > minIdle=10; initialSize=10; maxWait=3; testOnBorrow=true;
> > testOnReturn=false; timeBetweenEvictionRunsMillis=5000;
> > numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=6;
> > testWhileIdle=false; testOnConnect=false; password=;
> > url=jdbc:mysql://localhost:3306/?useUnicode=true&characterEn
> coding=UTF-8;
> >
> >
> username=root; validationQuery=SELECT 1; validationQueryTimeout=-1;
> > validatorClassName=null; validationInterval=3;
> > accessToUnderlyingConnectionAllowed=true; removeAbandoned=false;
> > removeAbandonedTimeout=300; logAbandoned=true;
> > connectionProperties=null; initSQL=null; jdbcInterceptors=null;
> > jmxEnabled=true;
> >
> > which shows maxWait=3.
>
> Of course. No component is going to guess that the number you put in
> configuration (5000) should be applied to some other setting.
>
> > So it looks Tomcat JDBC is forgiving with badly named params in
> > that it doesn't fail on startup. Whereas if I try to instantiate a
> > Tomcat JDBC DataSource directly, it fails when I try to set
> > properties that don't exist.
>
> I think this has more to do with the tolerance of the Spring
> configuration component relative to the tolerance of the Tomcat
> configuration component: both are creating a new DataSource and
> calling setFoo() on them, but only Spring fails when a setter is not
> available, while Tomcat just gives a warning and continues on its way.
>
> - -chris
> -BEGIN PGP SIGNATURE-
> Comment: GPGTools - http://gpgtools.org
>
> iQIcBAEBCAAGBQJWFXeZAAoJEBzwKT+lPKRY9RcP/iAu93Ib07WtvMru0ZCE8lFR
> ugWRHtXAnYs1AhwAFIKgGxOuQfnOSmY7ycHBVZSINfv+T9roSyFrgOq9/EMdM2Nj
> YyEsnxUtcGEyHFkDuwYDHhIE7u6hrXrXy4yNj/Ilsjlau8wjlah2yiDQgumES0m8
> E9N6QhLApXI+wvBPEJkBCWhxBfa5z50y9oXFVDrrnSZmynHiEzpBgx+bKaoJjeeh
> xUUg2ddLuJLONkWnvXnimkppjcdOfryIprtFeMjuLFr/DowyPXb3fC9lMGwJqmpc
> dSFNO8iMAfPVwX7LDyv1swGmMFG79HUb74GMlnFQhXH09f0CHVifSn/n9n5Oyf5M
> sOSvMCvdzb+c8rwuuJb0T6qDtZg+4BVDUSv7Mw6zHBU

Re: DBCP params different for the Tomcat JDBC (not DBCP) implementation in Tomcat 8?

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Bradley,

On 10/7/15 3:21 PM, Bradley Wagner wrote:
> Ah, I see what you're saying. My apologies for not seeing that
> sooner.
> 
> That post was also very helpful in explaining why both exist.
> Thank you!

It's definitely confusing to those you don't already understand.
"DBCP" usually just means "database connection pool", but in the
Apache world, it usually specifically means commons-dbcp.

> Is it your recommendation then to use DBCP 2 over Tomcat JDBC in
> Tomcat 8? If so, I think it would be helpful to have a page on the
> public Tomcat website about why both exist and which one is
> recommended.

The general recommendation is to use the default pool (commons-dbcp).

Unless you have narrowed a performance problem to the pool itself,
there's no reason to use one over the other. I suspect that there are
only a few companies in the world where the connection pool
implementation's overhead makes a noticeable difference in the
performance of their web applications.

Not doing a million transactions a minute? Don't worry about a thing.

> Also, in my case, after some more digging, I found that Tomcat JDBC
> was simply ignoring my badly named params. I had specified
> maxWaitMillis="5000" in my  in context.xml. Yet on
> initialization, I saw the following in my log:
> 
> Using DataSource 
> [org.apache.tomcat.jdbc.pool.DataSource@41a91006{ConnectionPool[defaul
tAutoCommit=null;
>
> 
defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=nul
l;
> driverClassName=com.mysql.jdbc.Driver; maxActive=100; maxIdle=10; 
> minIdle=10; initialSize=10; maxWait=3; testOnBorrow=true; 
> testOnReturn=false; timeBetweenEvictionRunsMillis=5000; 
> numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=6; 
> testWhileIdle=false; testOnConnect=false; password=; 
> url=jdbc:mysql://localhost:3306/?useUnicode=true&characterEn
coding=UTF-8;
>
> 
username=root; validationQuery=SELECT 1; validationQueryTimeout=-1;
> validatorClassName=null; validationInterval=3; 
> accessToUnderlyingConnectionAllowed=true; removeAbandoned=false; 
> removeAbandonedTimeout=300; logAbandoned=true;
> connectionProperties=null; initSQL=null; jdbcInterceptors=null;
> jmxEnabled=true;
> 
> which shows maxWait=3.

Of course. No component is going to guess that the number you put in
configuration (5000) should be applied to some other setting.

> So it looks Tomcat JDBC is forgiving with badly named params in
> that it doesn't fail on startup. Whereas if I try to instantiate a
> Tomcat JDBC DataSource directly, it fails when I try to set
> properties that don't exist.

I think this has more to do with the tolerance of the Spring
configuration component relative to the tolerance of the Tomcat
configuration component: both are creating a new DataSource and
calling setFoo() on them, but only Spring fails when a setter is not
available, while Tomcat just gives a warning and continues on its way.

- -chris
-BEGIN PGP SIGNATURE-
Comment: GPGTools - http://gpgtools.org

iQIcBAEBCAAGBQJWFXeZAAoJEBzwKT+lPKRY9RcP/iAu93Ib07WtvMru0ZCE8lFR
ugWRHtXAnYs1AhwAFIKgGxOuQfnOSmY7ycHBVZSINfv+T9roSyFrgOq9/EMdM2Nj
YyEsnxUtcGEyHFkDuwYDHhIE7u6hrXrXy4yNj/Ilsjlau8wjlah2yiDQgumES0m8
E9N6QhLApXI+wvBPEJkBCWhxBfa5z50y9oXFVDrrnSZmynHiEzpBgx+bKaoJjeeh
xUUg2ddLuJLONkWnvXnimkppjcdOfryIprtFeMjuLFr/DowyPXb3fC9lMGwJqmpc
dSFNO8iMAfPVwX7LDyv1swGmMFG79HUb74GMlnFQhXH09f0CHVifSn/n9n5Oyf5M
sOSvMCvdzb+c8rwuuJb0T6qDtZg+4BVDUSv7Mw6zHBUp4BTdFfdPuWlbwlHDO2Ed
gDirhHe2lECyScTx4vKVzZGRgLvArF+IY+EM5LHgP4FugC+0eUDoMmE2vVoI/TSt
Y7uQTd9BqbaXViAcDP/8vjiWBnVbrnOgW4jdSZyIforE2QnD3oq6H5JZFTfNfEQt
WG2KY7kOpF6JZ5BwDIqFoM2N9/Ywz9Wy42VzGsiP65sVDAEDlnAeysa2icsV9L3l
4Sk69mEJXsokcbzEiHkL+LwXf8DNl0Rw53Kk9SEOhNeICb+emQnMnaMBhndEX5ZW
8kudLYhkwjagMFl6hB+F
=I0lL
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP params different for the Tomcat JDBC (not DBCP) implementation in Tomcat 8?

[Bradley Wagner]

Ah, I see what you're saying. My apologies for not seeing that sooner.

That post was also very helpful in explaining why both exist. Thank you!

Is it your recommendation then to use DBCP 2 over Tomcat JDBC in Tomcat 8?
If so, I think it would be helpful to have a page on the public Tomcat
website about why both exist and which one is recommended.

Also, in my case, after some more digging, I found that Tomcat JDBC was
simply ignoring my badly named params. I had specified maxWaitMillis="5000"
in my  in context.xml. Yet on initialization, I saw the following
in my log:

Using DataSource
[org.apache.tomcat.jdbc.pool.DataSource@41a91006{ConnectionPool[defaultAutoCommit=null;
defaultReadOnly=null; defaultTransactionIsolation=-1; defaultCatalog=null;
driverClassName=com.mysql.jdbc.Driver; maxActive=100; maxIdle=10;
minIdle=10; initialSize=10; maxWait=3; testOnBorrow=true;
testOnReturn=false; timeBetweenEvictionRunsMillis=5000;
numTestsPerEvictionRun=0; minEvictableIdleTimeMillis=6;
testWhileIdle=false; testOnConnect=false; password=;
url=jdbc:mysql://localhost:3306/?useUnicode=true&characterEncoding=UTF-8;
username=root; validationQuery=SELECT 1; validationQueryTimeout=-1;
validatorClassName=null; validationInterval=3;
accessToUnderlyingConnectionAllowed=true; removeAbandoned=false;
removeAbandonedTimeout=300; logAbandoned=true; connectionProperties=null;
initSQL=null; jdbcInterceptors=null; jmxEnabled=true;

which shows maxWait=3.

So it looks Tomcat JDBC is forgiving with badly named params in that it
doesn't fail on startup. Whereas if I try to instantiate a Tomcat JDBC
DataSource directly, it fails when I try to set properties that don't exist.

Thanks again,
Bradley

On Wed, Oct 7, 2015 at 3:09 PM Mark Thomas  wrote:

> On 07/10/2015 19:54, Bradley Wagner wrote:
> > Did not what?
> >
> > We added "factory='org.apache.tomcat.jdbc.pool.DataSourceFactory'". That
> > switched us to Tomcat DBCP, correct?
>
> No. There is no such thing as Tomcat DBCP.
>
> There is Apache Commons DBCP 1. This is used by default in Tomcat 6.0.x
> and 7.0.x.
>
> There is Apache Commons DBCP 2. This is used in default Tomcat 8.0.x.
>
> Commons DBCP is packaged renamed to avoid class loading conflicts if the
> web application includes a copy of Commons DBCP in WEB-IN/lib.
>
> There is also the Tomcat JDBC connection pool. This is entirely separate
> from DBCP. For the full history, read this [1].
>
> > At that time, we were using the updated "maxWaitMillis" and "maxTotal" in
> > our context.xml and Tomcat didn't seem to complain on startup.
> >
> > Then, when we tried to set "maxWaitMillis" on a manually created Tomcat
> > DBCP dataSource:
> >  " > destroy-method="close">" in a test at which point we got an error about
> > that class not having a 'maxWaitMillis' setter.
> >
> > Was the Tomcat DBCP we were using in our context.xml just failing
> silently
> > or ignoring the 'maxWaitMillis' and 'maxTotal' params and we didn't
> realize
> > it?
>
> I suspect Tomcat was changing the names as required under the covers
> without telling you anything.
>
> > Are the params here:
> > https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html mean to be the
> > definitive ones for Tomcat DBCP?
>
> Those are for Tomcat JDBC. There is no Tomcat DBCP.
>
> Mark
>
>
> [1] http://markmail.org/message/nhayhdcstkj2lssf
>
> >
> > On Wed, Oct 7, 2015 at 2:46 PM Konstantin Kolinko <
> knst.koli...@gmail.com>
> > wrote:
> >
> >> 2015-10-07 21:36 GMT+03:00 Bradley Wagner <
> bradley.wag...@hannonhill.com>:
> >>> Hi,
> >>>
> >>> We recently upgraded to Tomcat 8. As per the Migration Guide:
> >>> https://tomcat.apache.org/migration-8.html#Database_Connection_Pooling
> >> and
> >>> DBCP documentation
> >>>
> >>
> https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP_2)_Configurations
> >> ,
> >>> we switched to using the new param values like: maxTotal and
> >> maxWaitMillis.
> >>>
> >>> Then, we switched to using the Tomcat DBCP by adding
> >>> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory". Tomcat did not
> >>> seem to have a problem with using Tomcat DBCP with the updated param
> >> names.
> >>
> >> No, you did not. The above factory is for "Tomcat JDBC" pool
> >> implementation.
> >>
> >> It was inspired by DBCP1, but is different from both Commons DBCP1
> >> (used by default in Tomcat 6, 7) and Commons DBCP2 (used by default in
> >> Tomcat 8).
> >>
> >>> Then, I modified a test we were running to also use  a Spring
> >> initialized,
> >>> Tomcat DBCP DataSource:
> >>>
> >>>  >>> destroy-method="close">
> >>>
> >>> Now, our test is complaining with: "Bean property 'maxWaitMillis' is
> not
> >>> writable or has an invalid setter method." because we were using the
> new
> >>> style param name:
> >>>
> >>> 
> >>>
> >>> So now I'm confused.
> >>>
> >>> 1. Does Tomcat DBCP (in Tomcat 8) indeed use different params than the
> >> new
> >>> DBCP2 that ships with Tomcat 8?
> >>> 

Re: DBCP params different for the Tomcat DBCP implementation in Tomcat 8?

[Mark Thomas]

On 07/10/2015 19:54, Bradley Wagner wrote:
> Did not what?
> 
> We added "factory='org.apache.tomcat.jdbc.pool.DataSourceFactory'". That
> switched us to Tomcat DBCP, correct?

No. There is no such thing as Tomcat DBCP.

There is Apache Commons DBCP 1. This is used by default in Tomcat 6.0.x
and 7.0.x.

There is Apache Commons DBCP 2. This is used in default Tomcat 8.0.x.

Commons DBCP is packaged renamed to avoid class loading conflicts if the
web application includes a copy of Commons DBCP in WEB-IN/lib.

There is also the Tomcat JDBC connection pool. This is entirely separate
from DBCP. For the full history, read this [1].

> At that time, we were using the updated "maxWaitMillis" and "maxTotal" in
> our context.xml and Tomcat didn't seem to complain on startup.
> 
> Then, when we tried to set "maxWaitMillis" on a manually created Tomcat
> DBCP dataSource:
>  " destroy-method="close">" in a test at which point we got an error about
> that class not having a 'maxWaitMillis' setter.
> 
> Was the Tomcat DBCP we were using in our context.xml just failing silently
> or ignoring the 'maxWaitMillis' and 'maxTotal' params and we didn't realize
> it?

I suspect Tomcat was changing the names as required under the covers
without telling you anything.

> Are the params here:
> https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html mean to be the
> definitive ones for Tomcat DBCP?

Those are for Tomcat JDBC. There is no Tomcat DBCP.

Mark


[1] http://markmail.org/message/nhayhdcstkj2lssf

> 
> On Wed, Oct 7, 2015 at 2:46 PM Konstantin Kolinko 
> wrote:
> 
>> 2015-10-07 21:36 GMT+03:00 Bradley Wagner :
>>> Hi,
>>>
>>> We recently upgraded to Tomcat 8. As per the Migration Guide:
>>> https://tomcat.apache.org/migration-8.html#Database_Connection_Pooling
>> and
>>> DBCP documentation
>>>
>> https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP_2)_Configurations
>> ,
>>> we switched to using the new param values like: maxTotal and
>> maxWaitMillis.
>>>
>>> Then, we switched to using the Tomcat DBCP by adding
>>> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory". Tomcat did not
>>> seem to have a problem with using Tomcat DBCP with the updated param
>> names.
>>
>> No, you did not. The above factory is for "Tomcat JDBC" pool
>> implementation.
>>
>> It was inspired by DBCP1, but is different from both Commons DBCP1
>> (used by default in Tomcat 6, 7) and Commons DBCP2 (used by default in
>> Tomcat 8).
>>
>>> Then, I modified a test we were running to also use  a Spring
>> initialized,
>>> Tomcat DBCP DataSource:
>>>
>>> >> destroy-method="close">
>>>
>>> Now, our test is complaining with: "Bean property 'maxWaitMillis' is not
>>> writable or has an invalid setter method." because we were using the new
>>> style param name:
>>>
>>> 
>>>
>>> So now I'm confused.
>>>
>>> 1. Does Tomcat DBCP (in Tomcat 8) indeed use different params than the
>> new
>>> DBCP2 that ships with Tomcat 8?
>>> 2. Why did Tomcat not complain when I was using maxWaitMillis with the
>>> updated factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" but does
>>> complain when I try to instantiate that pool implementation's DataSource
>>> directly?
>>> 3. Is there some other way that I should be instantiating the Tomcat DBCP
>>> DataSource in my test that would be more appropriate?
>>>
>>> I searched the archives and couldn't find mention of this.
>>>
>>> Thanks!
>>>
>>>
>>> --
>>> Bradley Wagner
>>> VP Engineering, Hannon Hill
>>> www.hannonhill.com | Twitter  | Github
>>> 
>>
>> -
>> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
>> For additional commands, e-mail: users-h...@tomcat.apache.org
>>
>> --
> Bradley Wagner
> VP Engineering, Hannon Hill
> www.hannonhill.com | Twitter  | Github
> 
> 


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP params different for the Tomcat DBCP implementation in Tomcat 8?

[Bradley Wagner]

Did not what?

We added "factory='org.apache.tomcat.jdbc.pool.DataSourceFactory'". That
switched us to Tomcat DBCP, correct?

At that time, we were using the updated "maxWaitMillis" and "maxTotal" in
our context.xml and Tomcat didn't seem to complain on startup.

Then, when we tried to set "maxWaitMillis" on a manually created Tomcat
DBCP dataSource:
 "" in a test at which point we got an error about
that class not having a 'maxWaitMillis' setter.

Was the Tomcat DBCP we were using in our context.xml just failing silently
or ignoring the 'maxWaitMillis' and 'maxTotal' params and we didn't realize
it? Are the params here:
https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html mean to be the
definitive ones for Tomcat DBCP?

On Wed, Oct 7, 2015 at 2:46 PM Konstantin Kolinko 
wrote:

> 2015-10-07 21:36 GMT+03:00 Bradley Wagner :
> > Hi,
> >
> > We recently upgraded to Tomcat 8. As per the Migration Guide:
> > https://tomcat.apache.org/migration-8.html#Database_Connection_Pooling
> and
> > DBCP documentation
> >
> https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP_2)_Configurations
> ,
> > we switched to using the new param values like: maxTotal and
> maxWaitMillis.
> >
> > Then, we switched to using the Tomcat DBCP by adding
> > factory="org.apache.tomcat.jdbc.pool.DataSourceFactory". Tomcat did not
> > seem to have a problem with using Tomcat DBCP with the updated param
> names.
>
> No, you did not. The above factory is for "Tomcat JDBC" pool
> implementation.
>
> It was inspired by DBCP1, but is different from both Commons DBCP1
> (used by default in Tomcat 6, 7) and Commons DBCP2 (used by default in
> Tomcat 8).
>
> > Then, I modified a test we were running to also use  a Spring
> initialized,
> > Tomcat DBCP DataSource:
> >
> >  > destroy-method="close">
> >
> > Now, our test is complaining with: "Bean property 'maxWaitMillis' is not
> > writable or has an invalid setter method." because we were using the new
> > style param name:
> >
> > 
> >
> > So now I'm confused.
> >
> > 1. Does Tomcat DBCP (in Tomcat 8) indeed use different params than the
> new
> > DBCP2 that ships with Tomcat 8?
> > 2. Why did Tomcat not complain when I was using maxWaitMillis with the
> > updated factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" but does
> > complain when I try to instantiate that pool implementation's DataSource
> > directly?
> > 3. Is there some other way that I should be instantiating the Tomcat DBCP
> > DataSource in my test that would be more appropriate?
> >
> > I searched the archives and couldn't find mention of this.
> >
> > Thanks!
> >
> >
> > --
> > Bradley Wagner
> > VP Engineering, Hannon Hill
> > www.hannonhill.com | Twitter  | Github
> > 
>
> -
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>
> --
Bradley Wagner
VP Engineering, Hannon Hill
www.hannonhill.com | Twitter  | Github

Re: DBCP params different for the Tomcat DBCP implementation in Tomcat 8?

[Konstantin Kolinko]

2015-10-07 21:36 GMT+03:00 Bradley Wagner :
> Hi,
>
> We recently upgraded to Tomcat 8. As per the Migration Guide:
> https://tomcat.apache.org/migration-8.html#Database_Connection_Pooling and
> DBCP documentation
> https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#Database_Connection_Pool_(DBCP_2)_Configurations,
> we switched to using the new param values like: maxTotal and maxWaitMillis.
>
> Then, we switched to using the Tomcat DBCP by adding
> factory="org.apache.tomcat.jdbc.pool.DataSourceFactory". Tomcat did not
> seem to have a problem with using Tomcat DBCP with the updated param names.

No, you did not. The above factory is for "Tomcat JDBC" pool implementation.

It was inspired by DBCP1, but is different from both Commons DBCP1
(used by default in Tomcat 6, 7) and Commons DBCP2 (used by default in
Tomcat 8).

> Then, I modified a test we were running to also use  a Spring initialized,
> Tomcat DBCP DataSource:
>
>  destroy-method="close">
>
> Now, our test is complaining with: "Bean property 'maxWaitMillis' is not
> writable or has an invalid setter method." because we were using the new
> style param name:
>
> 
>
> So now I'm confused.
>
> 1. Does Tomcat DBCP (in Tomcat 8) indeed use different params than the new
> DBCP2 that ships with Tomcat 8?
> 2. Why did Tomcat not complain when I was using maxWaitMillis with the
> updated factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" but does
> complain when I try to instantiate that pool implementation's DataSource
> directly?
> 3. Is there some other way that I should be instantiating the Tomcat DBCP
> DataSource in my test that would be more appropriate?
>
> I searched the archives and couldn't find mention of this.
>
> Thanks!
>
>
> --
> Bradley Wagner
> VP Engineering, Hannon Hill
> www.hannonhill.com | Twitter  | Github
> 

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP : Best practice javax.sql.DataSource object and Connection Object creation in Web Application

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Milan,

One followup (to your old thread) is sufficient.

On 1/1/14, 7:50 AM, Milan wrote:
> I would like to know that what is the best practice or efficient
> way to create  javax.sql.DataSource object
> 
> A) Following is my practice.
> 
> -  Using DBCP, I created javax.sql.DataSource and
> connection object for each call to method in my class.
> 
> -  Right now I am facing oracle session blocking problem.
> 
> 
> 
> B)  I am going to implement following practice
> 
> -  Creating only one object of javax.sql.DataSource using
> singleton pattern for whole application.
> 
> -  Using that single javax.sql.DataSource object I will
> create connection object of each method call and close the
> connection after transaction is finished.
> 
> Please advise me on B) practice.  Is there anything I am missing or
> how does B) impact on my DB transaction.

You should use a JNDI DataSource, created by the container (Tomcat).

http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBCAAGBQJSxGCgAAoJEBzwKT+lPKRY+QMP/07FkDqCaViZkGXYD3y/E9s8
veHqaH8kanEi6xi6zcWxwZ2ZMdIAgS84l3BACa4njRWyHnVe7AuPaKkh9SImu8i7
0K+hdpNBPxZwCKhLPaVWR/Bi7VsuacEKY0bCXRjD/Z+cWEvpX+OLC+MsaYJchauN
a1Q0Eja2+AE1difuHEuk5B5/Po4TlrJeQzIBKn6kJU/TnJcIwLFqPtksmZu11Rjb
JJmbv9/uJDJuY1WO/We0tm+wI/a7Uv9IoYhsqgCuezVkvMKd5ETDXnPB07KK2LUo
NYYD81GITrqm+JxxN9fmiV5p1+lslp/XrOk86QBl/m+/KZVeCbur4F4HS7e1gWdS
wPiCRaVx6yp5vr8Pz7ryWaEvziaJOSmrNgBjbT7eBCjjg5dQKfWq4FE8HfOO1rpD
47ENyn5RgtQBeUztR1dKY1Kp2scEKUZ2lION0AqwaQqo4XBRrtrRkLrxtGMeP3Nl
42NrDopcuZAhY+EnTu4+OssS4uBPzKxHu6VMdUeUfb6LRtZp68q2csye4uXzpmMN
5fg0wL3CDUEGTUqnyOcGfpODXtoqLqAUgGK/naQiyqGUJVN/Sgman+6uJeseCVsX
Xq8Js93v94nmDdj2T/l406KteBVgxui33SKbiMGqBZqY8HvI/G+/dlVGrQCTZ0kf
YuZuN/WURkt297mMlwa2
=dnHj
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP is Single Threaded

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Mark,

On 11/7/13, 4:44 PM, Mark Thomas wrote:
> On 07/11/2013 21:10, Christopher Schultz wrote:
> 
>> Can you describe the effective difference between my 
>> over-simplified description and what is implemented in
>> commons-pool (ignoring thread-fairness, which I'll admit is a
>> very useful feature)?
> 
> For starters, the relatively expensive validation process is
> outside the sync. Given where this thread started, that is a key
> point.
> 
> Object creation, destruction, activation and deactivation are also
> all outside the syncs. Creation and destruction are very likely to
> be expensive as well (else why bother with a pool).
> 
> The key difference is that pool uses syncs to give a thread
> permission to create an object, borrow an object, etc. Giving
> permission is very quick. The actual action is taken outside of the
> syncs as that is slow and expensive.
> 
> You will still hit a synchronisation bottleneck with very high 
> concurrency but I have a hard time believing most real world apps 
> would actually get anywhere near the concurrency levels you need
> to reach to hit this bottleneck - if you use a recent version of
> Pool and DBCP.

Agreed. Presumably, a Connection is being checked-out of the pool in
order to ... make some JDBC calls. Those are going to be way slower
than any of this synchronization we're discussing.

But, pathologically-speaking, a couple of hundred threads doing
nothing but repeatedly borrowing and returning JDBC connections are
going to expose a "horrible synchronization nightmare" in the
implementation. But that will be the case no matter what the
implementation, given the whole point of an object-pool.

> jdbc-pool and Pool2+DBCP2 remove the bottleneck entirely so in the 
> unliekly event you have an app with that much concurrency, a 
> bottleneck in the connection pool won't be an issue.

I'll have to look at how both of those libraries actually work under
the covers. I've never bothered to look...

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.15 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBCAAGBQJSfAxoAAoJEBzwKT+lPKRYVAIP/j72A6/+AHi89bG53rdBlvCI
wK550RM3udBt09lbhGZBWCe6qaj9sQdsgsp7MHO+ebj6/bx5bKdDQ+Pt4F28yUf7
ku3dubNjInTAB3xn25jY8MBPkzz82+8RrAsTc8QD0r/m/njsuRlM6EAu617GcI0q
PmAXlSLUmTAhSVAbPQ+ZqqdXA/eM8hu/NgT6ZyBIo/yez/jBrIzyGdHIGHKXsqRp
0N0ANH22o0uqgYms8tYpQgv1M/PJHLe9Br6e9aNPTJC9b3FZfq+fD/gbripGEnHC
GGqmT5+Pk3+n9bbPpp5JPJ1oDZY33SYE8CihJ7NBRrD14Egd3oqbotG8/LWmgDjk
lil/4Z0PuVpHPXzz3N+H5FGw83eJ4T8+vJs3EIxw4HrOSvwzyypmMFM6Z/UCmSbT
wCO4C47m84Omi3UX9r2ha9Wk7/oVh5O1g4nteHU1MJEO/BaiR7M/Q1BHAeKC4LSF
0zHwUVvgJzMHtCQBbaPYq2LynXiLk+RdWsIaC1ZBLAuBUVSThpv6NHau5xeKKHsx
gop897wXf3+t08js0JrfCRYq5ZglY2bO7DFXU6RFLwrRwVBGH6AV98us9a38nb8h
8XhK0GORm4p5QMRk2CBOOp0sKcSbihEhV3KHX4JymdnSvtdGXYMZuscBcpiwbMuV
nHZq/nfhfYXpIKKsPvMx
=8N8d
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP is Single Threaded

[Mark Thomas]

On 07/11/2013 21:10, Christopher Schultz wrote:

> Can you describe the effective difference between my
> over-simplified description and what is implemented in commons-pool
> (ignoring thread-fairness, which I'll admit is a very useful
> feature)?

For starters, the relatively expensive validation process is outside
the sync. Given where this thread started, that is a key point.

Object creation, destruction, activation and deactivation are also all
outside the syncs. Creation and destruction are very likely to be
expensive as well (else why bother with a pool).

The key difference is that pool uses syncs to give a thread permission
to create an object, borrow an object, etc. Giving permission is very
quick. The actual action is taken outside of the syncs as that is slow
and expensive.

You will still hit a synchronisation bottleneck with very high
concurrency but I have a hard time believing most real world apps
would actually get anywhere near the concurrency levels you need to
reach to hit this bottleneck - if you use a recent version of Pool and
DBCP. If you use the versions of 4 years ago then hitting the
bottleneck is easy.

You can use the unit tests in jdbc-pool to examine the sorts of
concurrency levels you need to reach before you hit the bottleneck in
DBCP+Pool.

jdbc-pool and Pool2+DBCP2 remove the bottleneck entirely so in the
unliekly event you have an app with that much concurrency, a
bottleneck in the connection pool won't be an issue.

Mark

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP is Single Threaded

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Mark,

On 11/7/13, 11:14 AM, Mark Thomas wrote:
> On 07/11/2013 16:03, Christopher Schultz wrote:
>> Yogesh,
>> 
>> On 11/7/13, 10:58 AM, yogesh hingmire wrote:
>>> While looking to upgrade to tomcat7, i understand the
>>> connection pool is a major improvement. I read that
>> 
>>> commons-dbcp is single threaded, in order to be thread safe 
>>> commons-dbcp locks the entire pool, even during query 
>>> validation.
>> 
>>> So as i understand, the connection pool will be locked when 
>>> handing out new connections and other threads which need 
>>> connections from the pool will wait until they are handed
>>> their respective connection?
>> 
>>> Is my understanding right and if anyone could explain this 
>>> better
>> 
>> That is correct. This is because the checkout method is 
>> implemented like this (ignore syntax issues, this is psuedocode, 
>> won't match method signatures, etc.):
>> 
>> public synchronized Connection checkout() { // obtain connection
>> by whatever means necessary, // validate the connection, etc. }
> 
> Huh?
> 
> Have you even looked at the source for Commons Pool at any point
> in the last 4 years? (Commons Pool being the component that
> provides the object pooling used by Commons DBCP).

Sorry, I should have said it looks like this (which nobody would be
able to comprehend):

public /* not synchronized */ Object borrowObject() {

  synchronized (this) {
 // register this thread's desire for an object
  }

  allocate(); /* note: synchronized method */

  for(;;) {
synchronized(this) {
  // check that pool is open
}

// use a synchronized Latch object

if(poolExhausted {
  synchronized (this) {
// take appropriate action
  }
}

// use a synchronized Latch object

if(got object)
  return object;
else
  continue;
  }
}

So... you're right: it's not one big synchronized method. Instead,
it's a mismash of monitor acquisitions and releases, many of which are
for "this". Mostly what all that acrobatics yields is the ability to
implement a fair thread-queuing system. In a fair thread-queuing
system, the effect is that ... all threads are serialized. So if 5
threads need objects from the pool, they will all wait in line.

Can you describe the effective difference between my over-simplified
description and what is implemented in commons-pool (ignoring
thread-fairness, which I'll admit is a very useful feature)?

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.15 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBCAAGBQJSfAHQAAoJEBzwKT+lPKRYk+QP/iu8wIha5rntfSdRZ/HshdMG
KohNrU+l7SLXNqNAaI8505YbNusc+jy+PT++FtQj8WfLoViCVMiPPEpnoqsoeuK+
AHkrw2nQYyVDelY+7s/BWtFYuUaupLAeg2O8Hk+d/Dr18Toh3ZOq3+PStoCeMJX8
HjlkgHdQGmyQ7qjjTHtykidC5V8x16uTjSWEUyVFauWEM5ZkpC9dXXPE0rPrDXWi
cWgUG9QxTztGcEsMLinapuwueldTDCP9ZHOadi85tk0OE7efr8E72XTmfhYKUIy5
ZHjt2obJeyJinOXZq2VAy8iOxMPM7SrS7E2vFIkjAiP+/f6MX8emAGy1lZ76ajb7
6w4kZuXdFlCJhpDc70ZwX90Xh4uusY2msmRFJQr+JOPZ70b3vpX1nYagsn7sPZ8G
n1R+9vtNgRRjivusuYXJjUxdWZOZudMAEKAjCrW/vwXxJICbGu1qnDvnqBlsVtjZ
23wc3IqfNlsvTYLKHAsgdjYv7zZiRmmzE4BPqLcuyBn7FkSZ/9dgUTfZE8SDXrTG
2RAJdL3nmqgAxOXHzVDx7i4OmC7JLgdgtQ7pNyy7B8N/xUxLeqnuJRgFnQcaLc4C
jGDhseWmIdUx1ZxJAOJJzV+SPV6wy/HlQoRhxccb99LSWpneptqhizvcmfhtHBYk
a+/0l/3CdT0Y9SIRw73A
=OBJ4
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP is Single Threaded

[Mark Thomas]

On 07/11/2013 16:08, Christopher Schultz wrote:
> Mark,
> 
> On 11/7/13, 11:03 AM, Mark Thomas wrote:
>> On 07/11/2013 15:58, yogesh hingmire wrote:
>>> While looking to upgrade to tomcat7, i understand the
>>> connection pool is a major improvement. I read that
>>> 
>>> commons-dbcp is single threaded, in order to be thread safe 
>>> commons-dbcp locks the entire pool, even during query 
>>> validation.
> 
>> Where did you read that? While it might have been true of
>> earlier DBCP releases I'm fairly sure it is not true for current
>> releases.
> 
> http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Introduction
>
> 
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Introduction
> 
> Bullet-point #1 in both cases.

Yep. Over 4 years out of date. I've made some edits.

The key messages are:
- jdbc-pool is faster than Commons DBCP
- at low concurrency there is little between them
- as concurrent borrow()s and return()s increase, the differential
increases
- how much difference it makes depends on the app so as with any
performance optimisation, test it first
- Tomcat 8 with DBCP2 should be a lot closer to jdbc-pool irrespective
of concurrency but I still expect jdbc-pool to be faster

Mark

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP is Single Threaded

[Mark Thomas]

On 07/11/2013 16:03, Christopher Schultz wrote:
> Yogesh,
> 
> On 11/7/13, 10:58 AM, yogesh hingmire wrote:
>> While looking to upgrade to tomcat7, i understand the connection 
>> pool is a major improvement. I read that
> 
>> commons-dbcp is single threaded, in order to be thread safe 
>> commons-dbcp locks the entire pool, even during query
>> validation.
> 
>> So as i understand, the connection pool will be locked when
>> handing out new connections and other threads which need
>> connections from the pool will wait until they are handed their
>> respective connection?
> 
>> Is my understanding right and if anyone could explain this
>> better
> 
> That is correct. This is because the checkout method is
> implemented like this (ignore syntax issues, this is psuedocode,
> won't match method signatures, etc.):
> 
> public synchronized Connection checkout() { // obtain connection by
> whatever means necessary, // validate the connection, etc. }

Huh?

Have you even looked at the source for Commons Pool at any point in
the last 4 years? (Commons Pool being the component that provides the
object pooling used by Commons DBCP).

Mark

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP is Single Threaded

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Mark,

On 11/7/13, 11:03 AM, Mark Thomas wrote:
> On 07/11/2013 15:58, yogesh hingmire wrote:
>> While looking to upgrade to tomcat7, i understand the connection
>> pool is a major improvement. I read that
>> 
>> commons-dbcp is single threaded, in order to be thread safe
>> commons-dbcp locks the entire pool, even during query
>> validation.
> 
> Where did you read that? While it might have been true of earlier
> DBCP releases I'm fairly sure it is not true for current releases.

http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Introduction
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Introduction

Bullet-point #1 in both cases.

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.15 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBCAAGBQJSe7rwAAoJEBzwKT+lPKRY7TgQAJvqpR1vX9TRqx/QiQ3M4/ja
x7A6mPueIz0Xr1Ay4K/DT2Sd1d+2FkKIKOkEmb4bLa3bX3dcibeGrM/NOTg3E4wO
XEtsUqt79cQeBdodE2+JcBFPI811Gv2rAXDxP7X9zE7UAKNvzE3l84jLiiVR4UXS
JdzsDHj6plQw0j5qfbUq2QnanObd1gsww2Dff59RbRxn+o1b1prgcAzipmVPPOx5
R7sTrvjiSr5EbmCCATfhsQKPXoLG0hAqfjDjfGgRVA5Bu3NRNYKO70oh/BaCqURi
CztV1flZivvLvsNpyW5pCXBNLzN3kxJC6KP090r+M8voOw+u4OpthATKqxjAdjM1
5rXBWv7fAXlrsCZVst4zdoe9SBONBUEG8KqpyG50zBeKzYW4D6gvAcqod9aZBB+5
BEby3Cwe/dzf0+LzDJFNgWjTjPvDFQsj3//QY1PcUVYqxxO4J5mA834U4z5wSBkl
c6ZGv12cJ2WgvBLkzTIwXZkMng6k1tC2i1GcAPUl+VTbIZfN8H9pJsJFAxw2Kzsg
M9pl0D3ftueVAvMFV/nu8EEFttg15V/B3V2BH5qP/L4R0W38BCTQZCgVnvWjDo8b
UUdXJr9hkcJq57mJOIVxJoYiAzuLBDZeHXNOFRHnbGky5KAvAA7riRlrAsjK0mOy
LqS8xJJwyTPWrs+BX9ix
=lS+X
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP is Single Threaded

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

Yogesh,

On 11/7/13, 10:58 AM, yogesh hingmire wrote:
> While looking to upgrade to tomcat7, i understand the connection
> pool is a major improvement. I read that
> 
> commons-dbcp is single threaded, in order to be thread safe
> commons-dbcp locks the entire pool, even during query validation.
> 
> So as i understand, the connection pool will be locked when handing
> out new connections and other threads which need connections from
> the pool will wait until they are handed their respective
> connection?
> 
> Is my understanding right and if anyone could explain this better

That is correct. This is because the checkout method is implemented
like this (ignore syntax issues, this is psuedocode, won't match
method signatures, etc.):

public synchronized Connection checkout()
{
   // obtain connection by whatever means necessary,
   // validate the connection, etc.
}

There's nothing particularly awkward, stupid or evil about the above
code. It's just that it uses "synchronized" for the whole method and
there are some performance penalties for doing that.

The term "single-threaded" is a bit misleading... of course DBCP is
thread-safe and multiple threads can access it, but there is
serialized access to the checkout method.

Tomcat-pool uses a different strategy for thread-safety that lends
itself to better performance under heavier loads.

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.15 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBCAAGBQJSe7nvAAoJEBzwKT+lPKRYgg4P/iC9QSrDoeNxpleTp5Rli1H0
fMQF2jW6BfVn82ITbViaXBMW54r8Xci2h4+DjNEOicw7TSEGxXU9UPuCzfTIlGwL
JDBObNBFSOaeMVK4NwcIAVz0ZKzRErCieXgOueJBUCOUqZEOOTcf4MzMS21EK97A
FAxhiwmcuRoSSMXZY55fTaE5bkPCSrZgkSPSwcz42FHu2gzbGPKzo+OKkSagZrUK
Or+fwKDiMXmZyWmpyCG/fyDVIQv3KAuYDA6TPqYXIf6vB3z64HjTvTcU5kvRw8A5
IlBgnp4lI62LG2ofC6GdRT188hQz2RTxg3P4Mg3EyVuWgUY5Ndozxtb+UJs7fafV
9xHnnK64eRTfN7aKS3On6znu+RCXFXs5UMPvzNtR6GJl5moIUMjA6kII3IbTYAx+
EE3MVLm/sr5SwVuZEH2x0f45lu5QFeFch8IdlwIE6YMNZkZtZ+79SOhDsMr8ZbE2
M6/jDhAoQiiOH+asOLdf+7IUf85qrYUXm9Dd3DrZ9y+GSJLux+QOQVq8onBnQlA4
eW6AeZO9yb4qhRvkqUr5idDE77HNpwb8gtBsFlbHor8pSVbGIJQYSvYGraxSSy3/
UmmjKGkewTz/AcTMq85Czt1QpbESsWT86jwEsRIxAxxkRBFuP1TCSo1PUelkzW3i
pm6PUNE4bsEXblYQ5aOT
=7qzz
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP is Single Threaded

[Mark Thomas]

On 07/11/2013 15:58, yogesh hingmire wrote:
> While looking to upgrade to tomcat7, i understand the connection pool is a
> major improvement. I read that
> 
> commons-dbcp is single threaded, in order to be thread safe commons-dbcp
> locks the entire pool, even during query validation.

Where did you read that? While it might have been true of earlier DBCP
releases I'm fairly sure it is not true for current releases.

> So as i understand, the connection pool will be locked when handing out new
> connections and other threads which need connections from the pool will
> wait until they are handed their respective connection?

There are brief periods of locking but they do no last the entire of the
borrow() and the return() methods.

There is contention during borrow() and return() but you'll only notice
it on multi-core systems the borrow() / return() at high rates.

Tomcat 7 ships with jdbc-pool that does not have this contention.

DBCP2 also does not have this contention and is used (in snapshot form
as there has not yet been an official release) in Tomcat 8 onwards.

> Is my understanding right and if anyone could explain this better

The best way to understand exactly what is locked and when is to look at
the source.

Mark


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp datasource encryption

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Filip,

On 4/23/12 1:47 PM, Filip Hanik Mailing Lists wrote:
>> http://wiki.apache.org/tomcat/FAQ/Password
>> 
>> 
>> In short, no.
>> 
>> Encrypting your database, database user, and database password
>> buys you virtually (and most people would say actually) nothing.
> 
> "virtually nothing" is the opposite of what I would call it. What 
> about compliance, this is HUGE for companies, and not to be
> discarded as an unimportant requirement

While it is viewed as an important requirement, there are really only
3 possible ways to fulfill it:

1. Enter the password at the command-prompt as the service starts (or
   allow such credentials to be provided after start-up in some way).
   This is the only one of these 3 possibilities that actually offers
   any real security. Unfortunately, it's an operational impossibility.
   Apache httpd has the same problem with server keys. See #3 for how
   that is (stupidly and commonly) handled.

2. Use a de-obfuscator to recover your plaintext credentials from some
   non-plain-text input (your suggestion, Filip). This just moves the
   problem somewhere else. So, your server.xml (or, better,
   context.xml) is clean but your context.xml.secret-stuff is suddenly
   not clean because it contains the password for the password. Sure,
   you pass an audit that is looking for something very specific, but
   you don't buy yourself any real security. It's like requiring a
   really good lock on a door when the window next to the door is
   always wide open. Yep, that's a great-lookin' lock you got there...
   you're definitely protected.

3. Remove the password completely. That is, use a password-less
   credential. Sounds completely stupid, right? Well, having a
   plaintext password is essentially the same thing, since who would
   guess that the password is nothing, right? You'd have to have access
   to the server.xml (or context.xml) to know that (unless you just
   /tried/ it, of course). Your database ought to be locked-down
   enough that you can't connect from just anywhere, so having
   remove access to the server that connects to the database is just
   as good as having access to the database, right?
   This is how Apache httpd recommends that you *don't* set up your
   web server's certificate keys. In my experience, *everybody* does
   this: you make a password-less key and use that to start your
   server. Just make sure you set permissions appropriately.
   This is also not secure, but it's very practical: if someone
   has access to your box, the game is over anyway. Having a
   trivially-discovered password is as good as having no password.

> http://tomcat.markmail.org/thread/wmdu4e52y2msjzal

VMWare's documentation shows you how to do #2 above either with some
other credential (good luck protecting that one any better) or with no
credential (in the example of using Base64 encoding).

- From a requirements-complance perspective, yes, there's some utility
to not having a cleartext password in your configuration file. An
honest risk analysis will show that the case before and after such
obfuscation is identical: you are no more protected than when you
started. It's actually worse, because you *think* you are somehow
magically protected. The security checkboxes have all been checked.
We're secure, right? Drinks all around.

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk+VtIIACgkQ9CaO5/Lv0PB1ggCgtGxUz183qITETmSyZS7yW2JU
nDoAoJSFWQJvY3B5TGeJlAolohb0rxOg
=LfY/
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp datasource encryption

[Filip Hanik Mailing Lists]

- Original Message -

> 
> http://wiki.apache.org/tomcat/FAQ/Password
> 
> 
> In short, no.
> 
> Encrypting your database, database user, and database password buys
> you virtually (and most people would say actually) nothing.

"virtually nothing" is the opposite of what I would call it. What about 
compliance, this is HUGE for companies, and not to be discarded as an 
unimportant requirement

http://tomcat.markmail.org/thread/wmdu4e52y2msjzal

If you wish to implement password obfuscator/deobfuscator yourself, you can set 
the 
org.apache.tomcat.util.digester.PROPERTY_SOURCE system property to a class that 
deobfuscates your password for you

reference: http://tomcat.apache.org/tomcat-7.0-doc/config/systemprops.html


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp datasource encryption

[Mark Eggers]

- Original Message -

> From: 이재만 
> To: Tomcat Users List 
> Cc: 
> Sent: Thursday, April 19, 2012 4:37 PM
> Subject: Re: dbcp datasource encryption
> 
>t hanks..
> 
> 
> 
> i want to encrypt password and database in my <Resource> element.
> 
> please give me a some imformation about it.
> 
> 
> 
> 
> -Original Message-
> From: "Christopher 
> Schultz"<ch...@christopherschultz.net> 
> To: "Tomcat Users List"<users@tomcat.apache.org>; 
> Cc: 
> Sent: 2012-04-20 (금) 01:14:54
> Subject: Re: dbcp datasource encryption
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> 이재만,
> 
> On 4/19/12 2:07 AM, 이재만 wrote:
> > i am operating website on tomcat6 and tomcat7
> > 
> > and i used dbcp as datasource on tomcat7 so i want to encrypt dbcp 
> > datasource
> > 
> > how do i encrypt my dbcp datasource .. plase give me some
> > samples.. thanks.
> 
> It's not clear what you are trying to do:
> 
> Are you trying to encrypt your data in the database?
> Are you trying to encrypt your connection to your database?
> Are you trying to encrypt the password to your database in your
> <Resource> element?
> 
> - -chris


http://wiki.apache.org/tomcat/FAQ/Password


In short, no.

Encrypting your database, database user, and database password buys you 
virtually (and most people would say actually) nothing.

. . . . just my two cents.
/mde/

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp datasource encryption

[이재만]

thanks..

 

i want to encrypt password and database in my <Resource> element.

please give me a some imformation about it.


 

-Original Message-
From: "Christopher Schultz"<ch...@christopherschultz.net> 
To: "Tomcat Users List"<users@tomcat.apache.org>; 
Cc: 
Sent: 2012-04-20 (금) 01:14:54
Subject: Re: dbcp datasource encryption

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

이재만,

On 4/19/12 2:07 AM, 이재만 wrote:
> i am operating website on tomcat6 and tomcat7
> 
> and i used dbcp as datasource on tomcat7 so i want to encrypt dbcp 
> datasource
> 
> how do i encrypt my dbcp datasource .. plase give me some
> samples.. thanks.

It's not clear what you are trying to do:

Are you trying to encrypt your data in the database?
Are you trying to encrypt your connection to your database?
Are you trying to encrypt the password to your database in your
<Resource> element?

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk+QOf4ACgkQ9CaO5/Lv0PBHogCggb3wdvpiYMRQfkgs9vWJ9LA9
T7cAn0g31CA6PuT3dZOVdtFc2BYmVRZK
=ra9N
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp datasource encryption

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

이재만,

On 4/19/12 2:07 AM, 이재만 wrote:
> i am operating website on tomcat6 and tomcat7
> 
> and i used dbcp as datasource on tomcat7 so i want to encrypt dbcp 
> datasource
> 
> how do i encrypt my dbcp datasource .. plase give me some
> samples.. thanks.

It's not clear what you are trying to do:

Are you trying to encrypt your data in the database?
Are you trying to encrypt your connection to your database?
Are you trying to encrypt the password to your database in your
 element?

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk+QOf4ACgkQ9CaO5/Lv0PBHogCggb3wdvpiYMRQfkgs9vWJ9LA9
T7cAn0g31CA6PuT3dZOVdtFc2BYmVRZK
=ra9N
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

RE: dbcp datasource encryption

[Osipov, Michael]

이재만 wrote:
> and i used dbcp as datasource on tomcat7 so i want to encrypt dbcp
> datasource 
> 
> how do i encrypt my dbcp datasource .. plase give me some samples..
> thanks. 

This should probably go to the DBCP pool but you should evaluate SSL with your 
database.


With best regards,
Michael Osipov

Re: DBCP Connections build up on one server

[Pid *]

On 17 Feb 2012, at 22:02, Shanti Suresh  wrote:

> The threaddump look the same across both servers.  The heapdump shows 
> increasing heap on the suspect server in the Finalizer class.  The Finalizer 
> class is holding references to another class which is a wrapper class for 
> ConectionPool objects.

You are probably leaking connections. Check that your code is properly
returning DB objects after use.

You might also consider using the built in connection pools, or at
least a newer version than 1.2.1.


p


>
> Thanks for all tips/suggestions!
>
>
>-Shanti
>
> On Feb 17, 2012, at 4:59 PM, Shanti Suresh wrote:
>
>> All,
>>
>> The Mbean for a JDBC datasource that uses the commons-dbcp-1.2.1.jar library 
>> builds up on one server.  The other server is okay.  They run the same code.
>>
>> We are using Tomcat-7.0.23.
>> They are both RedHat Linux servers
>>
>> What could be causing this anomaly?
>>
>>  -Shanti
>>
>
>
>
>
> -
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP Connections build up on one server

[Shanti Suresh]

The threaddump look the same across both servers.  The heapdump shows 
increasing heap on the suspect server in the Finalizer class.  The Finalizer 
class is holding references to another class which is a wrapper class for 
ConectionPool objects.

Thanks for all tips/suggestions!


-Shanti

On Feb 17, 2012, at 4:59 PM, Shanti Suresh wrote:

> All,
> 
> The Mbean for a JDBC datasource that uses the commons-dbcp-1.2.1.jar library 
> builds up on one server.  The other server is okay.  They run the same code.
> 
> We are using Tomcat-7.0.23.
> They are both RedHat Linux servers
> 
> What could be causing this anomaly?
> 
>   -Shanti
> 




-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp is mixing up connections

[Terence M. Bandoian]

On 1:59 PM, Pid * wrote:
> On 16 Dec 2011, at 09:32, "Aitor Garcia | Tempel.es" 
> wrote:
>
>  Mark:
>
> I'm just declaring variables there, no putting logic.
>
> You had overlooked log4jdbc, if you have read books in the same way
>
> Library log4jdbc is COOL! Works very Well, is really a good library that
> helps me a lot, without it I cannot see what really is happening behind the
> scenes. Bravo log4jdbc : http://code.google.com/p/log4jdbc/!!
>
> Terence:
>
> I had read the same, the first time I started a JSP page and rereaded it
> yesterday: http://java.sun.com/products/jsp/pdf/jsptut.pdf.
>
>
>
> Maybe I had been never understood how JSP/Beans works or maybe this kind
> programing language simply does not fit.
>
> I'm not an expert but in past I worked a with MVC, Swing and JAVA: Cool
> stuff, but I never have seen how it helps in the web development. Java
> turned into the web, makes huge amount of writing (the exact formula is :
> lines_of_JSP_code = lines_of_PHP_code * 10) & no way to fit
> objects/classes, into Web Application needs. Have you seen Hashes in PHP?
> try to use them in java... Ehhh and don't put an Integer into a HashMap and
> try to get a String!! You have to then Integer Values with a Try() Catch(),
> Swith statement does not support Strings!!!... thinks like this fucks a lot!
>
> PHP is a hundred times more versatile for Web, and there is lots and lots
> of support: lib GD is great, making PDFs is preatty simple almost every
> problem you are going to face is resolved and published somewhere, you only
> have to Google a little. I think PHP makes sense, of course in conjunction
> with a good framework.
>
> I'm going to read a lot of books but not about JavaServer(TM) (Oracle).
>
>
>
> I think you're on the wrong mailing list.
>
>
> p

But thanks for stopping by.

-Terence

>
> 
>
>
> Este  mensaje se dirige exclusivamente a su destinatario. Contiene
> información *CONFIDENCIAL* sometida a secreto profesional o cuya
> divulgación está prohibida por Ley.
> Si ha recibido este mensaje por error, debe saber que su lectura, copia y
> uso están prohibidos.
> Le rogamos nos lo comunique inmediatamente por esta misma vía o por
> teléfono 93 600 36 00  y proceda a su destrucción.
> El correo electrónico vía Internet no permite asegurar la confidencialidad
> de los mensajes que se transmiten ni su integridad o correcta recepción.
> *TEMPEL S.A.* no asume responsabilidad por estas circunstancias. Si el
> destinatario de este mensaje no consintiera la utilización del correo
> electrónico vía Internet y la grabación de los mensajes, rogamos lo ponga
> en nuestro conocimiento de forma inmediata.
> En cumplimiento de la Ley Orgánica 15/1999, de 13 de diciembre, de
> Protección de Datos de Carácter Personal le informamos de que sus datos
> personales y de empresa pasarán a formar parte de un fichero registrado
> ante la Agencia Española de Protección de Datos.
> Los datos personales que existen en nuestro poder están protegidos por
> nuestra Política de Seguridad, y no serán compartidos con ninguna otra
> empresa.
> Usted puede ejercitar sus derechos de acceso, rectificación, cancelación y
> oposición dirigiéndose por escrito a c/ Cobalto, 4 08907 L'Hospitalet de
> LLobregat (Barcelona).
>
> El 16/12/2011 3:32, Mark Eggers escribió:
>
> ral people
>

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp is mixing up connections

[Pid *]

On 16 Dec 2011, at 09:32, "Aitor Garcia | Tempel.es" 
wrote:

 Mark:

I'm just declaring variables there, no putting logic.

You had overlooked log4jdbc, if you have read books in the same way

Library log4jdbc is COOL! Works very Well, is really a good library that
helps me a lot, without it I cannot see what really is happening behind the
scenes. Bravo log4jdbc : http://code.google.com/p/log4jdbc/!!

Terence:

I had read the same, the first time I started a JSP page and rereaded it
yesterday: http://java.sun.com/products/jsp/pdf/jsptut.pdf.



Maybe I had been never understood how JSP/Beans works or maybe this kind
programing language simply does not fit.

I'm not an expert but in past I worked a with MVC, Swing and JAVA: Cool
stuff, but I never have seen how it helps in the web development. Java
turned into the web, makes huge amount of writing (the exact formula is :
lines_of_JSP_code = lines_of_PHP_code * 10) & no way to fit
objects/classes, into Web Application needs. Have you seen Hashes in PHP?
try to use them in java... Ehhh and don't put an Integer into a HashMap and
try to get a String!! You have to then Integer Values with a Try() Catch(),
Swith statement does not support Strings!!!... thinks like this fucks a lot!

PHP is a hundred times more versatile for Web, and there is lots and lots
of support: lib GD is great, making PDFs is preatty simple almost every
problem you are going to face is resolved and published somewhere, you only
have to Google a little. I think PHP makes sense, of course in conjunction
with a good framework.

I'm going to read a lot of books but not about JavaServer(TM) (Oracle).



I think you're on the wrong mailing list.


p




Este  mensaje se dirige exclusivamente a su destinatario. Contiene
información *CONFIDENCIAL* sometida a secreto profesional o cuya
divulgación está prohibida por Ley.
Si ha recibido este mensaje por error, debe saber que su lectura, copia y
uso están prohibidos.
Le rogamos nos lo comunique inmediatamente por esta misma vía o por
teléfono 93 600 36 00  y proceda a su destrucción.
El correo electrónico vía Internet no permite asegurar la confidencialidad
de los mensajes que se transmiten ni su integridad o correcta recepción.
*TEMPEL S.A.* no asume responsabilidad por estas circunstancias. Si el
destinatario de este mensaje no consintiera la utilización del correo
electrónico vía Internet y la grabación de los mensajes, rogamos lo ponga
en nuestro conocimiento de forma inmediata.
En cumplimiento de la Ley Orgánica 15/1999, de 13 de diciembre, de
Protección de Datos de Carácter Personal le informamos de que sus datos
personales y de empresa pasarán a formar parte de un fichero registrado
ante la Agencia Española de Protección de Datos.
Los datos personales que existen en nuestro poder están protegidos por
nuestra Política de Seguridad, y no serán compartidos con ninguna otra
empresa.
Usted puede ejercitar sus derechos de acceso, rectificación, cancelación y
oposición dirigiéndose por escrito a c/ Cobalto, 4 08907 L'Hospitalet de
LLobregat (Barcelona).

El 16/12/2011 3:32, Mark Eggers escribió:

ral people

Re: dbcp is mixing up connections

[Aitor Garcia | Tempel.es]

  
  
Mark:
  
I'm just declaring variables
there, no putting logic.

  You had
overlooked log4jdbc, if you have read books in the same
way

Library log4jdbc is COOL! Works very Well, is really a good
library that helps me a lot, without it I cannot see what
really is happening behind the scenes. Bravo log4jdbc :
http://code.google.com/p/log4jdbc/!!

Terence:
  
I had read the same, the first time I
started a JSP page and rereaded it yesterday:
http://java.sun.com/products/jsp/pdf/jsptut.pdf.

  

Maybe I had been never understood
  how JSP/Beans works or maybe this kind programing language
  simply does not fit. 
  
  I'm not an expert but in past I worked a with MVC, Swing and JAVA:
  Cool stuff, but I never have seen how it helps in the web
  development. Java turned into the web, makes huge amount of
  writing (the exact formula is : lines_of_JSP_code =
  lines_of_PHP_code * 10) & no way to fit objects/classes, into
  Web Application needs. Have you seen Hashes in PHP? try to use
  them in java... Ehhh and don't put an Integer into a HashMap and
  try to get a String!! You have to then Integer Values with a Try()
  Catch(), Swith statement does not support Strings!!!... thinks
  like this fucks a lot!
  
  PHP is a hundred times more versatile for Web, and there is lots
  and lots of support: lib GD is great, making PDFs is preatty
  simple almost every problem you are going to face is resolved and
  published somewhere, you only have to Google a little. I think PHP
  makes sense, of course in conjunction with a good framework.
  
  I'm going to read a lot of books but not about JavaServer(TM)
  (Oracle).
  
  


  
  
  


Este  mensaje se dirige
exclusivamente a su
destinatario. Contiene información CONFIDENCIAL
sometida a secreto profesional o cuya divulgación está
prohibida por Ley.
Si ha
recibido este mensaje por error, debe saber que su lectura,
copia y uso
están
prohibidos.
Le rogamos
nos lo comunique inmediatamente por esta misma vía o por
teléfono 93 600 36
00  y proceda a su destrucción.
El correo
electrónico vía Internet no permite asegurar la
confidencialidad de los
mensajes que se transmiten ni su integridad o correcta
recepción.
  TEMPEL
  S.A. no
asume responsabilidad por estas
circunstancias. Si el destinatario de este mensaje no
consintiera la
utilización del correo electrónico vía Internet y
la grabación de los mensajes,
rogamos lo ponga en nuestro conocimiento de forma inmediata.
En
cumplimiento de la
  Ley Orgánica 15/1999, de 13 de diciembre,
de
Protección de Datos
de Carácter Personal le informamos de que sus datos
personales y
de empresa
pasarán a formar parte de un fichero registrado ante la Agencia
  Española
de Protección de Datos.
Los datos
personales que existen en nuestro poder están protegidos por
nuestra Política
de Seguridad, y no serán compartidos con ninguna otra
empresa.
  Usted
puede
ejercitar sus derechos de acceso, rectificación,
cancelación y oposición
dirigiéndose por escrito a c/ Cobalto, 4 08907 L'Hospitalet
de
LLobregat
(Barcelona).

  


El 16/12/2011 3:32, Mark Eggers escribió:

  ral people 

  

Re: dbcp is mixing up connections

[Mark Eggers]

- Original Message -

> From: Terence M. Bandoian 
> To: Tomcat Users List 
> Cc: 
> Sent: Thursday, December 15, 2011 11:31 AM
> Subject: Re: dbcp is mixing up connections
> 
> On 1:59 PM, Christopher Schultz wrote:
>>  Aitor,
>> 
>>  On 12/15/11 7:12 AM, Aitor Garcia | Tempel.es wrote:
>>  > 5) Tomcat, creates ONE (or maybe SOME) Class object and call to the
>>  >  _jspService on every script request
>> 
>>  > What happens if you handle Pool Coonections with a
>>  > 'java.sql.Connection conn' variable declared into the 
> definitions
>>  > block "<%! %>"?
>> 
>>  > Happens than if you are donig multitheading and executing the same
>>  > sctipt in parallel you will mix up connections because evey thead
>>  > is executing the same method in parallel and putting a different
>>  > connection into the java.sql.Connection conn class variable.
>> 
>>  > I don't know if this is a tomcat bug.
>> 
>>  Obviously it's not.
>> 
>>  This is one of the many reasons why application logic has no business
>>  being in a JSP. Whoever proposed (and, indeed approved) the
>>  introduction of scriptlets (<% ... %>) should have been flogged.
>> 
>>  -chris
> 
> Hi, Chris-
> 
> From the Java Server Pages Specification:
> 
> "There are three language-based types of scripting elements:
> declarations, scriptlets, and expressions. Declarations follow the
> syntax <%! ... %>. Scriptlets follow the syntax <% ... %>. 
> Expressions
> follow the syntax <%= ... %>."
> 
> -Terence Bandoian
>

Lots of things are causing problems here.

As many others have pointed out, instance variables are not thread safe. In 
short, do not put your business logic, database logic, or control logic in a 
JSP page. You will have problems.

I suggest reading at least two books (plus the servlet spec as others have 
recommended).

Head First Servlets and JSP by Bryan Basham, Kathy Sierra, and Bert Bates

I've read the first edition. The second edition is out and highly reviewed. It 
covers a lot of material in a very approachable way. It does have a chapter at 
the end on MVC, but it is not a design book.

Several people have mentioned the Design Patterns book by the Gang of Four 
(Gamma et. al). It's a great book, but you may or may not find it approachable.

Head First Design Patterns by Freeman and Freeman is another approachable book, 
with examples and exercises in Java. It obviously covers design patterns. Even 
Erich Gamma was impressed (according to the "praise page").

Finally, I took a look at the library you're using to do logging. It appears 
that what you are trying to do is not supported. See the following two links:

http://code.google.com/p/log4jdbc/wiki/FAQ

http://code.google.com/p/log4jdbc/wiki/DataSourceExampleForWebSphere


In short, it appears that without writing your own factory, you will not get 
the results you want with this library. Granted, I've not really explored the 
code that much so I could be completely wrong here.

I do not have any affiliation with any of the books or authors mentioned. I 
just have found them good learning resources.

just my two cents . . . .
/mde/

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp is mixing up connections

[Konstantin Kolinko]

2011/12/15 Aitor Garcia | Tempel.es 
>
> I had read all the JNDI & JDBC Official & Unofficial documentation but & only 
> found than you MUST close the connections.


That is why you have to read  the Servlet specification, JSP specification, etc.


> There insn't references to where to declare variables.
>
> Declaring into local scope forces that you have pass by reference the 
> connection, statement and resultset in every function that handle the pool os 
> use a class to handle the connection itsef.
>

Eh? There are many ways to write a program. Have you ever heard about
encapsulation? About patterns (such as "Design Patterns" book by
E.Gamma &co)?
http://www.ibookdb.net/isbn/0201633612


One more important thing to learn:
You should NEVER send HTML e-mails and images to mailing lists. It
wastes subscribers' bandwidth and storage area on archive servers.
Please use plain text.

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp is mixing up connections

[Terence M. Bandoian]

On 1:59 PM, Christopher Schultz wrote:
> Aitor,
>
> On 12/15/11 7:12 AM, Aitor Garcia | Tempel.es wrote:
> > 5) Tomcat, creates ONE (or maybe SOME) Class object and call to the
> >  _jspService on every script request
>
> > What happens if you handle Pool Coonections with a
> > 'java.sql.Connection conn' variable declared into the definitions
> > block "<%! %>"?
>
> > Happens than if you are donig multitheading and executing the same
> > sctipt in parallel you will mix up connections because evey thead
> > is executing the same method in parallel and putting a different
> > connection into the java.sql.Connection conn class variable.
>
> > I don't know if this is a tomcat bug.
>
> Obviously it's not.
>
> This is one of the many reasons why application logic has no business
> being in a JSP. Whoever proposed (and, indeed approved) the
> introduction of scriptlets (<% ... %>) should have been flogged.
>
> -chris

Hi, Chris-

>From the Java Server Pages Specification:

"There are three language-based types of scripting elements:
declarations, scriptlets, and expressions. Declarations follow the
syntax <%! ... %>. Scriptlets follow the syntax <% ... %>. Expressions
follow the syntax <%= ... %>."

-Terence Bandoian


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp is mixing up connections

[Terence M. Bandoian]

On 1:59 PM, Christopher Schultz wrote:
> Aitor,
>
> On 12/15/11 7:12 AM, Aitor Garcia | Tempel.es wrote:
> > 5) Tomcat, creates ONE (or maybe SOME) Class object and call to the
> >  _jspService on every script request
>
> > What happens if you handle Pool Coonections with a
> > 'java.sql.Connection conn' variable declared into the definitions
> > block "<%! %>"?
>
> > Happens than if you are donig multitheading and executing the same
> > sctipt in parallel you will mix up connections because evey thead
> > is executing the same method in parallel and putting a different
> > connection into the java.sql.Connection conn class variable.
>
> > I don't know if this is a tomcat bug.
>
> Obviously it's not.
>
> This is one of the many reasons why application logic has no business
> being in a JSP. Whoever proposed (and, indeed approved) the
> introduction of scriptlets (<% ... %>) should have been flogged.
>
> -chris

Hi, Aitor-

Tomcat reuses JSP class instances in multiple threads.  Variables
defined in declarations blocks <%! ... %> result in member (or instance)
variables.  If you define instance variables, you have to ensure their
usage is thread-safe.

Variables defined in scriptlet blocks <% ... %> result in variables that
are local to the JSP service method.

-Terence Bandoian

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp is mixing up connections

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Aitor,

On 12/15/11 7:12 AM, Aitor Garcia | Tempel.es wrote:
> 5) Tomcat, creates ONE (or maybe SOME) Class object and call to the
>  _jspService on every script request
> 
> What happens if you handle Pool Coonections with a
> 'java.sql.Connection conn' variable declared into the definitions
> block "<%! %>"?
> 
> Happens than if you are donig multitheading and executing the same 
> sctipt in parallel you will mix up connections because evey thead
> is executing the same method in parallel and putting a different
> connection into the java.sql.Connection conn class variable.
> 
> I don't know if this is a tomcat bug.

Obviously it's not.

This is one of the many reasons why application logic has no business
being in a JSP. Whoever proposed (and, indeed approved) the
introduction of scriptlets (<% ... %>) should have been flogged.

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk7qFLAACgkQ9CaO5/Lv0PBjBgCfeCtAcWmeoSWJsva+VGEevvnU
xvoAoKCMa1i5YwxqbrHkiAeo9SKGe+Rr
=0kQv
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp is mixing up connections

[Pid]

On 15/12/2011 12:55, Aitor Garcia | Tempel.es wrote:
> I had read all the JNDI & JDBC Official & Unofficial documentation but &
> only found than you MUST close the connections.
> 
> There insn't references to where to declare variables.
> 
> Declaring into local scope forces that you have pass by reference the
> connection, statement and resultset in every function that handle the
> pool os use a class to handle the connection itsef.

Strongly recommend not doing any of this in a JSP.  Look up MVC.


p



> FIRMA
> 
> 
> Este  mensaje se dirige exclusivamente a su destinatario. Contiene
> información *CONFIDENCIAL* sometida a secreto profesional o cuya
> divulgación está prohibida por Ley.
> Si ha recibido este mensaje por error, debe saber que su lectura, copia
> y uso están prohibidos.
> Le rogamos nos lo comunique inmediatamente por esta misma vía o por
> teléfono 93 600 36 00  y proceda a su destrucción.
> El correo electrónico vía Internet no permite asegurar la
> confidencialidad de los mensajes que se transmiten ni su integridad o
> correcta recepción.
> *TEMPEL S.A.*no asume responsabilidad por estas circunstancias. Si el
> destinatario de este mensaje no consintiera la utilización del correo
> electrónico vía Internet y la grabación de los mensajes, rogamos lo
> ponga en nuestro conocimiento de forma inmediata.
> En cumplimiento de la Ley Orgánica 15/1999, de 13 de diciembre, de
> Protección de Datos de Carácter Personal le informamos de que sus datos
> personales y de empresa pasarán a formar parte de un fichero registrado
> ante la Agencia Española de Protección de Datos.
> Los datos personales que existen en nuestro poder están protegidos por
> nuestra Política de Seguridad, y no serán compartidos con ninguna otra
> empresa.
> Usted puede ejercitar sus derechos de acceso, rectificación, cancelación
> y oposición dirigiéndose por escrito a c/ Cobalto, 4 08907 L'Hospitalet
> de LLobregat (Barcelona).
> 
> 
> El 15/12/2011 13:40, Mark Thomas escribió:
>> On 15/12/2011 12:12, Aitor Garcia | Tempel.es wrote:
>>> I don't know if this is a tomcat bug.
>> This is clearly not a Tomcat bug. This comes under the category of "user
>> error".
>>
>> Mark
>>
>> -
>> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
>> For additional commands, e-mail: users-h...@tomcat.apache.org
>>


-- 

[key:62590808]



signature.asc
Description: OpenPGP digital signature

Re: dbcp is mixing up connections

[Aitor Garcia | Tempel.es]

  
  
I had read all the JNDI &
JDBC Official & Unofficial documentation but & only
found than you MUST close the connections.

There insn't references to where to declare variables.

Declaring into local scope forces that you have pass by
reference the connection, statement and resultset in every
function that handle the pool os use a class to handle the
connection itsef.

  
  
  


Este  mensaje se dirige
exclusivamente a su
destinatario. Contiene información CONFIDENCIAL
sometida a secreto profesional o cuya divulgación está
prohibida por Ley.
Si ha
recibido este mensaje por error, debe saber que su lectura,
copia y uso
están
prohibidos.
Le rogamos
nos lo comunique inmediatamente por esta misma vía o por
teléfono 93 600 36
00  y proceda a su destrucción.
El correo
electrónico vía Internet no permite asegurar la
confidencialidad de los
mensajes que se transmiten ni su integridad o correcta
recepción.
  TEMPEL
  S.A. no
asume responsabilidad por estas
circunstancias. Si el destinatario de este mensaje no
consintiera la
utilización del correo electrónico vía Internet y
la grabación de los mensajes,
rogamos lo ponga en nuestro conocimiento de forma inmediata.
En
cumplimiento de la
  Ley Orgánica 15/1999, de 13 de diciembre,
de
Protección de Datos
de Carácter Personal le informamos de que sus datos
personales y
de empresa
pasarán a formar parte de un fichero registrado ante la Agencia
  Española
de Protección de Datos.
Los datos
personales que existen en nuestro poder están protegidos por
nuestra Política
de Seguridad, y no serán compartidos con ninguna otra
empresa.
  Usted
puede
ejercitar sus derechos de acceso, rectificación,
cancelación y oposición
dirigiéndose por escrito a c/ Cobalto, 4 08907 L'Hospitalet
de
LLobregat
(Barcelona).

  


El 15/12/2011 13:40, Mark Thomas escribió:

  On 15/12/2011 12:12, Aitor Garcia | Tempel.es wrote:

  
I don't know if this is a tomcat bug.

  
  
This is clearly not a Tomcat bug. This comes under the category of "user
error".

Mark

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org



  

Re: dbcp is mixing up connections

[Mark Thomas]

On 15/12/2011 12:12, Aitor Garcia | Tempel.es wrote:
> I don't know if this is a tomcat bug.

This is clearly not a Tomcat bug. This comes under the category of "user
error".

Mark

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP deadlock similar to DBCP-270

[Mark Thomas]

On 31/08/2011 07:54, Neil Laurance wrote:
> Hi there,
> 
> We are using:
> 
> Red Hat Enterprise Linux Server release 5.4 (Tikanga)
> 
> Tomcat 6.0.29
> 
> We appear to be having a similar issue to:
> 
> https://issues.apache.org/jira/browse/DBCP-270
> 
> Except our stacktrace line-numbers are different.

Hmm. The stack trace indicates you are using Pool 1.4 but Tomcat 6.0.29
should be based on Pool 1.5.4 (which has fixed a lot of deadlock issues).

It looks like 6.0.29 was built with the wrong version of Commons Pool.

An upgrade to the latest 6.0.x should fix this.

Mark

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP deadlock similar to DBCP-270

[Konstantin Kolinko]

2011/8/31 Neil Laurance :
> Hi there,
>
> We are using:
>
> Red Hat Enterprise Linux Server release 5.4 (Tikanga)
>
> Tomcat 6.0.29
>
> (...)
>
> Is there any easy of confirming the version of dbcp we have? I exploded
>  tomcat-dbcp.jar and took a look, and nothing obvious ?

apache-tomcat-6.0.x-src.zip -> build.properties.default
See
commons-dbcp.version=
commons-pool.version=

also all version updates are mentioned in changelog either under
"General" or under "Other".

Best regards,
Konstantin Kolinko

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: dbcp with embedded tomcat7

[Konstantin Kolinko]

2011/2/7 Holger Veltrup :
>  InitialContext ic = new InitialContext();

In short:
1) InitialContext depends on classloader:  the context you created
here and the one that your webapp will create with "new
InitialContext()" are different.
2) Usually binding a pool after tomcat.start() will be too late if
webapp obtains the reference to the pool during its startup. Though if
it does jdbc lookup only when needed you will be fine.

Best regards,
Konstantin Kolinko

-
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

[Christopher Schultz]

-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

[Christopher Schultz]

-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

[sasidhar prabhakar]

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  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

[sasidhar prabhakar]

On Wed, Nov 10, 2010 at 3:02 AM, 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?
>

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

[Pid]

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

[Pid]

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

[Mark Thomas]

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

[Pid]

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: DBCP abandoned trace - unable to understand the leak

[Pid]

On 04/11/2010 15:41, Mikolaj Rydzewski wrote:
> 
> On Thu, 4 Nov 2010 10:37:25 -0500, "Propes, Barry L "
>  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: [OT] Re: DBCP abandoned trace - unable to understand the leak

[Pid]

On 04/11/2010 11:09, Peter Crowther wrote:
> On 4 November 2010 10:54, Mark Thomas  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

[Pid]

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

[sasidhar prabhakar]

Sorry for that. I changed it 300 seconds.

On Wed, Nov 10, 2010 at 2:12 AM, Pid  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
> >
>  
>  
> >
> > "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
> >  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

[Pid]

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
> 
 
 
> 
> "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
>  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

[sasidhar prabhakar]

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
>
> > 
> > 
>
> "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
> >>  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

[Christopher Schultz]

-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

> 
> 

"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
>>  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

[sasidhar prabhakar]

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











> 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
>  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

[Mikolaj Rydzewski]

On Thu, 4 Nov 2010 10:37:25 -0500, "Propes, Barry L " 
 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 

-
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

[Christopher Schultz]

-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
 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

[Propes, Barry L]

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

[Rob Gregory]

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

[sasidhar prabhakar]

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

[Rob Gregory]

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

[sasidhar prabhakar]

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

[Rob Gregory]

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  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

[sasidhar prabhakar]

On Thu, Nov 4, 2010 at 4:24 PM, Mark Thomas  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.

[OT] Re: DBCP abandoned trace - unable to understand the leak

[Peter Crowther]

On 4 November 2010 10:54, Mark Thomas  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

[Mark Thomas]

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

Re: DBCP abandoned trace - unable to understand the leak

[sasidhar prabhakar]

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

[Mikolaj Rydzewski]

On Thu, 4 Nov 2010 13:20:48 +0530, sasidhar prabhakar 
 wrote:

We are using struts and following DAO pattern.


Looks fine. Does the problem occur everytime?

--
Mikolaj Rydzewski 

-
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

[sasidhar prabhakar]

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

[Mikolaj Rydzewski]

On Thu, 4 Nov 2010 11:08:07 +0530, sasidhar prabhakar 
 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 

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP connections not in accordance with netstat output

[Phil Steitz]

On 10/20/10 5:32 AM, Felix Schumacher wrote:

Hi Marc,

Am Mittwoch, den 20.10.2010, 00:54 +0200 schrieb Marc Wilmots:

Hi List,

I installed Lambda Probe to debug a problem that I'm having with a Liferay
portal (5.1.2):

Tomcat: 6.0.26 with dbcp
JDK: 1.6.0_18
DB: Oracle 10.2.0.4 (ojdbc14)
RHEL 5.4 64Bits

When launching a stress test with JMeter, Lambda Probe indicates that my
connection pool (150 connections (I know..extremely high)) is out of free
connections (150 Busy and Established). However, when I do a netstat, there
are only 20-30 established connections to the database.

I would rather believe netstat over Probe, however when Lambda Probe
indicates that my pool is out of connections I noticed that the stress test
is going extremely slow and several requests are failing because of a
response time out (60secs). (The Tomcat connection pool (500 threads) gets
filled as well)

Try generating thread-dumps when your tomcat is under that load. That
way you can see where you might have problems.



Why could it be that Probe indicates 150 established connections and netstat
only 20-30?

I think Probe differentiates between established (pooled) connections
and busy (currently in use) connections. Maybe the established
connections are lazy and have no direct connection to the database.
Haven't checked it, so they very well be in another state, like
abandoned and cut down by a firewall... Your best bet is to look at the
thread-dumps.


According to the DBA, the DB is not stressed at all.

As soon as the stress test stops, the pool recovers itself. Netstat and
Lambda numbers (idle connections) are now exactly the same.

dbcp will shrink the pool to the desired "minIdle" settings when no new
connections are needed.


You mean "maxIdle."  If abandoned connection cleanup is enabled, 
that will also eventually run at configured intervals, closing 
abandoned connections if they are still physically open and updating 
pool counters to reflect recovery.


Can you post your full DBCP configuration?  Have you verified that 
your code is closing all connections that it borrows from the pool?


Phil


HTH
  Felix


I know it's extremely (if not impossible) to indicate me what the problem
might be. I just would like to know how I should proceed to investigate this
problem, as I'm currently out of ideas.


Thanks in advance!




-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org




-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP connections not in accordance with netstat output

[Felix Schumacher]

Hi Marc,

Am Mittwoch, den 20.10.2010, 00:54 +0200 schrieb Marc Wilmots:
> Hi List,
> 
> I installed Lambda Probe to debug a problem that I'm having with a Liferay
> portal (5.1.2):
> 
> Tomcat: 6.0.26 with dbcp
> JDK: 1.6.0_18
> DB: Oracle 10.2.0.4 (ojdbc14)
> RHEL 5.4 64Bits
> 
> When launching a stress test with JMeter, Lambda Probe indicates that my
> connection pool (150 connections (I know..extremely high)) is out of free
> connections (150 Busy and Established). However, when I do a netstat, there
> are only 20-30 established connections to the database.
> 
> I would rather believe netstat over Probe, however when Lambda Probe
> indicates that my pool is out of connections I noticed that the stress test
> is going extremely slow and several requests are failing because of a
> response time out (60secs). (The Tomcat connection pool (500 threads) gets
> filled as well)
Try generating thread-dumps when your tomcat is under that load. That
way you can see where you might have problems.

> 
> Why could it be that Probe indicates 150 established connections and netstat
> only 20-30?
I think Probe differentiates between established (pooled) connections
and busy (currently in use) connections. Maybe the established
connections are lazy and have no direct connection to the database.
Haven't checked it, so they very well be in another state, like
abandoned and cut down by a firewall... Your best bet is to look at the
thread-dumps.

> According to the DBA, the DB is not stressed at all.
> 
> As soon as the stress test stops, the pool recovers itself. Netstat and
> Lambda numbers (idle connections) are now exactly the same.
dbcp will shrink the pool to the desired "minIdle" settings when no new
connections are needed.

HTH
 Felix
> 
> I know it's extremely (if not impossible) to indicate me what the problem
> might be. I just would like to know how I should proceed to investigate this
> problem, as I'm currently out of ideas.
> 
> 
> Thanks in advance!



-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Elli Albek]

Hi,
>From what I can remember, which has been a long time ago, requests are
being handled by servlets after the shutdown process has started and
the pool was closed. I would imagine that this is a violation of the
spec as well, but I am not sure. You would imagine that JNDI resources
should be available for the web app until it is closed, as well as
during the shutdown process. Context listeners should be able to
access the pool via JNDI even during shutdown.

The problem with "about to be shut down" is that it is not fully shut
down. It is not shut down at least in the sense that the context
listener is running. There may be another listener that tries to use
the database for flushing asynch writes. So I rather keep connection
management outside of the app. Thats why the tomcat listener seems
like a better choice, especially when there is a dedicated event for
after stop.

>From the testing, after stop is truly the last event in the chain and
runs after the web app is completely shut down and all its internal
listeners are called. In other words, objects that are started outside
the app should not be shut in from within the app if possible. The web
app should only stop what it starts by itself.

E

On 10/28/09, Christopher Schultz  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Elli,
>
> On 10/28/2009 12:21 AM, Elli Albek wrote:
>> In terms of listeners, I saw that Tomcat executes requests while in
>> the closing process.
>
> That's a problem. What kind of listener are you using?
>
>>> My understanding was that:
>>>
>>> 1. Tomcat does not remove the connection pool
>>> 2. Once the shutdown process has started, no new requests will be
>>> handled by the currently-shutting-down context
>>>
>>> Could these two items give the result you are observing? It's easy
>>> enough to have your webapp close the DataSource (even though it's kinda
>>> stupid that Tomcat doesn't do it for you).
>>
>> That depends on which shutdown it is. Understanding 2 is not correct
>> from my debugging. You can have a sequence of:
>> 1. Connection pool closed
>> 2. Request sent to servlet
>
> The documentation for ServletContextListener.contextDestroyed says:
>
> "
> Notification that the servlet context is about to be shut down. All
> servlets and filters have been destroy()ed before any
> ServletContextListeners are notified of context destruction.
> "
>
> Assuming that servlets aren't being re-initialized to service new
> requests, using a ServletContextListener ought to work in this case. I'm
> happy to hear an explanation for why my assumption isn't true, but this
> woul dbe my expectation. If your webapp is handling requests after the
> ServletContextListener.contextDestroyed method is called, I would
> consider that a violation of the servlet specification.
>
>> If you want to add a listener that closes the pool, I would try
>> adding tomcat lifecycle listener to the context. You can handle
>> event Lifecycle.AFTER_STOP_EVENT. Try both listeners (web app and
>> tomcat) and see which one runs last, and put the code there.
>
> I'm guessing that Tomcat's listener would execute after the
> ServletContextListener, since AFTER_STOP_EVENT indicates that the stop
> has occurred, while the servlet-API listener says it "is about to be
> shut down".
>
>> Then the vision of J2EE designers of "separation of roles"
>> materializes :)
>
> Something tells me those guys didn't exactly invent the concept of
> separation of roles.
>
> - -chris
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.10 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkrodboACgkQ9CaO5/Lv0PAhGQCgooVyE8753TIz635X2lSvZJOV
> j8cAoJnX6kDBSuBscVlwjEX0kZ5SlAun
> =u9j7
> -END PGP SIGNATURE-
>
> -
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>
>

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Elli,

On 10/28/2009 12:21 AM, Elli Albek wrote:
> In terms of listeners, I saw that Tomcat executes requests while in
> the closing process.

That's a problem. What kind of listener are you using?

>> My understanding was that:
>>
>> 1. Tomcat does not remove the connection pool
>> 2. Once the shutdown process has started, no new requests will be
>> handled by the currently-shutting-down context
>>
>> Could these two items give the result you are observing? It's easy
>> enough to have your webapp close the DataSource (even though it's kinda
>> stupid that Tomcat doesn't do it for you).
> 
> That depends on which shutdown it is. Understanding 2 is not correct
> from my debugging. You can have a sequence of:
> 1. Connection pool closed
> 2. Request sent to servlet

The documentation for ServletContextListener.contextDestroyed says:

"
Notification that the servlet context is about to be shut down. All
servlets and filters have been destroy()ed before any
ServletContextListeners are notified of context destruction.
"

Assuming that servlets aren't being re-initialized to service new
requests, using a ServletContextListener ought to work in this case. I'm
happy to hear an explanation for why my assumption isn't true, but this
woul dbe my expectation. If your webapp is handling requests after the
ServletContextListener.contextDestroyed method is called, I would
consider that a violation of the servlet specification.

> If you want to add a listener that closes the pool, I would try
> adding tomcat lifecycle listener to the context. You can handle
> event Lifecycle.AFTER_STOP_EVENT. Try both listeners (web app and
> tomcat) and see which one runs last, and put the code there.

I'm guessing that Tomcat's listener would execute after the
ServletContextListener, since AFTER_STOP_EVENT indicates that the stop
has occurred, while the servlet-API listener says it "is about to be
shut down".

> Then the vision of J2EE designers of "separation of roles"
> materializes :)

Something tells me those guys didn't exactly invent the concept of
separation of roles.

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrodboACgkQ9CaO5/Lv0PAhGQCgooVyE8753TIz635X2lSvZJOV
j8cAoJnX6kDBSuBscVlwjEX0kZ5SlAun
=u9j7
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Elli Albek]

Thanks for your replies. I agree that Tomcat should be responsible for
all objects that are configured in Tomcat, and the web app should be
responsible for objects that are created by the webapp. Currently it
does not happen properly in tomcat. This is not related to DBCP code,
it is all Tomcat code.

In terms of listeners, I saw that Tomcat executes requests while in
the closing process. This may mean that after the context listener
closed the pool, a new request will cause it to initialize again. We
had a similar problem of things being created after shutdown started,
and there are many permutations of listeners and sequences to test.
Lots of debugging to do here. This again is irrespective of the
listener which I am sure is closing the connection pool, it is a
tomcat lifecycle issue.

> It is built into Tomcat. Just read the docs for JDBC DataSources:
> http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html

I am talking about something else. Basically the XML is short:





No configuration in XML other than JNDI related bindings.

> My understanding was that:
>
> 1. Tomcat does not remove the connection pool
> 2. Once the shutdown process has started, no new requests will be
> handled by the currently-shutting-down context
>
> Could these two items give the result you are observing? It's easy
> enough to have your webapp close the DataSource (even though it's kinda
> stupid that Tomcat doesn't do it for you).

That depends on which shutdown it is. Understanding 2 is not correct
from my debugging. You can have a sequence of:
1. Connection pool closed
2. Request sent to servlet

This happens during tomcat shutdown and will trigger a new connection.
I suspect that similar sequence exists during redeploy. I cant recall
exactly what happened in this case, but I remember that it was in more
than one scenario. We decided to shut down tomcat every time and
forget about it, even though we do have a listener that closes pools,
along the same lines as the listener that you wrote I imagine.

If you want to add a listener that closes the pool, I would try adding
tomcat lifecycle listener to the context. You can handle event
Lifecycle.AFTER_STOP_EVENT. Try both listeners (web app and tomcat)
and see which one runs last, and put the code there. You have higher
probability of catching an open connection if you do it later. If the
listener is configured via tomcat and not the web app, you also get a
configuration that each party is responsible for cleaning up its own
bugs, as opposed to web app cleaning up tomcat bugs. Then the vision
of J2EE designers of "separation of roles" materializes :)

Elli

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Elli,

On 10/26/2009 10:59 PM, Elli Albek wrote:
> To disable caching in DBCP, use configuration option:
> poolPreparedStatements=false

Note that this is the default. Presumably, if you don't know if your
prepared statements are being cached, then they are not being cached.

[Side note: MySQL users should be aware that enabling server-side
prepared statements on your connections will almost certainly result in
disaster. See MysQL's bug database and Connector/J changelog for more
information.]

> I don’t remember where exactly you specify it

In your  definition in context.xml/appName.xml/server.xml

> This is actually something that I would like
> to see built into Tomcat/DBCP (property file configuration). Your system
> admin will thank you.

It is built into Tomcat. Just read the docs for JDBC DataSources:
http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html

Though not specifically mentioned, any attribute set on the 
element in your XML will be set on the DataSource that gets created.
That includes things like logAbandoned, poolPreparedStatements,
defaultTransactionIsolation, etc.

> It may not be possible to use the mysql caching algorithms, since they may
> use features that are not publicly available through JDBC and are part of
> their protocol. I don’t know the source well enough to comment on how it
> works internally.

Also, the Connector/J code itself is released under the GPL which may or
may not be compatible with the APL.

> Specific example of leaks:
> 1.Close tomcat
> 2.Connection pool is removed.
> 3.Request comes in to the war file (the one that is in the closing
> process)
> 4.Request gets processed.
> 5.Servlet needs a connection from DBCP.
> 6.Tomcat does not find the DBCP pool in JNDI, so it recreates it.
> 7.The newly created connection pool will not be used after this request,
> and will not be closed. If the pool pings the connection, it will stay open.

My understanding was that:

1. Tomcat does not remove the connection pool
2. Once the shutdown process has started, no new requests will be
handled by the currently-shutting-down context

Could these two items give the result you are observing? It's easy
enough to have your webapp close the DataSource (even though it's kinda
stupid that Tomcat doesn't do it for you).

> After all this work, we just shut down tomcat when we update war files.

I would always advocate this approach IMHO: just in case you've damaged
your server at some point, starting fresh with a new deployment is a
good idea.

> 2.Call shutdown callbacks of different APIs, give developer an option to
> deal with it. For example JMX shutdown process does not happen correctly.

Please log a bug for any legitimate complaints that you may have.
Remember that Tomcat shouldn't shut down anything that the webapp itself
started.

> We got to the
> conclusion that shutting down objects in tomcat was little chaotic and no
> one could say in confidence that using a certain listener API will work
> better than another without spending a few days of tracing the code and
> doing lots of tests.

The servlet specification makes it clear which listeners should be
notified of various events in a very specific order. If Tomcat is not
following these criteria, there's a bug somewhere and you should report
it. If you are using Tomcat-specific listeners, they should be properly
documented and working per the docs. If they are not, please file a bug
and help get things fixed.

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrnTD8ACgkQ9CaO5/Lv0PBR3ACeNHW25/eOTWp7F905j7nwBVBF
nJ4AoJDxZKGAAD0MwBvZipyPV7lCOATi
=Cle2
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Elli,

On 10/26/2009 9:01 PM, Elli Albek wrote:
> 2. If your JDBC driver supports caching of prepared statements and 
> metadata, do it in the driver and disable this in DBCP. IMO DBCP
> does a poor job at best in caching. We use mysql and its JDBC driver
> is doing an excellent job.

I don't believe that DBCP caches any metadata, and there are no settings
to tweak that AFAICT.

> 3. Your JDBC driver may already be caching metadata that DBCP is 
> caching. In this case you are caching the same data twice. Make sure 
> it dose not happen, it is a big memory overload on the JVM.

Do you mean PreparedStatements?

> 4. Tomcat has a problem doing a clean shutdown of DBCP, and other
> JNDI resources. I traced in the debugger dangling connection pools
> that are created during the shutdown process. If your pool is
> configure to ping the connections once in a while, they can stay open
> for a long time, possibly forever. Our solution is custom extension
> that cleans up pools, which works in conjunction with our extended
> implementation of DBCP.

This was recently discussed on this list:

http://tomcat.markmail.org/search/?q=%22right+way+to+close+database+connection+pool%22+list%3Aorg.apache.tomcat.users#query:%22right%20way%20to%20close%20database%20connection%20pool%22%20list%3Aorg.apache.tomcat.users%20order%3Adate-forward+page:1+mid:xq2q5wk6skv3exoz+state:results

If that link doesn't work, search MarkMail for "Right way to close
database connection pool" which is the subject of the thread.

I contributed the code for a ServletContextListener that you can use to
shut down your DataSource just before the servlet context is discarded.
This ought to resolve any issues you may be having, and you're of course
welcome to look at my code and/or make comments.

Note that this resource issue has nothing to do with commons-DBCP: it is
entirely Tomcat's "fault" that the DataSource is allowed to live forever.

> I am not aware of any way to completely avoid dangling connection
> pool after hot deployment under load. We tried to fix this but it got
> too complicated, it is much easier to restart Tomcat and swallow the 
> bitter pill.

I believe tools like LambdaProbe will also allow you to shut down
DataSource objects as well. If you can afford to stop your webapp, close
the DataSource, then re-reploy, then this may be an option for you. Of
course, if you have time to do this, you probably have time to stop
Tomcat, update your WAR file, and start Tomcat again.

> You can still do hot deployment of war files that do not access the
> database, though it is possible that the same leaks will leave lots
> of hanging objects of other types (like email clients, JMS clients,
> thread pools, HttpClient, etc).

These things ought to clean themselves up by using
ServletContextListeners as well. Certainly, it's surprising (to me, at
least) that Tomcat creates and configures a resource yet never cleans it
up, but anything your webapp does also needs to be un-done by your webapp.

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrnSOgACgkQ9CaO5/Lv0PC7ywCcCdxixuwTCCm1FdxlFxmu5X9u
GkMAnjCcuSwqBCwSyDKd2RgwIvP+9iM4
=8NRT
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Elli Albek wrote:
>2. If your JDBC driver supports caching of prepared statements and
>metadata, do it in the driver and disable this in DBCP. IMO DBCP does
>a poor job at best in caching. We use mysql and its JDBC driver is
>doing an excellent job.

It didn't occur to me that that was available.

>3. Your JDBC driver may already be caching metadata that DBCP is
>caching. In this case you are caching the same data twice. Make sure
>it dose not happen, it is a big memory overload on the JVM.

Interesting.  I need to do more research.

>B. Avoid hot deployment of apps by shutting down tomcat before
>updates. This is safer, but also not 100% clean.

We're actually doing that already, but for other reasons.  We currently
have different servers for different parts of the world and schedule down
time for off-peak periods for the regions they serve.

Thanks for the info.

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Elli Albek]

Hi,
More information about tomcat shutdown and object swapping probably belongs
in the development list. It is quite a bit of work to extend DBCP and write
extensions to tomcat, and at the end of the day most of those problems I
would consider as bugs. DBCP specifically cannot be easily extended, we had
to use the original source files with some modifications, my least favorite
option.

MySQL documentation covers their JDBC client configuration very well. The
number of options is little overwhelming, but after some time and a few
tests it is possible to find a configuration that is a match to the system
resources.
See:
http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html
The configuration is version specific, find your version before reading.

About the database clearing up dangling connections, you will have to read
the database documentation. I hate to say RTFM, but there is nothing else I
can say here :)

To disable caching in DBCP, use configuration option:
poolPreparedStatements=false

I don’t remember where exactly you specify it, since our extension just
passes a property file to DBCP. This is actually something that I would like
to see built into Tomcat/DBCP (property file configuration). Your system
admin will thank you.

To troubleshoot where caching is being done, your best bet is stepping
through the code with a debugger. Make sure the configuration of the test
system mirrors the production system.

It may not be possible to use the mysql caching algorithms, since they may
use features that are not publicly available through JDBC and are part of
their protocol. I don’t know the source well enough to comment on how it
works internally.

Specific example of leaks:
1.Close tomcat
2.Connection pool is removed.
3.Request comes in to the war file (the one that is in the closing
process)
4.Request gets processed.
5.Servlet needs a connection from DBCP.
6.Tomcat does not find the DBCP pool in JNDI, so it recreates it.
7.The newly created connection pool will not be used after this request,
and will not be closed. If the pool pings the connection, it will stay open.

A similar path I believe exists while hot deploying a war file, which also
exhibits the problem of handling requests in the middle of shutdown.

It has been a while since we worked on it, the details may vary from what is
listed above. However, one thing I do remember is that after some objects
were shut down Tomcat was still processing requests that required those
objects, and there was more than one way to reproduce it.

After all this work, we just shut down tomcat when we update war files.

It is possible to fix this. Two things that I would start with:
1.Standard server shutdown order. This is too much information for this
list.
2.Call shutdown callbacks of different APIs, give developer an option to
deal with it. For example JMX shutdown process does not happen correctly.

You may be able to fix this via listeners on various levels. We got to the
conclusion that shutting down objects in tomcat was little chaotic and no
one could say in confidence that using a certain listener API will work
better than another without spending a few days of tracing the code and
doing lots of tests. The problem is that in a simple invocation everything
seems to work fine, but if you throw in a few threads and methods that take
some time to respond then tracing becomes complicated and time intensive.
>From my past experience multithreaded debugging can be time intensive,
especially when it involves so many components and possible paths as we have
in a full blown application container.

To make a long story short:
1.Disable caching in DBCP
2.Enable caching in your JDBC client
3.Shut down tomcat when you replace war files or when you change
configuration.

Elli

On Mon, Oct 26, 2009 at 6:54 PM, Martin Gainty  wrote:

>
> mg>good work
>
> > From: e...@sustainlane.com
> > To: users@tomcat.apache.org
> >
> > Hi,
> > I did not follow this thread form the beginning, but I can provide a
> > few tips about using connection pools in tomcat.
> > 1. DBCP has quite a few problems in the area of scalability. We have
> > our own modified version of the source that makes it more concurrent,
> > and I believe some of those changes were integrated into DBCP. Use the
> > latest stable version of DBCP form the commons project. It is a jar
> > file in tomcat that you can easily replace. The fixes do not require
> > using concurrent, that part of the code is not causing the problems.
> > 2. If your JDBC driver supports caching of prepared statements and
> > metadata, do it in the driver and disable this in DBCP. IMO DBCP does
> > a poor job at best in caching. We use mysql and its JDBC driver is
> > doing an excellent job.
> mg>is it possible to port the working (MySQL) Driver caching algorithms to
> DBCP?
>
> > 3. Your JDBC driver may already be caching metadata that DBCP is
> > caching. In

RE: DBCP woes (running out of cursors).

[Martin Gainty]

mg>good work

> From: e...@sustainlane.com
> To: users@tomcat.apache.org
> 
> Hi,
> I did not follow this thread form the beginning, but I can provide a
> few tips about using connection pools in tomcat.
> 1. DBCP has quite a few problems in the area of scalability. We have
> our own modified version of the source that makes it more concurrent,
> and I believe some of those changes were integrated into DBCP. Use the
> latest stable version of DBCP form the commons project. It is a jar
> file in tomcat that you can easily replace. The fixes do not require
> using concurrent, that part of the code is not causing the problems.
> 2. If your JDBC driver supports caching of prepared statements and
> metadata, do it in the driver and disable this in DBCP. IMO DBCP does
> a poor job at best in caching. We use mysql and its JDBC driver is
> doing an excellent job.
mg>is it possible to port the working (MySQL) Driver caching algorithms to DBCP?

> 3. Your JDBC driver may already be caching metadata that DBCP is
> caching. In this case you are caching the same data twice. Make sure
> it dose not happen, it is a big memory overload on the JVM.
mg>how to determine which metadata is being cached?

> 4. Tomcat has a problem doing a clean shutdown of DBCP, and other JNDI
> resources. I traced in the debugger dangling connection pools that are
> created during the shutdown process. If your pool is configure to ping
> the connections once in a while, they can stay open for a long time,
> possibly forever. Our solution is custom extension that cleans up
> pools, which works in conjunction with our extended implementation of
> DBCP.
mg>any way to factor the cleanup code to DBCP?

> 5. The connection pool leak is caused mostly when war files are
> replaced under load. If you are experiencing a problem of leaks in
> those conditions, then some common options are:
> A. Write custom extension to the pooling mechanism as we did. This is
> not a 100% solution.
mg>any specific examples?

> B. Avoid hot deployment of apps by shutting down tomcat before
> updates. This is safer, but also not 100% clean.
mg>any way to factor the cleanup code to TC hot deployment code?

> C. Block Tomcat during the update. If you have a load balancer,
> redirect traffic to other tomcat instances, and then do the update
> while tomcat has no load. This reduces the problems significantly.
mg>tough to request the ops people to block all TC instances to be updated
mg>personnel would have to be allocated to showup on off hours..
mg>this could be the most resource-intensive (most expensive) of all options 
> 
> When you do a full tomcat shutdown, there will still be connections
> that are not closed, but the process itself will finish, and the
> database will clean up the connections after some time. 
mg>how is the time span assigned?

>This is of course not as clean as closing all the connections on server 
>shutdown,
> but I don't know of any better option. I believe our custom cleanup
> code does close most connections on shutdown, but I have no 100%
> certainty or evidence that this is actually true. However it does do a
> lot of closing that did not happen before.
mg>if your algorithm is based on any events such as ContainerDestroy i'm 
thinking a listener could accomplish the objective?

> I am not aware of any way to completely avoid dangling connection pool
> after hot deployment under load. We tried to fix this but it got too
> complicated, it is much easier to restart Tomcat and swallow the
> bitter pill. You can still do hot deployment of war files that do not
> access the database, though it is possible that the same leaks will
> leave lots of hanging objects of other types (like email clients, JMS
> clients, thread pools, HttpClient, etc).
mg>anything you can suggest to clean up these orphaned resources will help 
everyone

> E
mg>many thanks for the thoughtful commentary
mg>ccing the commons-users list as I'm sure they would be very interested in 
your solution
mg>Martin

> On 10/26/09, Mark Thomas  wrote:
> > Bill Davidson wrote:
> >> Christopher Schultz wrote:
> >>>When you've played with it for a bit, tell us how things turned out.
> >>
> >> It's looking like optimal is caching about 40 PreparedStatement objects.
> >> However, I should qualify that noting that it's with our application and
> >> specifically with our little pummeling benchmark, which requests a
> >> specific
> >> subset of services, and probably isn't even a great test of real world
> >> traffic.
> >> It was mainly designed to see how the app handled being heavily stressed
> >> (like getting hit with 1000 requests at a time).
> >>
> >> The system is still about 3-4% slower with DBCP than with our old pooling
> >> library.  Our old pooling library did not wrap the Connection objects like
> >> DBCP does, though it did close old Connection objects so that they only
> >> got reused for up to two minutes at a time.  I'm actually a little
> >> surprised by
> >> this.  I would have 

Re: DBCP woes (running out of cursors).

[Elli Albek]

Hi,
I did not follow this thread form the beginning, but I can provide a
few tips about using connection pools in tomcat.
1. DBCP has quite a few problems in the area of scalability. We have
our own modified version of the source that makes it more concurrent,
and I believe some of those changes were integrated into DBCP. Use the
latest stable version of DBCP form the commons project. It is a jar
file in tomcat that you can easily replace. The fixes do not require
using concurrent, that part of the code is not causing the problems.
2. If your JDBC driver supports caching of prepared statements and
metadata, do it in the driver and disable this in DBCP. IMO DBCP does
a poor job at best in caching. We use mysql and its JDBC driver is
doing an excellent job.
3. Your JDBC driver may already be caching metadata that DBCP is
caching. In this case you are caching the same data twice. Make sure
it dose not happen, it is a big memory overload on the JVM.
4. Tomcat has a problem doing a clean shutdown of DBCP, and other JNDI
resources. I traced in the debugger dangling connection pools that are
created during the shutdown process. If your pool is configure to ping
the connections once in a while, they can stay open for a long time,
possibly forever. Our solution is custom extension that cleans up
pools, which works in conjunction with our extended implementation of
DBCP.
5. The connection pool leak is caused mostly when war files are
replaced under load. If you are experiencing a problem of leaks in
those conditions, then some common options are:
A. Write custom extension to the pooling mechanism as we did. This is
not a 100% solution.
B. Avoid hot deployment of apps by shutting down tomcat before
updates. This is safer, but also not 100% clean.
C. Block Tomcat during the update. If you have a load balancer,
redirect traffic to other tomcat instances, and then do the update
while tomcat has no load. This reduces the problems significantly.

When you do a full tomcat shutdown, there will still be connections
that are not closed, but the process itself will finish, and the
database will clean up the connections after some time. This is of
course not as clean as closing all the connections on server shutdown,
but I don't know of any better option. I believe our custom cleanup
code does close most connections on shutdown, but I have no 100%
certainty or evidence that this is actually true. However it does do a
lot of closing that did not happen before.

I am not aware of any way to completely avoid dangling connection pool
after hot deployment under load. We tried to fix this but it got too
complicated, it is much easier to restart Tomcat and swallow the
bitter pill. You can still do hot deployment of war files that do not
access the database, though it is possible that the same leaks will
leave lots of hanging objects of other types (like email clients, JMS
clients, thread pools, HttpClient, etc).

E

On 10/26/09, Mark Thomas  wrote:
> Bill Davidson wrote:
>> Christopher Schultz wrote:
>>>When you've played with it for a bit, tell us how things turned out.
>>
>> It's looking like optimal is caching about 40 PreparedStatement objects.
>> However, I should qualify that noting that it's with our application and
>> specifically with our little pummeling benchmark, which requests a
>> specific
>> subset of services, and probably isn't even a great test of real world
>> traffic.
>> It was mainly designed to see how the app handled being heavily stressed
>> (like getting hit with 1000 requests at a time).
>>
>> The system is still about 3-4% slower with DBCP than with our old pooling
>> library.  Our old pooling library did not wrap the Connection objects like
>> DBCP does, though it did close old Connection objects so that they only
>> got reused for up to two minutes at a time.  I'm actually a little
>> surprised by
>> this.  I would have expected that to make DBCP faster, since it keeps them
>> open.
>
> The current DBCP (actually commons-pool) can struggle under very heavy
> load in multi-thread environments. There are plans afoot for a pool 2.0
> that will be based on java.util.concurrent that should enable much
> better multi-threaded performance.
>
> As always, it is best to check with a profiler to see what is actually
> slowing you down.
>
> Mark
>
>
>
>
> -
> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
> For additional commands, e-mail: users-h...@tomcat.apache.org
>
>

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Mark Thomas]

Bill Davidson wrote:
> Christopher Schultz wrote:
>>When you've played with it for a bit, tell us how things turned out.
> 
> It's looking like optimal is caching about 40 PreparedStatement objects.
> However, I should qualify that noting that it's with our application and
> specifically with our little pummeling benchmark, which requests a specific
> subset of services, and probably isn't even a great test of real world
> traffic.
> It was mainly designed to see how the app handled being heavily stressed
> (like getting hit with 1000 requests at a time).
> 
> The system is still about 3-4% slower with DBCP than with our old pooling
> library.  Our old pooling library did not wrap the Connection objects like
> DBCP does, though it did close old Connection objects so that they only
> got reused for up to two minutes at a time.  I'm actually a little
> surprised by
> this.  I would have expected that to make DBCP faster, since it keeps them
> open.

The current DBCP (actually commons-pool) can struggle under very heavy
load in multi-thread environments. There are plans afoot for a pool 2.0
that will be based on java.util.concurrent that should enable much
better multi-threaded performance.

As always, it is best to check with a profiler to see what is actually
slowing you down.

Mark




-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Christopher Schultz wrote:
>When you've played with it for a bit, tell us how things turned out.

It's looking like optimal is caching about 40 PreparedStatement objects.
However, I should qualify that noting that it's with our application and
specifically with our little pummeling benchmark, which requests a specific
subset of services, and probably isn't even a great test of real world 
traffic.

It was mainly designed to see how the app handled being heavily stressed
(like getting hit with 1000 requests at a time).

The system is still about 3-4% slower with DBCP than with our old pooling
library.  Our old pooling library did not wrap the Connection objects like
DBCP does, though it did close old Connection objects so that they only
got reused for up to two minutes at a time.  I'm actually a little 
surprised by

this.  I would have expected that to make DBCP faster, since it keeps them
open.


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

I already got a response to the ehancement request.

Apparently the documentation will be changed to this:

   Make sure your connection has some resources left for the other
   statements.  Pooling PreparedStatements may keep their cursors open in
   the database, causing a connection to run out of cursors, especially
   if maxOpenPreparedStatements is left at the default (unlimited) and
   an application opens a large number of different PreparedStatements
   per connection. To avoid this problem, maxOpenPreparedStatements
   should be set to a value less than the maximum number of cursors
   that can be open on a Connection.

Looks good to me.  Not sure if it will have to wait for 1.3 or if they will
work it into the current documentation after some review.

Bill Davidson wrote:

Christopher Schultz wrote:
>I see you've cross-posted to commons-user. That's good, but you probably
>want to file an actually bug report / enhancement request for the
>documentation.

Filed.  Key: DBCP-301





-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Christopher Schultz wrote:
>I see you've cross-posted to commons-user. That's good, but you probably
>want to file an actually bug report / enhancement request for the
>documentation.

Filed.  Key: DBCP-301



-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bill,

On 10/19/2009 3:14 PM, Bill Davidson wrote:
> Christopher Schultz wrote:
>> I'm curious about the usefulness of caching prepared statements in
>> general, though. What is the default maxOpenPreparedStatements setting
>> and what did you set it to in order to get it to work out well for you?
>>   
> 
> The default is unlimited, which is the problem.  I've been playing with
> different settings to try and find the best one, but obviously, it needs to
> be less than 300.
> 
>> If you have 100 different prepared statements in your code and you are
>> only allowing a connection to cache, say, 5 of them, then I have to
>> expect that you'll be constantly thrashing your cache as new statements
>> are prepared and executed. Do you see a performance gain between
>> disabling statement caching and enabling it, but setting the cache size
>> relatively low?
>>   
> 
> That's one of the things I'm hoping to find out.

Sounds good. When you've played with it for a bit, tell us how things
turned out.

Good luck,
- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrcvhoACgkQ9CaO5/Lv0PAEZQCfSkIv2eVTjZiKNtIA4x4EF5Ah
U1wAn05svLDn6hYk+u6iHIDDd6/ggmU9
=BmOv
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Christopher Schultz wrote:

I'm curious about the usefulness of caching prepared statements in
general, though. What is the default maxOpenPreparedStatements setting
and what did you set it to in order to get it to work out well for you?
  


The default is unlimited, which is the problem.  I've been playing with
different settings to try and find the best one, but obviously, it needs to
be less than 300.


If you have 100 different prepared statements in your code and you are
only allowing a connection to cache, say, 5 of them, then I have to
expect that you'll be constantly thrashing your cache as new statements
are prepared and executed. Do you see a performance gain between
disabling statement caching and enabling it, but setting the cache size
relatively low?
  


That's one of the things I'm hoping to find out.




-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bill,

On 10/16/2009 5:36 PM, Bill Davidson wrote:
> Bill Davidson wrote:
>>Could maxOpenPreparedStatements possibly fix this?
> 
> Apparently it does.

Glad to get the bottom of this problem, though I'm not entirely sure
where it leaves you.

> The DBCP config docs need a better warning on poolPreparedStatements:
> 
> "*NOTE* - Make sure your connection has some resources left for the
> other statements."
> 
> just doesn't quite cut it.

I see you've cross-posted to commons-user. That's good, but you probably
want to file an actually bug report / enhancement request for the
documentation.

As for your performance issues, I'm not sure what to tell you. There are
probably opportunities for you to optimize certain queries, but if
changing the caching of prepared statements is changing your performance
significantly, then I wouldn't be surprised if you are simply running
into performance problems with Oracle's query parser. Again, talking to
a DBA would be very beneficial.

I'm curious about the usefulness of caching prepared statements in
general, though. What is the default maxOpenPreparedStatements setting
and what did you set it to in order to get it to work out well for you?
If you have 100 different prepared statements in your code and you are
only allowing a connection to cache, say, 5 of them, then I have to
expect that you'll be constantly thrashing your cache as new statements
are prepared and executed. Do you see a performance gain between
disabling statement caching and enabling it, but setting the cache size
relatively low?

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrcewcACgkQ9CaO5/Lv0PDfhgCff1Q4gyvajWR/oYyZSiRJhX+I
RQkAoImJjWXr5dYOK37kxDttddhEBU4X
=b9v7
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Bill Davidson wrote:
>Could maxOpenPreparedStatements possibly fix this?

Apparently it does.

The DBCP config docs need a better warning on poolPreparedStatements:

"*NOTE* - Make sure your connection has some resources left for the 
other statements."


just doesn't quite cut it.  Something more like:

"Pooling PreparedStatement's may keep their cursors open in the database,
causing you to run out of cursors.  You should also set
maxOpenPreparedStatements to some value less than the maximum number
of cursors you can have on a Connection."

or something along those lines.

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Just thinking about this some more

So apparently, when I was using poolPreparedStatements="true", and
I did conn.prepareStatement(SomeSQLString), I was getting back a
Statement object created by DBCP to be pooled.  When I called close()
on that statement, it did not really close(), which was leaving lots of open
cursors around.

Could maxOpenPreparedStatements possibly fix this?





-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Christopher Schultz wrote:
>Uh, oh. Are you doing something like this:

Possibly.  I didn't write that code.

>If you're doing that, then you're probably making a big mistake. Are you
>storing connections in sessions or anything like that? Yuk.

For certain transactional operations, I think it is.

>>1. Thread 1 returns a connection with an uncommitted transaction to 
the pool.

>Mistake: see above.

I know it's a mistake.  I'm not sure that that is what is happening.  It's
just a hypothesis.

>I'm still interested in what happens if you disable prepared statement
>pooling.

I finally figured out how to make the app use up the cursors consistently
so I tried disabling PreparedStatement pooling a few minutes ago.  That
makes the problem go away.  I can make cursors accumulate with it turned
on.  I can't with it turned off.

That makes me think it's not the app after all.

However, it also makes me concerned about performance.  Interactive
performance on my test machine is nice and fast.  However, the performance
problems show up when the system is being pummeled by hundreds or
thousands of visitors to the site simultaneously (I have a script that can
simulate such pummeling against the server).

Would this then be considered a problem with DBCP's PreparedStatement
pooling?



-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bill,

(I've decided to stop cross-posting to commons-user. If you want to
forward come of my comments, you are welcome to do so).

On 10/15/2009 5:07 PM, Bill Davidson wrote:
> I'm a little confused about auto-commit.  I don't want my transactions
> auto-committed.  Sometimes there's a need for a rollback of an entire
> transaction.  To my thinking, auto-commit implies that you don't have
> true transactions.

You can always issue the equivalent of a BEGIN by calling
Connection.setAutoCommit(false) and then, later, Connection.commit() or
Connection.rollback(). The DBCP pool's default mode (which matches the
JDBC spec) is to keep all connections in autoCommit=true mode. When
connections are returned to the pool, Connection.setAutoCommit(true) is
called, which is documented to commit any in-progress transaction (which
is why you'd better not be sloppy with your rollback code: calling
Connection.close() in a pooled environment does /not/ roll-back your
transaction: it commits it!!).

> The transactions in the app are a lot more complex, and a transaction
> can hold onto a connection for quite a while as a user is editing things.

Uh, oh. Are you doing something like this:

BEGIN
SELECT
UPDATE

(wait for user to make a selection on a screen and re-submit a form)

UPDATE
COMMIT

??

If you're doing that, then you're probably making a big mistake. Are you
storing connections in sessions or anything like that? Yuk.

> The queries themselves have the same try-catch-finally situation but
> there can be quite a few different calls on that connection for one
> transaction.

Not a problem.

>>But what is the likelihood of "SELECT * FROM foo WHERE foo.id=?"
>>appearing in a transaction?
> 
> Very low.  The scenario I was describing was:
> 
> 1. Thread 1 returns a connection with an uncommitted transaction to the
> pool.

Mistake: see above.

> 2. Thread 2 gets the same connection from the pool and does a query on it

Another mistake: you can't be sure which connection gets handed-out b
the pool. Or, maybe you're just describing a way to get yourself into
the situation you're observing.

> 3. The query from Thread 2, unrelated Thread 1, ends up in cursor limbo
>   because of the uncommitted transaction on the connection.

Unless you have set autoCommit="false" in your DBCP configuration, this
shouldn't happen.

> I don't know that this is happening in the app, and it's going to be very
> difficult to find it if it is.  I don't even know that this scenario
> could cause this cursor limbo.  It's just a hypothesis which is hard to test.

Definitely. A good DBA ought to be able to instrument the server and
watch a connection create these cursors. If that's possible, then have
your DBA do just that while you make a connection, maybe using a special
user id so it's easier to track. Just set up a test bed for your webapp
and then simply use it yourself until you catch a cursor being "lost",
then try to back-track and figure out where it came from, and why it
won't go away.

I'm still interested in what happens if you disable prepared statement
pooling.

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrYjGEACgkQ9CaO5/Lv0PBqXQCgnPqv8/S1Z4n4jJ72TtwRdSuW
J3MAn341zxwTxcm0w+mStlmem4ahdGLN
=ulWR
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Christopher Schultz wrote:
>Probably not. DBCP calls setAutoCommit(true) by default in order to
>reset the connection as it goes back into the pool. Any pending
>transaction is committed (!) when that happens, so there shouldn't be
>any in-progress transactions lingering around.
>
>If you set autoCommit="false" in your DBCP configuration, that might
>change things. Are your transaction finally blocks and commit/rollback
>logic blocks sane? You can refer to my previously-mentioned blog post
>for my thoughts on how to do it properly.

I'm a little confused about auto-commit.  I don't want my transactions
auto-committed.  Sometimes there's a need for a rollback of an entire
transaction.  To my thinking, auto-commit implies that you don't have
true transactions.

The transactions in the app are a lot more complex, and a transaction
can hold onto a connection for quite a while as a user is editing things.
The queries themselves have the same try-catch-finally situation but
there can be quite a few different calls on that connection for one
transaction.

>But what is the likelihood of "SELECT * FROM foo WHERE foo.id=?"
>appearing in a transaction?

Very low.  The scenario I was describing was:

1. Thread 1 returns a connection with an uncommitted transaction to the 
pool.

2. Thread 2 gets the same connection from the pool and does a query on it
3. The query from Thread 2, unrelated Thread 1, ends up in cursor limbo
  because of the uncommitted transaction on the connection.

I don't know that this is happening in the app, and it's going to be very
difficult to find it if it is.  I don't even know that this scenario 
could cause this

cursor limbo.  It's just a hypothesis which is hard to test.


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Hassan Schroeder]

On Thu, Oct 15, 2009 at 1:46 PM, Christopher Schultz
 wrote:

>  You could also ask on some Java-oriented Oracle forums.

Oracle has a user community called Mix -- https://mix.oracle.com/ --
where you could post your issue (requires registration but otherwise
free).

/* It's JRuby on Rails but not, unfortunately, running on Tomcat :-) */

FWIW,
-- 
Hassan Schroeder  hassan.schroe...@gmail.com
twitter: @hassan

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bill,

On 10/15/2009 2:24 PM, Bill Davidson wrote:
> That does make me wonder though if there are Connection's getting sent
> back to the pool that had a pending transaction without a commit/rollback
> and if that could be making any cursors on that connection after that
> linger?

Probably not. DBCP calls setAutoCommit(true) by default in order to
reset the connection as it goes back into the pool. Any pending
transaction is committed (!) when that happens, so there shouldn't be
any in-progress transactions lingering around.

If you set autoCommit="false" in your DBCP configuration, that might
change things. Are your transaction finally blocks and commit/rollback
logic blocks sane? You can refer to my previously-mentioned blog post
for my thoughts on how to do it properly.

> It might explain the randomness of the queries showing up in
> the list.

But what is the likelihood of "SELECT * FROM foo WHERE foo.id=?"
appearing in a transaction?

- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrXiuYACgkQ9CaO5/Lv0PDX1QCfUhGfjpNnU6mnff8g6WqRb7MN
vxMAoK3xes9KEfE5V8J/ZSGKCY2SqMRp
=6lz1
-END PGP SIGNATURE-

-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bill,

On 10/15/2009 2:15 PM, Bill Davidson wrote:
> Christopher Schultz wrote:
>>Is it possible that your server just doesn't want to allocate 245 * 4
>>cursors, and that you are just hitting that barrier?
> 
> cursor != connection

Right... but presumably your code is doing something useful with that
connection, rather than simply fetching it from the pool and then
discarding it. IIRC, all SELECT queries allocate a cursor. Others may as
well.

> Oracle is set up to allow up to 300 cursors per session (connection).
> I could up that limit, but it probably won't fix the problem, as these
> things seem to just keep accumulating.

Agreed. But, I wonder if there's a max cursors globally or something
like that, so that when you have all those connections going at once,
their sum total cursors exceeds that limit?

When this happens, does Tomcat continue running otherwise unscathed? Or,
do you get an avalanche of exceptions after the first one occurs. What
about on the other members of the cluster (just a descriptive term, not
a technical one)?

>>I though you said that after a connection was checked-out for 120
>>seconds, it was forcibly closed by the connection pool.
> 
> Only when it is sent back to the pool by the servlet.  The pool manager
> doesn't have a background thread looking for old connections to kill.
> It's not a work around for connection leaks.  Apparently it's a work
> around for cursor leaks.

Hah, okay. Well, it doesn't look like commons-dbcp has a similar
configuration option; you can only evict connections that have been
sitting idle, not those that have simply lived too long. Perhaps the
commons-dbcp folks have some ideas.

>>Oh, so this query is intended to find out what is happening on the
>>server side, so you can see what cursors are open and what their queries
>>are. I thought you meant that a query such as this was being executed
>>from your webapp.
> 
> Correct.  Sorry if I didn't make that clear.  Those queries are not in
> my webapp.  They are only used to help track down lingering cursors.
> For people not familiar with Oracle, special characters like '$' in FROM
> clauses are usually an indication of something being in Oracle's "data
> dictionary" which keeps track of everything in Oracle.  The other one
> in that query was "v$session" which keeps track of session information.
> A session, in this context, is a connection.

Gotcha. I wonder if somehow your app-based queries are allocating
cursors and never freeing them. Simple SELECT queries (or even complex
ones) shouldn't really be allocating cursors that aren't freed when you
call ResultSet.close or Statement.close (whichever actually kills them,
I'm not sure). Also, "closing" a pooled connection should call
setAutoCommit(true) (this is the default setting for autocommit) which
will commit any in-progress transactions lingering, which I would
imagine results in all cursors being closed as well. I must admit, I'm
at the limit of my (quite limited) Oracle knowledge at this point. Got a
DBA handy?

>>The eviction run will only to remove connections from the pool: it won't
>>fix any resource allocation problems. Your webapp and server ought to be
>>able to tolerate all connections being open and active at once (so, a
>>full 245 connections in each webapp instance, and 980 connections on the
>>server).
> 
> The Oracle instance is set up to handle 1000 connections.  Tomcat has
> maxThreads=240, just because I'm paranoid and want to leave a little slop
> factor.  I shouldn't ever see more than 240 actual connections per server.

Agreed.

> I suspect that you are correct.  I'm baffled as to why I have old cursors
> lying around.  The close call on the statements has to happen.

Yeah, I agree. The only thing I can think of is that you are pooling
prepared statements. If you have more than 300 different queries in your
webapp (not at all a stretch IMO), and they are all being pooled, it's
possible that each one of those cached prepared statements consumes a
cursor. In that case, you'll easily consume your cursor allocation per
request. I'm just grasping at straws, now.

What happens, other than an awful slowdown, if you disable statement
pooling?

>>Do you have access to an Oracle DBA? They may be able to help uncover
>>the implications of some of the queries being run... it's possible that
>>cursors are being allocated that you didn't expect, or that aren't being
>>closed for /other/ reasons.
> 
> The Oracle DBA that I have access to doesn't know much about Java/JDBC
> which is why I was hoping I could find some Oracle expertise in the
> commons or tomcat lists.

Yeah, see what the commons-dbcp list has to say. Things are ... pretty
slow on the commons-user list, so you may have to wait a bit for a
reply. You could also ask on some Java-oriented Oracle forums.
Definitely post back if you find a satisfactory explanation so everyone
can ready about your new-found sage advice ;)

- -chris
--

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Martin Gainty wrote:
>are you running as a Transaction?

In some cases, but a lot of these lingering cursors are on very simple
queries, with no insert/update/delete involved.  As I said before,
I'm finding lingering cursors on things as simple as "SELECT * FROM
some_table WHERE id = ?".

That does make me wonder though if there are Connection's getting sent
back to the pool that had a pending transaction without a commit/rollback
and if that could be making any cursors on that connection after that
linger?  It might explain the randomness of the queries showing up in
the list.  It also gives me a question I can send to my Java-challenged
Oracle DBA.  Thanks for the idea.


-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

Re: DBCP woes (running out of cursors).

[Bill Davidson]

Christopher Schultz wrote:
>Is it possible that your server just doesn't want to allocate 245 * 4
>cursors, and that you are just hitting that barrier?

cursor != connection

Oracle is set up to allow up to 300 cursors per session (connection).
I could up that limit, but it probably won't fix the problem, as these
things seem to just keep accumulating.

>I though you said that after a connection was checked-out for 120
>seconds, it was forcibly closed by the connection pool.

Only when it is sent back to the pool by the servlet.  The pool manager
doesn't have a background thread looking for old connections to kill.
It's not a work around for connection leaks.  Apparently it's a work
around for cursor leaks.

>Oh, so this query is intended to find out what is happening on the
>server side, so you can see what cursors are open and what their queries
>are. I thought you meant that a query such as this was being executed
>from your webapp.

Correct.  Sorry if I didn't make that clear.  Those queries are not in
my webapp.  They are only used to help track down lingering cursors.
For people not familiar with Oracle, special characters like '$' in FROM
clauses are usually an indication of something being in Oracle's "data
dictionary" which keeps track of everything in Oracle.  The other one
in that query was "v$session" which keeps track of session information.
A session, in this context, is a connection.

>Do some of those methods have multiple queries being executed? If so,
>it's possible that one statement remains open while the second one is
>closed. For example:

A very small number do, but the vast overwhelming majority do not.
I'm finding these lingering cursors on all sorts of things.  On the ones
that do, they are all still closed.

>This is perfect. I noticed that I don't see a conn.close in there (which
>is probably appropriate, given that the Connection object is a parameter
>to the method). I assume you have similar finally blocks in calling
>methods, right?

Correct.  I'm not seeing anything resembling a connection leak problem.
This is strictly a cursor problem.

>The eviction run will only to remove connections from the pool: it won't
>fix any resource allocation problems. Your webapp and server ought to be
>able to tolerate all connections being open and active at once (so, a
>full 245 connections in each webapp instance, and 980 connections on the
>server).

The Oracle instance is set up to handle 1000 connections.  Tomcat has
maxThreads=240, just because I'm paranoid and want to leave a little slop
factor.  I shouldn't ever see more than 240 actual connections per server.

>The only thing the eviction will really help with is reducing the memory
>being used on both the client and server. I suppose that calling a
>"true" close() on the connection might clean-up any sloppiness going on
>in the client OR the server, and thus might solve your problem, but I
>believe it will be merely hiding the symptom, not actually solving the
>underlying problem.

I suspect that you are correct.  I'm baffled as to why I have old cursors
lying around.  The close call on the statements has to happen.

>Do you have access to an Oracle DBA? They may be able to help uncover
>the implications of some of the queries being run... it's possible that
>cursors are being allocated that you didn't expect, or that aren't being
>closed for /other/ reasons.

The Oracle DBA that I have access to doesn't know much about Java/JDBC
which is why I was hoping I could find some Oracle expertise in the
commons or tomcat lists.



-
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org

RE: DBCP woes (running out of cursors).

[Martin Gainty]

Connection
 StatementHandle(this is where the READONLY/UPDATABLE/FORWARD/REVERSE cursor is 
defined)
  ResultSet
  close ResultSet
 CloseStatementHandle
CloseConnection

closing ResultSet closes the ResultSet only but has no effect  on Statement 
Handle
closing StatementHandle closes ResultSet and StatementHandle
closing Connection closes all \

are you running as a Transaction?
Begin_Transaction
 Select into Buffer
 UPDATE/DELETE/Insert with dynamic variables
 COMMIT will flush to disk and close all handles
 Rollback will restore Transaction state to before Begin_Transaction state
END

if you display the queries here and we can help you show how to properly 
structure the statements you're using 
Martin Gainty 
__ 
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und 
Vertraulichkeitanmerkung/Note de déni et de confidentialité
 Ez az
üzenet bizalmas.  Ha nem ön az akinek szánva volt, akkor kérjük, hogy
jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának
készítése nem megengedett.  Ez az üzenet csak ismeret cserét szolgál és
semmiféle jogi alkalmazhatósága sincs.  Mivel az electronikus üzenetek
könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet
ezen üzenet tartalma miatt.

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




> Date: Wed, 14 Oct 2009 21:22:35 -0400
> From: ch...@christopherschultz.net
> To: users@tomcat.apache.org
> CC: u...@commons.apache.org
> Subject: Re: DBCP woes (running out of cursors).
> 
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Bill,
> 
> On 10/14/2009 5:05 PM, Bill Davidson wrote:
> > Usually, we don't need that many [connections], but sometimes, we get hit 
> > really hard
> > with a lot of traffic and do need that many.  BTW, this is load balanced
> > across 4 servers that can each do 245 connections.
> 
> Is it possible that your server just doesn't want to allocate 245 * 4
> cursors, and that you are just hitting that barrier? I don't believe the
> JDBC driver cares at all how many cursors are allocated, so it's
> unlikely to be a client-side exception being thrown (or, if you prefer,
> it's a server-side error being represented by a client-side exception).
> 
> > I thought [logAbandoned and removeAbandoned] was for Connection leaks.
> 
> They are. I just thought it would be a good idea to enable these, just
> in case there was a case where leaks were occurring.
> 
> > If we had Connection leaks, then the old pools wouldn't work properly
> > either, because the old pools only kill connections when the servlets
> > "free" the Connection (the same as close() on a DBCP connection).
> > The Connection's are being sent back to the pool, but apparently with
> > open cursors lingering.
> 
> I though you said that after a connection was checked-out for 120
> seconds, it was forcibly closed by the connection pool.
> 
> >>I don't know a thing about Oracle-specific queries, but what does:
> >>
> >>>  v$open_cursor a
> >>
> >>mean? Does this explicitly open a new cursor, or use an existing one
> >>called a?
> > 
> > v$cursor is a view in the Oracle data dictionary that shows currently
> > open cursors in the current Oracle instance.  The sql_text column shows
> > the first 40-50 characters or so of SQL being executed for that cursor.
> > It shows them for both active and inactive sessions.  I'm only guessing
> > that the inactive sessions are from Connection's that are closed without
> > having all of their ResultSet's closed.  That might be incorrect.
> > Finding concrete information is difficult.
> 
> Oh, so this query is intended to find out what is happening on the
> server side, so you can see what cursors are open and what their queries
> are. I thought you meant that a query such 

Re: DBCP woes (running out of cursors).

[Christopher Schultz]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Bill,

On 10/14/2009 5:05 PM, Bill Davidson wrote:
> Usually, we don't need that many [connections], but sometimes, we get hit 
> really hard
> with a lot of traffic and do need that many.  BTW, this is load balanced
> across 4 servers that can each do 245 connections.

Is it possible that your server just doesn't want to allocate 245 * 4
cursors, and that you are just hitting that barrier? I don't believe the
JDBC driver cares at all how many cursors are allocated, so it's
unlikely to be a client-side exception being thrown (or, if you prefer,
it's a server-side error being represented by a client-side exception).

> I thought [logAbandoned and removeAbandoned] was for Connection leaks.

They are. I just thought it would be a good idea to enable these, just
in case there was a case where leaks were occurring.

> If we had Connection leaks, then the old pools wouldn't work properly
> either, because the old pools only kill connections when the servlets
> "free" the Connection (the same as close() on a DBCP connection).
> The Connection's are being sent back to the pool, but apparently with
> open cursors lingering.

I though you said that after a connection was checked-out for 120
seconds, it was forcibly closed by the connection pool.

>>I don't know a thing about Oracle-specific queries, but what does:
>>
>>>  v$open_cursor a
>>
>>mean? Does this explicitly open a new cursor, or use an existing one
>>called a?
> 
> v$cursor is a view in the Oracle data dictionary that shows currently
> open cursors in the current Oracle instance.  The sql_text column shows
> the first 40-50 characters or so of SQL being executed for that cursor.
> It shows them for both active and inactive sessions.  I'm only guessing
> that the inactive sessions are from Connection's that are closed without
> having all of their ResultSet's closed.  That might be incorrect.
> Finding concrete information is difficult.

Oh, so this query is intended to find out what is happening on the
server side, so you can see what cursors are open and what their queries
are. I thought you meant that a query such as this was being executed
from your webapp.

> Maybe, but as I said, I've tracked down the SQL for all of the open
> cursors that don't seem to go away and they all have guaranteed close
> calls on the Statement's, and many also have them on the ResultSet's.
> A lot of the SQL is not that funky either.  A lot of it is as simple
> as grabbing a single record "SELECT * FROM some_table WHERE id = ?"
> or a few records like "SELECT * FROM some_table WHERE some_col = ?".

Do some of those methods have multiple queries being executed? If so,
it's possible that one statement remains open while the second one is
closed. For example:

PreparedStatement ps = null;
PreparedStatement ps2 = null;

try {
  ps  = conn.prepareStatement(...);
  ps2 = conn.prepareStatement(...);

  ...
}
finally
{
  ps.close();
}

>}finally{
>if ( rs != null ){
>try{
>rs.close();
>}catch ( SQLException ex ){
>// log it.
>}
>}

This is perfect. I noticed that I don't see a conn.close in there (which
is probably appropriate, given that the Connection object is a parameter
to the method). I assume you have similar finally blocks in calling
methods, right?

>>>- Should I use timeBetweenEvictionRunsMillis - What's an "eviction" run?
>>
>>It's what happens every so often to flush-out all the connections that
>>have been (for instance) idle too long, etc.
> 
> That might help.  The stuff I'm finding more recently is implying
> to me that connections are never closed if I don't enable eviction
> runs.

The eviction run will only to remove connections from the pool: it won't
fix any resource allocation problems. Your webapp and server ought to be
able to tolerate all connections being open and active at once (so, a
full 245 connections in each webapp instance, and 980 connections on the
server).

The only thing the eviction will really help with is reducing the memory
being used on both the client and server. I suppose that calling a
"true" close() on the connection might clean-up any sloppiness going on
in the client OR the server, and thus might solve your problem, but I
believe it will be merely hiding the symptom, not actually solving the
underlying problem.

Do you have access to an Oracle DBA? They may be able to help uncover
the implications of some of the queries being run... it's possible that
cursors are being allocated that you didn't expect, or that aren't being
closed for /other/ reasons.

Good luck,
- -chris
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkrWeVsACgkQ9CaO5/Lv0PClbgCgnYUGJ/Uzh/UvTDeT8NpdzD/p
94sAoKjGV9j3GA01nbZZaBGIdFaC6nlA
=9VFy
-END PGP SIGNATURE-