commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jon Wilmoth <jonwilm...@yahoo.com>
Subject Re: [dbcp] Connection pooling error w/MySQL
Date Mon, 13 Oct 2003 04:16:41 GMT
Sorry about the delay.  Here's everything you've asked
for:

There is a servlet that is loaded at application
startup that performs a db connection test.  Calls to
getConnection() on the DataSource after the webapp has
been deployed also fail with the same error.  Since
Tomcat doesn't support external jvm access to the JNDI
implementation, I'm including the code for a struts
action used to test datasource health.

HARDWARE - Compaq n600
OS - Windows XP Professional SP1
JAVA - HotSpot(TM) Server VM (build 1.4.2_01-b06,
mixed mode)
NUMBER_OF_PROCESSORS=1
PROCESSOR_IDENTIFIER=x86 Family 6 Model 11 Stepping 1,
GenuineIntel
NETWORK - N/A.  Both tomcat & mysql server are on same
machine in development env.
MYSQL - 4.0.15-NT using InnoDB
JDBC DRIVER - mysql-connector-java-3.0.8-stable

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

<ResourceParams name="jdbc/ChronosPool">
  <parameter>
    <name>factory</name>
   
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
  </parameter>
  <parameter>
	<name>username</name>
	<value>mydbuser</value>
  </parameter>
  <parameter>
	<name>password</name>
	<value>mydbuserpassword</value>
  </parameter>
  <parameter>
	<name>driverClassName</name>
	<value>com.mysql.jdbc.Driver</value>
  </parameter>
  <parameter>
	<name>url</name>
	<value>jdbc:mysql://localhost:3306/testDB</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>maxActive</name>
	<value>15</value>
  </parameter>
  <parameter>
	<name>maxIdle</name>
	<value>3</value>
  </parameter>
  <parameter>
	<name>maxWait</name>
	<value>-1</value>
  </parameter>
  <parameter>
	<name>validationQuery</name>
	<value>select sysdate from dual</value>
  </parameter>
  <parameter>
	<name>defaultAutoCommit</name>
	<value>false</value>
  </parameter>
</ResourceParams>


Test Case Files:
========================
struts-config.xml
========================
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts-config PUBLIC "-//Apache Software
Foundation//DTD Struts Configuration 1.1//EN"
"http://jakarta.apache.org/struts/dtds/struts-config_1_1.dtd">
<struts-config>
	<form-beans>
		<form-bean name="DataSourceTestForm"
type="org.apache.struts.action.DynaActionForm">
			<form-property name="jndiName"
type="java.lang.String" initial="jdbc/ChronosPool"/>
			<form-property name="testSQL"
type="java.lang.String" initial="x_link"/>
		</form-bean>
	</form-beans>
	<action-mappings>
		<action path="/system/testDataSource"
		
type="com.apex.chronos.ui.system.TestDataSourceAction"
			name="DataSourceTestForm"
			scope="request"
			input="system/healthCheck.jsp"
			validate="false">
			<forward name="success"
path="/system/healthCheck.jsp"/>
			<forward name="failure"
path="/system/healthCheck.jsp"/>
		</action>
	</action-mappings>
</struts-config>

==========================
TestDataSourceAction.java
==========================
package com.apex.chronos.ui.system;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import javax.naming.InitialContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.DynaActionForm;

public class TestDataSourceAction extends Action {

	public static final String DATASOURCE_TEST_RESULTS =
"dataSourceTestResults";
	public static final String JDBC_SUBCONTEXT_NAME =
"java:comp/env/jdbc";

    public ActionForward execute(ActionMapping
mapping, ActionForm form,
								 HttpServletRequest request,
HttpServletResponse response) {
		DynaActionForm myForm = (DynaActionForm) form;


		String dataSrc = (String) myForm.get("jndiName");
		String sql = (String) myForm.get("testSQL");

		String dataSrcJndiPath = JDBC_SUBCONTEXT_NAME + "/"
+ dataSrc;
		String result = "healthy";
		PreparedStatement ps = null;
		Connection conn = null;
		InitialContext ctx = null;
		try {
			ctx = new InitialContext();

			//Lookup specified data source
			DataSource ds = (DataSource)
ctx.lookup(dataSrcJndiPath);

			//run sql through to ensure healthy connection
			conn = ds.getConnection();
			ps = conn.prepareStatement(sql);
			boolean didExecute = ps.execute();
			if (!didExecute) {
				result = "failed: No result set produced from sql:
" + sql;
			}
		} catch (Exception e) {
			result = "failed: " + e.getMessage();
		} finally {
			if (ctx != null) {
				try {
					ctx.close();
				} catch (Exception e) {}//ignore
			}

			if (ps != null) {
				try {
					ps.close();
					ps = null;
				} catch (Exception e){}//ignore
			}
			if (conn != null) {
				try {
					conn.close();
					conn = null;
				} catch (Exception e){}//ignore
			}
		}

		request.setAttribute(DATASOURCE_TEST_RESULTS,
result);
        return mapping.findForward("success");
	}
}


=================
healthCheck.jsp
=================
<%@ page
   language="java"
   session="true"
   isThreadSafe="false"
   contentType="text/html"
   isErrorPage="false"
%>

<%@ page
import="com.apex.chronos.ui.system.TestDataSourceAction"
%>
<%@ page import="com.apex.common.SystemException" %>
<%@ page import="com.apex.common.jndi.TreeUtil" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="javax.naming.Context" %>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.naming.NamingEnumeration" %>
<%@ page import="javax.sql.DataSource" %>

<%@ taglib uri="struts-html.tld" prefix="html" %>
<%@ taglib uri="struts-bean.tld" prefix="bean" %>
<%@ taglib uri="struts-logic.tld" prefix="logic" %>

<h4>Test DataSources for connectivity health</h4>
<table border="0">
<html:form action="/system/testDataSource"
focus="jndiName">
	<tr>
		<th>DataSource</th>
		<th>Health Check Query</th>
	</tr>
	<tr>
		<td>
			<html:text property="jndiName" size="40"/>		</td>
		<td>
			<html:text property="testSQL" size="60"/> <b>;</b>
		</td>
	</tr>
	<tr>
		<td>
			<html:submit value="Test DataSource"/>
		</td>
		<td>
			<html:cancel/>
		</td>
	</tr>
</html:form>
</table>


<logic:present name="<%=
TestDataSourceAction.DATASOURCE_TEST_RESULTS %>">
<hr/>
	<h4><bean:write name="<%=
TestDataSourceAction.DATASOURCE_TEST_RESULTS
%>"/></h4>
</logic:present>


--- Dirk Verbeeck <dirk.verbeeck@pandora.be> wrote:
> Let us first gather some more info.
> Hardware, OS, network (firewall, ...), versions of
> all relevant software
> Configuration of your database pool (server.xml) &
> database server (host 
> access parameters)
> 
> Is it during startup / under load?
> Can you provide a small test case?
> 
> Can you retest using the latest versions of DBCP &
> pool (v1.1RC1)?
> http://cvs.apache.org/~dirkv/builds/
> 
> At the bottom of your stack trace you see that the
> cause is inside the 
> driver
> 
> ================
> Caused
> by: java.sql.SQLException: Unable to connect to any
> hosts due to exception: java.net.BindException:
> Address already in use: connect        at
>
com.mysql.jdbc.Connection.createNewIO(Connection.java:1622)
>        at
>
com.mysql.jdbc.Connection.<init>(Connection.java:491)
> 
>       at
>
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:346)
>        at
>
org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:83)
> ================
> 
> But  let us investigate further...
> 
> Dirk
> 
> 
> Jon Wilmoth wrote:
> 
> >After doing some further research I think there's
> >might be a bug in Jakarata Common's DBCP.  I was
> able
> >to successfully configure and test a connection
> pool
> >using the same MySQL Driver and connection
> parameters
> >with Weblogic 8.1 SP1.  Should this move to the
> >developers list?  Into Bugzilla?
> >  
> >
> 
> 
> 
> 
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail:
> commons-user-help@jakarta.apache.org
> 



---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message