You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by "Joe Reger, Jr." <jo...@joereger.com> on 2005/02/25 22:53:13 UTC
[dbcp] abandoned connection removal and logging not working
Hi,
The AbandonedConfig object appears to be deprecated. In planning for future
versions of dbcp, should I remove all usages of AbandonedConfig,
AbandonedObjectPool, etc? If I do remove these usages, will my code not
function properly? When it's deprecated will it be replaced with something?
Or folded into one of the other classes?
Thanks for sharing your code... it's exactly what I was looking for.
Best,
Joe Reger
-----Original Message-----
From: Trenton D. Adams [mailto:trenta@athabascau.ca]
Sent: Thursday, February 24, 2005 2:03 PM
To: Jakarta Commons Users List
Subject: Re: abandoned connection removal and logging not working
I looked in the AbandonedObjectPool code and found the following.
if (config != null
&& config.getRemoveAbandoned()
&& (getNumIdle() < 2)
&& (getNumActive() > getMaxActive() - 3) ) {
removeAbandoned();
So, I never came close to the abandoned connections being removed because my
max active was set to 20 or something, and I had only a couple of dangling
connections. I thought this would be done in a background thread, but I
guess not.
Trenton D. Adams wrote:
> I have the following code, and it doesn't clean up abandoned
> connections. Anybody have an idea of what I am doing wrong?
>
> AbandonedConfig abandonedConfig;
> ObjectPool connectionPool;
> ConnectionFactory connectionFactory;
> PoolableConnectionFactory poolableConnectionFactory;
> PoolingDriver driver;
>
> // setup the abandoned configuration
> abandonedConfig = new AbandonedConfig();
> abandonedConfig.setLogAbandoned(true);
> abandonedConfig.setRemoveAbandoned(true);
> abandonedConfig.setRemoveAbandonedTimeout(60);
>
> // setup the AbandonedObjectPool
> connectionPool = new AbandonedObjectPool(null, abandonedConfig);
> ((GenericObjectPool)connectionPool).setMaxActive(maxConn);
> ((GenericObjectPool)connectionPool).setMaxIdle(maxFree);
> ((GenericObjectPool)connectionPool).setTestWhileIdle(true);
> ((GenericObjectPool)connectionPool).setWhenExhaustedAction(
> GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
> ((GenericObjectPool)connectionPool).setMaxWait(30);
> ((GenericObjectPool)connectionPool).setTestOnBorrow(true);
> ((GenericObjectPool)connectionPool).setTestOnReturn(true);
>
> ((GenericObjectPool)connectionPool).setTimeBetweenEvictionRunsMillis(1
> 0000);
>
> ((GenericObjectPool)connectionPool).setNumTestsPerEvictionRun(10);
>
> ((GenericObjectPool)connectionPool).setMinEvictableIdleTimeMillis(10);
>
> connectionFactory = new DriverManagerConnectionFactory(url, user,
> password);
> poolableConnectionFactory = new PoolableConnectionFactory(
> connectionFactory,connectionPool,null,
> "SELECT 'ping' FROM dual",false,false,
> Connection.TRANSACTION_SERIALIZABLE, null, abandonedConfig);
> try
> {
> Class.forName("org.apache.commons.dbcp.PoolingDriver");
> dbcpURL = "jdbc:apache:commons:dbcp:" + name;
> driver = (PoolingDriver) DriverManager.getDriver(dbcpURL);
> driver.registerPool(name, connectionPool);
> }
> catch (Exception exception)
> {
> RemoteBannerServer.log(exception, "error creating DBCP
> connection pool");
> }
--
Trenton D. Adams
Web Programmer Analyst
Navy Penguins at your service!
Athabasca University
(780) 675-6195
__
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [dbcp] abandoned connection removal and logging not working
Posted by "Trenton D. Adams" <tr...@athabascau.ca>.
Joe Reger, Jr. wrote:
> Hi Trenton,
>
> I agree with you... it's quick enough to add. Thanks for the response. I
> think I'm almost there.
>
> First, where do I put the "if (config != null &&..." code to cleanup
> abandoned connection?
You misunderstood me. That's the code I found in dbcp that did the
work. So, it will work if you use my code. You just have to do
something that will trigger that logic as indicated on the bottom of the
configuration page.
http://jakarta.apache.org/commons/dbcp/configuration.html
Here, I'll repost my latest code so that I know it works for you. :)
ResourceBundle dbProperties = ResourceBundle.getBundle("server");
removeAbandoned = Boolean.getBoolean(
dbProperties.getString("dbcp.removeAbandoned"));
logAbandoned = Boolean.getBoolean(
dbProperties.getString("dbcp.logAbandoned"));
removeAbandonedTimeout = Integer.parseInt(
dbProperties.getString("dbcp.removeAbandonedTimeout"));
freeConnectionWait = Integer.parseInt(
dbProperties.getString("dbcp.freeConnectionWait"));
exaustedAction = dbProperties.getString("dbcp.exaustedAction");
AbandonedConfig abandonedConfig;
AbandonedObjectPool connectionPool;
ConnectionFactory connectionFactory;
PoolingDriver driver;
// setup the abandoned configuration
abandonedConfig = new AbandonedConfig();
abandonedConfig.setLogAbandoned(logAbandoned);
abandonedConfig.setRemoveAbandoned(removeAbandoned);
abandonedConfig.setRemoveAbandonedTimeout(removeAbandonedTimeout);
// setup the AbandonedObjectPool
connectionPool = new AbandonedObjectPool(null, abandonedConfig);
connectionPool.setMaxActive(maxConn);
connectionPool.setMaxIdle(maxFree);
connectionPool.setTestWhileIdle(true);
if (exaustedAction.equals("WHEN_EXAUSTED_BLOCK"))
{
connectionPool.setWhenExhaustedAction(
GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
connectionPool.setMaxWait(freeConnectionWait);
}
else if (exaustedAction.equals("WHEN_EXAUSTED_GROW"))
{
connectionPool.setWhenExhaustedAction(
GenericObjectPool.WHEN_EXHAUSTED_GROW);
}
else
{ // all other cases, including unrecognized ones
connectionPool.setWhenExhaustedAction(
GenericObjectPool.WHEN_EXHAUSTED_FAIL);
}
connectionPool.setTestOnBorrow(true);
connectionPool.setTestOnReturn(true);
/**
* setup a poolable connection factory with all of the above objects
* Take note that this object's constructor calls
* connectionPool.setFactory(), which is what causes it to work.
*/
new PoolableConnectionFactory(
new DriverManagerConnectionFactory(url, user, password),
connectionPool,null,
"SELECT 'ping' FROM dual",false,false,
Connection.TRANSACTION_SERIALIZABLE, null, abandonedConfig);
Class.forName("org.apache.commons.dbcp.PoolingDriver");
dbcpURL = "jdbc:apache:commons:dbcp:" + name;
driver = (PoolingDriver)
DriverManager.getDriver("jdbc:apache:commons:dbcp:");
driver.registerPool(name, connectionPool);
}
catch (Exception exception)
{
RemoteBannerServer.log(exception, "error creating DBCP connection
pool");
}
Then you can get a connection like so, and print out some stats.
connection = DriverManager.getConnection(dbcpURL);
PoolingDriver driver = (PoolingDriver) DriverManager.getDriver(
"jdbc:apache:commons:dbcp:");
ObjectPool connectionPool = driver.getConnectionPool(poolName);
RemoteBannerServer.log(poolName + " POOL - out: " +
connectionPool.getNumActive() +
" - free : " + connectionPool.getNumIdle());
If you don't need the extra options above, someone named Kle Miller sent
me the following code, which is a lot simpler...
B_DATASOURCE = new BasicDataSource();
B_DATASOURCE.setDriverClassName(DRIVER);
B_DATASOURCE.setUrl(uri);
B_DATASOURCE.setUsername(user);
B_DATASOURCE.setPassword(password);
B_DATASOURCE.setRemoveAbandoned(true);
B_DATASOURCE.setInitialSize(1);
B_DATASOURCE.setMaxActive(20);
B_DATASOURCE.setValidationQuery("select top 1 role_type from
roles");
B_DATASOURCE.setTestOnBorrow(true);
B_DATASOURCE.setTestOnReturn(true);
B_DATASOURCE.setLogAbandoned(true);
And get a connection like so...
DBCONNECTION = B_DATASOURCE.getConnection();
System.out.println("#### num active:" +
B_DATASOURCE.getNumActive() + ":: num idle:" +
B_DATASOURCE.getNumIdle());
--
Trenton D. Adams
Web Programmer Analyst
Navy Penguins at your service!
Athabasca University
(780) 675-6195
:wq!
__
This communication is intended for the use of the recipient to whom it
is addressed, and may contain confidential, personal, and or privileged
information. Please contact us immediately if you are not the intended
recipient of this communication, and do not copy, distribute, or take
action relying on it. Any communications received in error, or
subsequent reply, should be deleted or destroyed.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
RE: [dbcp] abandoned connection removal and logging not working
Posted by "Joe Reger, Jr." <jo...@joereger.com>.
Hi Trenton,
I agree with you... it's quick enough to add. Thanks for the response. I
think I'm almost there.
First, where do I put the "if (config != null &&..." code to cleanup
abandoned connection?
Second, how do I tell the PoolingDriver to use the MySQL driver? I think
I'm missing a step somewhere.
Below is how I've customized your code (basically, db parameters stored in
environment vars). I'll run it once per tomcat session and from then
forward get a connection with
DriverManager.getConnection("jdbc:apache:commons:dbcp:" + name)... at least
that's how I think it'll work.
Thanks again,
Joe
AbandonedConfig abandonedConfig;
ObjectPool connectionPool;
ConnectionFactory connectionFactory;
PoolableConnectionFactory poolableConnectionFactory;
PoolingDriver driver;
// setup the abandoned configuration
abandonedConfig = new AbandonedConfig();
abandonedConfig.setLogAbandoned(true);
abandonedConfig.setRemoveAbandoned(true);
abandonedConfig.setRemoveAbandonedTimeout(60);
// setup the AbandonedObjectPool
connectionPool = new AbandonedObjectPool(null, abandonedConfig);
((GenericObjectPool)connectionPool).setMaxActive(((Integer)reger.Vars.getEnv
Var("DBMAXACTIVEDBPOOLCONNECTIONS")).intValue());
((GenericObjectPool)connectionPool).setMaxIdle(((Integer)reger.Vars.getEnvVa
r("DBMAXIDLEDBPOOLCONNECTIONS")).intValue());
((GenericObjectPool)connectionPool).setTestWhileIdle(false);
((GenericObjectPool)connectionPool).setWhenExhaustedAction(GenericObjectPool
.WHEN_EXHAUSTED_BLOCK);
((GenericObjectPool)connectionPool).setMaxWait(((Integer)reger.Vars.getEnvVa
r("DBMAXWAIT")).intValue());
((GenericObjectPool)connectionPool).setTestOnBorrow(false);
((GenericObjectPool)connectionPool).setTestOnReturn(false);
((GenericObjectPool)connectionPool).setTimeBetweenEvictionRunsMillis(10000);
((GenericObjectPool)connectionPool).setNumTestsPerEvictionRun(10);
((GenericObjectPool)connectionPool).setMinEvictableIdleTimeMillis(10);
connectionFactory = new
DriverManagerConnectionFactory((String)reger.Vars.getEnvVar("DBCONNECTIONURL
"), (String)reger.Vars.getEnvVar("DBUSERNAME"),
(String)reger.Vars.getEnvVar("DBPASSWORD"));
poolableConnectionFactory = new
PoolableConnectionFactory(connectionFactory,connectionPool,null,"SELECT
accountid FROM account",false,false,Connection.TRANSACTION_SERIALIZABLE,
null, abandonedConfig);
try {
Class.forName("org.apache.commons.dbcp.PoolingDriver");
String dbcpURL = "jdbc:apache:commons:dbcp:" + name;
driver = (PoolingDriver) DriverManager.getDriver(dbcpURL);
driver.registerPool(name, connectionPool);
} catch (Exception e){
e.printStackTrace();
}
-----Original Message-----
From: Trenton D. Adams [mailto:trenta@athabascau.ca]
Sent: Friday, February 25, 2005 4:57 PM
To: Jakarta Commons Users List
Subject: Re: [dbcp] abandoned connection removal and logging not working
Joe Reger, Jr. wrote:
> Hi,
>
> The AbandonedConfig object appears to be deprecated. In planning for
> future versions of dbcp, should I remove all usages of
> AbandonedConfig, AbandonedObjectPool, etc? If I do remove these
> usages, will my code not function properly? When it's deprecated will it
be replaced with something?
> Or folded into one of the other classes?
>
> Thanks for sharing your code... it's exactly what I was looking for.
I was told that it is deprecated until they can find a better way of doing
the abandoned connection code. So yes it's being removed, but being kept at
the same time. :)
Personally, I'm leaving mine the way it is until they re-engineer it.
It's really only a small section of code to change when they've finally got
their part done, so I figured, what the heck. :)
>
> Best,
>
> Joe Reger
>
> -----Original Message-----
> From: Trenton D. Adams [mailto:trenta@athabascau.ca]
> Sent: Thursday, February 24, 2005 2:03 PM
> To: Jakarta Commons Users List
> Subject: Re: abandoned connection removal and logging not working
>
> I looked in the AbandonedObjectPool code and found the following.
>
> if (config != null
> && config.getRemoveAbandoned()
> && (getNumIdle() < 2)
> && (getNumActive() > getMaxActive() - 3) ) {
> removeAbandoned();
>
> So, I never came close to the abandoned connections being removed
> because my max active was set to 20 or something, and I had only a
> couple of dangling connections. I thought this would be done in a
> background thread, but I guess not.
>
> Trenton D. Adams wrote:
>
>>I have the following code, and it doesn't clean up abandoned
>>connections. Anybody have an idea of what I am doing wrong?
>>
>> AbandonedConfig abandonedConfig;
>> ObjectPool connectionPool;
>> ConnectionFactory connectionFactory;
>> PoolableConnectionFactory poolableConnectionFactory;
>> PoolingDriver driver;
>>
>> // setup the abandoned configuration
>> abandonedConfig = new AbandonedConfig();
>> abandonedConfig.setLogAbandoned(true);
>> abandonedConfig.setRemoveAbandoned(true);
>> abandonedConfig.setRemoveAbandonedTimeout(60);
>>
>> // setup the AbandonedObjectPool
>> connectionPool = new AbandonedObjectPool(null, abandonedConfig);
>> ((GenericObjectPool)connectionPool).setMaxActive(maxConn);
>> ((GenericObjectPool)connectionPool).setMaxIdle(maxFree);
>> ((GenericObjectPool)connectionPool).setTestWhileIdle(true);
>> ((GenericObjectPool)connectionPool).setWhenExhaustedAction(
>> GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
>> ((GenericObjectPool)connectionPool).setMaxWait(30);
>> ((GenericObjectPool)connectionPool).setTestOnBorrow(true);
>> ((GenericObjectPool)connectionPool).setTestOnReturn(true);
>>
>>((GenericObjectPool)connectionPool).setTimeBetweenEvictionRunsMillis(1
>>0000);
>>
>> ((GenericObjectPool)connectionPool).setNumTestsPerEvictionRun(10);
>>
>>((GenericObjectPool)connectionPool).setMinEvictableIdleTimeMillis(10);
>>
>> connectionFactory = new DriverManagerConnectionFactory(url, user,
>>password);
>> poolableConnectionFactory = new PoolableConnectionFactory(
>> connectionFactory,connectionPool,null,
>> "SELECT 'ping' FROM dual",false,false,
>> Connection.TRANSACTION_SERIALIZABLE, null, abandonedConfig);
>> try
>> {
>> Class.forName("org.apache.commons.dbcp.PoolingDriver");
>> dbcpURL = "jdbc:apache:commons:dbcp:" + name;
>> driver = (PoolingDriver) DriverManager.getDriver(dbcpURL);
>> driver.registerPool(name, connectionPool);
>> }
>> catch (Exception exception)
>> {
>> RemoteBannerServer.log(exception, "error creating DBCP
>>connection pool");
>> }
>
>
>
> --
> Trenton D. Adams
> Web Programmer Analyst
> Navy Penguins at your service!
> Athabasca University
> (780) 675-6195
>
> __
> This communication is intended for the use of the recipient to whom it
> is addressed, and may contain confidential, personal, and or
privileged
> information. Please contact us immediately if you are not the intended
> recipient of this communication, and do not copy, distribute, or take
> action relying on it. Any communications received in error, or
> subsequent reply, should be deleted or destroyed.
> ---
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
--
Trenton D. Adams
Web Programmer Analyst
Navy Penguins at your service!
Athabasca University
(780) 675-6195
:wq!
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [dbcp] abandoned connection removal and logging not working
Posted by "Trenton D. Adams" <tr...@athabascau.ca>.
Joe Reger, Jr. wrote:
> Hi,
>
> The AbandonedConfig object appears to be deprecated. In planning for future
> versions of dbcp, should I remove all usages of AbandonedConfig,
> AbandonedObjectPool, etc? If I do remove these usages, will my code not
> function properly? When it's deprecated will it be replaced with something?
> Or folded into one of the other classes?
>
> Thanks for sharing your code... it's exactly what I was looking for.
I was told that it is deprecated until they can find a better way of
doing the abandoned connection code. So yes it's being removed, but
being kept at the same time. :)
Personally, I'm leaving mine the way it is until they re-engineer it.
It's really only a small section of code to change when they've finally
got their part done, so I figured, what the heck. :)
>
> Best,
>
> Joe Reger
>
> -----Original Message-----
> From: Trenton D. Adams [mailto:trenta@athabascau.ca]
> Sent: Thursday, February 24, 2005 2:03 PM
> To: Jakarta Commons Users List
> Subject: Re: abandoned connection removal and logging not working
>
> I looked in the AbandonedObjectPool code and found the following.
>
> if (config != null
> && config.getRemoveAbandoned()
> && (getNumIdle() < 2)
> && (getNumActive() > getMaxActive() - 3) ) {
> removeAbandoned();
>
> So, I never came close to the abandoned connections being removed because my
> max active was set to 20 or something, and I had only a couple of dangling
> connections. I thought this would be done in a background thread, but I
> guess not.
>
> Trenton D. Adams wrote:
>
>>I have the following code, and it doesn't clean up abandoned
>>connections. Anybody have an idea of what I am doing wrong?
>>
>> AbandonedConfig abandonedConfig;
>> ObjectPool connectionPool;
>> ConnectionFactory connectionFactory;
>> PoolableConnectionFactory poolableConnectionFactory;
>> PoolingDriver driver;
>>
>> // setup the abandoned configuration
>> abandonedConfig = new AbandonedConfig();
>> abandonedConfig.setLogAbandoned(true);
>> abandonedConfig.setRemoveAbandoned(true);
>> abandonedConfig.setRemoveAbandonedTimeout(60);
>>
>> // setup the AbandonedObjectPool
>> connectionPool = new AbandonedObjectPool(null, abandonedConfig);
>> ((GenericObjectPool)connectionPool).setMaxActive(maxConn);
>> ((GenericObjectPool)connectionPool).setMaxIdle(maxFree);
>> ((GenericObjectPool)connectionPool).setTestWhileIdle(true);
>> ((GenericObjectPool)connectionPool).setWhenExhaustedAction(
>> GenericObjectPool.WHEN_EXHAUSTED_BLOCK);
>> ((GenericObjectPool)connectionPool).setMaxWait(30);
>> ((GenericObjectPool)connectionPool).setTestOnBorrow(true);
>> ((GenericObjectPool)connectionPool).setTestOnReturn(true);
>>
>>((GenericObjectPool)connectionPool).setTimeBetweenEvictionRunsMillis(1
>>0000);
>>
>> ((GenericObjectPool)connectionPool).setNumTestsPerEvictionRun(10);
>>
>>((GenericObjectPool)connectionPool).setMinEvictableIdleTimeMillis(10);
>>
>> connectionFactory = new DriverManagerConnectionFactory(url, user,
>>password);
>> poolableConnectionFactory = new PoolableConnectionFactory(
>> connectionFactory,connectionPool,null,
>> "SELECT 'ping' FROM dual",false,false,
>> Connection.TRANSACTION_SERIALIZABLE, null, abandonedConfig);
>> try
>> {
>> Class.forName("org.apache.commons.dbcp.PoolingDriver");
>> dbcpURL = "jdbc:apache:commons:dbcp:" + name;
>> driver = (PoolingDriver) DriverManager.getDriver(dbcpURL);
>> driver.registerPool(name, connectionPool);
>> }
>> catch (Exception exception)
>> {
>> RemoteBannerServer.log(exception, "error creating DBCP
>>connection pool");
>> }
>
>
>
> --
> Trenton D. Adams
> Web Programmer Analyst
> Navy Penguins at your service!
> Athabasca University
> (780) 675-6195
>
> __
> This communication is intended for the use of the recipient to whom it
> is addressed, and may contain confidential, personal, and or privileged
> information. Please contact us immediately if you are not the intended
> recipient of this communication, and do not copy, distribute, or take
> action relying on it. Any communications received in error, or
> subsequent reply, should be deleted or destroyed.
> ---
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
--
Trenton D. Adams
Web Programmer Analyst
Navy Penguins at your service!
Athabasca University
(780) 675-6195
:wq!
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org