4 Replies Latest reply on Nov 12, 2008 11:46 AM by Artem Petroff

    Two datasources at one method

    Artem Petroff Newbie

      I have a requirement to import data from mssql DB into mine, while it's mysql.
      I've created two datasources via components.xml, and both work, but separately. for example:



      @In(create = true)
      private EntityManager exportEntityManager;
      
      @In(create = true)
      private EntityManager entityManager;
      ....
      public void textExport() {
              log.info(a());
              log.info(b());
          }
          private int a() {
              List<ViewStaff> lst = exportEntityManager.createQuery("select s from ViewStaff s").getResultList();
              return lst.size();
          }
          private int b() {
              List<Instructor> lst = entityManager.createQuery("select s from Instructor s").getResultList();
              return lst.size();
          }


      When i call only a() or b() - everything works fine, i have number of selected rows. But this code produces exception:


      15:57:16,972 WARN  [loggerI18N] [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] [com.arjuna.ats.internal.jta.transaction.arjunacore.enliststarterror] Transact
      ionImple.enlistResource - XAResource.start returned: XAException.XAER_PROTO for < 131075, 29, 27, 1--3ff637f8:442:49117cd4:212a-3ff637f8:442:49117cd4:2130
                                                     >
      15:57:16,972 ERROR [STDERR] org.jboss.resource.connectionmanager.JBossLocalXAException: Trying to start a new tx when old is not complete! old: < 131075, 29, 27, 1--3ff637f8:442:49
      117cd4:20ee-3ff637f8:442:49117cd4:20f5                                                                         >, new < 131075, 29, 27, 1--3ff637f8:442:49117cd4:212a-3ff637f8:442:4
      9117cd4:2130                                                                         >, flags 0
      15:57:16,972 ERROR [STDERR]     at org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource.start(TxConnectionManager.java:901)
      15:57:16,972 ERROR [STDERR]     at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:776)
      15:57:16,972 ERROR [STDERR]     at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:499)
      15:57:16,972 ERROR [STDERR]     at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener$TransactionSynchronization.enlist(TxConnectionManager.java:788
      ...
              at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
              at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:262)
              at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
              at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
              at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:446)
              at java.lang.Thread.run(Thread.java:619)
      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Cannot open connection
              at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629)
              at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73)
              at org.spbstu.cqc.demo.CountQuantityInGroup.a(CountQuantityInGroup.java:49)
              at org.spbstu.cqc.demo.CountQuantityInGroup.textExport(CountQuantityInGroup.java:43)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
              at java.lang.reflect.Method.invoke(Method.java:597)
              at org.jboss.seam.util.Reflections.invoke(Reflections.java:22)
              at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
              at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
              at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
              at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
              at org.jboss.seam.core.BijectionInterceptor.aroundInvoke(BijectionInterceptor.java:77)
              at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
              at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
              at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
              at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
              at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:166)
              at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:102)
              at org.spbstu.cqc.demo.CountQuantityInGroup_$$_javassist_2.textExport(CountQuantityInGroup_$$_javassist_2.java)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
              at java.lang.reflect.Method.invoke(Method.java:597)
              at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:329)
              at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:274)
              at org.jboss.el.parser.AstMethodSuffix.getValue(AstMethodSuffix.java:59)
              at org.jboss.el.parser.AstMethodSuffix.invoke(AstMethodSuffix.java:65)
              at org.jboss.el.parser.AstValue.invoke(AstValue.java:96)
              at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
              at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
              at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
              ... 51 more
      Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
              at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
              at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
              at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
              at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
              at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426)
              at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
              at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
              at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
              at org.hibernate.loader.Loader.doQuery(Loader.java:673)
              at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
              at org.hibernate.loader.Loader.doList(Loader.java:2220)
              at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
              at org.hibernate.loader.Loader.list(Loader.java:2099)
              at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
              at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
              at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
              at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
              at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
              at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:64)
              ... 82 more
      Caused by: org.jboss.util.NestedSQLException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.Thro
      wable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: -3ff637f8:442:49117cd4:212a status: ActionStatus.ABORT_ONLY >); - nested throwa
      ble: (org.jboss.resource.JBossResourceException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.lang.T
      hrowable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: -3ff637f8:442:49117cd4:212a status: ActionStatus.ABORT_ONLY >))
              at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:95)
              at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:47)
              at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
              ... 96 more
      Caused by: org.jboss.resource.JBossResourceException: Could not enlist in transaction on entering meta-aware object!; - nested throwable: (javax.transaction.SystemException: java.l
      ang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: -3ff637f8:442:49117cd4:212a status: ActionStatus.ABORT_ONLY >)
              at org.jboss.resource.connectionmanager.TxConnectionManager.managedConnectionReconnected(TxConnectionManager.java:358)
              at org.jboss.resource.connectionmanager.BaseConnectionManager2.reconnectManagedConnection(BaseConnectionManager2.java:524)
              at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:405)
              at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:849)
              at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)
              ... 98 more
      Caused by: javax.transaction.SystemException: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: -3ff637f8:442:49117
      cd4:212a status: ActionStatus.ABORT_ONLY >
              at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener$TransactionSynchronization.checkEnlisted(TxConnectionManager.java:759)
              at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener.enlist(TxConnectionManager.java:592)
              at org.jboss.resource.connectionmanager.TxConnectionManager.managedConnectionReconnected(TxConnectionManager.java:352)
              ... 102 more



      This is not the complete stack trace but i hope it's enough.
      If a try to swap methods call i have same problem.
      Tried to annotate methods with @Transactional(TransactionPropagationType.NEVER)
      but brought no result.


      Thanks for your attention!

        • 1. Re: Two datasources at one method
          Alexander Torstling Newbie

          Hi!


          Just to check, are you using distributed (XA) drivers for both sources? This is necessary when having transactions spanning more than one source.

          • 2. Re: Two datasources at one method
            Artem Petroff Newbie

            thanks, i've changed driver for mysql, mssql server controlled by other person, i hope he will be able to install driver.


            maybe there's a way to split transactions anyway? i mean to get some data using first EntityManager, create some data objects to store data between different transactions and then use another EM.

            • 3. Re: Two datasources at one method
              Rob Hills Newbie

              Hi Artem,


              We're currently doing something similar, except we're copying data between a SQLServer DB and an Oracle DB and are using local-tx datasources.  In our Action bean, the only annotation we have on the whole bean is:



              @Scope(value = ScopeType.CONVERSATION).
              



              We inject the Hibernate session:



              @In private Session session;
              



              Then, for the copy method itself, we simply get a hibernate transaction:


              Transaction tx = session.getTransaction();
              


              do the copy process in the normal way (well almost, I actually use a parameterised query to insert/update entities in the target DB rather than updating the entity classes directly as I've found the former method at least twice as quick) and then commit the transaction or roll it back if any exception occurs.


              This seems to commit/rollback both DBs without any problems.


              So, I guess this is using some slightly older techniques than the nice streamlined stuff we're used to with Seam, but it works well and is quite quick.


              HTH,

              • 4. Re: Two datasources at one method
                Artem Petroff Newbie

                Thanks everyone, finally i've done what i need, this http://www.seamframework.org/Community/MultipleNonxadatasources helped me a lot.
                i have similar thing, i've just turned off tx management for one datasource that is read-only for me. if someone needs it, at persistence.xml:



                <persistence-unit name="uName" transaction-type="RESOURCE_LOCAL">
                          <provider>org.hibernate.ejb.HibernatePersistence</provider>
                          <non-jta-data-source>java:/someDatasource</non-jta-data-source>
                


                and i've defuned this datasource as 'no-tx-datasource' at ds.xml
                so now i can inject both entity managers into one component and use wherever i need. Course, only db is writable.