Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...
Java Forum / General / January 2004

Tip: Looking for answers? Try searching our database.

DBCP Configuration with Tomcat 4.1.27 and mySQL

Thread view: 
Enable EMail AlertsStart New Thread
Tom Martin - 29 Dec 2003 20:24 GMT
I'm a Java Web developer NEWBIE that has inherited a website that
fails every 2 hours due to poor connection pooling between Tomcat
4.0.6 and mySQL.  In efforts to resolve this problem, I've created
another replica DEVELOPMENT website and upgraded it to Tomcat 4.1.27.
I was told this version of Tomcat supports Database Connection Pooling
(DBCP) better than previous versions.

I followed the instructions as listed at:
<http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html>
with minor configurations to adjust for my environment.

Something I've done doesn't seem to be working...  Could someone
please assist?

Inserted below, the error message and snippets of the configuration
files; ERROR on WEB PAGE, Server.xml, web.xml, Prop.java, CatMan.java,
and showproducts.jsp.

==========ERROR on WEB PAGE==================

type Exception report

message

description The server encountered an internal error () that prevented
it from fulfilling this request.

exception

org.apache.jasper.JasperException
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:254)
    at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
    at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain...............
root cause

java.lang.NullPointerException
    at jsp.sanjamar.test.CatMan.SelectSubCategories(CatMan.java:110)
    at jsp.sanjamar.test.CatMan.ListSubCategories(CatMan.java:54)
    at org.apache.jsp.showproducts_jsp._jspService(showproducts_jsp.java:135)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
    at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210)................

==========SERVER.XML=========================
     <Host name="www.sanjamar2003.kattare.com" debug="0"
appBase="public_html" unpackWARs="true" autoDeploy="true">
       <Alias>sanjamar2003.kattare.com</Alias>
       <Alias>maya.kattare.com</Alias>

       <Logger className="org.apache.catalina.logger.FileLogger"
                directory="logs"
prefix="sanjamar2003.kattare.com_log." suffix=".txt"
           timestamp="true"/>
<Context path="/sanjamar" docBase="sanjamar"
                debug="5" reloadable="true" crossContext="true">
       
         <Logger className="org.apache.catalina.logger.FileLogger"
                    prefix="localhost_DBCP_log." suffix=".txt"
                    timestamp="true"/>
       
         <Resource name="jdbc/sanjamar"
                      auth="Container"
                      type="javax.sql.DataSource"/>
       
         <ResourceParams name="jdbc/sanjamar">
            <parameter>
             <name>factory</name>
             <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
            </parameter>
            <parameter>
             <name>maxActive</name>
             <value>100</value>
            </parameter>
       
            <parameter>
             <name>maxIdle</name>
             <value>30</value>
            </parameter>
       
            <parameter>
             <name>maxWait</name>
             <value>10000</value>
            </parameter>
           
            <parameter>
             <name>removeAbandoned</name>
             <value>true</value>
           </parameter>
           
            <parameter>
             <name>removeAbandonedTimeout</name>
             <value>60</value>
           </parameter>
           
            <parameter>
            <name>username</name>
            <value>xxxxxxxxxxxxxxx</value>
            </parameter>
           
            <parameter>
            <name>password</name>
            <value>xxxxxxxxxxxxxxxxx</value>
            </parameter>
   
            <parameter>
              <name>driverClassName</name>
              <value>org.gjt.mm.mysql.Driver</value>
            </parameter>
       
            <parameter>
             <name>url</name>
             <value>jdbc:mysql://dc1-mysql-01.kattare.com:3306/sanjamar</value>
            </parameter>
           
         </ResourceParams>
        </Context>
     </Host>

==========WEB.XML============================
<web-app>
 <description>MySQL App</description>
 <resource-ref>
     <description>DB Connection</description>
     <res-ref-name>jdbc/sanjamar</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
 </resource-ref>
</web-app>

==========Prop.java============================

public Prop() {
       // Setting up database connection, etc.
 
       errout = new PrintWriter(System.err);
       
       try {
           Class.forName("org.gjt.mm.mysql.Driver");
       } catch (ClassNotFoundException e) {
           errout.println("ClassNotFoundException: "
           +e.getMessage());
           System.err.println("ClassNotFoundException: "
           +e.getMessage());
       } catch (Exception e2) {
           System.err.println("Exception: "+e2.getMessage());
       }
       try {
             
             Context ctx = new InitialContext();
             if(ctx == null )
                 throw new Exception("No Context");
             DataSource ds =
(DataSource)ctx.lookup("java:comp/env/jdbc/sanjamar");
             if (ds != null) {
               Connection con = ds.getConnection();              
               /*Original Code
               con =
DriverManager.getConnection("jdbc:mysql://127.0.0.1/sanjamar",
"xxxxxxxxxxxx", "xxxxxxxxxxx");  */
               stmt = con.createStatement();
             }
           
       } catch (SQLException ex) {
           errout.println("SQLException: "+ex.getMessage());
           errout.flush();
           System.err.println("SQLException: "+ex.getMessage());
       } catch (Exception e2) {
           System.err.println("Exception: "+e2.getMessage());
       }
   } // end constructor
==========CatMan.java==========================
public class CatMan extends Prop { // category manager

     public String ListSubCategories(String category) {
        // returns the specified subcategories for the provided category
     try {              
               //Get recordset
               SelectSubCategories(category);
               
               String errormsgs = new String ("Error no
subcategories");
        if (rs==null) return errormsgs;
           
               String subcategory = "";
               
        while (rs.next()) {
           
                       String newsubcategory = rs.getString("name");
            subcategory = subcategory + "<br><a href='#" + newsubcategory +
"'>" + newsubcategory +"</a>";
        }
               return subcategory;
               
     } catch (SQLException ex) {
        errout.println("Can't get "+category+" subcategory: "+
            ex.getMessage());
        errout.flush();
        System.err.println("Can't get "+category+" subcategory: "+
            ex.getMessage());
        return null;
     }
    }  

    public boolean SelectSubCategories(String MajorCat) {
      try {
            rs = stmt.executeQuery("SELECT name FROM prodtypes "+
                "WHERE category = '"+MajorCat+"' group by name order by name;");
      }
      catch (SQLException ex) {
        errout.println("Can't List Sub Categories: "+ex.getMessage());
        errout.flush();
        System.err.println("Can't List Sub Categories: "+ex.getMessage());
        return false;
      }
      return true;
       }
}

==========showproducts.jsp==========================
<%@ page import="java.net.*" %>
<%@ page import="jsp.sanjamar.test.*" %>
<%
    int prodid = 0; // for mouseovers
    CatMan cm = new CatMan();
    cm.setRequest(request);
    PriceDB pdb = new PriceDB();
    pdb.setRequest(request);
    if (!cm.ListTypes(category)) {
        %><h2>Unable to list types for category
            <%= category %></h2>
       
     <%
        out.flush();
        return;
    }
    while (cm.NextType()) {
        prodid++;
        String type = cm.GetNext("name");
        String subtitle = cm.GetNext("subtitle",false);

        int numprods = 0;
        pdb.ListProducts(subtitle);
        while (pdb.NextProduct()) {
            %><a name="<%= pdb.ListCol("prodnum",false) %>">
     <%
            numprods++;
        }
%
Reply to this Message
Jared Dykstra - 30 Dec 2003 05:23 GMT
> I'm a Java Web developer NEWBIE that has inherited a website that
> fails every 2 hours due to poor connection pooling between Tomcat
> 4.0.6 and mySQL.  In efforts to resolve this problem, I've created
> another replica DEVELOPMENT website and upgraded it to Tomcat 4.1.27.

Well, tomcat is telling you where the program fails:
java.lang.NullPointerException
at jsp.sanjamar.test.CatMan.SelectSubCategories(CatMan.java:110)
at jsp.sanjamar.test.CatMan.ListSubCategories(CatMan.java:54)

Unfortunately, from your previous post, I have no idea what line 110
of CatMan.java is, nor do I know which line corresponds to #54.  Some
operation is being performed on a NULL object.  Look at these two
lines and the problem will probably become obvious.  It's likely a
logic problem, not a server configuration issue.

---
Jared Dykstra
http://www.bork.org/~jared
Reply to this Message
Tom Martin - 31 Dec 2003 19:08 GMT
> > I'm a Java Web developer NEWBIE that has inherited a website that
> > fails every 2 hours due to poor connection pooling between Tomcat
[quoted text clipped - 15 lines]
> Jared Dykstra
> http://www.bork.org/~jared

Jared,

Thanks for taking the time to even look at this - much appreciated.

Line 54   SelectSubCategories(category);
Line 110  rs = stmt.executeQuery("SELECT name FROM prodtypes "+
Line 111  "WHERE category = '"+MajorCat+"' group by name order by
name;");

I don't get this error on the live site (i.e. Tomcat 4.0.6).  Agreed,
from what little I've learned thus far, this doesn't seem DBCP
related.  Question: Could I have coded this below snippet incorrectly?
Thus the con object isn't created successfully, therefore, it can't
pass it (via extends Prop) to the CatMan.class for Line 110 - "stmt".

   //New Code for DBCP
   Connection con = ds.getConnection();              
   //Original Code
   //con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/sanjamar",
"xxxxxxxxxx", "xxxxxxxxxxx");

Thoughts?

Thanks in advance
Reply to this Message
Tom Martin - 06 Jan 2004 17:00 GMT
Inserted below, the error message and snippets of the configuration
files; ERROR in Catalina.out and revised Prop.java.

==========ERROR in Catalina.out==================

Context Created Successfully - org.apache.commons.dbcp.BasicDataSource@6355dc
GetConnection_Error: Cannot load JDBC driver class 'null'
Exception: null

NOTE: This is a result of the line; Connection con = ds.getConnection();  

==========Prop.java============================
   public Prop() {
       // Setting up database connection, etc.
       errout = new PrintWriter(System.err);
       
       try {
           Class.forName("org.gjt.mm.mysql.Driver");
       } catch (ClassNotFoundException e) {
           errout.println("ClassNotFoundException: "
           +e.getMessage());
           System.err.println("ClassNotFoundException: "
           +e.getMessage());
       } catch (Exception e2) {
           System.err.println("Exception: "+e2.getMessage());
       }
       
        DataSource ds = null;
        try
        {
            InitialContext ctx = new InitialContext();
            ds = (DataSource)ctx.lookup ("java:comp/env/jdbc/sanjamar" );
            System.err.println("Context Created Successfully - " + ds);
        }
        catch (Exception e2)
        {
           System.err.println("Context_Exception: " + e2.getMessage());
       }

       try
        {
          if (ds != null)
          {
                try
                {
                  Connection con = ds.getConnection();  
                  System.err.println("GetConnection created successfully.");
                }
                catch (SQLException ex)
                {
                    System.err.println("GetConnection_Error: "+ex.getMessage());
                }
                try
                {
                    stmt = con.createStatement();
                    System.err.println("STMT created successfully.");
                }
                catch (SQLException ex)
                {
                    System.err.println("STMT_Error: "+ex.getMessage());
                }
          }
          else System.err.println("DS is null.");
       }
        catch (Exception e2)
        {
           System.err.println("Exception: "+e2.getMessage());
       }
}

Any ideas?

Thanks in advance,

Tom Martin
San Jamar a division of The Colman Group, Inc.
IT Technical Support, Application & Web Development
262.723.6133 x120
Reply to this Message
Tom Martin - 15 Jan 2004 16:18 GMT
Thanks everyone for your help.  Ultimately, I found the solution or a
valid workaround to my DBCP problems...

Turns out that the combination of *.java and *.jsp pages were not able
to create a good DBCP connection using the Context and parameters
defined in the server.xml.

I had to create the Context in the Prop.java file (snippets of file
below):

import org.apache.commons.dbcp.BasicDataSource;

   public Prop() {
       errout = new PrintWriter(System.err);
       
        BasicDataSource ds = null;
        try
        {
            System.err.println(System.getProperty("java.class.path"));
            InitialContext ctx = new InitialContext();
            ds = (BasicDataSource)ctx.lookup ("java:comp/env/jdbc/sanjamar" );
            initDataSource(ds);
            System.err.println("Context Created Successfully - " +
ds);
            System.err.println("DS user name/password:" +
ds.getUsername() + "/" + ds.getPassword());
            System.err.println("DS driver class name:" +
ds.getDriverClassName());
            System.err.println("DS url:" + ds.getUrl());
        }
        catch (Exception e2)
        {
           System.err.println("Context_Exception: " +
e2.getMessage());
           }

       try
        {
          if (ds != null)
          {
                try
                {
                  con = ds.getConnection();  
                  System.err.println("GetConnection created successfully.");
                  System.err.println("Connection - " + con);
                }
                catch (SQLException ex)
                {
                    System.err.println("GetConnection_Error: "+ex.getMessage());
                }
                try
                {
                    stmt = con.createStatement();
                    System.err.println("STMT created successfully.");
                }
                catch (Throwable ex)
                {
                    System.err.println("STMT_Error: "+ex.getMessage());
                }
          }
          else System.err.println("DS is null.");
       }
        catch (Exception e2)
        {
           System.err.println("Exception: "+e2.getMessage());
       }

private static void initDataSource(BasicDataSource ds)
{
     ds.setMaxActive(100);
     ds.setMaxIdle(30);
     ds.setMaxWait(10000);
     ds.setRemoveAbandoned(true);
     ds.setRemoveAbandonedTimeout(60);
     ds.setDriverClassName("org.gjt.mm.mysql.Driver");

     ds.setUsername("************");
     ds.setPassword("************");
     ds.setUrl("jdbc:mysql://69.59.195.2:3306/sanjamar?autoReconnect=true");
     
}

Once I did this, the DBCP worked, but I still had problems related to
the Maximun number of connections mySQL would allow me to create (i.e.
my ISP limits shared websites to 10 Database connections).

Error in catalina.out:  GetConnection_Error: DBCP could not obtain an
idle db connection, pool exhausted.

So I had to change ds.setMaxActive(100); to ds.setMaxActive(9);.  Then
the above error message went away

Other than that, I just had to verify that all the opened and closed
recordsets, statements, and connections were closed properly.

Thanks again,

Tom Martin
Reply to this Message
Jared Dykstra - 19 Jan 2004 20:38 GMT
> Thanks everyone for your help.  Ultimately, I found the solution or a
> valid workaround to my DBCP problems...
[quoted text clipped - 19 lines]
>
> Tom Martin

Tom,

Sorry for my absense in this thread.  Glad to hear you found the null
connection object which was causing the exception.

---
Jared Dykstra
http://www.bork.org/~jared
Reply to this Message


Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.