Russell Beattie

It took me a an hour or so this evening, but I finally tracked down what the problem was with the pooling implementation in Tomcat when using MySQL's JDBC driver. I was passing it a URL with the proper params for using Unicode and managing connections, but anyone who's named something like Félix would be able to tell you, lately non-ascii characters all looked like crap on my pages. This wasn't a problem with Orion, using the same codebase and the same MySQL driver, so it's pretty odd.

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