ConnectionPool question

[JoshInWV]

I wrote some code on top of the Tomcat's ConnectionPool class.  In regular
Java based programming if I close a ResultSet with connection.close(), this
frees up both the statement and resultset's memory associated with the
connection if it was still open.  If I close a connection with Tomcat's
ConnectionPool, does it also close the statement and resultset's associated
with that particular connection or do I need to manually close them?

I know best practice is to not rely on anything to be closed automatically,
but I inherited a code base and I am looking at making some pretty
significant changes to fix some problems, and this is one of them.

Thanks in advance,

- Josh

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Josh,

On 10/29/2009 9:17 PM, Josh Gooding wrote:
> I wrote some code on top of the Tomcat's ConnectionPool class.  In regular
> Java based programming if I close a ResultSet with connection.close(), this
> frees up both the statement and resultset's memory associated with the
> connection if it was still open.  If I close a connection with Tomcat's
> ConnectionPool, does it also close the statement and resultset's associated
> with that particular connection or do I need to manually close them?

This is a good question that was partially discussed over the past few
days (see the thread "DBCP woes (running out of cursors)." for the whole
sordid mess, but here's a quote from me:

"
Technically speaking, the JDBC specification requires that calling
Connection.close() also close any Statement (and therefore ResultSet)
objects that were opened as well. The lines become blurred a bit when
you're talking about pooled connections, because Connection.close()
doesn't really get called... it's a grey area in the spec if you ask me,
but I'd prefer that a pooled connection act like a non-pooled connection
in this case, but there's no "recycle" or "reset" method in the
java.sql.Connection class, and calling Connection.close() on the actual
connection is not appropriate (since it's pooled) so there may be no way
to actually implement this mimicry.
"

> I know best practice is to not rely on anything to be closed automatically,
> but I inherited a code base and I am looking at making some pretty
> significant changes to fix some problems, and this is one of them.

At the risk of being doubly-self-referential:
http://blog.christopherschultz.net/?p=68

Fortunately, clean JDBC code doesn't need to be all that messy (uh...
right).

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

iEYEARECAAYFAkrqcmgACgkQ9CaO5/Lv0PBc/QCdHPc6AFdcLPhxYDU6hpL+mFEP
s9gAoJJznfRIoDhFPvm98R8Q9kx6n7Tr
=puM5
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Filip Hanik - Dev Lists]

hi Josh, calling Connection.close() does not close statements and
resultsets.
There is an interceptor you can configure called StatementFinalizer that
does exactly that during the close call.

Filip


On 10/29/2009 07:17 PM, Josh Gooding wrote:

> I wrote some code on top of the Tomcat's ConnectionPool class.  In regular
> Java based programming if I close a ResultSet with connection.close(), this
> frees up both the statement and resultset's memory associated with the
> connection if it was still open.  If I close a connection with Tomcat's
> ConnectionPool, does it also close the statement and resultset's associated
> with that particular connection or do I need to manually close them?
>
> I know best practice is to not rely on anything to be closed automatically,
> but I inherited a code base and I am looking at making some pretty
> significant changes to fix some problems, and this is one of them.
>
> Thanks in advance,
>
> - Josh
>
>    

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[JoshInWV]

I have been reading it and ALMOST chimed in, but decided to read farther
first.  While I asked a question about it on the sun forums and got slammed
for a class I wrote and posted about why a ConnectionPool would ever have to
implement ServletContextListener.  basically I was told to write in
accordance with what the API's contract is,/ not implementation specific
behaviors.

Thank goodness I archive the tomcat udev / user listing in my Gmail.

The main problem I was having was similar to what he was.  I was able to
solve my problem by the following:

        maxActive="-1" maxIdle="5" maxWait="15"
        removeAbandoned="true" removeAbandonedTimeout="15"
        testWhileIdle="false" timeBetweenEvictionRunsMillis="900"/>

I changed up 4 parameters in the context.xml file.  maxWait,
removeAbandonedTimeout, testWhileIdle, and timeBetweenEvictionRunsMIllis.
The problem was despite me closing the rs's in the program, MySQL & Tomcat
was still seeing the connections as active and would keep them open.

I started by trying to tweak the wait timeout settings in the my.ini file,
but that really caused some jams especially if the connections would
timeout, then for some reason go to become active again, Tomcat would throw
me an error, so changing the settings on MySQL was not the answer.  The
problem was with how Tomcat was handling the time frame for recycling
connections.  I cut the time that tomcat held on to the closed connections
and the problem remarkably went away.  I could also monitor this from the
MySQL Administrator panel in real time when I ran heavy load queries to the
DB.  Now every 15 seconds after a close, the connection is returned to the
pool.  That seems to be about perfect, just long enough to run gc() and
continue on.

It took me about a day to figure out, but I did.

On Fri, Oct 30, 2009 at 12:58 AM, Christopher Schultz <
chris@...> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Josh,
>
> On 10/29/2009 9:17 PM, Josh Gooding wrote:
> > I wrote some code on top of the Tomcat's ConnectionPool class.  In
> regular
> > Java based programming if I close a ResultSet with connection.close(),
> this
> > frees up both the statement and resultset's memory associated with the
> > connection if it was still open.  If I close a connection with Tomcat's
> > ConnectionPool, does it also close the statement and resultset's
> associated
> > with that particular connection or do I need to manually close them?
>
> This is a good question that was partially discussed over the past few
> days (see the thread "DBCP woes (running out of cursors)." for the whole
> sordid mess, but here's a quote from me:
>
> "
> Technically speaking, the JDBC specification requires that calling
> Connection.close() also close any Statement (and therefore ResultSet)
> objects that were opened as well. The lines become blurred a bit when
> you're talking about pooled connections, because Connection.close()
> doesn't really get called... it's a grey area in the spec if you ask me,
> but I'd prefer that a pooled connection act like a non-pooled connection
> in this case, but there's no "recycle" or "reset" method in the
> java.sql.Connection class, and calling Connection.close() on the actual
> connection is not appropriate (since it's pooled) so there may be no way
> to actually implement this mimicry.
> "
>
> > I know best practice is to not rely on anything to be closed
> automatically,
> > but I inherited a code base and I am looking at making some pretty
> > significant changes to fix some problems, and this is one of them.
>
> At the risk of being doubly-self-referential:
> http://blog.christopherschultz.net/?p=68
>
> Fortunately, clean JDBC code doesn't need to be all that messy (uh...
> right).
>
> - -chris
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.10 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
>
> iEYEARECAAYFAkrqcmgACgkQ9CaO5/Lv0PBc/QCdHPc6AFdcLPhxYDU6hpL+mFEP
> s9gAoJJznfRIoDhFPvm98R8Q9kx6n7Tr
> =puM5
> -----END PGP SIGNATURE-----
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

[JoshInWV]

AHHHHHH, I will read the API for the StatementFinalizer.  I was looking at
something to do that.  Thank you Filip!

On Fri, Oct 30, 2009 at 9:53 AM, Filip Hanik - Dev Lists <devlists@...
> wrote:

> hi Josh, calling Connection.close() does not close statements and
> resultsets.
> There is an interceptor you can configure called StatementFinalizer that
> does exactly that during the close call.
>
> Filip
>
>
>
> On 10/29/2009 07:17 PM, Josh Gooding wrote:
>
>> I wrote some code on top of the Tomcat's ConnectionPool class.  In regular
>> Java based programming if I close a ResultSet with connection.close(),
>> this
>> frees up both the statement and resultset's memory associated with the
>> connection if it was still open.  If I close a connection with Tomcat's
>> ConnectionPool, does it also close the statement and resultset's
>> associated
>> with that particular connection or do I need to manually close them?
>>
>> I know best practice is to not rely on anything to be closed
>> automatically,
>> but I inherited a code base and I am looking at making some pretty
>> significant changes to fix some problems, and this is one of them.
>>
>> Thanks in advance,
>>
>> - Josh
>>
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

[JoshInWV]

Hey what API holds the statementFinalizer?

On Fri, Oct 30, 2009 at 9:57 AM, Josh Gooding <josh.gooding@...>wrote:

> AHHHHHH, I will read the API for the StatementFinalizer.  I was looking at
> something to do that.  Thank you Filip!
>
>
> On Fri, Oct 30, 2009 at 9:53 AM, Filip Hanik - Dev Lists <
> devlists@...> wrote:
>
>> hi Josh, calling Connection.close() does not close statements and
>> resultsets.
>> There is an interceptor you can configure called StatementFinalizer that
>> does exactly that during the close call.
>>
>> Filip
>>
>>
>>
>> On 10/29/2009 07:17 PM, Josh Gooding wrote:
>>
>>> I wrote some code on top of the Tomcat's ConnectionPool class.  In
>>> regular
>>> Java based programming if I close a ResultSet with connection.close(),
>>> this
>>> frees up both the statement and resultset's memory associated with the
>>> connection if it was still open.  If I close a connection with Tomcat's
>>> ConnectionPool, does it also close the statement and resultset's
>>> associated
>>> with that particular connection or do I need to manually close them?
>>>
>>> I know best practice is to not rely on anything to be closed
>>> automatically,
>>> but I inherited a code base and I am looking at making some pretty
>>> significant changes to fix some problems, and this is one of them.
>>>
>>> Thanks in advance,
>>>
>>> - Josh
>>>
>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@...
>> For additional commands, e-mail: users-help@...
>>
>>
>

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Josh,

On 10/30/2009 9:54 AM, Josh Gooding wrote:
> While I asked a question about it on the sun forums and got slammed
> for a class I wrote and posted about why a ConnectionPool would ever have to
> implement ServletContextListener.  basically I was told to write in
> accordance with what the API's contract is,/ not implementation specific
> behaviors.

Heh. Obviously, someone hasn't worked in the real world. Did he realize
that writing to the contract in this case could potentially bring-down
the database server?

> Thank goodness I archive the tomcat udev / user listing in my Gmail.

You can always search the archives on markmail, too. There's no need to
keep your own archive.

> The main problem I was having was similar to what he was.  I was able to
> solve my problem by the following:
>
>         maxActive="-1" maxIdle="5" maxWait="15"
>         removeAbandoned="true" removeAbandonedTimeout="15"
>         testWhileIdle="false" timeBetweenEvictionRunsMillis="900"/>

900ms is a /very/ short time for an eviction run. Given that you have a
15-second timeout, I would probably make your eviction runs somewhere in
that range rather than in the sub-second range. Do you really want your
DataSource waking up every second to check all the connections?

> I changed up 4 parameters in the context.xml file.  maxWait,
> removeAbandonedTimeout, testWhileIdle, and timeBetweenEvictionRunsMIllis.
> The problem was despite me closing the rs's in the program, MySQL & Tomcat
> was still seeing the connections as active and would keep them open.

Well, you have to close all your resources, not just ResultSets. Closing
a ResultSet does not close the connection (nor does it return it to the
pool, which may have been your whole problem).

> I started by trying to tweak the wait timeout settings in the my.ini file,
> but that really caused some jams especially if the connections would
> timeout, then for some reason go to become active again, Tomcat would throw
> me an error, so changing the settings on MySQL was not the answer.

You really ought to use validationQuery="/* ping */ SELECT 1" as well.
Any connection that has been closed while sitting in the pool will be
re-checked before it's given-out to the caller. That means that you
shouldn't get connection exceptions being thrown when this kind of thing
happens.

> The
> problem was with how Tomcat was handling the time frame for recycling
> connections.  I cut the time that tomcat held on to the closed connections
> and the problem remarkably went away.  I could also monitor this from the
> MySQL Administrator panel in real time when I ran heavy load queries to the
> DB.  Now every 15 seconds after a close, the connection is returned to the
> pool.  That seems to be about perfect, just long enough to run gc() and
> continue on.

Heh. I highly recommend reviewing you code: abandoned expirations and
evictions can get you by in a pinch, but it's no way to live long-term.
Fix your resource leaks and your server(s) will thank you by increasing
their throughput.

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

iEYEARECAAYFAkrrBy8ACgkQ9CaO5/Lv0PBsHwCfUxbNB8s7haZMI9ZZwf3fhBtf
NqoAoMPsCTZiH5NgB69d/iOk3BVcakkf
=TfJV
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Filip,

On 10/30/2009 9:53 AM, Filip Hanik - Dev Lists wrote:
> There is an interceptor you can configure called StatementFinalizer that
> does exactly that during the close call.

Can you point me to some documentation for this? The only thing I can
find with Google is this:

http://static.springsource.com/projects/tc-server/6.0/admin/radmjdbc.html

...and it appears to have some dubious "facts" in it, such as:

"
Set the factory attribute to
org.apache.tomcat.jdbc.pool.DataSourceFactory to use the
high-concurrency connection pool. This is also the default value of the
factory attribute for tc Server, so you will automatically use the
high-concurrency connection pool if you do not specify this attribute at
all, as shown in the example below.
"

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

iEYEARECAAYFAkrrCSAACgkQ9CaO5/Lv0PBE0wCfWmwbyfFfyxLefYR0lQ06lbB+
3/0AoJssV+w7BYT7ASbzJAsx8YPO2g8C
=SCE7
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Filip Hanik - Dev Lists]

http://svn.apache.org/viewvc/tomcat/trunk/modules/jdbc-pool/java/org/apache/tomcat/jdbc/pool/interceptor/StatementFinalizer.java?view=log

Filip

On 10/30/2009 08:34 AM, Josh Gooding wrote:

> Hey what API holds the statementFinalizer?
>
> On Fri, Oct 30, 2009 at 9:57 AM, Josh Gooding<josh.gooding@...>wrote:
>
>    
>> AHHHHHH, I will read the API for the StatementFinalizer.  I was looking at
>> something to do that.  Thank you Filip!
>>
>>
>> On Fri, Oct 30, 2009 at 9:53 AM, Filip Hanik - Dev Lists<
>> devlists@...>  wrote:
>>
>>      
>>> hi Josh, calling Connection.close() does not close statements and
>>> resultsets.
>>> There is an interceptor you can configure called StatementFinalizer that
>>> does exactly that during the close call.
>>>
>>> Filip
>>>
>>>
>>>
>>> On 10/29/2009 07:17 PM, Josh Gooding wrote:
>>>
>>>        
>>>> I wrote some code on top of the Tomcat's ConnectionPool class.  In
>>>> regular
>>>> Java based programming if I close a ResultSet with connection.close(),
>>>> this
>>>> frees up both the statement and resultset's memory associated with the
>>>> connection if it was still open.  If I close a connection with Tomcat's
>>>> ConnectionPool, does it also close the statement and resultset's
>>>> associated
>>>> with that particular connection or do I need to manually close them?
>>>>
>>>> I know best practice is to not rely on anything to be closed
>>>> automatically,
>>>> but I inherited a code base and I am looking at making some pretty
>>>> significant changes to fix some problems, and this is one of them.
>>>>
>>>> Thanks in advance,
>>>>
>>>> - Josh
>>>>
>>>>
>>>>
>>>>          
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: users-unsubscribe@...
>>> For additional commands, e-mail: users-help@...
>>>
>>>
>>>        
>>      
>    

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Filip Hanik - Dev Lists]

look at jdbc-pool.html it has all the info, here are examples out of it

Configuration


<Resource name="jdbc/TestDB"
               auth="Container"
               type="javax.sql.DataSource"
               factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
               testWhileIdle="true"
               testOnBorrow="true"
               testOnReturn="false"
               validationQuery="SELECT 1"
               validationInterval="30000"
               timeBetweenEvictionRunsMillis="30000"
               maxActive="100"
               minIdle="10"
               maxWait="10000"
               initialSize="10"
               removeAbandonedTimeout="60"
               removeAbandoned="true"
               logAbandoned="true"
               minEvictableIdleTimeMillis="30000"
               jmxEnabled="true"
               jdbcInterceptors=
"org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
               username="root"
               password="password"
               driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/mysql"/>




Code:

import java.sql.Connection;
         import java.sql.ResultSet;
         import java.sql.Statement;

         import org.apache.tomcat.jdbc.pool.DataSource;
         import org.apache.tomcat.jdbc.pool.PoolProperties;

         public class SimplePOJOExample {

             public static void main(String[] args) throws Exception {
                 PoolProperties p = new PoolProperties();
                 p.setUrl("jdbc:mysql://localhost:3306/mysql");
                 p.setDriverClassName("com.mysql.jdbc.Driver");
                 p.setUsername("root");
                 p.setPassword("password");
                 p.setJmxEnabled(true);
                 p.setTestWhileIdle(false);
                 p.setTestOnBorrow(true);
                 p.setValidationQuery("SELECT 1");
                 p.setTestOnReturn(false);
                 p.setValidationInterval(30000);
                 p.setTimeBetweenEvictionRunsMillis(30000);
                 p.setMaxActive(100);
                 p.setInitialSize(10);
                 p.setMaxWait(10000);
                 p.setRemoveAbandonedTimeout(60);
                 p.setMinEvictableIdleTimeMillis(30000);
                 p.setMinIdle(10);
                 p.setLogAbandoned(true);
                 p.setRemoveAbandoned(true);
                 
p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
                   
"org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
                 DataSource datasource = new DataSource();
                 datasource.setPoolProperties(p);

                 Connection con = null;
                 try {
                   con = datasource.getConnection();
                   Statement st = con.createStatement();
                   ResultSet rs = st.executeQuery("select * from user");
                   int cnt = 1;
                   while (rs.next()) {
                       System.out.println((cnt++)+". Host:"
+rs.getString("Host")+
                         " User:"+rs.getString("User")+"
Password:"+rs.getString("Password"));
                   }
                   rs.close();
                   st.close();
                 } finally {
                   if (con!=null) try {con.close();}catch (Exception
ignore) {}
                 }
             }

         }


On 10/30/2009 09:54 AM, Filip Hanik - Dev Lists wrote:

>
> http://svn.apache.org/viewvc/tomcat/trunk/modules/jdbc-pool/java/org/apache/tomcat/jdbc/pool/interceptor/StatementFinalizer.java?view=log 
>
>
> Filip
>
> On 10/30/2009 08:34 AM, Josh Gooding wrote:
>> Hey what API holds the statementFinalizer?
>>
>> On Fri, Oct 30, 2009 at 9:57 AM, Josh
>> Gooding<josh.gooding@...>wrote:
>>
>>> AHHHHHH, I will read the API for the StatementFinalizer.  I was
>>> looking at
>>> something to do that.  Thank you Filip!
>>>
>>>
>>> On Fri, Oct 30, 2009 at 9:53 AM, Filip Hanik - Dev Lists<
>>> devlists@...>  wrote:
>>>
>>>> hi Josh, calling Connection.close() does not close statements and
>>>> resultsets.
>>>> There is an interceptor you can configure called StatementFinalizer
>>>> that
>>>> does exactly that during the close call.
>>>>
>>>> Filip
>>>>
>>>>
>>>>
>>>> On 10/29/2009 07:17 PM, Josh Gooding wrote:
>>>>
>>>>> I wrote some code on top of the Tomcat's ConnectionPool class.  In
>>>>> regular
>>>>> Java based programming if I close a ResultSet with
>>>>> connection.close(),
>>>>> this
>>>>> frees up both the statement and resultset's memory associated with
>>>>> the
>>>>> connection if it was still open.  If I close a connection with
>>>>> Tomcat's
>>>>> ConnectionPool, does it also close the statement and resultset's
>>>>> associated
>>>>> with that particular connection or do I need to manually close them?
>>>>>
>>>>> I know best practice is to not rely on anything to be closed
>>>>> automatically,
>>>>> but I inherited a code base and I am looking at making some pretty
>>>>> significant changes to fix some problems, and this is one of them.
>>>>>
>>>>> Thanks in advance,
>>>>>
>>>>> - Josh
>>>>>
>>>>>
>>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: users-unsubscribe@...
>>>> For additional commands, e-mail: users-help@...
>>>>
>>>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[JoshInWV]

On Fri, Oct 30, 2009 at 11:33 AM, Christopher Schultz <
chris@...> wrote:

>
> Heh. Obviously, someone hasn't worked in the real world. Did he realize
> that writing to the contract in this case could potentially bring-down
> the database server?
>
>
Eh, I just let it go.  I'm beyond the fact that I know the code I wrote and
inherited is wacked.  It's not worth starting an internet flame war,
especially over a programming topic on the sun board.  I respect the other
people on there too much to make the guy look like an..... well we all get
the point.


> > Thank goodness I archive the tomcat udev / user listing in my Gmail.
>
> You can always search the archives on markmail, too. There's no need to
> keep your own archive.
>

I don't "actively" archive them, I have them auto sorted and split into
categories.  I have 7GB+ of storage, so all of my Tomcat / PHP / C++ / etc
mails are all sent to different folders and marked accordingly.  I knew
about markmail,

>
> > The main problem I was having was similar to what he was.  I was able to
> > solve my problem by the following:
> >
> >         maxActive="-1" maxIdle="5" maxWait="15"
> >         removeAbandoned="true" removeAbandonedTimeout="15"
> >         testWhileIdle="false" timeBetweenEvictionRunsMillis="900"/>
>
> 900ms is a /very/ short time for an eviction run. Given that you have a
> 15-second timeout, I would probably make your eviction runs somewhere in
> that range rather than in the sub-second range. Do you really want your
> DataSource waking up every second to check all the connections?
>

OOPS, good catch, should have been 54000ms (15 sec) I forgot to x by another
60 in there.

>
> > I changed up 4 parameters in the context.xml file.  maxWait,
> > removeAbandonedTimeout, testWhileIdle, and timeBetweenEvictionRunsMIllis.
> > The problem was despite me closing the rs's in the program, MySQL &
> Tomcat
> > was still seeing the connections as active and would keep them open.
>
> Well, you have to close all your resources, not just ResultSets. Closing
> a ResultSet does not close the connection (nor does it return it to the
> pool, which may have been your whole problem).
>
> > I started by trying to tweak the wait timeout settings in the my.ini
> file,
> > but that really caused some jams especially if the connections would
> > timeout, then for some reason go to become active again, Tomcat would
> throw
> > me an error, so changing the settings on MySQL was not the answer.
>
> You really ought to use validationQuery="/* ping */ SELECT 1" as well.
> Any connection that has been closed while sitting in the pool will be
> re-checked before it's given-out to the caller. That means that you
> shouldn't get connection exceptions being thrown when this kind of thing
> happens.
>

I need to look into this to see how I need to implement it.  (there is a
nice example down lower in this "thread")

>
> > The
> > problem was with how Tomcat was handling the time frame for recycling
> > connections.  I cut the time that tomcat held on to the closed
> connections
> > and the problem remarkably went away.  I could also monitor this from the
> > MySQL Administrator panel in real time when I ran heavy load queries to
> the
> > DB.  Now every 15 seconds after a close, the connection is returned to
> the
> > pool.  That seems to be about perfect, just long enough to run gc() and
> > continue on.
>
> Heh. I highly recommend reviewing you code: abandoned expirations and
> evictions can get you by in a pinch, but it's no way to live long-term.
> Fix your resource leaks and your server(s) will thank you by increasing
> their throughput.
>

Oh this code BLEEDS resources.  The only semi annoyance is that the methods
that get RS's are usually returned like:
        return
ConnectionPool.getConnection().createStatement().executeQuery(sqlCode);

This is not bad, but there is NO PLACE in the code that actively closes all
of the resources.  Sometimes the code is LUCKY to have a call that is to
rs.close(); but I am more than positive that there is no statement /
connection closing going on. The hardest part is that everything is mixed
into the presentation layer.  I'll fix the DAO first, then move to more
back-end fixing.  Thanks for the 411 about the StatementFinalize and the
Millis timeout.  The millis has been changed to 54000 (15 seconds), and I'll
look up more info about the StatementFinalize and the validationQuery.

This biggest problem I'm having is that I'm the only coder in a .NET shop
(they all run IIS and code in .NET only) and no one has experience with TC
except me.  Honestly if it wasn't for this list and the sun forums, I'd be
sunk on most things Tomcat / java wise.  This list with the members have
been an invaluable asset to my learning Tomcat.



>
> Good luck,
> - -chris
>

Thanks, Josh

[JoshInWV]

Wait a second.  What I am seeing from you Filip and what I have in my
context.xml are similar:

<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <Resource
        name="jdbc/RealmDB" auth="Container" type="javax.sql.DataSource"
        username="root" password="password"
driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/monk"
        maxActive="-1" maxIdle="5" maxWait="15"
        removeAbandoned="true" removeAbandonedTimeout="15"
        testWhileIdle="false" timeBetweenEvictionRunsMillis="54000"/>
    <Realm
        className="org.apache.catalina.realm.DataSourceRealm"
        dataSourceName="jdbc/RealmDB" localDataSource="true"
        digest="MD5"
        userTable="user" userNameCol="user_name" userCredCol="password"
        userRoleTable="tcrole" roleNameCol="role_name" />
</Context>

My ConnectionPool class:

import java.sql.Connection;
import java.sql.SQLException;

import javax.naming.InitialContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;

import aardvark.exceptions.AardvarkResourceException;


public class ConnectionPool implements ServletContextListener {

    @Override
    public void contextDestroyed(ServletContextEvent arg0) {}

    @Override
    public void contextInitialized(ServletContextEvent ci) {}

    public static synchronized Connection getConnection() throws
AardvarkResourceException {
        try {
            DataSource ds = (DataSource) new
InitialContext().lookup("java:/comp/env/jdbc/RealmDB");
            return ds.getConnection();
        } catch (Exception e) {
            throw new AardvarkResourceException(e);
        }
    }

    public static synchronized void recycleConnection(Connection c) {
        try {
            if (!c.isClosed()) {
                c.close();
            }
        } catch (SQLException e) {
            // eat the exception, not much else to do.
        }
    }
}

Honestly, this should be a very simple fix to replace the current coding
with the new ConnectionPooling stuff.  If looks like the old project lead
used the javax.sql ConnectionPool over Tomcat's ConnectionPool coding.  I
wonder why?  Oh well I'm going to implement this and also change the
recycleConnection to close the statement and resultset as well.  That way I
can run the recycleConnection method and it will take care of everything
instead of having more spaghetti code.  Doing this much should increase the
servers performance.


On Fri, Oct 30, 2009 at 12:12 PM, Filip Hanik - Dev Lists <
devlists@...> wrote:

> look at jdbc-pool.html it has all the info, here are examples out of it
>
> Configuration
>
>
> <Resource name="jdbc/TestDB"
>              auth="Container"
>              type="javax.sql.DataSource"
>              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>              testWhileIdle="true"
>              testOnBorrow="true"
>              testOnReturn="false"
>              validationQuery="SELECT 1"
>              validationInterval="30000"
>              timeBetweenEvictionRunsMillis="30000"
>              maxActive="100"
>              minIdle="10"
>              maxWait="10000"
>              initialSize="10"
>              removeAbandonedTimeout="60"
>              removeAbandoned="true"
>              logAbandoned="true"
>              minEvictableIdleTimeMillis="30000"
>              jmxEnabled="true"
>              jdbcInterceptors=
>
> "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
>              username="root"
>              password="password"
>              driverClassName="com.mysql.jdbc.Driver"
>              url="jdbc:mysql://localhost:3306/mysql"/>
>
>
>
>
> Code:
>
> import java.sql.Connection;
>        import java.sql.ResultSet;
>        import java.sql.Statement;
>
>        import org.apache.tomcat.jdbc.pool.DataSource;
>        import org.apache.tomcat.jdbc.pool.PoolProperties;
>
>        public class SimplePOJOExample {
>
>            public static void main(String[] args) throws Exception {
>                PoolProperties p = new PoolProperties();
>                p.setUrl("jdbc:mysql://localhost:3306/mysql");
>                p.setDriverClassName("com.mysql.jdbc.Driver");
>                p.setUsername("root");
>                p.setPassword("password");
>                p.setJmxEnabled(true);
>                p.setTestWhileIdle(false);
>                p.setTestOnBorrow(true);
>                p.setValidationQuery("SELECT 1");
>                p.setTestOnReturn(false);
>                p.setValidationInterval(30000);
>                p.setTimeBetweenEvictionRunsMillis(30000);
>                p.setMaxActive(100);
>                p.setInitialSize(10);
>                p.setMaxWait(10000);
>                p.setRemoveAbandonedTimeout(60);
>                p.setMinEvictableIdleTimeMillis(30000);
>                p.setMinIdle(10);
>                p.setLogAbandoned(true);
>                p.setRemoveAbandoned(true);
>
>  p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
>
>  "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
>                DataSource datasource = new DataSource();
>                datasource.setPoolProperties(p);
>
>                Connection con = null;
>                try {
>                  con = datasource.getConnection();
>                  Statement st = con.createStatement();
>                  ResultSet rs = st.executeQuery("select * from user");
>                  int cnt = 1;
>                  while (rs.next()) {
>                      System.out.println((cnt++)+". Host:"
> +rs.getString("Host")+
>                        " User:"+rs.getString("User")+"
> Password:"+rs.getString("Password"));
>                  }
>                  rs.close();
>                  st.close();
>                } finally {
>                  if (con!=null) try {con.close();}catch (Exception ignore)
> {}
>
>                }
>            }
>
>        }
>
>
> On 10/30/2009 09:54 AM, Filip Hanik - Dev Lists wrote:
>
>>
>>
>> http://svn.apache.org/viewvc/tomcat/trunk/modules/jdbc-pool/java/org/apache/tomcat/jdbc/pool/interceptor/StatementFinalizer.java?view=log
>>
>> Filip
>>
>> On 10/30/2009 08:34 AM, Josh Gooding wrote:
>>
>>> Hey what API holds the statementFinalizer?
>>>
>>> On Fri, Oct 30, 2009 at 9:57 AM, Josh Gooding<josh.gooding@...
>>> >wrote:
>>>
>>>  AHHHHHH, I will read the API for the StatementFinalizer.  I was looking
>>>> at
>>>> something to do that.  Thank you Filip!
>>>>
>>>>
>>>> On Fri, Oct 30, 2009 at 9:53 AM, Filip Hanik - Dev Lists<
>>>> devlists@...>  wrote:
>>>>
>>>>  hi Josh, calling Connection.close() does not close statements and
>>>>> resultsets.
>>>>> There is an interceptor you can configure called StatementFinalizer
>>>>> that
>>>>> does exactly that during the close call.
>>>>>
>>>>> Filip
>>>>>
>>>>>
>>>>>
>>>>> On 10/29/2009 07:17 PM, Josh Gooding wrote:
>>>>>
>>>>>  I wrote some code on top of the Tomcat's ConnectionPool class.  In
>>>>>> regular
>>>>>> Java based programming if I close a ResultSet with connection.close(),
>>>>>> this
>>>>>> frees up both the statement and resultset's memory associated with the
>>>>>> connection if it was still open.  If I close a connection with
>>>>>> Tomcat's
>>>>>> ConnectionPool, does it also close the statement and resultset's
>>>>>> associated
>>>>>> with that particular connection or do I need to manually close them?
>>>>>>
>>>>>> I know best practice is to not rely on anything to be closed
>>>>>> automatically,
>>>>>> but I inherited a code base and I am looking at making some pretty
>>>>>> significant changes to fix some problems, and this is one of them.
>>>>>>
>>>>>> Thanks in advance,
>>>>>>
>>>>>> - Josh
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: users-unsubscribe@...
>>>>> For additional commands, e-mail: users-help@...
>>>>>
>>>>>
>>>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@...
>> For additional commands, e-mail: users-help@...
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

[Filip Hanik - Dev Lists]

nope, you're using commons-dbcp, see the "factory" attribute in my config

Filip

On 10/30/2009 12:22 PM, Josh Gooding wrote:

> Wait a second.  What I am seeing from you Filip and what I have in my
> context.xml are similar:
>
> <Context>
>      <WatchedResource>WEB-INF/web.xml</WatchedResource>
>      <Resource
>          name="jdbc/RealmDB" auth="Container" type="javax.sql.DataSource"
>          username="root" password="password"
> driverClassName="com.mysql.jdbc.Driver"
>          url="jdbc:mysql://localhost:3306/monk"
>          maxActive="-1" maxIdle="5" maxWait="15"
>          removeAbandoned="true" removeAbandonedTimeout="15"
>          testWhileIdle="false" timeBetweenEvictionRunsMillis="54000"/>
>      <Realm
>          className="org.apache.catalina.realm.DataSourceRealm"
>          dataSourceName="jdbc/RealmDB" localDataSource="true"
>          digest="MD5"
>          userTable="user" userNameCol="user_name" userCredCol="password"
>          userRoleTable="tcrole" roleNameCol="role_name" />
> </Context>
>
> My ConnectionPool class:
>
> import java.sql.Connection;
> import java.sql.SQLException;
>
> import javax.naming.InitialContext;
> import javax.servlet.ServletContextEvent;
> import javax.servlet.ServletContextListener;
> import javax.sql.DataSource;
>
> import aardvark.exceptions.AardvarkResourceException;
>
>
> public class ConnectionPool implements ServletContextListener {
>
>      @Override
>      public void contextDestroyed(ServletContextEvent arg0) {}
>
>      @Override
>      public void contextInitialized(ServletContextEvent ci) {}
>
>      public static synchronized Connection getConnection() throws
> AardvarkResourceException {
>          try {
>              DataSource ds = (DataSource) new
> InitialContext().lookup("java:/comp/env/jdbc/RealmDB");
>              return ds.getConnection();
>          } catch (Exception e) {
>              throw new AardvarkResourceException(e);
>          }
>      }
>
>      public static synchronized void recycleConnection(Connection c) {
>          try {
>              if (!c.isClosed()) {
>                  c.close();
>              }
>          } catch (SQLException e) {
>              // eat the exception, not much else to do.
>          }
>      }
> }
>
> Honestly, this should be a very simple fix to replace the current coding
> with the new ConnectionPooling stuff.  If looks like the old project lead
> used the javax.sql ConnectionPool over Tomcat's ConnectionPool coding.  I
> wonder why?  Oh well I'm going to implement this and also change the
> recycleConnection to close the statement and resultset as well.  That way I
> can run the recycleConnection method and it will take care of everything
> instead of having more spaghetti code.  Doing this much should increase the
> servers performance.
>
>
> On Fri, Oct 30, 2009 at 12:12 PM, Filip Hanik - Dev Lists<
> devlists@...>  wrote:
>
>    
>> look at jdbc-pool.html it has all the info, here are examples out of it
>>
>> Configuration
>>
>>
>> <Resource name="jdbc/TestDB"
>>               auth="Container"
>>               type="javax.sql.DataSource"
>>               factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>>               testWhileIdle="true"
>>               testOnBorrow="true"
>>               testOnReturn="false"
>>               validationQuery="SELECT 1"
>>               validationInterval="30000"
>>               timeBetweenEvictionRunsMillis="30000"
>>               maxActive="100"
>>               minIdle="10"
>>               maxWait="10000"
>>               initialSize="10"
>>               removeAbandonedTimeout="60"
>>               removeAbandoned="true"
>>               logAbandoned="true"
>>               minEvictableIdleTimeMillis="30000"
>>               jmxEnabled="true"
>>               jdbcInterceptors=
>>
>> "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
>>               username="root"
>>               password="password"
>>               driverClassName="com.mysql.jdbc.Driver"
>>               url="jdbc:mysql://localhost:3306/mysql"/>
>>
>>
>>
>>
>> Code:
>>
>> import java.sql.Connection;
>>         import java.sql.ResultSet;
>>         import java.sql.Statement;
>>
>>         import org.apache.tomcat.jdbc.pool.DataSource;
>>         import org.apache.tomcat.jdbc.pool.PoolProperties;
>>
>>         public class SimplePOJOExample {
>>
>>             public static void main(String[] args) throws Exception {
>>                 PoolProperties p = new PoolProperties();
>>                 p.setUrl("jdbc:mysql://localhost:3306/mysql");
>>                 p.setDriverClassName("com.mysql.jdbc.Driver");
>>                 p.setUsername("root");
>>                 p.setPassword("password");
>>                 p.setJmxEnabled(true);
>>                 p.setTestWhileIdle(false);
>>                 p.setTestOnBorrow(true);
>>                 p.setValidationQuery("SELECT 1");
>>                 p.setTestOnReturn(false);
>>                 p.setValidationInterval(30000);
>>                 p.setTimeBetweenEvictionRunsMillis(30000);
>>                 p.setMaxActive(100);
>>                 p.setInitialSize(10);
>>                 p.setMaxWait(10000);
>>                 p.setRemoveAbandonedTimeout(60);
>>                 p.setMinEvictableIdleTimeMillis(30000);
>>                 p.setMinIdle(10);
>>                 p.setLogAbandoned(true);
>>                 p.setRemoveAbandoned(true);
>>
>>   p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
>>
>>   "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
>>                 DataSource datasource = new DataSource();
>>                 datasource.setPoolProperties(p);
>>
>>                 Connection con = null;
>>                 try {
>>                   con = datasource.getConnection();
>>                   Statement st = con.createStatement();
>>                   ResultSet rs = st.executeQuery("select * from user");
>>                   int cnt = 1;
>>                   while (rs.next()) {
>>                       System.out.println((cnt++)+". Host:"
>> +rs.getString("Host")+
>>                         " User:"+rs.getString("User")+"
>> Password:"+rs.getString("Password"));
>>                   }
>>                   rs.close();
>>                   st.close();
>>                 } finally {
>>                   if (con!=null) try {con.close();}catch (Exception ignore)
>> {}
>>
>>                 }
>>             }
>>
>>         }
>>
>>
>> On 10/30/2009 09:54 AM, Filip Hanik - Dev Lists wrote:
>>
>>      
>>>
>>> http://svn.apache.org/viewvc/tomcat/trunk/modules/jdbc-pool/java/org/apache/tomcat/jdbc/pool/interceptor/StatementFinalizer.java?view=log
>>>
>>> Filip
>>>
>>> On 10/30/2009 08:34 AM, Josh Gooding wrote:
>>>
>>>        
>>>> Hey what API holds the statementFinalizer?
>>>>
>>>> On Fri, Oct 30, 2009 at 9:57 AM, Josh Gooding<josh.gooding@...
>>>>          
>>>>> wrote:
>>>>>            
>>>>   AHHHHHH, I will read the API for the StatementFinalizer.  I was looking
>>>>          
>>>>> at
>>>>> something to do that.  Thank you Filip!
>>>>>
>>>>>
>>>>> On Fri, Oct 30, 2009 at 9:53 AM, Filip Hanik - Dev Lists<
>>>>> devlists@...>   wrote:
>>>>>
>>>>>   hi Josh, calling Connection.close() does not close statements and
>>>>>            
>>>>>> resultsets.
>>>>>> There is an interceptor you can configure called StatementFinalizer
>>>>>> that
>>>>>> does exactly that during the close call.
>>>>>>
>>>>>> Filip
>>>>>>
>>>>>>
>>>>>>
>>>>>> On 10/29/2009 07:17 PM, Josh Gooding wrote:
>>>>>>
>>>>>>   I wrote some code on top of the Tomcat's ConnectionPool class.  In
>>>>>>              
>>>>>>> regular
>>>>>>> Java based programming if I close a ResultSet with connection.close(),
>>>>>>> this
>>>>>>> frees up both the statement and resultset's memory associated with the
>>>>>>> connection if it was still open.  If I close a connection with
>>>>>>> Tomcat's
>>>>>>> ConnectionPool, does it also close the statement and resultset's
>>>>>>> associated
>>>>>>> with that particular connection or do I need to manually close them?
>>>>>>>
>>>>>>> I know best practice is to not rely on anything to be closed
>>>>>>> automatically,
>>>>>>> but I inherited a code base and I am looking at making some pretty
>>>>>>> significant changes to fix some problems, and this is one of them.
>>>>>>>
>>>>>>> Thanks in advance,
>>>>>>>
>>>>>>> - Josh
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>                
>>>>>> ---------------------------------------------------------------------
>>>>>> To unsubscribe, e-mail: users-unsubscribe@...
>>>>>> For additional commands, e-mail: users-help@...
>>>>>>
>>>>>>
>>>>>>
>>>>>>              
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: users-unsubscribe@...
>>> For additional commands, e-mail: users-help@...
>>>
>>>
>>>
>>>        
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@...
>> For additional commands, e-mail: users-help@...
>>
>>
>>      
>    

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[JoshInWV]

I also found this tid bit lying around.  It get's a connection, but doesn't
close the statement.

    public static void closeResources(ResultSet rs) throws
AardvarkResourceException {
        try {
            Statement s = rs.getStatement();
            if (s != null) { // ResultSets produced by metadata queries do
not have associated statements
                Connection c = s.getConnection();
                //s.close();
                //c.close();
                ConnectionPool.recycleConnection(c);
            }
            rs.close();
        } catch (SQLException e) {
            throw new AardvarkResourceException("Error closing resources
associated with ResultSet", e);
        }
    }

It still doesn't close the statement, but closes the connection leaving the
statement hanging.... I'll fix and make sure to use it.

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Josh,

On 10/30/2009 2:10 PM, Josh Gooding wrote:
> Oh this code BLEEDS resources.  The only semi annoyance is that the methods
> that get RS's are usually returned like:
>         return
> ConnectionPool.getConnection().createStatement().executeQuery(sqlCode);

Woo hoo! That looks like Visual Basic code to me. No wonder you have to
expire your connections pretty much immediately.

> This is not bad, but there is NO PLACE in the code that actively closes all
> of the resources.  Sometimes the code is LUCKY to have a call that is to
> rs.close(); but I am more than positive that there is no statement /
> connection closing going on. The hardest part is that everything is mixed
> into the presentation layer.

Double yay! You've definitely got your work cut out for you.

Here's a possibility: if you know that certain code does this
foolishness, you could wrap the Connection object (and Statement, and
ResultSet) with your own wrappers that may be able to clean up after
themselves. For instance, if you write a ResultSet wrapper such that a
call to "close" also closes the Statement that created it, and also
closes the Connection that created that statement, you may be able to
recover from some of this. You could also wrap the 'next' method and, if
the ResultSet is out of results, call a close there, too. Unfortunately,
if you ever try to issue two queries on a single connection, you'll
shoot yourself in the foot, of course.

> The millis has been changed to 54000 (15 seconds), and I'll
> look up more info about the StatementFinalize and the validationQuery.

Once you start actually fixing all the code to properly clean-up after
itself, you should set logAbandoned="true". That will give you a stack
trace for every connection pool check-out that doesn't have a
corresponding check-in (after 15s). At first, it'll drive you crazy
because all your code will be choking and spitting-out stack traces, but
once you fix some of the major ones, you'll find that one or two leaks
still exist and they're super easy to find if you get a stack trace
showing you exactly where they came from.

> This biggest problem I'm having is that I'm the only coder in a .NET shop
> (they all run IIS and code in .NET only) and no one has experience with TC
> except me.  Honestly if it wasn't for this list and the sun forums, I'd be
> sunk on most things Tomcat / java wise.  This list with the members have
> been an invaluable asset to my learning Tomcat.

Glad to help. Good luck.

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

iEYEARECAAYFAkrrO1EACgkQ9CaO5/Lv0PCwsACgwYH4EFewdTv6q/kSPI2GpWZx
XmMAn0JuYN4XYk8H/XYu4cHJe0IL3qj8
=U0UA
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Christopher Schultz-2]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Josh,

On 10/30/2009 2:42 PM, Josh Gooding wrote:
> I also found this tid bit lying around.  It get's a connection, but doesn't
> close the statement.

Might I suggest the following changes:

>     public static void closeResources(ResultSet rs) throws
> AardvarkResourceException {

          Statement s = null;
          Connection c = null;

>         try {
              s = rs.getStatement();
>             if (s != null) { // ResultSets produced by metadata queries do
> not have associated statements

                  // The previous comment is false. :(

                  c = s.getConnection();
>                 //s.close();
>                 //c.close();
                  // ConnectionPool.recycleConnection(c);
>             }
              // rs.close();
>         } catch (SQLException e) {
>             throw new AardvarkResourceException("Error closing resources
> associated with ResultSet", e);
>         }

          finally
          {
              if(null != rs) try { rs.close(); }
                  catch (SQLException sqle)
                  { sqle.printStackTrace(); /* or other logger */ }

              if(null != s) try { s.close(); } catch (SQLException sqle)
                  { sqle.printStackTrace(); /* or other logger */ }

              if(null != c)
                  ConnectionPool.recycleConnection(c);

              // Note that if you're using DBCP or really any decent
              // connection pool, you can just call Connection.close()
              // and it goes back to the pool itself.
          }

> It still doesn't close the statement, but closes the connection leaving the
> statement hanging.... I'll fix and make sure to use it.

I would dump this method unless it's being widely used. If it's being
widely used, I'd fix it as shown above. Remember: finally blocks are the
way to go, here.

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

iEYEARECAAYFAkrrPMQACgkQ9CaO5/Lv0PCL1ACfb3GzBOkJUnOErod9W2JDwnq2
f4kAn3xkTgWVgWe3P/LTqBs6SMLDDu/k
=9KTw
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[Propes, Barry L]

-----Original Message-----
From: Josh Gooding [mailto:josh.gooding@...]
Sent: Friday, October 30, 2009 1:11 PM
To: Tomcat Users List
Subject: Re: ConnectionPool question



This is not bad, but there is NO PLACE in the code that actively closes all of the resources.  Sometimes the code is LUCKY to have a call that is to rs.close(); but I am more than positive that there is no statement / connection closing going on. The hardest part is that everything is mixed into the presentation layer.  I'll fix the DAO first, then move to more back-end fixing.  Thanks for the 411 about the StatementFinalize and the Millis timeout.  The millis has been changed to 54000 (15 seconds), and I'll look up more info about the StatementFinalize and the validationQuery.
=======================================

You REALLY need to get this fixed though; you'll have crazy leaks all over the place. I battled this very thing for a while!




This biggest problem I'm having is that I'm the only coder in a .NET shop (they all run IIS and code in .NET only) and no one has experience with TC except me.  Honestly if it wasn't for this list and the sun forums, I'd be sunk on most things Tomcat / java wise.  This list with the members have been an invaluable asset to my learning Tomcat.
==============

Yeah, I feel your pain, too. Similar situation here. I have to hand-hold all of the server admins because they don't have the confidence or knowledge to do this stuff, and I don't have the permissions to touch the servers!!





---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[JoshInWV]

Hey Filip, I do not have /jdbc/pool/DataSourceFactory in my
tomcat-dbcp.jar.  Is there some other place I should look?

My jar's structure goes as:

org/apache/tomcat/

dbcp
jocl
pool

Under the dbcp I have a DataSourceConnectionFactory.class (which I am making
an assumption that is what it is.)

On Fri, Oct 30, 2009 at 12:12 PM, Filip Hanik - Dev Lists <
devlists@...> wrote:

> look at jdbc-pool.html it has all the info, here are examples out of it
>
> Configuration
>
>
> <Resource name="jdbc/TestDB"
>              auth="Container"
>              type="javax.sql.DataSource"
>              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
>              testWhileIdle="true"
>              testOnBorrow="true"
>              testOnReturn="false"
>              validationQuery="SELECT 1"
>              validationInterval="30000"
>              timeBetweenEvictionRunsMillis="30000"
>              maxActive="100"
>              minIdle="10"
>              maxWait="10000"
>              initialSize="10"
>              removeAbandonedTimeout="60"
>              removeAbandoned="true"
>              logAbandoned="true"
>              minEvictableIdleTimeMillis="30000"
>              jmxEnabled="true"
>              jdbcInterceptors=
>
> "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
>              username="root"
>              password="password"
>              driverClassName="com.mysql.jdbc.Driver"
>              url="jdbc:mysql://localhost:3306/mysql"/>
>
>
>
>
> Code:
>
> import java.sql.Connection;
>        import java.sql.ResultSet;
>        import java.sql.Statement;
>
>        import org.apache.tomcat.jdbc.pool.DataSource;
>        import org.apache.tomcat.jdbc.pool.PoolProperties;
>
>        public class SimplePOJOExample {
>
>            public static void main(String[] args) throws Exception {
>                PoolProperties p = new PoolProperties();
>                p.setUrl("jdbc:mysql://localhost:3306/mysql");
>                p.setDriverClassName("com.mysql.jdbc.Driver");
>                p.setUsername("root");
>                p.setPassword("password");
>                p.setJmxEnabled(true);
>                p.setTestWhileIdle(false);
>                p.setTestOnBorrow(true);
>                p.setValidationQuery("SELECT 1");
>                p.setTestOnReturn(false);
>                p.setValidationInterval(30000);
>                p.setTimeBetweenEvictionRunsMillis(30000);
>                p.setMaxActive(100);
>                p.setInitialSize(10);
>                p.setMaxWait(10000);
>                p.setRemoveAbandonedTimeout(60);
>                p.setMinEvictableIdleTimeMillis(30000);
>                p.setMinIdle(10);
>                p.setLogAbandoned(true);
>                p.setRemoveAbandoned(true);
>
>  p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
>
>  "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
>                DataSource datasource = new DataSource();
>                datasource.setPoolProperties(p);
>
>                Connection con = null;
>                try {
>                  con = datasource.getConnection();
>                  Statement st = con.createStatement();
>                  ResultSet rs = st.executeQuery("select * from user");
>                  int cnt = 1;
>                  while (rs.next()) {
>                      System.out.println((cnt++)+". Host:"
> +rs.getString("Host")+
>                        " User:"+rs.getString("User")+"
> Password:"+rs.getString("Password"));
>                  }
>                  rs.close();
>                  st.close();
>                } finally {
>                  if (con!=null) try {con.close();}catch (Exception ignore)
> {}
>
>                }
>            }
>
>        }
>
>
> On 10/30/2009 09:54 AM, Filip Hanik - Dev Lists wrote:
>
>>
>>
>> http://svn.apache.org/viewvc/tomcat/trunk/modules/jdbc-pool/java/org/apache/tomcat/jdbc/pool/interceptor/StatementFinalizer.java?view=log
>>
>> Filip
>>
>> On 10/30/2009 08:34 AM, Josh Gooding wrote:
>>
>>> Hey what API holds the statementFinalizer?
>>>
>>> On Fri, Oct 30, 2009 at 9:57 AM, Josh Gooding<josh.gooding@...
>>> >wrote:
>>>
>>>  AHHHHHH, I will read the API for the StatementFinalizer.  I was looking
>>>> at
>>>> something to do that.  Thank you Filip!
>>>>
>>>>
>>>> On Fri, Oct 30, 2009 at 9:53 AM, Filip Hanik - Dev Lists<
>>>> devlists@...>  wrote:
>>>>
>>>>  hi Josh, calling Connection.close() does not close statements and
>>>>> resultsets.
>>>>> There is an interceptor you can configure called StatementFinalizer
>>>>> that
>>>>> does exactly that during the close call.
>>>>>
>>>>> Filip
>>>>>
>>>>>
>>>>>
>>>>> On 10/29/2009 07:17 PM, Josh Gooding wrote:
>>>>>
>>>>>  I wrote some code on top of the Tomcat's ConnectionPool class.  In
>>>>>> regular
>>>>>> Java based programming if I close a ResultSet with connection.close(),
>>>>>> this
>>>>>> frees up both the statement and resultset's memory associated with the
>>>>>> connection if it was still open.  If I close a connection with
>>>>>> Tomcat's
>>>>>> ConnectionPool, does it also close the statement and resultset's
>>>>>> associated
>>>>>> with that particular connection or do I need to manually close them?
>>>>>>
>>>>>> I know best practice is to not rely on anything to be closed
>>>>>> automatically,
>>>>>> but I inherited a code base and I am looking at making some pretty
>>>>>> significant changes to fix some problems, and this is one of them.
>>>>>>
>>>>>> Thanks in advance,
>>>>>>
>>>>>> - Josh
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: users-unsubscribe@...
>>>>> For additional commands, e-mail: users-help@...
>>>>>
>>>>>
>>>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscribe@...
>> For additional commands, e-mail: users-help@...
>>
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>

[Propes, Barry L]

Where does it close the connection? Looks like it gets it, but the close part is commented out. The rs gets closed, but the connection gets recycled. Theoretically.

Seems like you put a finally block down there to try catching/closing the wayward connection.


-----Original Message-----
From: Josh Gooding [mailto:josh.gooding@...]
Sent: Friday, October 30, 2009 1:43 PM
To: Tomcat Users List
Subject: Re: ConnectionPool question

I also found this tid bit lying around.  It get's a connection, but doesn't close the statement.

    public static void closeResources(ResultSet rs) throws AardvarkResourceException {
        try {
            Statement s = rs.getStatement();
            if (s != null) { // ResultSets produced by metadata queries do not have associated statements
                Connection c = s.getConnection();
                //s.close();
                //c.close();
                ConnectionPool.recycleConnection(c);
            }
            rs.close();
        } catch (SQLException e) {
            throw new AardvarkResourceException("Error closing resources associated with ResultSet", e);
        }
    }

It still doesn't close the statement, but closes the connection leaving the statement hanging.... I'll fix and make sure to use it.

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@...
For additional commands, e-mail: users-help@...

[JoshInWV]

Barry I changed the code to this:

public static void closeResources(ResultSet rs) throws
AardvarkResourceException {
        Statement s = null;
        Connection c = null;

        try {
            s = rs.getStatement();
            if (s != null) {
                c = s.getConnection();
            }
            rs.close();
        }
        catch (SQLException e) {
            throw new AardvarkResourceException("Error closing resources
associated with ResultSet", e);
        }
        finally
        {
            if(null != rs) try { rs.close(); }
                catch (SQLException sqle)
                { sqle.printStackTrace(); }

            if(null != s) try { s.close(); } catch (SQLException sqle)
                { sqle.printStackTrace(); }

            if(null != c)
                try { c.close(); } catch (SQLException sqle)
                { sqle.printStackTrace(); }
        }
    }

No luck using the DataSoruceConnectionFactory though. I am using 6.0.18.

On Fri, Oct 30, 2009 at 4:28 PM, Propes, Barry L <barry.l.propes@...>wrote:

> Where does it close the connection? Looks like it gets it, but the close
> part is commented out. The rs gets closed, but the connection gets recycled.
> Theoretically.
>
> Seems like you put a finally block down there to try catching/closing the
> wayward connection.
>
>
> -----Original Message-----
> From: Josh Gooding [mailto:josh.gooding@...]
> Sent: Friday, October 30, 2009 1:43 PM
> To: Tomcat Users List
> Subject: Re: ConnectionPool question
>
> I also found this tid bit lying around.  It get's a connection, but doesn't
> close the statement.
>
>    public static void closeResources(ResultSet rs) throws
> AardvarkResourceException {
>        try {
>            Statement s = rs.getStatement();
>            if (s != null) { // ResultSets produced by metadata queries do
> not have associated statements
>                Connection c = s.getConnection();
>                //s.close();
>                //c.close();
>                ConnectionPool.recycleConnection(c);
>            }
>            rs.close();
>        } catch (SQLException e) {
>            throw new AardvarkResourceException("Error closing resources
> associated with ResultSet", e);
>        }
>    }
>
> It still doesn't close the statement, but closes the connection leaving the
> statement hanging.... I'll fix and make sure to use it.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@...
> For additional commands, e-mail: users-help@...
>
>