tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "SMaric" <sma...@ntlworld.com>
Subject Re: Another Try at MySQL Connection Pooling
Date Wed, 11 Feb 2004 23:40:33 GMT
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" <merrill.cornish@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&amp;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


Mime
View raw message