Problem with connection pooling

[Paolo Rodeo]

Hi all,

I'm running into the following exception with connection pooling when trying to get a connection from the pool:

Cannot create JDBC driver of class '' for connect URL 'null'
java.lang.NullPointerException
	at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(JdbcOdbcDriver.java:507)
	at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(JdbcOdbcDriver.java:476)
	at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(JdbcOdbcDriver.java:307)
	at java.sql.DriverManager.getDriver(DriverManager.java:253)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
	at nl.timeware.data.TDataBase.connectFromPool(TDataBase.java:83)
	.....

In my application's /WEB-INF/web.xml I have:

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

In my application's /META-INF/context.xml I have:

<Context>
    <Resource name="jdbc/mysqldb" auth="Container" type="javax.sql.DataSource"
        maxActive="40" maxIdle="10" maxWait="5000"
        testWhileIdle="true" timeBetweenEvictionRunsMillis="300000"
        username="mysqluser" password="mysqlpswd" driverClassName="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost/mysqldb?useOldAliasMetadataBehavior=true"/>
</Context>

I'm connecting as follows:

public void connectFromPool() throws Exception
    {	  InitialContext jndiContext = new InitialContext();
        if (jndiContext == null) 
            throw new Exception("No JNDI context");
        DataSource ds = (DataSource)jndiContext.lookup("java:/comp/env/jdbc/mysqldb");
        if (ds == null) 
            throw new Exception("No data source available");
        connection = ds.getConnection();  // <-- this is were the above exception occurs
        connection.setAutoCommit(true); 
    }

I know the database name, MySQL username and password are working correctly. The same configuration works OK on another host
(with slightly different minor software version numbers). The latest mysql-connector-java.jar is on the Tomcat\lib directory. I suspect that the com.mysql.jdbc.Driver class can't be loaded, but don't understand why, as the path is correct and this works with exactly the same configuration on my other host. How should I start debugging this?

Regards and thanks, Paul.

[Vijitha Kumara]

Welcome to Javaranch Paul van Rossem . What version of Tomcat are you using?

[Paolo Rodeo]

Hi Vijitha,
Thanks for your reply. I was using 6.0.29, and tried exactly the same configuration with 6.0.18. On 6.0.18 this works fine. So I expect, there have lately been some (undocumented?) changes in the way Tomcat handles resources. Or privileges? (I saw some changes in the permissions in the catalina.policy file, could that have anything to do with my problem?). It would be nice if you had some suggestion what to check next...
Thanks and regards, Paul.

[Jan Cumps]

I have moved this post to our Tomcat forum.

[Vijitha Kumara]

Well, I have just tried this in Tomcat 6.0.29 and It seems work fine. You are sure you have the driver jar inside lib directory right?

[Paolo Rodeo]

Yes, it's definitely there, on the tomcat/lib dir. And the same setup works with the older 6.0.18 Tomcat. But indeed, it looks like Tomcat can't find it. That's why I was thinking about access priviliges or such. But Tomcat hasn't any problem finding the other jars in the same dir...
Regards, Paul.

[Misha Ver]

I would compare file permissions with other jars in the same directory.

[Vijitha Kumara]

In addition checking for permission, try with a different (version) Connetor/J whether it works.

[Paolo Rodeo]

I already checked file permissions and different Connector/J versions. Didn't help. Only downgrading Tomcat itself makes a difference. So, it must have something to do with new features in Tomcat 6.0.29 that I'am unaware of. I also run exactly the same configuration on a few different Windows and Linux machines, and they all gave the same problem with 6.0.29.
Do you know if there is a way to get more info from Tomcat why it can't load the driver?
Regards, Paul.

[Vijitha Kumara]

..I also run exactly the same configuration on a few different Windows and Linux machines, and they all gave the same problem with 6.0.29...

In my Fedora 13 this works fine

Have you tried loading the driver in your application (Class.forName()...) and try to connect to DB?

[Paolo Rodeo]

Hi Vijitha,
Good thinking. Yes, if I connect from within my application, using the same MySQL database, username and password, it connects without problem. So, I'm getting more and more convinced that the problem must be within Tomcat, how it deals with database resources. Not necessarily a Tomcat bug, but some change in 6.0.29 that I'm not aware of because it is poorly documented (?).
Also I notice that Tomcat doesn't give any warning that it can't create the connection pool. So it looks like it completely ignores my /META-INF/context.xml file. Any idea how I could check if it processes that file?
Regards, Paul.

[Jaikiran Pai]

Looking at that stacktrace, it's not really the MySQL JDBC driver which is running into problems. The DriverManager passes on the connection url to all registered JDBC drivers to see if they "accept" that URL. It looks like the sun.jdbc.odbc.JdbcOdbcDriver is registered before the MySQL JDBC driver and that sun.jdbc.odbc.JdbcOdbcDriver has a bug where it throws a NPE. You might have to narrow down to see who is registering the sun.jdbc.odbc.JdbcOdbcDriver with the DriverManager.

[Paolo Rodeo]

Hi Jaikiran,
Thanks for your comment. I'm afraid debugging the sun.jdbc.odbc.JdbcOdbcDriver is a bit out of my scope. But I still wonder why I seem to be the only one running into this problem. So, there has to be a relation with the other things I'm doing. This is the only place I'm using JDBC, or it must be somewhere hiden in one of the libraries I'm using. And then still, why only in Tomcat 6.0.29?

I think I'll just downgrade Tomcat. For me, that's the easy way out (yes, I feel defeated...)
Regards, Paul.

[Jaikiran Pai]

I just did a quick check of the Tomcat DBCP code and it appears that the BasicDataSource is being passed a null URL during the getConnection call, after the lookup. The "url" (which is case-sensitive, by the way) value comes from the <Resource> configuration. Are you sure your context.xml is being picked up and not some other file? I'm not a regular user of Tomcat and don't exactly remember which context.xml (or server.xml) is given preference for an application. The Tomcat docs however have the detailed explanation on that one.

[Tim Holloway]

If you use the JDBC-ODBC bridge driver, your problems are just beginning. The primary employment for that driver is to talk to MS-Access or FoxPro databases, neither of which can handle multi-threaded updates. Pretty much all the "real" databases have their own specific drivers which not only can deal with the issues of a multi-user server like Tomcat but also are fine-tuned to support that particular brand of database. Some DBMS's even come with a choice of drivers, and in the case of MS SQL Server, there are even vendor (Microsoft), third-party, and open-source drivers.

Any time you get the NullPointerException on a database connection pool, check the database URL, user ID and password carefully. I think the 4th member of that unholy alliance (driverClass) will throw a ClassNotFoundException, but if you get one of the first three of those items wrong, you usually don't get a helpful error message, you get an NPE. Especially if the URL isn't right.

[Paolo Rodeo]

Hi Jaikiran,
You've put me on the right path with your questions. Indeed the context.xml wasn't being picked up, due to some deployment problem. I don't know what exactly has gone wrong, and why this only occured in 6.0.29 repeatedly on several of my servers. Probably has to do with the context descriptor in the server file. I fiddeld a bit with that and now it works! Appearently Tomcat interprets things a bit different (may be more stringent ?) in 6.0.18 and 6.0.29.
Anyway, thanks for your help, regards,
Paul.

[Jkgfd Tse]

Hi Paul,

Would you please post your solution? How do you make the context.xml seen by tomcat?

Thanks a lot
TSEFiona

[Paolo Rodeo]

It's hard to tell what exactly did it, but I ended up by a clean re-install of Tomcat and then do a deploy by uploading a war file instead of deploying a directory tree. Then it did read my application's context.xml.
Paul.