Tomcat with MySQL Connection Pool

[Jeff Wisard]

Hello!
I am having difficulty setting up a connection pool from Tomcat 4 to a MySQL database using the MM.MySQL driver version 2.0.12 found at SourceForge.net. Essentially, if you've done this before, I would like to see your entries in server.xml that pertain to the connection pool. (And any other code/config/etc. that you use).
BTW: I want to use DataSource objects and Tomcat's JNDI.
Thanks!
[ April 23, 2002: Message edited by: Jeff Wisard ]

[Justin Chu]

I've difficulty doing that too..
I've set it up successfully once. But not this time. However, here's the general attempts.
Here's my entries.

        <Context path="/chutan" docBase="chutan" reloadable="true">
          <Resource name="jdbc/MyDb" auth="Container"
                    type="javax.sql.DataSource"/>
          <ResourceParams name="jdbc/MyDb">
            <parameter><name>user</name><value>chutan</value></parameter>
            <parameter><name>password</name><value>chutan</value></parameter>
            <parameter><name>driverClassName</name>
              <value>org.gjt.mm.mysql.Driver</value></parameter>
            <parameter><name>driverName</name>
              <value>jdbc:mysql://localhost/chutan</value></parameter>
          </ResourceParams>
        </Context>
  

Store your mm.mysql.jar in /common directory.
WEB.XML file

<?xml version="1.0"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN" "http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">
<web-app>
  <resource-ref>
    <description>MySQL Database</description>
    <res-ref-name>jdbc/MyDb</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>CONTAINER</res-auth>
  </resource-ref>
</web-app>

Please tell me if u get it working.
Chu

[Justin Chu]

Correction:
store your mm.mysql.jar in /common/lib directory
My test.jsp file

<%@ page import="javax.sql.*,java.sql.*,javax.naming.*,java.util.*" %>
<%
Connection c = null;
try {
  InitialContext ic = new InitialContext();
  DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/MyDb");
  if (ds == null){
    out.println("data source is null");
  } else {
    c = ds.getConnection();
  }
} catch (Exception e){
  e.printStackTrace();
  out.println("Exception: "+ e);
  if (c != null){
    c.close();
  }
}
%>

I keep getting null for my DataSource.
I followed this exact steps on my last installation, and works. Maybe I'm missing something.

[Jeff Wisard]

Thanks, I'll try it and get back to you.

[Mike Curwen]

Have you tried breaking out the Context lookup from the resource lookup? It shouldn't matter, but if nothing else works, why not try it? Stranger things have been the cause of trouble.

 
Context ic = new InitialContext();
 
Context c = (Context) ic.lookup("java:comp/env");
 
DataSource ds = (DataSource) c.lookup("jdbc/MyDb");

The added advantage of this approach, is that once you have c, you can look up everything else, since all JNDI resources should be rooted at java:comp/env

Also.. make sure your mysql connection URI has the appropriate port number after 'localhost' 3306 seemed to be a popular choice on google

[Jeff Wisard]

Well, I haven't been able to get this to work yet. I'm stuck. I've read all I can find about setting this up with Tomcat and followed the instructions very carefully....it just doesn't work. My DataSource object always ends up being null...
Any help from anyone would be greatly appreciated.

[Mike Curwen]

Jeff, have you tried writing a very small stand-alone java application that can test your JDBC driver URL, and by doing so, you also test the database install, and the way you are programming JDBC?

[Jeff Wisard]

Yes, I have. In fact, I have FORM based authentication working with Tomcat through the same driver to database tables in the same database.
I am also able to create a connection using DriverManager through a jsp page.
The only thing that I can't get to work is obtaining a DataSource object through Tomcat's JNDI tree. Like I said, I have followed the Tomcat documentation (several times)...I am either missing something or their docs are in error (or both). Maybe something is wrong with the driver...
Have you tried this yet? (using the mm.MySql driver)

[Mike Curwen]

I haven't tried using DataSources in Tomcat actually, but I googled.

Here is one in Korean that has screenshots of the code
http://www.superuser.co.kr/jsp/kenu/page45.htm

The only other 'good' hit was unfortunately a problem post with no resolution. And wouldn't you know it? They guy hid his email, so you can't even email him to see if he solved it
http://www.geocrawler.com/lists/3/Databases/193/0/8291588/

[Justin Chu]

did u solve this problem yet?
I used 2 jdbc drivers, Sybase and MySQL, Sybase seemed to work fine, while MySQL returns null.
Chu

[Steve O]

Correct me if i'm wrong, but don't you have to bind a specific connection pool implementation instance to the JNDI before attenpting to obtain pooled connections?

[Ryan Bailey]

Hello Mr Wisard,
I have this particular case working at home.
This is what I have done in order to use connection pooling with Tomcat Version 4.*
Right before the </host> tag in your server.xml file include the following xml code:

 <DefaultContext>
        <Resource name="jdbc/resourceName" auth="Container"
  type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/SFV">
  <parameter>
  <name>driverClassName</name>
  <value>com.mysql.jdbc.Driver</value>
  </parameter>
  <parameter>
   <name>url</name>
    <value>jdbc:mysql://localhost/ryan</value>
  </parameter>
  <parameter>
    <name>username</name>
    <value>username</value>
  </parameter>
  <parameter>
    <name>password</name>
    <value>password</value>
  </parameter>
  <parameter>
    <name>maxActive</name>
    <value>20</value>
  </parameter>
  <parameter>
    <name>maxIdle</name>
    <value>30000</value>
  </parameter>
  <parameter>
    <name>maxWait</name>
    <value>100</value>
  </parameter>
</ResourceParams>
</DefaultContext> 

After this is done you will also need to put a little xml sniplet in your web.xml file that pertains to the WebApp that you are using. The following code is below:

 <!-- this is for Connection Pooling -->
<resource-ref>
    <res-ref-name>jdbc/resourceName</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref> 

The above following needs to be defined after your servlet mapping. I believe order does mater for the WebApp Spec.
Finally use the following code in order to access a connection in Tomcat's pool

 InitialContext context = new InitialContext();
DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/resourceName");
conn = ds.getConnection();
context.close(); 

I got a book from the JavaRanch Giveway that I would highly recommend entitled Professional Apache Tomcat by Wrox
email me if you have any questions on this because I probably will forget about this thread.
[email protected]
I believe my jar file is either located in my common/lib or shared/lib directory.
Cheers,
Ryan

[dipak mehta]

Hello Mr. Wishard,
Here's the solution to your problem.
Please set naming property to "true" through tomcat administration in your web application context.
Contect me on [email protected] if problem persist.
- Dipak Mehta

[shankar vembu]

hi,
i am also facing the problem of getting null for the datasource. i followed exactly the same steps as outlined here
tomcat page.
I am using tomcat5 and mysql driver version 3.0.8
What is missing ??
Thank you,
SHankar.

[dipak mehta]

Hello All,

The problem which I was faced is same... null datasource.
I found in my case that "useNaming" must be set to "true"... by setting it my problem get solved.
Please have a look at below context entry, you can see that useNaming=true is there.
If you are not familiar with tomcat administration console, you can directly edit your context entry in server.xml file.
<Context className="org.apache.catalina.core.StandardContext" cachingAllowed="true" charsetMapperClass="org.apache.catalina.util.CharsetMapper" cookies="true" crossContext="false" debug="0" docBase="/MyApp" mapperClass="org.apache.catalina.core.StandardContextMapper" path="/MyApp" privileged="false" reloadable="true" swallowOutput="false" useNaming="true" wrapperClass="org.apache.catalina.core.StandardWrapper">
Let me know if still problem persist.