You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Merrill Cornish <me...@earthlink.net> on 2004/02/10 23:24:43 UTC
Another Try at MySQL Connection Pooling
I'm running
* Tomcat 5.0.18 running as a Wndows service,
* MySQL 4.0.16 running as a Windows service, and
* MySQL Connector/J 3.0.10 stable
all on Windows 2000 Server, which is where I'm also doing the testing (i.e., on localhost).
A number of tomcat-user mailing list denizens have given me snippets of XML to put in various places. Nothing worked. So I tried following the "MySQL Configuration" example from http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html. After all changes were made to server.xml, web.xml and timesheet.xml (the <context> fragment under conf/Catalina/localhost), I restarted Tomcat and tried to login (the first action that would have had to touch the MySQL database).
I got the following errorPage:
> At Timesheet.Util.errorPage(Util.java:550) there occurred a
> java.lang.Exception: A fatal exception occurred while retrieving login name and pass phrase because...
>
> At Timesheet.Util.getSqlConnection(Util.java:492) there occurred a
> java.lang.Exception: Unable to establish a MysqlDataSource connection for ResourceLine name "jdbc/TimesheetsDB" >because...
>
> At org.apache.naming.NamingContext.lookup(NamingContext.java:814) there occurred a
> javax.naming.NameNotFoundException: Name jdbc is not bound in this Context
OK, that seems to say it doesn't know what the name "jdbc/TimesheetsDB" is. Tomcat has written nothing to stderr.log and stdout.log shows a clean startup, so Tomcat itself isn't too perturbed. stdout.log includes the following line:
> INFO: Processing Context configuration file URL file:C:\Program Files\Apache Group\Tomcat 5.0.18\conf\Catalina\localhost\timesheet.xml
implying that my timesheet.xml context fragment file was found and processed. The Timesheet application log file contains the following:
> 2004-02-10 16:20:05 NamingContextListener[/Catalina/localhost/timesheet]: Resource parameters for jdbc/TimesheetsDB = ResourceParams[name=jdbc/TimesheetsDB, parameters={factory=org.apache.commons.dbcp.BasicDataSourceFactory, url=jdbc:mysql://localhost/Timesheets?autoReconnect=true&socketFactory=com.mysql.jdbc.NamedPipeSocketFactory, password=All41a14all, maxWait=10000, maxActive=100, driverClassName=org.gjt.mm.mysql.Driver, username=timesheet_app, maxIdle=30}]
> 2004-02-10 16:20:05 NamingContextListener[/Catalina/localhost/timesheet]: Adding resource ref jdbc/TimesheetsDB
> 2004-02-10 16:20:05 NamingContextListener[/Catalina/localhost/timesheet]: ResourceRef[className=javax.sql.DataSource,factoryClassLocation=null,factoryClassName=org.apache.naming.factory.ResourceFactory,{type=scope,content=Shareable},{type=auth,content=Container},{type=factory,content=org.apache.commons.dbcp.BasicDataSourceFactory},{type=url,content=jdbc:mysql://localhost/Timesheets?autoReconnect=true&socketFactory=com.mysql.jdbc.NamedPipeSocketFactory},{type=password,content=All41a14all},{type=maxWait,content=10000},{type=maxActive,content=100},{type=driverClassName,content=org.gjt.mm.mysql.Driver},{type=username,content=timesheet_app},{type=maxIdle,content=30}]
> 2004-02-10 16:20:05 NamingContextListener[/Catalina/localhost/timesheet]: Resource parameters for UserTransaction = null
[Blank lines added for clarity.] As per the instructions in the jndi-datasource-examples-howto.html page, my web.xml file has the following addition
> <resource-ref>
> <description>MySql Connection Pool</description>
> <res-ref-name>jdbc/TimesheetsDB</res-ref-name>
> <res-type>javax.sql.DataSource</res-type>
> <res-auth>Container</res-auth>
> </resource-ref>
immediately after the <error-page> element and immediately before the <security-constraint> element.
Now that I am using the timesheet.xml <context> fragment file, server.xml is unchanged except that I have modified it to use port 80 in place of 8080 and port 443 in place of 8443.
The timesheet.xml <context> fragment file looks like this:
<Context path="/timesheet" docBase="timesheet" debug="9" reloadable="true"
crossContext="true" displayName="ConnectTel Timesheet Application">
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_timesheet_log." suffix=".txt" timestamp="true"/>
<Resource name="jdbc/TimesheetsDB" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/TimesheetsDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxActive</name> <!-- max db connections in the pool -->
<value>100</value> <!-- 0 implies no limit -->
</parameter>
<parameter>
<name>maxIdle</name> <!-- max idle db connections to retain in pool -->
<value>30</value> <!-- 0 implies no limit -->
</parameter>
<parameter>
<name>maxWait</name> <!-- max wait for connection before throwing exception -->
<value>10000</value> <!-- -1 implies no limit (time is in milliseconds) -->
</parameter>
<parameter>
<name>username</name>
<value>timesheet_app</value>
</parameter>
<parameter>
<name>password</name>
<value>xxxxxxxxxxx</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost/Timesheets?autoReconnect=true&socketFactory=com.mysql.jdbc.NamedPipeSocketFactory</value>
</parameter>
<parameter>
<name>driverClassName</name> <!-- Class name for mm.mysql JDBC driver -->
<value>org.gjt.mm.mysql.Driver</value>
</parameter>
</ResourceParams>
</Context>
I apologize for being so verbose, but I'm trying to provide all of the relevant information in the initial message. Does anyone see what I've missed? The example in jndi-datasource-examples-howto.html does NOT look like the other things I was told to do. (?)
Merrill
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Another Try at MySQL Connection Pooling
Posted by David Smith <dn...@cornell.edu>.
I'm using this setup on a linux box and the only thing I can see
different is your URL. I'm not familiar with the named pipes method
of accessing MySQL from Tomcat. My URL is of this format:
jdbc:mysql://[server name]:3306/[database name]?autoReconnect=true
Also, did you place the mysql-connnector jar in the common/lib
directory of the Tomcat directory? It has to be there to use the
DBCP connection pool.
Beyond those things, the error is most likely in your java code
somehow. Heaven knows I've beaten my head over enough simple errors
and typos.
--David
>I'm running
> * Tomcat 5.0.18 running as a Wndows service,
> * MySQL 4.0.16 running as a Windows service, and
> * MySQL Connector/J 3.0.10 stable
>all on Windows 2000 Server, which is where I'm also doing the
>testing (i.e., on localhost).
>
>A number of tomcat-user mailing list denizens have given me snippets
>of XML to put in various places. Nothing worked. So I tried
>following the "MySQL Configuration" example from
>http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html.
>After all changes were made to server.xml, web.xml and timesheet.xml
>(the <context> fragment under conf/Catalina/localhost), I restarted
>Tomcat and tried to login (the first action that would have had to
>touch the MySQL database).
>
>I got the following errorPage:
>
>> At Timesheet.Util.errorPage(Util.java:550) there occurred a
>> java.lang.Exception: A fatal exception occurred while retrieving
>>login name and pass phrase because...
>>
>> At Timesheet.Util.getSqlConnection(Util.java:492) there occurred a
>> java.lang.Exception: Unable to establish a MysqlDataSource
>>connection for ResourceLine name "jdbc/TimesheetsDB" >because...
>>
>> At org.apache.naming.NamingContext.lookup(NamingContext.java:814)
>>there occurred a
>> javax.naming.NameNotFoundException: Name jdbc is not bound in this Context
>
>OK, that seems to say it doesn't know what the name
>"jdbc/TimesheetsDB" is. Tomcat has written nothing to stderr.log
>and stdout.log shows a clean startup, so Tomcat itself isn't too
>perturbed. stdout.log includes the following line:
>
>> INFO: Processing Context configuration file URL file:C:\Program
>>Files\Apache Group\Tomcat
>>5.0.18\conf\Catalina\localhost\timesheet.xml
>
>implying that my timesheet.xml context fragment file was found and
>processed. The Timesheet application log file contains the
>following:
>
>> 2004-02-10 16:20:05
>>NamingContextListener[/Catalina/localhost/timesheet]: Resource
>>parameters for jdbc/TimesheetsDB =
>>ResourceParams[name=jdbc/TimesheetsDB,
>>parameters={factory=org.apache.commons.dbcp.BasicDataSourceFactory,
>>url=jdbc:mysql://localhost/Timesheets?autoReconnect=true&socketFactory=com.mysql.jdbc.NamedPipeSocketFactory,
>>password=All41a14all, maxWait=10000, maxActive=100,
>>driverClassName=org.gjt.mm.mysql.Driver, username=timesheet_app,
>>maxIdle=30}]
>
>> 2004-02-10 16:20:05
>>NamingContextListener[/Catalina/localhost/timesheet]: Adding
>>resource ref jdbc/TimesheetsDB
>
>> 2004-02-10 16:20:05
>>NamingContextListener[/Catalina/localhost/timesheet]:
>>ResourceRef[className=javax.sql.DataSource,factoryClassLocation=null,factoryClassName=org.apache.naming.factory.ResourceFactory,{type=scope,content=Shareable},{type=auth,content=Container},{type=factory,content=org.apache.commons.dbcp.BasicDataSourceFactory},{type=url,content=jdbc:mysql://localhost/Timesheets?autoReconnect=true&socketFactory=com.mysql.jdbc.NamedPipeSocketFactory},{type=password,content=All41a14all},{type=maxWait,content=10000},{type=maxActive,content=100},{type=driverClassName,content=org.gjt.mm.mysql.Driver},{type=username,content=timesheet_app},{type=maxIdle,content=30}]
>
>> 2004-02-10 16:20:05
>>NamingContextListener[/Catalina/localhost/timesheet]: Resource
>>parameters for UserTransaction = null
>
>[Blank lines added for clarity.] As per the instructions in the
>jndi-datasource-examples-howto.html page, my web.xml file has the
>following addition
>
>> <resource-ref>
> > <description>MySql Connection Pool</description>
>> <res-ref-name>jdbc/TimesheetsDB</res-ref-name>
>> <res-type>javax.sql.DataSource</res-type>
>> <res-auth>Container</res-auth>
>> </resource-ref>
>
>immediately after the <error-page> element and immediately before
>the <security-constraint> element.
>
>Now that I am using the timesheet.xml <context> fragment file,
>server.xml is unchanged except that I have modified it to use port
>80 in place of 8080 and port 443 in place of 8443.
>
>The timesheet.xml <context> fragment file looks like this:
>
><Context path="/timesheet" docBase="timesheet" debug="9" reloadable="true"
> crossContext="true" displayName="ConnectTel Timesheet Application">
> <Logger className="org.apache.catalina.logger.FileLogger"
> prefix="localhost_timesheet_log." suffix=".txt" timestamp="true"/>
> <Resource name="jdbc/TimesheetsDB" auth="Container"
>type="javax.sql.DataSource"/>
> <ResourceParams name="jdbc/TimesheetsDB">
> <parameter>
> <name>factory</name>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
>
> <parameter>
> <name>maxActive</name> <!-- max db connections in the pool -->
> <value>100</value> <!-- 0 implies no limit -->
> </parameter>
>
> <parameter>
> <name>maxIdle</name> <!-- max idle db connections to retain
>in pool -->
> <value>30</value> <!-- 0 implies no limit -->
> </parameter>
>
> <parameter>
> <name>maxWait</name> <!-- max wait for connection before
>throwing exception -->
> <value>10000</value> <!-- -1 implies no limit (time is in
>milliseconds) -->
> </parameter>
>
> <parameter>
> <name>username</name>
> <value>timesheet_app</value>
> </parameter>
>
> <parameter>
> <name>password</name>
> <value>xxxxxxxxxxx</value>
> </parameter>
>
> <parameter>
> <name>url</name>
>
><value>jdbc:mysql://localhost/Timesheets?autoReconnect=true&socketFactory=com.mysql.jdbc.NamedPipeSocketFactory</value>
> </parameter>
>
> <parameter>
> <name>driverClassName</name> <!-- Class name for mm.mysql
>JDBC driver -->
> <value>org.gjt.mm.mysql.Driver</value>
> </parameter>
> </ResourceParams>
></Context>
>
>I apologize for being so verbose, but I'm trying to provide all of
>the relevant information in the initial message. Does anyone see
>what I've missed? The example in
>jndi-datasource-examples-howto.html does NOT look like the other
>things I was told to do. (?)
>
>
>Merrill
>
>
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
Re: Another Try at MySQL Connection Pooling
Posted by SMaric <sm...@ntlworld.com>.
This is what works for me - (see NOTES at end - if anyone can shed any light
on Limiting Pool size)
#1 in your Server.xml
<GlobalNamingResources>
<Resource name="MySQLDataSourceFactory"
type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource"
auth="Container"/>
<ResourceParams name="MySQLDataSourceFactory">
<parameter>
<name>factory</name>
<value>com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory</value>
</parameter>
<parameter>
<name>port</name>
<value>3306</value>
</parameter>
<parameter>
<name>user</name>
<value>dbUsername</value>
</parameter>
<parameter>
<name>password</name>
<value>dbUserPassword</value>
</parameter>
<parameter>
<name>serverName</name>
<value>dbHostIP_Name</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>dbName</value>
</parameter>
</ResourceParams>
.....
</GlobalNamingResources>
#2 in your Context section (either in its own context fragment file or
Context section of server.xml)
<Context
className="org.apache.catalina.core.StandardContext"
backgroundProcessorDelay="-1"
cachingAllowed="true"
charsetMapperClass="org.apache.catalina.util.CharsetMapper"
configFile="......\Catalina\localhost\yourContextFragment.xml" *****
NOT req'd if your Context is defined in server.xml
cookies="true"
crossContext="false"
debug="9"
displayName="Your webApp name to be displayed in webApp manager"
docBase="....../yourWebApp.war"
***** NOT req'd if deploying via Tomcat WebApp manager - see NOTEs below
domain="Catalina"
engineName="Catalina"
j2EEApplication="none"
j2EEServer="none"
lazy="true"
managerChecksFrequency="6"
path="/yourWebAppContextPath"
privileged="false"
reloadable="false"
startupTime="47"
swallowOutput="false"
tldScanTime="875"
useNaming="true"
wrapperClass="org.apache.catalina.core.StandardWrapper">
<ResourceLink
global="MySQLDataSourceFactory" **** the JNDI
name as defined in GlobalNamingResources|Resource
name="jdbc/yourJNDIResourceDBName" **** the JNDI name
as used in your webApp code - see below
type="javax.sql.DataSource"/>
</Context>
#3 you can test this with the following code fragment
System.out.println("Getting Context info");
Context initCtx = new InitialContext();
System.out.println("Got Initial Context");
Context envCtx = (Context)initCtx.lookup("java:comp/env");
System.out.println("Got JWSDP Environment Context");
try{
System.out.println("Getting MySQL DataSource");
DataSource ds =
(DataSource)envCtx.lookup("jdbc/yourJNDIResourceDBName");
if(ds != null)
{
System.out.println("Got MySQL DataSource");
Vector vConns = new Vector();
try{
for(int i = 0; i < SOMEREASONABLYLARGENUMBER; i++)
{
vConns.add(ds.getConnection());
System.out.println("Got MySQL DB Connection '" + i + "'
from Pool");
}
}catch(Exception eX){
eX.printStackTrace();
}finally{
//************* Don't forget to release the DB
Connections **********
Iterator iT = vConns.iterator();
while(iT.hasNext())
{
Connection conn = (Connection)iT.next();
conn.close();
}
}
}
else
System.out.println("NULL MySQL DataSource");
}catch(Exception eX){
eX.printStackTrace();
}
NOTEs
1 you do NOT need a <resource-ref> entry in you web.xml -
the <ResourceLink ..../> in your Context definition is an
alternative mechanism & provides the JNDI name translation from your webApp
world to the GLOBAL name in the Container world
2 Tomcat WebAdmin tool does NOT show this connection pool in
Resource|Data Sources (so don't waste time looking for it)
3 Apparently you can build the Context fragment into the WAR file (will
check this next as it means you can have a self contained webApp that you
can deploy easily via Tomcat Web Manager without Stopping/starting Tomcat
all the time)
4 Make sure the MySQL jar file
(mysql-connector-java-3.0.10-stable-bin.jar) is in
...Tomact_Install_Dir\common\lib
5 using the jdbc prefix to yourJNDIResourceDBName is just a CONVENTION -
you don'y have to comply with it
Hope this is of use
Any comments/corrections please post a reply
Stefan
--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=gcdmj-java@m.gmane.org
"Merrill Cornish" <me...@earthlink.net> wrote in message
news:5300774.1076451884406.JavaMail.root@bigbird.psp.pas.earthlink.net...
> I'm running
> * Tomcat 5.0.18 running as a Wndows service,
> * MySQL 4.0.16 running as a Windows service, and
> * MySQL Connector/J 3.0.10 stable
> all on Windows 2000 Server, which is where I'm also doing the testing
(i.e., on localhost).
>
> A number of tomcat-user mailing list denizens have given me snippets of
XML to put in various places. Nothing worked. So I tried following the
"MySQL Configuration" example from
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html.
After all changes were made to server.xml, web.xml and timesheet.xml (the
<context> fragment under conf/Catalina/localhost), I restarted Tomcat and
tried to login (the first action that would have had to touch the MySQL
database).
>
> I got the following errorPage:
>
> > At Timesheet.Util.errorPage(Util.java:550) there occurred a
> > java.lang.Exception: A fatal exception occurred while retrieving login
name and pass phrase because...
> >
> > At Timesheet.Util.getSqlConnection(Util.java:492) there occurred a
> > java.lang.Exception: Unable to establish a MysqlDataSource connection
for ResourceLine name "jdbc/TimesheetsDB" >because...
> >
> > At org.apache.naming.NamingContext.lookup(NamingContext.java:814) there
occurred a
> > javax.naming.NameNotFoundException: Name jdbc is not bound in this
Context
>
> OK, that seems to say it doesn't know what the name "jdbc/TimesheetsDB"
is. Tomcat has written nothing to stderr.log and stdout.log shows a clean
startup, so Tomcat itself isn't too perturbed. stdout.log includes the
following line:
>
> > INFO: Processing Context configuration file URL file:C:\Program
Files\Apache Group\Tomcat 5.0.18\conf\Catalina\localhost\timesheet.xml
>
> implying that my timesheet.xml context fragment file was found and
processed. The Timesheet application log file contains the following:
>
> > 2004-02-10 16:20:05
NamingContextListener[/Catalina/localhost/timesheet]: Resource parameters
for jdbc/TimesheetsDB = ResourceParams[name=jdbc/TimesheetsDB,
parameters={factory=org.apache.commons.dbcp.BasicDataSourceFactory,
url=jdbc:mysql://localhost/Timesheets?autoReconnect=true&socketFactory=com.m
ysql.jdbc.NamedPipeSocketFactory, password=All41a14all, maxWait=10000,
maxActive=100, driverClassName=org.gjt.mm.mysql.Driver,
username=timesheet_app, maxIdle=30}]
>
> > 2004-02-10 16:20:05
NamingContextListener[/Catalina/localhost/timesheet]: Adding resource ref
jdbc/TimesheetsDB
>
> > 2004-02-10 16:20:05
NamingContextListener[/Catalina/localhost/timesheet]:
ResourceRef[className=javax.sql.DataSource,factoryClassLocation=null,factory
ClassName=org.apache.naming.factory.ResourceFactory,{type=scope,content=Shar
eable},{type=auth,content=Container},{type=factory,content=org.apache.common
s.dbcp.BasicDataSourceFactory},{type=url,content=jdbc:mysql://localhost/Time
sheets?autoReconnect=true&socketFactory=com.mysql.jdbc.NamedPipeSocketFactor
y},{type=password,content=All41a14all},{type=maxWait,content=10000},{type=ma
xActive,content=100},{type=driverClassName,content=org.gjt.mm.mysql.Driver},
{type=username,content=timesheet_app},{type=maxIdle,content=30}]
>
> > 2004-02-10 16:20:05
NamingContextListener[/Catalina/localhost/timesheet]: Resource parameters
for UserTransaction = null
>
> [Blank lines added for clarity.] As per the instructions in the
jndi-datasource-examples-howto.html page, my web.xml file has the following
addition
>
> > <resource-ref>
> > <description>MySql Connection Pool</description>
> > <res-ref-name>jdbc/TimesheetsDB</res-ref-name>
> > <res-type>javax.sql.DataSource</res-type>
> > <res-auth>Container</res-auth>
> > </resource-ref>
>
> immediately after the <error-page> element and immediately before the
<security-constraint> element.
>
> Now that I am using the timesheet.xml <context> fragment file, server.xml
is unchanged except that I have modified it to use port 80 in place of 8080
and port 443 in place of 8443.
>
> The timesheet.xml <context> fragment file looks like this:
>
> <Context path="/timesheet" docBase="timesheet" debug="9" reloadable="true"
> crossContext="true" displayName="ConnectTel Timesheet
Application">
> <Logger className="org.apache.catalina.logger.FileLogger"
> prefix="localhost_timesheet_log." suffix=".txt"
timestamp="true"/>
> <Resource name="jdbc/TimesheetsDB" auth="Container"
type="javax.sql.DataSource"/>
> <ResourceParams name="jdbc/TimesheetsDB">
> <parameter>
> <name>factory</name>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
>
> <parameter>
> <name>maxActive</name> <!-- max db connections in the pool -->
> <value>100</value> <!-- 0 implies no limit -->
> </parameter>
>
> <parameter>
> <name>maxIdle</name> <!-- max idle db connections to retain in
pool -->
> <value>30</value> <!-- 0 implies no limit -->
> </parameter>
>
> <parameter>
> <name>maxWait</name> <!-- max wait for connection before throwing
exception -->
> <value>10000</value> <!-- -1 implies no limit (time is in
milliseconds) -->
> </parameter>
>
> <parameter>
> <name>username</name>
> <value>timesheet_app</value>
> </parameter>
>
> <parameter>
> <name>password</name>
> <value>xxxxxxxxxxx</value>
> </parameter>
>
> <parameter>
> <name>url</name>
>
<value>jdbc:mysql://localhost/Timesheets?autoReconnect=true&socketFactor
y=com.mysql.jdbc.NamedPipeSocketFactory</value>
> </parameter>
>
> <parameter>
> <name>driverClassName</name> <!-- Class name for mm.mysql JDBC
driver -->
> <value>org.gjt.mm.mysql.Driver</value>
> </parameter>
> </ResourceParams>
> </Context>
>
> I apologize for being so verbose, but I'm trying to provide all of the
relevant information in the initial message. Does anyone see what I've
missed? The example in jndi-datasource-examples-howto.html does NOT look
like the other things I was told to do. (?)
>
>
> Merrill
---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org