working JOTM/XAPool/MySQL config with Tomcat 5.5?

[Nikita Tovstoles <ni...@eviltwinstudios.net>]

I have been struggling trying to get JOTM/XAPool 1.5 to do the following 
as part of webapp running in Tomcat 5.5 (talking to MySQL 5):

1. Validate connections: if MySQL server kills all jdbc connections, and 
*later* the webapp asks for a JDBC cxn, the db pool should reestablish 
and return a working connection. my setup does not.
2. Kill off/reclaim stale connections (I do use session.close() in 
finally{}). However, all failed TXs leave behind jdbc connections that 
appear to simply sleep and do nothing, but never go away.

Here's my attempt at context.xml which doesn't achieve the above. I'd 
appreciate any feedback on these, or a working context.xml

thanks
-nikita

--context.xml------


<Context antiJARLocking="true">
    <!-- without JTA -->
    <!-- Resource
    auth="Container"
    description="Hibernate DB Connection for Evilsite"
    name="jdbc/EvilsiteDB"
    type="javax.sql.DataSource"   
    driverClassName="com.mysql.jdbc.Driver"
    maxIdle="2"
    maxWait="5000"  
    username="xxxx"
    password="xxx"
    url="jdbc:mysql://localhost/evilsite"
    maxActive="4"/ -->
   
    <!-- with JTA - JOTM -->
   
    <!-- @TODO customize MySQL JDBC conf here -->
    <Resource name="jdbc/EvilsiteDB" auth="Application" 
type="javax.sql.DataSource"
        factory="org.objectweb.jndi.DataSourceFactory"
        driverClassName="com.mysql.jdbc.Driver"
        username="xxxx" password="xxx" url="jdbc:mysql://localhost/db"
        >

        <!-- Don't set this any higher than max_connections on your
        MySQL server, usually this should be a 10 or a few 10's
        of connections, not hundreds or thousands -->
        <parameter>
            <name>maxActive</name>
            <value>100</value>
        </parameter>

        <!-- You don't want to many idle connections hanging around
        if you can avoid it, only enough to soak up a spike in
        the load -->

        <parameter>
            <name>maxIdle</name>
            <value>10   </value>
        </parameter>

        <!-- Don't use autoReconnect=true, it's going away eventually
        and it's a crutch for older connection pools that couldn't
        test connections. You need to decide if your application is
        supposed to deal with SQLExceptions (hint, it should), and
        how much of a performance penalty you're willing to pay
        to ensure 'freshness' of the connection -->

        <parameter>
            <name>validationQuery</name>
            <value>SELECT 1</value>
        </parameter>

        <!-- The most conservative approach is to test connections
        before they're given to your application. For most applications
        this is okay, the query used above is very small and takes
        no real server resources to process, other than the time used
        to traverse the network.

        If you have a high-load application you'll need to rely on
        something else. -->

        <parameter>
            <name>testOnBorrow</name>
            <value>true</value>
        </parameter>

        <!-- Otherwise, or in addition to testOnBorrow, you can test
        while connections are sitting idle -->

        <parameter>
            <name>testWhileIdle</name>
            <value>true</value>
        </parameter>

        <!-- You have to set this value, otherwise even though
        you've asked connections to be tested while idle,
        the idle evicter thread will never run -->

        <parameter>
            <name>timeBetweenEvictionRunsMillis</name>
            <value>10000</value>
        </parameter>

        <!-- Don't allow connections to hang out idle too long,
        never longer than what wait_timeout is set to on the
        server...A few minutes or even fraction of a minute
        is sometimes okay here, it depends on your application
        and how much spikey load it will see -->

        <!-- this may needed to be higher -->
        <parameter>
            <name>minEvictableIdleTimeMillis</name>
            <value>60000</value>
        </parameter>
    </Resource>

    <Transaction name="UserTransaction" auth="Application"
    type="javax.transaction.UserTransaction"
    factory="org.objectweb.jotm.UserTransactionFactory"
    jotm.timeout="30"/>
   
   
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>META-INF/context.xml</WatchedResource>
</Context>


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org