This post originated from an RSS feed registered with Java Buzz by Russell Beattie. Original Post: MySQL JDBC Driver and Tomcat Pooling Feed Title: Russell Beattie Notebook Feed URL: http://www.russellbeattie.com/notebook/rss.jsp?q=java,code,mobile Feed Description: My online notebook with thoughts, comments, links and more. Latest Java Buzz Posts Latest Java Buzz Posts by Russell Beattie Latest Posts From Russell Beattie Notebook

It turns out that the MysqlConnectionPoolDataSource is being called through a class called MysqlDataSourceFactory which takes the parameters in from the ResourceParams in the server.xml and uses that to instantiate the Pool. It turns out that it only was bothering with username, password, port and database name. That's it, no URL or any other params. If you check out the readme or documentation of the MySQL JDBC driver you'll see there's about 30 more parameters you can pass, including "autoReconnect", "useUnicode" and pool options like "max-connections" etc. For some reason those are *left out* of the 3.0 codebase.

So I implemented a factory myself and threw it into the /common/classes directory and it worked (it's running now). *THEN* as I'm writing this it dawns on me to check out the 3.11 Alpha build and sure enough, the magical missing URL parameter is in there. Doh! Now I wonder whether I should continue with my own rolled factory, or upgrade to a potentially unstable build of the driver. Urgh.

Anyways, for search engines in the future, here's how to set up the MySQL pool in Tomcat 5.0 using the 3.1 version of MySQL's Connector/J JDBC Driver:

    <Resource name="jdbc/MyDS" type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource" auth="Container"/>
    
    <ResourceParams name="jdbc/MyDS">
        <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>USER</value>
        </parameter>
        <parameter>
            <name>password</name>
            <value>PASSWORD</value>
        </parameter>
        <parameter>
            <name>serverName</name>
            <value>localhost</value>
        </parameter>
        <parameter>
            <name>databaseName</name>
            <value>DATABASE</value>
        </parameter>
        <parameter>
            <name>explicitUrl</name>
            <value>true</value>
        </parameter>        
        <parameter>
        	<name>url</name>
        	<value>jdbc:mysql://localhost:3306/DATABASE?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;max-connections=50&amp;min-connections=2&amp;inactivity-timeout=30&amp;wait-timeout=30</value>
        </parameter>        

    </ResourceParams>

Or at least that's what I *think* it'll be because I can't actually get it to work. Urgh. Life in the fast lane of Java development...

-Russ