9 Replies Latest reply on Apr 6, 2010 10:29 AM by Ramesh Reddy

    Using local-tx-datasource with teiid and hibernate

    Immo Benjes Apprentice

      Hi,

       

      I think I might have a big problem. Let me explain.

      We have two databases, one local (MySQL) and one remote (OpenEdge Progress) accessed via teiid.

      We use XA datasources for MySQL and Teiid.

      So far teiid was using a local-tx-datasource to the Progress database. That was okay for reading entities but when I tried to insert an entity I got an exception saying that the transaction wasn't supported.

      I found this discussion :

      http://community.jboss.org/message/531241

      which suggests that you have to use XA datasource when you use hibernate even if you only have one datasource.

       

      Okay I created a XA datasource connector for the Progress Database but when I tried to insert an entity I got this exception:

       

       

      javax.transaction.xa.XAException: [DataDirect][OpenEdge JDBC Driver][OpenEdge]  Global transaction mode not enabled
              at com.ddtek.jdbcx.openedge.OpenEdgeImplXAResource.createAndThrowXAException(Unknown Source)
              at com.ddtek.jdbcx.openedge.OpenEdgeImplXAResource.start(Unknown Source)
              at com.ddtek.jdbcx.base.BaseXAResource.start(Unknown Source)
              at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:777)
              at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:500)
              at org.teiid.dqp.internal.transaction.TransactionServerImpl.enlist(TransactionServerImpl.java:541)

       

       

      I assume this tells me that the ProgressDB is not configured to use global transactions.

      Unfortunately it is very unlikely that they will configure the ProgressDB to support JTA transactions. So I will have to use local-tx-datasources.

      I don't really need transaction spanning multiple datasources (the local db is only for static data) but the comment from Ramesh

      got me scared:

      Teiid would also require all participating connectors to be XA connectors, if you are updating multiple sources in a single transaction. If you are not using Hibernate on top, and this is the only source that is doing an "insert" in Teiid, then it would have used local transaction fine with current driver defined for your source.

       

      We are using Hibernate, does that mean we have to use XA datasources with Teiid?

       

      There was another quote:

       

      If your Hibernate layer is using local transactions by setting "autocommit = false" on the JDBC driver, you can use TeiidDriver or TeiidDatasource. You can just create a regular JDBC connection on Teiid. However, in this scenario Hibernate layer *must* issue a commit.


      If your Hibernate layer is using request level transactions (autocommit=true), then the burden is on Teiid to wrap the transaction around the multiple data sources involved inside Teiid. here we provide more options to the users as to how they would like to handle this. Please see explanation on "txnautoWrap" here.   You can see how to set this property here. You can supply this on the connection URL.

       

       

      Does that mean I can use local-tx-datasource with hibernate as long as I set TxnAutoWrap=PESSIMISTIC ?

       

      So what are my options?

      I guess I can't use a XA datasource to teiid and a local-tx-datasource to the actual db? This would be ideal as I then don't have problems using two datasources (otherwise I get concurrent transactions errors).

       

       

       

      Regards

       

      Immo

        • 1. Re: Using local-tx-datasource with teiid and hibernate
          Ramesh Reddy Master
          Does that mean I can use local-tx-datasource with hibernate as long as I set TxnAutoWrap=PESSIMISTIC ?

           

          Looks like if you use "local-tx-datasource" for your Teiid instead of XA datasource with Hibernate that suffices your requirement. It should not matter what you are using for the "progress" DB in the Teiid configuration. Since, it is always one DB under Teiid, it will not invoke a XA transaction.

           

          I am not too familier with Hibernate, however in this case it can not issue a JTA transaction, if it does not have access to the XAConnection.

           

          Let us know if this works.

           

          Thanks.

           

          Ramesh..

          • 2. Re: Using local-tx-datasource with teiid and hibernate
            Immo Benjes Apprentice

            I really don't know what to do next.

            I am not using any CMT (Container Managed Transactions) now.

             

            I am trying to use a local-tx-datasource to teiid and teiid is using a normal (non-XA) datasource to the progress DB (which is configured to not support JTA). The JDBC connection uses autoCommit false and I am not using TxnAutoWrap (I tried, did not work)

             

            I've annotated my session bean to use bean managed transactions:

            @TransactionManagement(TransactionManagementType.BEAN)

             

            I am injecting javax.transaction.UserTransaction and control it by hand but I am still getting

             

            org.teiid.connector.api.ConnectorException: Transaction is not supported by the connector.
                    at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.createConnection(ConnectorWorkItem.java:142)
                    at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.createExecution(ConnectorWorkItem.java:304)
                    at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:158)
                    at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
            when I try to insert an entity.
            What other options do I have?
            If this all fails I will probably have to go down the route of WebServices to access that DB. I had a look at the Teiid documentation but couldn't find much on WebServices. Has that support been removed? I know you TeiidDesigner supports it but how do you actually deploy your webservices?
            I really hope I don't have to go down that route. Will I have to use the JDBC connection directly? I've never done that outside of a simple Servlet, I don't even know how to get to it in Seam.
            Regards
            Immo
            • 3. Re: Using local-tx-datasource with teiid and hibernate
              Immo Benjes Apprentice

              I've tried to insert an entity with SQuirreL and I get the same error: Transaction is not supported by Connector

              I've checked my VDB and all tables and columns support updates.

               

              Have I configured something wrong?

               

              Regards

               

              Immo

              • 4. Re: Using local-tx-datasource with teiid and hibernate
                Ramesh Reddy Master

                Both CMT or BEAN managed transactions are JTA transactions.  You should look into RESOURCE_LOCAL transactions through JPA. However, I *think* if you did not supply a XA capable connection under a JTA transaction, they participate as if there is no wrapping transaction around them. I would have to verify that!

                 

                Based on your SquirreL based test, seem like some thing is wrong. SquirreL only uses the TeiidDriver in the 'autocommit = true' mode. If you Teiid has *single* source, Teiid does not invoke XA, it uses local transaction on your 'Progress' JDBC driver, unless you are executing stored procedures where Teiid can not evaluate updates properly.

                 

                In the Designer, on your procedures, check the property called "update count", and set this value to "1". By setting this to "1", you are telling the Teiid engine that there is only single source update involved. That will also disable the XA logic, when only one source is involved.

                 

                Ramesh..

                • 5. Re: Using local-tx-datasource with teiid and hibernate
                  Immo Benjes Apprentice

                  Hi Ramesh,

                   

                  I've used a virtual view of the actual database in teiid. The original db doesn't enforce foreign keys so I created a view which uses foreign keys in the designer. I also removed lots of unneeded columns (about a 100 columns per table) in my view. When I tried to insert an entity into the real db via teiid everything worked okay from Teiid (not from jboss though).

                  I didn't create any procedure.

                   

                  Is there any way to see what transaction type teiid is using?

                   

                  Regards and thanks for your help

                   

                  Immo

                  • 6. Re: Using local-tx-datasource with teiid and hibernate
                    Ramesh Reddy Master

                    Immo,

                     

                    Teiid Designer lets the your create the pk/fk relationships, however the runtime does not enforce them. So, be mindful of your virtual layer designs about this information.

                     

                    I do not understand your comment about not working from JBoss. Are you using the Teiid as a data source in a EJB? If it is then it is subject to the transactional behavior I explained.

                     

                    When you create virtual table in the Designer and mark it as "updatable", then it implicitly creates a "update procedure". Check out the extra tabs for "update", "delete" along with "select" transformation. User has the ability to change this default procedure to what they see fit.

                     

                    To see the transaction details turn on logging. Teiid uses JBoss TM called "arjuna", so look for "com.arjuna" logging context in the "log4j.xml" file inside the "deploy" directory for enabling it.

                     

                    Hope this helps.

                     

                    Ramesh..

                    • 7. Re: Using local-tx-datasource with teiid and hibernate
                      Immo Benjes Apprentice

                      Hi Ramesh,

                       

                      I've configured Teiid as a non-jta datasource:

                       

                      From my persistence.xml

                       

                       

                      {code:xml}<persistence-unit name="Teiid" transaction-type="RESOURCE_LOCAL">
                          <provider>org.hibernate.ejb.HibernatePersistence</provider>
                          <non-jta-data-source>java:/TeiidDatasource</non-jta-data-source>
                          <class>com.locuslive.uniserve.uvosi.entity.Porder.pub.PoAlloc</class>
                            <class>com.locuslive.uniserve.uvosi.entity.Porder.pub.PoDetails</class>
                            <class>com.locuslive.uniserve.uvosi.entity.Porder.pub.PoHeader</class>
                            <class>com.locuslive.uniserve.uvosi.entity.Porder.pub.PoShipment</class>
                            <class>com.locuslive.uniserve.uvosi.entity.Porder.pub.PoShipmentItem</class>
                            <exclude-unlisted-classes />
                            <properties>
                               <property name="hibernate.dialect" value="org.teiid.dialect.TeiidDialect"/>
                               <property name="hibernate.hbm2ddl.auto" value="validate"/>
                               <property name="hibernate.show_sql" value="true"/>
                               <property name="hibernate.format_sql" value="true"/>
                               <property name="jboss.entity.manager.factory.jndi.name" value="java:/TeiidEntityManagerFactory"/>
                               <property name="jboss.entity.manager.jndi.name" value="java:/TeiidEntityManager"/>
                               <property name="hibernate.connection.autocommit" value="true"/> 
                               <property name="hibernate.connection.release_mode" value="after_transaction"/>
                               <property name="hibernate.ejb.interceptor" value="com.locuslive.uniserve.util.HibernateInterceptor" />
                            </properties>
                      </persistence-unit>{code:xml}

                       

                      I've also told Hibernate to use autocommit.

                       

                      In my JEE app I use EntityTransaction

                      EntityTransaction tx = entityManager.getTransaction();

                      tx.begin();

                      entityManager.merge(entity);

                      tx.commit();

                       

                      The following warning confirms that I am not using JTA:

                       

                      {code}17:29:36,797 WARN  [AbstractEntityManagerImpl] Calling joinTransaction() on a non JTA EntityManager{code}

                       

                       

                       

                      If I use txnAutoWrap=OFF (or not specified) I still get the same error: org.teiid.connector.api.ConnectorException: Transaction is not supported by the connector.

                       

                       

                      {code}2010-04-06 11:05:06,585 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.DQP - ############# PW PROCESSING on 0.12 with state NEW ###########

                      2010-04-06 11:05:06,585 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.DQP - 0.12 executing prepared insert into VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER (FACTORY, FACTORY_NAME, PO_NUMBER, SUPPLIER, BUYER_CODE, FOREIGN_AGENT, SHIPMENT_METHOD, PO_VERSION_NUMBER, PO_STATUS, CUSTOMER, PO_DATE, SHIPMENT_TERMS, PAYMENT_TERMS, LAST_AMENDED, COO, COO_DESC, NOTES, SUPPLIER_NAME, TERMS_DESC, SHIPMENT_NO_SEQUENCE, POL, POD, POD_DESC, POL_DESC, FINAL_DEST, FINAL_DESC, AVAILABLE_DATE, SHIPMENT_DATE, APPROVAL_CODE, FULLY_SHIP_DATE, ALLOC_STATUS, DELETED, LAST_AMENDED_TIME, LAST_AMENDED_UNI, HDR_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

                      2010-04-06 11:05:06,585 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.DQP - Query does not exist in cache:  insert into VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER (FACTORY, FACTORY_NAME, PO_NUMBER, SUPPLIER, BUYER_CODE, FOREIGN_AGENT, SHIPMENT_METHOD, PO_VERSION_NUMBER, PO_STATUS, CUSTOMER, PO_DATE, SHIPMENT_TERMS, PAYMENT_TERMS, LAST_AMENDED, COO, COO_DESC, NOTES, SUPPLIER_NAME, TERMS_DESC, SHIPMENT_NO_SEQUENCE, POL, POD, POD_DESC, POL_DESC, FINAL_DEST, FINAL_DESC, AVAILABLE_DATE, SHIPMENT_DATE, APPROVAL_CODE, FULLY_SHIP_DATE, ALLOC_STATUS, DELETED, LAST_AMENDED_TIME, LAST_AMENDED_UNI, HDR_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

                      2010-04-06 11:05:06,587 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.QUERY_RESOLVER - Resolving command INSERT INTO VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER (FACTORY, FACTORY_NAME, PO_NUMBER, SUPPLIER, BUYER_CODE, FOREIGN_AGENT, SHIPMENT_METHOD, PO_VERSION_NUMBER, PO_STATUS, CUSTOMER, PO_DATE, SHIPMENT_TERMS, PAYMENT_TERMS, LAST_AMENDED, COO, COO_DESC, NOTES, SUPPLIER_NAME, TERMS_DESC, SHIPMENT_NO_SEQUENCE, POL, POD, POD_DESC, POL_DESC, FINAL_DEST, FINAL_DESC, AVAILABLE_DATE, SHIPMENT_DATE, APPROVAL_CODE, FULLY_SHIP_DATE, ALLOC_STATUS, DELETED, LAST_AMENDED_TIME, LAST_AMENDED_UNI, HDR_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

                      2010-04-06 11:05:06,592 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.DQP - ProcessTree for 0.12 AccessNode(1) output=["Count"] INSERT INTO VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER (VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FACTORY, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FACTORY_NAME, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PO_NUMBER, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SUPPLIER, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.BUYER_CODE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FOREIGN_AGENT, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SHIPMENT_METHOD, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PO_VERSION_NUMBER, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PO_STATUS, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.CUSTOMER, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PO_DATE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SHIPMENT_TERMS, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PAYMENT_TERMS, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.LAST_AMENDED, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.COO, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.COO_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.NOTES, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SUPPLIER_NAME, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.TERMS_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SHIPMENT_NO_SEQUENCE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.POL, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.POD, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.POD_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.POL_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FINAL_DEST, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FINAL_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.AVAILABLE_DATE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SHIPMENT_DATE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.APPROVAL_CODE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FULLY_SHIP_DATE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.ALLOC_STATUS, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.DELETED, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.LAST_AMENDED_TIME, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.LAST_AMENDED_UNI, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.HDR_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

                       

                      2010-04-06 11:05:06,592 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.TXN_LOG - before getOrCreateTransactionContext:com.metamatrix.dqp.embedded.services.EmbeddedTransactionService@1effc3eb(0)

                      2010-04-06 11:05:06,592 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.TXN_LOG - after getOrCreateTransactionContext : TxnContext: a000072:c053:4bbb012c:3

                      2010-04-06 11:05:06,592 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.BUFFER_MGR - Creating TupleBuffer: 22 of type PROCESSOR

                      2010-04-06 11:05:06,592 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.CONNECTOR - 0.12.1.2 Create State

                      2010-04-06 11:05:06,592 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.DQP - ############# PW EXITING on 0.12 - processor blocked ###########

                      2010-04-06 11:05:06,592 DEBUG [Worker3_QueryProcessorQueue16] org.teiid.DQP - 0.12 end processing WORKING

                      2010-04-06 11:05:06,593 DEBUG [Worker4_UVOS tx Connector<0>5] org.teiid.RESOURCE_POOLING - Beginning work with virtual worker Worker4_UVOS tx Connector<0>5

                      2010-04-06 11:05:06,593 DEBUG [Worker4_UVOS tx Connector<0>5] org.teiid.DQP - 0.12.1.2 start processing MORE_WORK

                      2010-04-06 11:05:06,593 DEBUG [Worker4_UVOS tx Connector<0>5] org.teiid.CONNECTOR - 0.12.1.2 Processing NEW request: INSERT INTO VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER (VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FACTORY, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FACTORY_NAME, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PO_NUMBER, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SUPPLIER, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.BUYER_CODE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FOREIGN_AGENT, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SHIPMENT_METHOD, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PO_VERSION_NUMBER, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PO_STATUS, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.CUSTOMER, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PO_DATE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SHIPMENT_TERMS, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.PAYMENT_TERMS, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.LAST_AMENDED, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.COO, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.COO_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.NOTES, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SUPPLIER_NAME, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.TERMS_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SHIPMENT_NO_SEQUENCE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.POL, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.POD, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.POD_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.POL_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FINAL_DEST, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FINAL_DESC, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.AVAILABLE_DATE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.SHIPMENT_DATE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.APPROVAL_CODE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.FULLY_SHIP_DATE, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.ALLOC_STATUS, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.DELETED, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.LAST_AMENDED_TIME, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.LAST_AMENDED_UNI, VIRT_UVOS.UVOS.PORDER.PUB.PO_HEADER.HDR_ID) VALUES (null, null, null, null, null, null, null, null, null, null, {d'2010-04-06'}, null, null, {d'2010-04-06'}, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 0, 39883, '06/04/2010 11:04:43', 125689)

                      2010-04-06 11:05:06,593 DEBUG [Worker4_UVOS tx Connector<0>5] org.teiid.CONNECTOR - 0.12.1.2 creating connection for atomic-request

                      2010-04-06 11:05:06,621 WARN [Worker4_UVOS tx Connector<0>5] org.teiid.CONNECTOR - Connector worker process failed for atomic-request=0.12.1.2

                      org.teiid.connector.api.ConnectorException: Transaction is not supported by the connector.

                           at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.createConnection(ConnectorWorkItem.java:142)

                           at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.createExecution(ConnectorWorkItem.java:304)

                           at org.teiid.dqp.internal.datamgr.impl.ConnectorWorkItem.process(ConnectorWorkItem.java:158)

                           at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)

                           at org.teiid.dqp.internal.datamgr.impl.SynchConnectorWorkItem.run(SynchConnectorWorkItem.java:60)

                           at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)

                           at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

                           at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

                           at java.lang.Thread.run(Thread.java:637)

                      2010-04-06 11:05:06,622 DEBUG [Worker4_UVOS tx Connector<0>5] org.teiid.DQP - closed atomic-request: 0.12.1.2

                      2010-04-06 11:05:06,622 DEBUG [Worker4_UVOS tx Connector<0>5] org.teiid.DQP - 0.12 more work IDLE{code}

                       

                       

                       

                      If I specify txnAutoWrap=PESSIMISTIC (in this case I expect teiid to create a JTA transaction) I get the following error:

                       

                      {code}2010-04-04 18:12:13,757 DEBUG [Worker14_SocketWorker69] org.teiid.QUERY_SERVICE - [Ljava.lang.Object;@5e15e68d

                      [XATransactionException][com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] [com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] The transactio

                      n is not active!

                      1 [IllegalStateException][com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] [com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] The transacti

                      on is not active!

                              at org.teiid.dqp.internal.transaction.TransactionServerImpl.commit(TransactionServerImpl.java:416)

                              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 com.metamatrix.common.log.LogManager$1.invoke(LogManager.java:407)

                              at $Proxy20.commit(Unknown Source)

                              at org.teiid.dqp.internal.process.DQPCore.commit(DQPCore.java:673)

                              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.teiid.transport.ServerWorkItem.run(ServerWorkItem.java:100)

                              at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)

                              at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

                              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

                              at java.lang.Thread.run(Thread.java:637)

                      Caused by: java.lang.IllegalStateException: [com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] [com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] The transaction is not active!

                              at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1380)

                              at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:135)

                              at org.teiid.dqp.internal.transaction.TransactionServerImpl.commit(TransactionServerImpl.java:413)

                              ... 16 more

                      2010-04-04 18:12:13,758 WARN [Worker14_SocketWorker69] org.teiid.QUERY_SERVICE - Processing exception '[com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] [com.arjuna.ats.internal.jta.transaction.arjunacore.inactive] The transaction is not active!' for session 1.  Exception type com.metamatrix.common.xa.XATransactionException thrown from com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1380). Enable more detai:{code}

                       

                       

                      Is my understanding correct that when autocommit is true and I use txnAutoWrap=OFF and I use a non-jta datasource then no JTA transactions should be used, neither in JBoss (the application server) nor in Teiid? So why do I still get the transaction not supported exception?

                       

                      Any help is appreciated as I really don't know what else to try. I know I can insert an entity with SquirreL, so how can I mimic this behaviour? I know it is far from ideal but this seems to be my only option.

                       

                      Regards

                       

                      Immo

                      • 8. Re: Using local-tx-datasource with teiid and hibernate
                        Immo Benjes Apprentice

                        I've compared the logs for when talking to Teiid from JBoss and from SquirreL

                        Creating an entity from SquirreL works and this is the log:

                         

                        {quote}2010-04-06 12:41:20,571 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.DQP - ############# PW PROCESSING on 1.6 with state NEW ###########
                        2010-04-06 12:41:20,571 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.DQP - 1.6 executing  insert into "UVOS"."PORDER.PUB.PO_ALLOC"(UID_alloc) values(1500035940)
                        2010-04-06 12:41:20,571 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.QUERY_RESOLVER - Resolving command INSERT INTO UVOS.PORDER.PUB.PO_ALLOC (UID_alloc) VALUES (1500035940)
                        2010-04-06 12:41:20,572 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.DQP - ProcessTree for 1.6 AccessNode(1) output=["Count"] INSERT INTO UVOS.PORDER.PUB.PO_ALLOC (UVOS.PORDER.PUB.PO_ALLOC.UID_alloc) VALUES (1500035940)
                        2010-04-06 12:41:20,572 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.TXN_LOG - before getOrCreateTransactionContext:com.metamatrix.dqp.embedded.services.EmbeddedTransactionService@31a734ff(1)
                        2010-04-06 12:41:20,572 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.TXN_LOG - after getOrCreateTransactionContext : TxnContext: null
                        2010-04-06 12:41:20,572 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.BUFFER_MGR - Creating TupleBuffer: 35 of type PROCESSOR
                        2010-04-06 12:41:20,572 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.CONNECTOR - 1.6.1.5 Create State
                        2010-04-06 12:41:20,572 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.DQP - ############# PW EXITING on 1.6 - processor blocked ###########
                        2010-04-06 12:41:20,572 DEBUG [Worker1_QueryProcessorQueue30] org.teiid.DQP - 1.6 end processing WORKING
                        2010-04-06 12:41:20,573 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.RESOURCE_POOLING - Beginning work with virtual worker Worker0_UVOS tx Connector<0>6
                        2010-04-06 12:41:20,573 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.DQP - 1.6.1.5 start processing MORE_WORK
                        2010-04-06 12:41:20,573 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 Processing NEW request: INSERT INTO UVOS.PORDER.PUB.PO_ALLOC (UVOS.PORDER.PUB.PO_ALLOC.UID_alloc) VALUES (1500035940)
                        2010-04-06 12:41:20,573 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 creating connection for atomic-request
                        2010-04-06 12:41:20,573 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - Obtaining connection for id SingleIdentity
                        2010-04-06 12:41:20,573 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - Existing connection leased for SingleIdentity
                        2010-04-06 12:41:20,573 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 creating execution for atomic-request
                        2010-04-06 12:41:20,584 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 Obtained execution
                        2010-04-06 12:41:20,585 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - Source-specific command: INSERT INTO "PORDER"."PUB"."po_alloc" ("UID_alloc") VALUES (1500035940)
                        2010-04-06 12:41:20,880 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 Executed command
                        2010-04-06 12:41:20,880 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 Sending results from connector
                        2010-04-06 12:41:20,880 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 Obtained last batch, total row count: 1
                        2010-04-06 12:41:20,880 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.DQP - 1.6 more work IDLE
                        2010-04-06 12:41:20,880 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 Processing Close : INSERT INTO UVOS.PORDER.PUB.PO_ALLOC (UVOS.PORDER.PUB.PO_ALLOC.UID_alloc) VALUES (1500035940)
                        2010-04-06 12:41:20,880 DEBUG [Worker1_QueryProcessorQueue31] org.teiid.RESOURCE_POOLING - Beginning work with virtual worker Worker1_QueryProcessorQueue31
                        2010-04-06 12:41:20,880 DEBUG [Worker1_QueryProcessorQueue31] org.teiid.DQP - 1.6 start processing MORE_WORK
                        2010-04-06 12:41:20,880 DEBUG [Worker1_QueryProcessorQueue31] org.teiid.DQP - ############# PW PROCESSING on 1.6 with state PROCESSING ###########
                        2010-04-06 12:41:20,881 DEBUG [Worker1_QueryProcessorQueue31] org.teiid.CONNECTOR - 1.6.1.5 Already closing request
                        2010-04-06 12:41:20,881 DEBUG [Worker1_QueryProcessorQueue31] org.teiid.DQP - QueryProcessor: closing processor
                        2010-04-06 12:41:20,881 DEBUG [Worker1_QueryProcessorQueue31] org.teiid.DQP - [RequestWorkItem.sendResultsIfNeeded] requestID: 1.6 resultsID: 35 done: false
                        2010-04-06 12:41:20,881 DEBUG [Worker1_QueryProcessorQueue31] org.teiid.Server -  message: MessageHolder: contents=ResultsMessage rowCount=1 finalRow=1 for request ID:10
                        2010-04-06 12:41:20,881 DEBUG [Worker1_QueryProcessorQueue31] org.teiid.DQP - 1.6 end processing WORKING
                        2010-04-06 12:41:20,917 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 Closed execution
                        2010-04-06 12:41:20,917 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - ConnectionPool(release) connection released: SingleIdentity
                        2010-04-06 12:41:20,917 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 Closed connection
                        2010-04-06 12:41:20,917 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.CONNECTOR - 1.6.1.5 Remove State
                        2010-04-06 12:41:20,917 DEBUG [Worker0_UVOS tx Connector<0>6] org.teiid.DQP - closed atomic-request: 1.6.1.5{quote}

                         

                        The interesting line is

                        org.teiid.TXN_LOG - after getOrCreateTransactionContext : TxnContext: null

                         

                        while when talking to it from JBoss with autocommit=true and txnAutoWrap=OFF it is:

                        org.teiid.TXN_LOG - after getOrCreateTransactionContext : TxnContext: a000072:c053:4bbb012c:3

                         

                        So teiid creates a transaction for the latter one but not for the squirreL request.

                         

                        Regards

                         

                        Immo

                        • 9. Re: Using local-tx-datasource with teiid and hibernate
                          Ramesh Reddy Master

                          org.teiid.TXN_LOG - after getOrCreateTransactionContext : TxnContext: null

                           

                          while when talking to it from JBoss with autocommit=true and txnAutoWrap=OFF it is:

                          org.teiid.TXN_LOG - after getOrCreateTransactionContext : TxnContext: a000072:c053:4bbb012c:3

                          Based on above log, SquirreL case you are using "autoCommit = true", so when the query entered the Teiid engine there is no transaction.

                           

                          In JBoss case,  looks like it is "local" transaction. i.e. Your entity manager is setting "autoCommit=false", then begin()-->work-->commit(). In this case Teiid wraps the query in transaction too, as Teiid does not manage the transaction boundaries. So, in this case if you want to disable the local transactions, you can set "disableLocalTxn=true" on your URL connection property, then the Teiid driver wil behave as if "autoCommit=true". Seems like we missed this in documentation, I created this JIRA to correct this. However, note that if you executed multiple queries between begin and commit calls of the entity transaction, they are not bound to a single transaction.

                           

                          Ramesh..