9 Replies Latest reply on Jan 22, 2014 4:15 AM by bharadwaj n

    Data source creation and MySQL driver problem

    JS Bournival Newbie

      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.

        • 1. Re: Data source creation and MySQL driver problem
          JS Bournival Newbie

          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

          • 2. Re: Data source creation and MySQL driver problem
            Stan Lewis Novice

            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?

            • 3. Re: Data source creation and MySQL driver problem
              JS Bournival Newbie

              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

              • 4. Re: Data source creation and MySQL driver problem
                Gert Vanthienen Novice

                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

                • 5. Re: Data source creation and MySQL driver problem
                  Marcelo Beltran Newbie

                  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

                  reg05@analisischile.cl

                   

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

                  platform Eclipse galileo w/ m2eclipse

                  fuse 4.2

                  • 6. Re: Data source creation and MySQL driver problem
                    unwired unwired Newbie

                    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.

                    • 7. Re: Data source creation and MySQL driver problem
                      paco st Newbie

                      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

                       

                       

                       

                       

                       

                       

                       

                       

                       

                      • 8. Re: Data source creation and MySQL driver problem
                        Freeman(Yue) Fang Master

                        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

                        • 9. Re: Data source creation and MySQL driver problem
                          bharadwaj n Novice

                          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>