Data source creation and MySQL driver problem

[jsbournival]

I am having problems connecting to a MySQL data source inside of FuseESB 4 (SMX4).  I have a typical app design, which needs to access a database.  In terms of OSGi modular design, I have 4 bundles, it goes like this:

JAX-RS service > Spring "business" Service > DAO > data source (DBCP)

The web service + the spring bundle are wired correctly, it's the database connectivity that gives me this big headache.  I created a data source bundle with the following manifest headers:

Manifest-Version: 1.0
Unversioned-Imports: *
Bundle-Classpath: .
Built-By: js
Bundle-Name: Catamaran :: Tophits Data Sources bundle
Created-By: Apache Maven
Build-Jdk: 1.6.0_07
Bundle-Version: 0.0.1.SNAPSHOT
Spring-DM-Version: 1.2.0
Bundle-ManifestVersion: 2
Bundle-SymbolicName: ds-bundle
Import-Package: com.mysql.jdbc,javax.sql,org.apache.commons.dbcp,org.s
 pringframework.jdbc.datasource
Archiver-Version: Plexus Archiver

This bundle is defining a DBCP DS bean like this:

<bean name="frDS" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:3306/fr?autoReconnect=true"></property>
    <property name="username" value="fr"></property>
    <property name="password" value="password"></property>
    <property name="initialSize" value="2"></property>
     <property name="maxActive" value="5"></property>
     <property name="maxIdle" value="2"></property>
</bean>

.. And exposing it as an OSGi service:

<osgi:service id="frDSOsgiService" ref="frDS" interface="javax.sql.DataSource" />

Then my DAO tries to use it:

<osgi:reference id="frDS" interface="javax.sql.DataSource" bean-name="frDS" />

But when I use the DataSource to create a connection ...

try {
    connection = getDataSourceFr().getConnection();
     // ...
}
// ...

... it's throwing me the following exception

org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.mysql.jdbc.Driver'
     at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1141)
     at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     <--cut for brevity-->
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver not found from bundle [tophits-integration-bundle]
     at org.springframework.osgi.util.BundleDelegatingClassLoader.findClass(BundleDelegatingClassLoader.java:103)
     at org.springframework.osgi.util.BundleDelegatingClassLoader.loadClass(BundleDelegatingClassLoader.java:156)
     at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
     at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1134)
     ... 105 more
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
     at org.apache.felix.framework.searchpolicy.ModuleImpl.findClassOrResourceByDelegation(ModuleImpl.java:558)
     at org.apache.felix.framework.searchpolicy.ModuleImpl.access$100(ModuleImpl.java:59)
     at org.apache.felix.framework.searchpolicy.ModuleImpl$ModuleClassLoader.loadClass(ModuleImpl.java:1427)
     at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
     at org.apache.felix.framework.searchpolicy.ModuleImpl.getClassByDelegation(ModuleImpl.java:421)
     at org.apache.felix.framework.Felix.loadBundleClass(Felix.java:1354)
     at org.apache.felix.framework.BundleImpl.loadClass(BundleImpl.java:746)
     at org.springframework.osgi.util.BundleDelegatingClassLoader.findClass(BundleDelegatingClassLoader.java:99)
     ... 108 more

Note:  I already have deployed the following supporting bundles:

Apache ServiceMix Bundles: commons-dbcp-1.2.2 (1.2.2.3)

MySQL AB's JDBC Driver for MySQL (5.1.6)

Spring JDBC (2.5.6)

Can someone tell me what I am doing wrong with this?

Thank you!  Your thoughts are more than welcome.

[jsbournival]

As a complement, here is DBCP's Manifest:

Apache ServiceMix Bundles: commons-dbcp-1.2.2 (199)
---------------------------------------------------
Tool = Bnd-0.0.255
Bundle-DocURL = http://www.apache.org/
Bundle-Description = This bundle simply wraps commons-dbcp-1.2.2.jar.
Export-Package = org.apache.commons.jocl;uses:="org.xml.sax,org.xml.sax.helpers";version="1.2.2",org.apache.commons.dbcp.datasources;uses:="javax.naming,org.apache.commons.pool.impl,org.apache.commons.pool,javax.naming.spi,javax.sql,org.apache.commons.dbcp";version="1.2.2",org.apache.commons.dbcp.cpdsadapter;uses:="javax.naming,org.apache.commons.pool.impl,org.apache.commons.pool,javax.naming.spi,org.apache.commons.dbcp,javax.sql";version="1.2.2",org.apache.commons.dbcp;uses:="org.xml.sax,org.apache.commons.pool.impl,org.apache.commons.jocl,javax.naming.spi,javax.sql,javax.naming,org.apache.commons.pool";version="1.2.2"
Bundle-Version = 1.2.2.3
Build-Jdk = 1.5.0_16
Created-By = Apache Maven Bundle Plugin
Bundle-License = http://www.apache.org/licenses/LICENSE-2.0.txt
Bundle-ManifestVersion = 2
Manifest-Version = 1.0
Bundle-Vendor = The Apache Software Foundation
Bnd-LastModified = 1237593255240
Bundle-Name = Apache ServiceMix Bundles: commons-dbcp-1.2.2
Built-By = gnodet
Import-Package = javax.naming,javax.naming.spi,javax.sql,org.apache.commons.dbcp;version="1.2.2",org.apache.commons.dbcp.cpdsadapter;version="1.2.2",org.apache.commons.dbcp.datasources;version="1.2.2",org.apache.commons.jocl;version="1.2.2",org.apache.commons.pool,org.apache.commons.pool.impl,org.xml.sax,org.xml.sax.helpers
Bundle-SymbolicName = org.apache.servicemix.bundles.commons-dbcp

And my DAO's:

Catamaran :: Tophits Integration Bundle (202)
---------------------------------------------
Archiver-Version = Plexus Archiver
Export-Package = com.canoe.catamaran.integration.tophits;version="0.0.1.SNAPSHOT";uses:="org.apache.abdera.model"
Bundle-Classpath = .
Bundle-Version = 0.0.1.SNAPSHOT
Build-Jdk = 1.6.0_07
Created-By = Apache Maven
Bundle-ManifestVersion = 2
Spring-DM-Version = 1.2.0
Manifest-Version = 1.0
Bundle-Name = Catamaran :: Tophits Integration Bundle
Built-By = js
Unversioned-Imports = *
Import-Package = javax.sql,org.apache.abdera,org.apache.abdera.model,org.slf4j,org.springframework.beans.factory
Bundle-SymbolicName = tophits-integration-bundle

Also, I'm on Fuse ESB 4.1.0.0

[stlewis]

It kind of seems like DBCP can't see MySQL's driver class because it's not declared in the Import-Package header.  What about adding this package to org.osgi.framework.bootdelegation in etc/config.properties and see if that sorts it out?

[jsbournival]

Fixed!

Got it working by repackaging the commons-dbcp bundle, adding the "DynamicImport-Package: *" header in the Manifest.

It seem that commons-dbcp 1.2.2.3 should have had this header.  Turns out it doesn't.  Now I can connect + query My database.

Also, I don't fully understand why DBCP needs this header (and what the header actually means).  I'll try to figure it out and post my answer here.  (If anyone have an explanation, it would be awesome)

Thank you

[gertv]

L.S.,

The DynamicImport-Package header tells the OSGi framework that, whenever it is looking for a package that is not in the ImportPackage list, it can scan the entire container for bundles providing the package.  So instead of statically wiring things together when bundles get resolved as you do with the Import/ExportPackage instructions, you can dynamically look up a class at runtime.

For your scenario, this allows the commons-dbcp classes to find your JDBC driver when creating the connection pool.  The DynamicImport header is more suitable here, because it would be an impossible to task to import all possible JDBC driver packages (for Oracle, IBM DB2, MySQL, ... and the thousand others out there) in the commons-dbcp bundle.

However, we thought we had found a way to avoid the requirement for the DynamicImport by changing the implementation in the class lookup mechanism for commons-dbcp.  Do you think you can provide a sample set of bundles -- that would be a huge timesaver for us to figure out why this is not working out-of-the-box?

Regards,

Gert

[kamotran]

jsbournival:

I.m having the same problem but repacking the commons-dbcp manifest don.t fix it.

Can u send me your project so i can compare and find out what i'm missing

Thanks in advance & regards

Marcelo

[e-mail]

i'm attaching my projects if anyone wants to take a look

platform Eclipse galileo w/ m2eclipse

fuse 4.2

[unwired]

Hi,

We too are experiencing this problem. It seems that when you first bring up the fuse server, we get that error on the first bundle that utilizes the driver, and then if you stop that bundle and start it again it works ok.

In fact our production operations deploy instructions now contain some convoluted instructions which make the deployers stop all our bundles, then bounce the server, then bring up the server.  Then start each bundle 'grepping' for the ClassNotFoundException.  Once that's received, they stop that bundle (it'll be the first one actually using the driver), then start start that bundle again.  Then all the other bundles can come up as they manually start them with no problems.

I can provide a bundle, but as I'm sure you can understand I prefer not to attach the whole thing with our internal code.  Is it really just the Manifest that you need - or what's the bare minimum?

thanks.

[paco078]

Hello all,

I have the same issue - using:

• Spring JDBC (2.5.6.SEC01)

• MySQL AB's JDBC Driver for MySQL (5.1.6)

• Apache ServiceMix Bundles: commons-dbcp-1.2.2 (1.2.2.5)

My service class is published through CXF. I setup DataSource in the spring configuration. After I install and start my bundle everything was OK. On the first request I received following error:

org.apache.cxf.interceptor.Fault: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.mysql.jdbc.Driver'at org.apache.cxf.service.invoker.AbstractInvoker.createFault(AbstractInvoker.java:155)at org.apache.cxf.jaxws.AbstractJAXWSMethodInvoker.createFault(AbstractJAXWSMethodInvoker.java:85)at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:121)

....

I think this is caused, from the fact, that CXF bundle does not have a DynamicImport: * declaration. So in order to load MySQL driver I did a fake SQL query in the setDataSource method:

  public void setDataSource(DataSource dataSource) {

    this.jdbcTemplate = new JdbcTemplate(dataSource);

    // PATCH: do some fake SQL query in order to load db Driver

    // Note: If this is NOT done here, it gets problem further. First DB

    // query executor should load db Driver and in our case this is

    // CXF, which does NOT import SQL driver

    this.jdbcTemplate.queryForInt("SELECT 0");

  }

Thus everyhting is working for me. The drawback here is that:

- I need to do some patches in all my service classes - populate my Java code with not business logic

- When service class is loaded it opens and holds a not needed DB connection

In order to avoid those drawbacks my question is - is it possible to put preload database driver outside my Java code ? Or is there some better approach to solve this problem ?

Thanks

[ffang]

Hi,

One solution I can come up with for this common case is that you create your DB driver as a fragment bundle and attach it to cxf bundle(cxf bundle now play the role as host bundle), so that all resource in fragment bundle is available for the host bundle.

Freeman

[bharadwaj]

Solution for org.apache.commons.dbcp.SQLNestedException: Cannot load JDBC driver class 'com.mysql.jdbc.Driver' :

Step1:

        <servicemix.osgi.dynamic.import>*</servicemix.osgi.dynamic.import>

                        <DynamicImportPackage>${servicemix.osgi.dynamic.import</DynamicImport-Package>

                        <Private-Package>${servicemix.osgi.private}</Private-Package>

                    </instructions>-

                </configuration>

            </plugin>

        </plugins>

    </build>