Return-Path: <anonymous@nagoya.betaversion.org>
Mailing-List: contact commons-dev-help@jakarta.apache.org; run by ezmlm
Delivered-To: mailing list commons-dev@jakarta.apache.org
Received: (qmail 43391 invoked from network); 20 Aug 2003 01:06:17 -0000
Received: from exchange.sun.com (192.18.33.10)
  by daedalus.apache.org with SMTP; 20 Aug 2003 01:06:17 -0000
Received: (qmail 9626 invoked by uid 50); 19 Aug 2003 22:22:15 -0000
Date: 19 Aug 2003 22:22:15 -0000
Message-ID: <20030819222215.9625.qmail@nagoya.betaversion.org>
From: bugzilla@apache.org
To: commons-dev@jakarta.apache.org
Cc: 
Subject: DO NOT REPLY [Bug 21182]  -
    [dbcp] removing a webapp does not force connections closed
X-Spam-Rating: daedalus.apache.org 1.6.2 0/1000/N

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://nagoya.apache.org/bugzilla/show_bug.cgi?id=21182>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

http://nagoya.apache.org/bugzilla/show_bug.cgi?id=21182

[dbcp] removing a webapp does not force connections closed





------- Additional Comments From dirk.verbeeck@pandora.be  2003-08-19 22:22 -------
More info from Michael Holly (commons-user 11/08/2003)
------------------------------------------------------------
I had much the same problem.  Starting/Stopping webapps did not deal with
cleaning up the pool correctly.

Since I was trying to implement junit and other testing mechanisims I wanted
my build script to stop, build, and
start the the app, then test it.  My problem was that the pools were being
treated as a quasi server/webapp resource.
For the current configurations I couldn't find one that would take ownership
of the pool. Then somebody told me
to create a Context listener and use it to clean up the pool when the webapp
shutdown occurs.  After several
iterations I go this to work.  I have checked it against my db through
several shutdown startup cycles and
it has yet to lose track of a connection.

Here is my Context Listener

/**
 *  The listener runs when the app is started and shutdown
 *
 * @author  Michael Holly
 * created  Apr 18, 2003
 */
package net.talisen.tsr;

import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.naming.NamingException;
import javax.naming.NamingEnumeration;

import org.apache.commons.dbcp.BasicDataSource;
import javax.servlet.*;
import org.apache.log4j.Logger;
import java.util.ResourceBundle;
import java.net.URL;
import java.net.MalformedURLException;
import java.io.*;
import java.util.*;
import org.apache.log4j.Logger;
import org.apache.log4j.Level;
import org.apache.log4j.PropertyConfigurator;

public final class ContextListener
implements ServletContextListener
{

   //get a logger
   Logger log = Logger.getLogger(ContextListener.class);

   private InitialContext initialContext = null;
   private Context namingContext = null;
   private ServletContext context = null;

   public void contextInitialized (ServletContextEvent servletContextEvent)
   {

      context = servletContextEvent.getServletContext ();
      try
      {

         System.out.println(" \n\n Intializing Context");
         log.info("Initializing logging");
         // configure the Log4j system
         String file = new String( "/WEB-INF/classes/log4j.properties" );
         URL url = context.getResource(file);
         PropertyConfigurator.configure( url );
         System.out.println("Log4j Properties @ " + url.toString() );


         log.info("Cataloging Context Resources");
         initialContext = new InitialContext();

         namingContext = (Context) initialContext.lookup("java:comp/env");

         DataSource ds1 =
(DataSource)namingContext.lookup("jdbc/oracle_myapp");
         DataSource ds2 =
(DataSource)namingContext.lookup("jdbc/oracle_company_db");

         context.setAttribute("dataSource1", ds1);
         context.setAttribute("dataSource2", ds2);

         log.info("oracle_myapp connection pool cataloged");
         log.info("oracle_company_db connection pool cataloged");

      }
      catch (NamingException ne)
      {
         log.error("Couldn't create context attribute: " + ne.getMessage
());
         ne.printStackTrace();
      }
      catch (Exception e)
      {
         log.error("Couldn't create context attribute: " + e.getMessage ());
         e.printStackTrace();
      }
   }

   public void contextDestroyed (ServletContextEvent servletContextEvent)
   {

      DataSource ds1 = ((DataSource) context.getAttribute("dataSource1"));
      DataSource ds2 = ((DataSource) context.getAttribute("dataSource2"));

      try
      {

         log.info("Cleaning up Context Resources");

         if (ds1 instanceof org.apache.commons.dbcp.BasicDataSource) {
            log.info("Found oracle_tsr connection pool " + ds1.toString());
            ((org.apache.commons.dbcp.BasicDataSource) ds1).close();
            ds1 = null;
         }
         log.info("Removed oracle_myapp connection ");

         if (ds2 instanceof org.apache.commons.dbcp.BasicDataSource) {
            log.info("Found oracle_talisen connection pool " +
ds2.toString() );
            ((org.apache.commons.dbcp.BasicDataSource) ds2).close();
            ds2 = null;
         }
         log.info("Removed oracle_company_db connection");


         context.removeAttribute ("dataSource1");
         context.removeAttribute ("dataSource2");
      }
      catch (Exception e)
      {
         log.error("Error destroying Context: " + e.getMessage ());
         e.printStackTrace();
      }
      finally
      {

System.out.println("########################################################
###########################################");

System.out.println("########################################################
###########################################");
         System.out.println("");
         System.out.println("");
         System.out.println("");
         System.out.println("");
         System.out.println("");
         System.out.println("");
         System.out.println("");
         System.out.println("");
      }
   }
}

then I add the Contect Listener to the web.xml file

<!-- CONTEXT LISTENER -->
    <listener>
        <listener-class>net.talisen.tsr.ContextListener</listener-class>
    </listener>



My server.xml does not contain any of the config info for the pools.  This
is
contained in a context xml file.  This file get stuffed into the META-INF
dir.
Then the whole thing gets jared up.  I deploy using Tomcat Manager.

<!--Context path="/tsr" docBase="/tsr" debug="9" reloadable="true"
crossContext="true"-->
<!--Context path="/tsr" docBase="tsr.war" debug="9" reloadable="true"
crossContext="true"-->

<Context path="/tsr" debug="9" privileged="true" docBase="C:\Tomcat
4.1\webapps\tsr" useNaming="true">

   <Loader checkInterval="6"/>

   <Logger className="org.apache.catalina.logger.FileLogger"
       prefix="localhost_tsrdb_log."
       suffix=".txt"
    timestamp="true"/>


   <Resource name="jdbc/oracle_tsr" auth="Container" scope="Shareable"
   type="javax.sql.DataSource"/>

   <ResourceParams name="jdbc/oracle_myapp">
      <parameter>
         <name>factory</name>
         <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
      </parameter>
      <parameter>
         <name>driverClassName</name>
         <!--value>oracle.jdbc.OracleDriver</value-->
         <!--value>oracle.jdbc.pool.OracleConnectionPoolDataSource</value-->
         <value>oracle.jdbc.pool.OracleDataSource</value>
      </parameter>
      <parameter>
         <name>url</name>
         <!--value>jdbc:oracle:thin:@myserver:1521:myco</value-->
      </parameter>
      <parameter>
         <name>username</name>
         <value>myapp</value>
      </parameter>
      <parameter>
         <name>password</name>
         <value>????????</value>
      </parameter>
      <parameter>
         <name>maxActive</name>
         <value>20</value>
      </parameter>
      <parameter>
         <name>maxIdle</name>
         <value>10</value>
      </parameter>
      <parameter>
         <name>maxWait</name>
         <value>-1</value>
      </parameter>
      <parameter>
         <name>removeAbandoned</name>
         <value>true</value>
      </parameter>
      <parameter>
         <name>removeAbandonedTimeout</name>
         <value>300</value>
      </parameter>
      <parameter>
         <name>logAbandoned</name>
         <value>true</value>
      </parameter>
      <parameter>
         <name>validationQuery</name>
         <value>select 'validationQuery' from dual</value>
      </parameter>
      <parameter>
         <name>testOnBorrow</name>
         <value>true</value>
      </parameter>
   </ResourceParams>

   <Resource name="jdbc/oracle_talisen" auth="Container"
type="javax.sql.DataSource"/>

   <ResourceParams name="jdbc/oracle_talisen">
      <parameter>
         <name>factory</name>
         <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
      </parameter>
      <parameter>
         <name>driverClassName</name>
         <value>oracle.jdbc.OracleDriver</value>
         <!--value>oracle.jdbc.pool.OracleConnectionPoolDataSource</value-->
         <!--value>oracle.jdbc.pool.OracleDataSource</value-->
      </parameter>
      <parameter>
         <name>url</name>
         <!--value>jdbc:oracle:thin:@myserver:1521:myco</value-->
      </parameter>
      <parameter>
         <name>username</name>
         <value>company_db</value>
      </parameter>
      <parameter>
         <name>password</name>
         <value>?????????</value>
      </parameter>
      <parameter>
         <name>maxActive</name>
         <value>20</value>
      </parameter>
      <parameter>
         <name>maxIdle</name>
         <value>10</value>
      </parameter>
      <parameter>
         <name>maxWait</name>
         <value>-1</value>
      </parameter>
      <parameter>
         <name>removeAbandoned</name>
         <value>true</value>
      </parameter>
      <parameter>
         <name>removeAbandonedTimeout</name>
         <value>300</value>
      </parameter>
      <parameter>
         <name>logAbandoned</name>
         <value>true</value>
      </parameter>
      <parameter>
         <name>validationQuery</name>
         <value>select 'validationQuery' from dual</value>
      </parameter>
      <parameter>
        <name>testOnBorrow</name>
        <value>true</value>
      </parameter>
   </ResourceParams>
</Context>

Hope this helps.

Michael