Can't get connection from DataSource object

[Fabio Piergentili]

I am having trouble setting up connection pooling on a linux server using mysql and tomcat 6

I have created a context.xml and put it in my deployed application WEB-INF directory

   
<Contex>
    <Resource name="jdbc/student"
    auth="Container" 
    type="javax.sql.DataSource"  
    maxActive="100" 
    maxIdle="30" 
    maxWait="10000"  
    username="xxx" 
    password="xxx" 
    driverClassName="com.mysql.jdbc.Driver"  
    url="jdbc:mysql://localhost:3306/student?autoReconnect=true"/>
</Context>

My web.xml

....

  <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/student</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
...

My application code

Context initialContext = new InitialContext();
if ( initialContext == null){
      log("JNDI problem. Cannot get InitialContext.");
}

DataSource ds = (DataSource)initialContext.lookup("java:/comp/env/jdbc/student");
                            
if (ds != null) {
     dbConnection = ds.getConnection();  // ****  I get the exception here **** It is finding the lookup but 
                                                           // it seems that tomcat is not getting the driver and url from the config files.
                                                          //  verified that the mysql driver jar is in the tomcat lib directory.
}

And finally the exception.

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'Apr 5, 2010 4:32:35 PM org.apache.catalina.core.ApplicationContext log
INFO: StudentServlet: SQLException caught Cannot create JDBC driver of class '' for connect URL 'null'

        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1150)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
        at StudentDBServlet.doPost(StudentDBServlet.java:100)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:849)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:454)
        at java.lang.Thread.run(Thread.java:637)
Caused by: java.sql.SQLException: No suitable driver
        at java.sql.DriverManager.getDriver(DriverManager.java:264)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1143)
        ... 16 more

I searched other similar posts but could not find problem. Any suggestions are greatly appreciated.

Thanks

[Tim McGuire]

I had same problem recently.

check in $TOMCAT_HOME/conf/Catalina/localhost

for a file called $appname.xml
where $appname is your web application name.
This file is a copy of your context.xml from when you first deployed. If you fixed context.xml since your first deployment, the changes won't be reflected in $appname.xml. Your app is still trying to read from it, however. You can edit $appname.xml by hand or delete it.

[Jaikiran Pai]

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'Apr 5, 2010 4:32:35 PM org.apache.catalina.core.ApplicationContext log
INFO: StudentServlet: SQLException caught Cannot create JDBC driver of class '' for connect URL 'null'

Wow, this is I think the third post I have seen this week about this issue! We weren't able to figure out the issue in the other two threads. Tim's suggestion here does make sense.

[Joe San]

Tim McGuire wrote:I had same problem recently.

check in $TOMCAT_HOME/conf/Catalina/localhost

for a file called $appname.xml
where $appname is your web application name.
This file is a copy of your context.xml from when you first deployed. If you fixed context.xml since your first deployment, the changes won't be reflected in $appname.xml. Your app is still trying to read from it, however. You can edit $appname.xml by hand or delete it.

Even I have the same problem with DBCP Tomcat Connection Pooling. I'm using Oracle 10g and Tomcat 5.5.28 and JDK 1.4.

I tried to look for the file ($MyProject.xml) in the location you specified. But I'm not finding any such file! I just see that there are two files (host-manager.xml and manager.xml).

Is there really a solution to this problem???

[Tim Holloway]

"No Suitable Driver" means that your driver class wasn't found in Tomcat's classpath. You've either misspelled the classname or you need to place a copy of the jar that contains the driver in $TOMCAT_HOME/lib.

The reason you haven't found a copy of the context in the $TOMCAT_HOME/Catalina/localhost directory is because you supplied it as file "META-INF/context.xml" in your WAR. Tomcat has no need to make a copy of it.

[Fabio Piergentili]

Tim Holloway wrote:"No Suitable Driver" means that your driver class wasn't found in Tomcat's classpath. You've either misspelled the classname or you need to place a copy of the jar that contains the driver in $TOMCAT_HOME/lib.

I have verified that the jar does exists in the tomcat lib directory. I actually have this working w/o connection pooling using the same driver so I do not think that the driver not being their is the issue. Is their a setting in the server.xml where you need to turn connection pooling on? Tomcat has all the info it needs to create connections but do you have to tell tomcat to do it?

Thanks

[Tim Holloway]

The only thing you need is the datasource resource definition itself. There are not additional switches to turn on. Although, as one of your fellow-sufferers has least, XML is case-sensitive.

Very definitely however, "No Suitable Driver" means that the driver class could not be resolved. Which means that a Class.forName("com.mysql.jdbc.Driver") done in the connection pool failed to find the class. Since I'm pretty sure you capitalized the driverClassName attribute properly (which is the third way this could fail), I'd recommend doing a "jar tvf $TOMCAT_HOME/lib/xxxxxx.jar" (replaces "x"s with your mysql driver jar filename) and verifying that the resulting output has a com.mysql.jdbc.Driver.class in it.

[Fabio Piergentili]

result from jar tvf command on mysql jar file in tomcat home lib directory

692 Wed Mar 05 17:26:52 EST 2008 com/mysql/jdbc/Driver.class

[Tim McGuire]

Fabio Piergentili wrote:result from jar tvf command on mysql jar file in tomcat home lib directory

692 Wed Mar 05 17:26:52 EST 2008 com/mysql/jdbc/Driver.class

Just want to confirm that when you say "tomcat home lib" you are not talking about

$TOMCAT_HOME/lib

it should be in
$TOMCAT_HOME/common/lib

[Tim Holloway]

Tim McGuire wrote:

Fabio Piergentili wrote:result from jar tvf command on mysql jar file in tomcat home lib directory

692 Wed Mar 05 17:26:52 EST 2008 com/mysql/jdbc/Driver.class

Just want to confirm that when you say "tomcat home lib" you are not talking about

$TOMCAT_HOME/lib

it should be in
$TOMCAT_HOME/common/lib

Not for Tomcat6. The unholy trinity of Tomcat libraries went away after tomcat 5.x. Now there's only TOMCAT_HOME/lib.

Technically, I think it's actually $CATALINA_BASE/lib, but I can never keep CATALINA_BASE and CATALINA_HOME straight unless I go back and reference the docs. I don't run enough instances where the two have differing values.

[Fabio Piergentili]

created a common/lib directory and put it in their just to test for bug but no luck. Still same error.

[Tim McGuire]

Tim Holloway wrote:

Not for Tomcat6. The unholy trinity of Tomcat libraries went away after tomcat 5.x. Now there's only TOMCAT_HOME/lib.

I'll be damned. I did not realize that.

[Tim Holloway]

Tomcat won't even see your common/lib. Even if you explicitly add it to the classpath, it's not likely to find it, since Tomcat maintains its own internal set of classpaths.

For Tomcat6 JDBC driver jars should be placed in TOMCAT_HOME/lib. I guarantee they'll be found there. So far I've done that for MySQL, PostgreSQL, DB2 and Oracle, and in quite a few cases, I've had multiple database vendor's jars in there at the same time.

[Fabio Piergentili]

I am a bit confused. You first said to put in common. I did that just to test if their was a bug. The mysql jar is in $TOMCAT_HOME/lib. I still get that error. If it is finding it then what could be the problem? Is it a bug? Is it the url? Since we verified that the driver class is their and in the correct place. I just do not know what to do next.....

Thanks for trying all...

[Tim Holloway]

I didn't say common/lib. That was the other Tim. common/lib, server/lib and whatever the third lib directory was (I forget) were features of earlier releases of Tomcat. They were replaced by TOMCAT_HOME/lib in Tomcat6.

There is no magic there.

jar -tvf /usr/local/tomcat6/lib/mysql-connector-java-3.1.14-bin.jar | grep Driver
   692 Wed Oct 18 23:35:46 GMT-05:00 2006 com/mysql/jdbc/Driver.class
  9275 Wed Oct 18 23:35:44 GMT-05:00 2006 com/mysql/jdbc/NonRegisteringDriver.class
  2359 Wed Oct 18 23:35:46 GMT-05:00 2006 com/mysql/jdbc/NonRegisteringReplicationDriver.class
   731 Wed Oct 18 23:35:46 GMT-05:00 2006 com/mysql/jdbc/ReplicationDriver.class
   336 Wed Oct 18 23:35:46 GMT-05:00 2006 org/gjt/mm/mysql/Driver.class

This will give me access to class com.mysql.jdbc.driver. Assuming I've made a proper MYSQL JDBC URL so that this driver will claim it, that's all there is.

[Fabio Piergentili]

I have verified all that. I did the jar thing and I do have the driver in the correct place. In addition, I know tomcat can find it as I can connect to the DB without pooling via

    

    static  Connection con = null;
    static private  final String JDBC_DRIVER = 
        "com.mysql.jdbc.Driver";

    static private final String URL =
        "jdbc:mysql://localhost/db_name";

I know their is no magic but it seems I have everything in place but it doesn't work. You mentioned the url. I did try different ways( w and wo password etc..)
I just do not know what to try next.

[Tim Holloway]

I'd try removing the "?autoreconnect=" part of the URL. For one thing, I think that's a Connection Pool attribute, so the Connection Pool URL parser may be mangling what you provided. Some people have suggested using 127.0.0.1 instead of localhost, but I don't think that's actually necessary.

I do talk to MySQL connection pools all the time with (relatively) little grief. It might help if you got a co-worker to look at this stuff. A lot of times when I'm having problems that nothing seems to solve it turns out I've developed a blind spot to something fairly simple.

[Fabio Piergentili]

Ok thanks for all your help. I will post back if I figure out something.

[Misha Ver]

Fabio Piergentili wrote:
I have created a context.xml and put it in my deployed application WEB-INF directory

   
<Contex>
    <Resource name="jdbc/student"
    auth="Container" 
    type="javax.sql.DataSource"  
    maxActive="100" 
    maxIdle="30" 
    maxWait="10000"  
    username="xxx" 
    password="xxx" 
    driverClassName="com.mysql.jdbc.Driver"  
    url="jdbc:mysql://localhost:3306/student?autoReconnect=true"/>
</Context>

I see the misprint in the line 2. Is this a real code?

[Fabio Piergentili]

I wish that was it....but it was just a typo in the post....thanks

Fabio

[Fabio Piergentili]

As Tim mentioned on a previous post I developed a blind spot and that spot was that I put the context.xml file under WEB-INF and not META-INF in the war file. It was not seeing it at all. Also for tomcat6 do not put
factory="org.apache.commons.dbcp.BasicDataSourceFactory entry in context.xml. Lastly, tomcat copies the context.xml from your war to $CATALINA_HOME/conf/Catalina/[host]/[renamedToApplicationContextPath.xml. I had to manually delete it for the new one to take effect.

Thanks all for your help