5 Replies Latest reply on Dec 13, 2008 4:44 PM by Francois Eric

    Multiple Datasources and XA-Datasource

    Francois Eric Novice

      Hello,


      I am trying to have my application access 2 datasources.  First I tried using a local-tx-datasource like so:


        <local-tx-datasource>
          <jndi-name>test</jndi-name>
          <connection-url>jdbc:mysql://localhost:3306/test</connection-url>
          <driver-class>com.mysql.jdbc.Driver</driver-class>
          <user-name>test</user-name>
          <password>test</password>
        </local-tx-datasource>
        <local-tx-datasource>
          <jndi-name>test_old</jndi-name>
          <connection-url>jdbc:mysql://localhost:3306/data_collect_francois</connection-url>
          <driver-class>com.mysql.jdbc.Driver</driver-class>
          <user-name>test</user-name>
          <password>test</password>
        </local-tx-datasource>
      



      I was getting an error when trying to mkae a transaction (Cannot Open Connection).  Reading the posts, a lot of people recommended using xa-datasource for this.  I therefore tried modifying the configuration for the following:


           <xa-datasource>
                <jndi-name>test</jndi-name>
                <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/test</xa-datasource-property>
                <user-name>test</user-name>
                <password>test</password>
              <track-connection-by-tx />
                <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                <max-pool-size>5</max-pool-size>
                <min-pool-size>1</min-pool-size>
                <blocking-timeout-millis>2000</blocking-timeout-millis>
                <idle-timeout-minutes>2</idle-timeout-minutes>
                <track-connection-by-tx>true</track-connection-by-tx>
                <new-connection-sql>set autocommit=1</new-connection-sql>
                <no-tx-separate-pools>true</no-tx-separate-pools>
                <exception-sorter-class-name>
                     org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
                </exception-sorter-class-name>
              <metadata>
                  <type-mapping>mySQL</type-mapping>
              </metadata>
           </xa-datasource>
      
           <xa-datasource>
                <jndi-name>test_old</jndi-name>
                <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/data_collect_francois</xa-datasource-property>
                <user-name>test</user-name>
                <password>test</password>
      
              <track-connection-by-tx />
                <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                <max-pool-size>5</max-pool-size>
                <min-pool-size>1</min-pool-size>
                <blocking-timeout-millis>2000</blocking-timeout-millis>
                <idle-timeout-minutes>2</idle-timeout-minutes>
                <track-connection-by-tx>true</track-connection-by-tx>
                <new-connection-sql>set autocommit=1</new-connection-sql>
                <no-tx-separate-pools>true</no-tx-separate-pools>
                <exception-sorter-class-name>
                     org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
                </exception-sorter-class-name>
              <metadata>
                  <type-mapping>mySQL</type-mapping>
              </metadata>
          </xa-datasource>
      



      And now when launching the embedded jboss (for my test case).  I get the following error:


      ...
      INFO  11-12 12:03:58,989 (ConnectionFactoryBindingService.java:bindConnectionFactory:160)  -Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=inukshuk' to JNDI name 'java:inukshuk'
      INFO  11-12 12:03:59,022 (ConnectionFactoryBindingService.java:bindConnectionFactory:160)  -Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=inukshuk_old' to JNDI name 'java:inukshuk_old'
      FAILED CONFIGURATION: @BeforeSuite startSeam
      org.jboss.deployers.client.spi.IncompleteDeploymentException: Summary of incomplete deployments (SEE PREVIOUS ERRORS FOR DETAILS):
      
      *** CONTEXTS MISSING DEPENDENCIES: Name -> Dependency{Required State:Actual State}
      
      jboss.jdbc:datasource=inukshuk,service=metadata
       -> jboss.jdbc:service=metadata{Start:** NOT FOUND **}
       -> jboss.jdbc:service=metadata{Create:** NOT FOUND **}
      
      jboss.jdbc:datasource=inukshuk_old,service=metadata
       -> jboss.jdbc:service=metadata{Create:** NOT FOUND **}
       -> jboss.jdbc:service=metadata{Start:** NOT FOUND **}
      
      
      *** CONTEXTS IN ERROR: Name -> Error
      
      jboss.jdbc:service=metadata -> ** NOT FOUND **
      
      
           at org.jboss.deployers.plugins.deployers.DeployersImpl.checkComplete(DeployersImpl.java:576)
           at org.jboss.deployers.plugins.main.MainDeployerImpl.checkComplete(MainDeployerImpl.java:559)
           at org.jboss.embedded.DeploymentGroup.process(DeploymentGroup.java:128)
           at org.jboss.embedded.Bootstrap.deployResourceBases(Bootstrap.java:289)
           at org.jboss.seam.mock.EmbeddedBootstrap.startAndDeployResources(EmbeddedBootstrap.java:15)
           at org.jboss.seam.mock.AbstractSeamTest.startJbossEmbeddedIfNecessary(AbstractSeamTest.java:1024)
           at org.jboss.seam.mock.AbstractSeamTest.startSeam(AbstractSeamTest.java:915)
           at org.jboss.seam.mock.SeamTest.startSeam(SeamTest.java:58)
      ... Removed 15 stack frames
      



      By the way there is no error prior to that in the log.


      Any idea what I'm doing wrong?  Just so you know when I had the local-tx-datasource configurations I was able to work on one database at a time with no problems.


      Thanks,


      Francois

        • 2. Re: Multiple Datasources and XA-Datasource
          Francois Eric Novice

          Ah finally on my part I corrected it by changing the mysql connector.  I updated to the nightly build and it worked. My working version is: mysql-connector-java-5.1-nightly-20081212-bin.jar

          • 3. Re: Multiple Datasources and XA-Datasource
            Francois Eric Novice

            Oh yeah and here is my datasources file( I had to remove the stuff about metadata)


            <?xml version="1.0" encoding="UTF-8"?>
            
            <datasources>
                <xa-datasource>
                    <jndi-name>test</jndi-name>
                    <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                    <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/test</xa-datasource-property>
                    <user-name>test</user-name>
                    <password>test</password>
            
                    <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                    <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/test</xa-datasource-property>
            
                    <track-connection-by-tx />
                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
            
                    <max-pool-size>5</max-pool-size>
                    <min-pool-size>1</min-pool-size>
            
                    <blocking-timeout-millis>2000</blocking-timeout-millis>
                    <idle-timeout-minutes>2</idle-timeout-minutes>
                    <track-connection-by-tx>true</track-connection-by-tx>
                    <new-connection-sql>set autocommit=1</new-connection-sql>
                    <no-tx-separate-pools>true</no-tx-separate-pools>
            
                    <exception-sorter-class-name>
                        org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
                    </exception-sorter-class-name>
                </xa-datasource>
            
                <xa-datasource>
                    <jndi-name>test_old</jndi-name>
                    <user-name>test</user-name>
                    <password>test</password>
            
                    <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                    <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/data_collect_francois</xa-datasource-property>
            
                    <track-connection-by-tx />
                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
            
                    <max-pool-size>5</max-pool-size>
                    <min-pool-size>1</min-pool-size>
            
                    <blocking-timeout-millis>2000</blocking-timeout-millis>
                    <idle-timeout-minutes>2</idle-timeout-minutes>
                    <track-connection-by-tx>true</track-connection-by-tx>
                    <new-connection-sql>set autocommit=1</new-connection-sql>
                    <no-tx-separate-pools>true</no-tx-separate-pools>
            
                    <exception-sorter-class-name>
                        org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
                    </exception-sorter-class-name>
                </xa-datasource>
            </datasources>
            
            

            • 4. Re: Multiple Datasources and XA-Datasource
              walter mauritz Newbie

              I need more information of the error.


              ! Post full error log - this part may not show the relevant part
              ! Post components.xml
              ! Post hibernate/JPA config


              the last two configs are important too.


              see my post with working 2PC:


              http://www.seamframework.org/Community/TransactionQuestionInJbpmContextNoTransation



              btw. you ONLY need XA if you have transactions over two different databases. If you have two different schema in same database, then no XA is required. Same goes for reading stable values from one remote db and writing to other

              • 5. Re: Multiple Datasources and XA-Datasource
                Francois Eric Novice

                Hi Walter,


                Thanks for your answer.  I wasn't really clear but 2 posts before yours I mentionned that I found the solution to my problem.


                FInally the problem resided in the mysql J connector 5.1.6.  By using the latest nightly build, it corrected the error.


                Thank you though for your precisions.  For the XA datasources, I'm reading in one db and read/writing in the other.  I tried using local-tx-datasources but to no avail. By using the xa-datasource it solved my problem.


                Thanks for your time,


                Francois