DBCP Configuration with Tomcat 4.1.27 and mySQL

[Tom Martin]

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.servic e(JspServletWrapper.java:254)
at org.apache.jasper.servlet.JspServlet.serviceJspFil e(JspServlet.java:295)
at org.apache.jasper.servlet.JspServlet.service(JspSe rvlet.java:241)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain............. ..
root cause

java.lang.NullPointerException
at jsp.sanjamar.test.CatMan.SelectSubCategories(CatMa n.java:110)
at jsp.sanjamar.test.CatMan.ListSubCategories(CatMan. java:54)
at org.apache.jsp.showproducts_jsp._jspService(showpr oducts_jsp.java:135)
at org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:137)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:853)
at org.apache.jasper.servlet.JspServletWrapper.servic e(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.BasicDataSourceFact ory</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++;
}
%>

[Jared Dykstra]

we*******@sanjamar.com (Tom Martin) wrote in message news:<1e*************************@posting.google.c om>...

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(CatMa n.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

[Tom Martin]

dy******@hotmail.com (Jared Dykstra) wrote in message news:<ba**************************@posting.google. com>...

we*******@sanjamar.com (Tom Martin) wrote in message news:<1e*************************@posting.google.c om>...

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(CatMa n.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

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

[Tom Martin]

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

[Tom Martin]

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

[Jared Dykstra]

we*******@sanjamar.com (Tom Martin) wrote in message news:<1e**************************@posting.google. com>...

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.

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

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