1 2 Previous Next 15 Replies Latest reply on Dec 30, 2015 2:05 PM by shawkins

    Insert data into view in jboss data vertualization.

    nish18
    nish18 Dec 14, 2015 2:11 AM

      Hi,

      I am creating a view from mysql datasource using jbos6.3 and teiid-client-8.7.0.Final.jar:

       

      Following steps i did while creating the view and i am able to create the view succesfully but not able to insert values into it.

       

       

      mysql datasource entry in standalone.xml file:

      --------------------------------------------

      <datasource jndi-name="java:/testmysql" pool-name="testmysql" enabled="true">

                          <connection-url>jdbc:mysql://XXXXX:3306/aakash_test</connection-url>

                          <driver>test_mysql</driver>

                          <security>

                              <user-name>root</user-name>

                              <password>root</password>

                          </security>

      </datasource>

       

      <driver name="test_mysql" module="com.mysql">

        <driver-class>com.mysql.jdbc.Driver</driver-class>

      </driver>

       

      vdbfile:

      -------------------

      <vdb name="EmployeeVDB" version="1">

          <description>A Dynamic VDB</description>

          <property name="UseConnectorMetadata" value="false"/>

       

            <model name="joinView" type="VIRTUAL">

              <metadata type="DDL"><![CDATA[

                CREATE VIEW SvcView (emp_id long,emp_fname string,emp_lname string,salary long) AS

                SELECT  emp_id,emp_fname,emp_lname,salary

                FROM "aakash_test.employee";

                    

              ]]>

              </metadata>

          </model> 

          <model visible="true" type="PHYSICAL" name="Test2">

              <source name="sqlTest2" translator-name="mysql5" connection-jndi-name="java:/testmysql"/>

          </model>

         

      </vdb>

       

       

      *)I am able to fetch the values from the view by using the below query:

       

      select * from SvcView;

       

      Output:

      Results

      1, Aakash,Sen,27000

      2, Lokesh,saini,14000

      3, test,testsername,55

      4, test_1,test_2,50

       

      *)But when i am trying to insert into the view it does not work.

       

      Used Sql Insert query:

      insert into SvcView(emp_id,emp_fname,emp_lname,salary) values(9,'testFname1','testLname1',4500);

       

      it is showing me the below error:

       

      Caused by: [TeiidProcessingException] TEIID30492: Error Code:TEIID30492 Message:Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 Metadata does not allow updates on the group: SvcView

       

       

      Please help me out to resolve this problem.

        • 1. Re: Insert data into view in jboss data vertualization.
          shawkins
          shawkins Dec 14, 2015 10:49 AM (in response to nish18)

          You need to add the updatable option in the metadata:

           

          CREATE VIEW SvcView (emp_id long,emp_fname string,emp_lname string,salary long) OPTIONS (UPDATABLE true)...


          From there your view is simple enough that we'll know how to perform updates against it.

            • Actions
          • 2. Re: Insert data into view in jboss data vertualization.
            nish18
            nish18 Dec 15, 2015 8:35 AM (in response to nish18)

            Thanks Steven !! for your valuable response.I made the change and now i am able to insert data into the view which is created from the single table of a datasource.

             

            But I am facing another two problems which are mentioned below.Can you please help me out on those two also.

             

            ****** Problem 1 ******

             

            *) I am creating a view using two different tables from two datasources ,one table from mysql datasource and another from sqlserver.

             

             

            Following steps i did while creating the view and i am able to create the view succesfully but not able to insert values into it.

             

             

            mysql datasource entry in standalone.xml file:

            --------------------------------------------

             

             

            <datasource jndi-name="java:/testmysql" pool-name="testmysql" enabled="true">

                                <connection-url>jdbc:mysql://xxxxxServer:3306/aakash_test</connection-url>

                                <driver>test_mysql</driver>

                                <security>

                                    <user-name>root</user-name>

                                    <password>root</password>

                                </security>

            </datasource>

             

             

            <driver name="test_mysql" module="com.mysql">

              <driver-class>com.mysql.jdbc.Driver</driver-class>

            </driver>

             

             

            sqlserver datasource entry in standalone.xml file:

            --------------------------------------------

            <datasource jndi-name="java:/testsqlserver" pool-name="testsqlserver" enabled="true">

                                <connection-url>jdbc:jtds:sqlserver://xxxxxxxServer/DQ_2012;instance=SQL2012;port=1433</connection-url>

                                <driver>test_sqlserver</driver>

                                <security>

                                    <user-name>root</user-name>

                                    <password>root</password>

                                </security>

            </datasource>

             

             

            <driver name="test_sqlserver" module="com.sqlserver">

              <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>

            </driver>

             

             

            vdbfile:

            -------------------

             

             

            <vdb name="EmployeeVDB" version="1">

                <description>A Dynamic VDB</description>

                <property name="UseConnectorMetadata" value="false"/>

             

                  <model name="join" type="VIRTUAL">

                    <metadata type="DDL"><![CDATA[

             

                   CREATE VIEW SvcView (cust_id long,cust_name string,cust_address string,item string,price double)OPTIONS (UPDATABLE true) AS

                  SELECT  c.cust_id,c.cust_name,c.cust_address,o.item,o.price

                  FROM "aakash_test.order" o

                  INNER JOIN "DQ_2012.customer" c  ON o.cust_id = c.cust_id;

             

                          ]]>

                    </metadata>

                </model> 

                <model visible="true" type="PHYSICAL" name="Test2">

                    <source name="sqlTest2" translator-name="mysql5" connection-jndi-name="java:/testmysql"/>

                </model>

              <model visible="true" type="PHYSICAL" name="Test1">

                    <source name="sqlTest" translator-name="sqlserver" connection-jndi-name="java:/testsqlserver"/>

                </model>

               

            </vdb>

             

             

            *)I am able to fetch the values from the view by using the below query:

            select query:

            select * from SvcView;

             

             

            Output:

            Results

            1:1, Aakash,pune,'item1',34234

            2:2, Lokesh,pune,'item2',14000

            3:3, test,pune,'item3',55

            4:4, test_1,pune,'item4',50

             

             

            *)But when i am trying to insert into the view it does not work.

             

             

            Insert query:

            insert into SvcView(cust_id,cust_name,cust_address,item,price)values(5,'testFname1','testAddress','testItem',45345);

             

             

            *)I am getting the below error message:

             

             

            A procedure may be needed to handle the Insert since: The query defining an updatable view has no valid target for INSERT

             

             

             

             

             

            ***Problem 2****

             

             

            *)There is another problem i am facing while creating the view from two different tables of same dataosurce(mysql)

             

             

            There are two tables A and B in mysql database:

             

             

            Table A(a,b,c)

            Table B(x,y,a)

             

             

            where a is the primary key with auto increament constraint in table A,

            a is the foreign key in table B,

            x is the primary key with auto increament constraint in table B.

             

             

            *)I am creating a view C from using these  two tables

             

             

            CREATE VIEW TestView (a long,b string ,c string ,x integer,y string)OPTIONS (UPDATABLE true) AS

            SELECT A.a,A.b,A.c,B.x,B.y

            FROM A ,B inner join A.a=B.a;

             

             

            *)I am able to fetch data from the view.But when i am trying to insert into the view using below query it does not work:

             

             

            insert into TestView(a,b,c,x,y)values(1,'testVal1','testVal2',5,'testVal3');

             

             

            I am getting below error:

            Caused by: [TeiidProcessingException] TEIID30492: Error Code:TEIID30492 Message:Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 TEIID30376 The specified change set [a,b,c,x,y] against an inherently updatable view does not map to a key preserving group.

             

             

            Please help me out to resolve these  problems.

            1 of 1 people found this helpful
              • Actions
            • 3. Re: Insert data into view in jboss data vertualization.
              rareddy
              rareddy Dec 15, 2015 11:12 AM (in response to nish18)

              Nishant,

               

              For both your issues, just like how you defined the "select" transformation, you can define "insert", "update", "delete" transformations using the triggers. An example VDB looks like

               

              <vdb name="EmployeeVDB" version="1">
  <description>A Dynamic VDB</description>
  <property name="UseConnectorMetadata" value="false" />
 <model name="join" type="VIRTUAL">
  <metadata type="DDL"><![CDATA[

            CREATE VIEW SvcView (cust_id long,cust_name string,cust_address string,item string,price double)
                OPTIONS (UPDATABLE true) AS
                SELECT  c.cust_id,c.cust_name,c.cust_address,o.item,o.price
                FROM "aakash_test.order" o INNER JOIN "DQ_2012.customer" c  ON o.cust_id = c.cust_id;

            CREATE TRIGGER ON SvcView INSTEAD OF INSERT AS
              FOR EACH ROW
              BEGIN ATOMIC
                INSERT INTO DQ_2012.customer (cust_id,cust_name,cust_address) VALUES (NEW.cust_id, NEW.cust_name, NEW.cust_address);
                INSERT INTO aakash_test.order (item,price) VALUES (NEW.item, NEW.price);
              END
    
            CREATE TRIGGER ON SvcView INSTEAD OF UPDATE AS
               FOR EACH ROW
               IF (CHANGING.cust_name)
               BEGIN ATOMIC
                 UPDATE DQ_2012.customer set cust_name = NEW.cust_name, cust_address = NEW.cust_address WHERE cust_id=OLD.cust_id;                 
               END
               IF (CHANGING.item)
               BEGIN ATOMIC
                 UPDATE aakash_test.order set item = NEW.item, price = NEW.price WHERE cust_id = OLD.cust_id;                 
               END 
               
            CREATE TRIGGER ON SvcView INSTEAD OF DELETE AS
               FOR EACH ROW
               BEGIN ATOMIC
                 DELETE FROM DQ_2012.customer WHERE cust_id = OLD.cust_id;
                 DELETE FROM aakash_test.order WHERE cust_id = OLD.cust_id;
               END                                                
              ]]>
  </metadata>
  </model>
  <model visible="true" type="PHYSICAL" name="Test2">
     <source name="sqlTest2" translator-name="mysql5" connection-jndi-name="java:/testmysql" />
  </model>
  <model visible="true" type="PHYSICAL" name="Test1">
     <source name="sqlTest" translator-name="sqlserver"  connection-jndi-name="java:/testsqlserver" />
  </model>
</vdb>

               

               

              You can read more about these at Update Procedures - Teiid 9.0 (draft) - Project Documentation Editor

               

              HTH

               

              Ramesh..

              1 of 1 people found this helpful
                • Actions
              • 4. Re: Insert data into view in jboss data vertualization.
                nish18
                nish18 Dec 16, 2015 7:27 AM (in response to rareddy)

                Thankyou Ramesh!! The way you have suggested is working for my problem no. 2, but problem no.1 is not resolving.

                 

                 

                I am using below Query to insert into the view:

                 

                insert into SvcView(cust_name,custAddress,cust_id,item,price)values('testCustName101','testCustAdd101',101,'TestItem101',42432)

                 

                 

                I am getting the below exception:

                 

                 

                17:41:16,871 WARN  [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue6) Connector worker process failed for atomic-request=WyVYfODFKIuH.0.2.3: org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@488b1a7b[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@526713d connection handles=0 lastUse=1450267876869 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@883d939 pool internal context=SemaphoreArrayListManagedConnectionPool@56fabeaa[pool=testmysql] xaResource=LocalXAResourceImpl@67fbb782[connectionListener=488b1a7b connectionManager=3ecced23 warned=false currentXid=null productName=MySQL productVersion=5.0.67-community-nt jndiName=java:/testmysql] txSync=null]

                  at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:285)

                  at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:57)

                  at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:194) [teiid-api-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:289) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:135) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:369) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:568) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:68) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:360) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:287) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:261) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.proc.ForEachRowPlan.nextBatch(ForEachRowPlan.java:148) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:136) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:159) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:141) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:444) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:326) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:254) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.7.1.redhat-8.jar:8.7.1.redhat-8]

                  at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [rt.jar:1.7.0_55]

                  at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [rt.jar:1.7.0_55]

                  at java.lang.Thread.run(Unknown Source) [rt.jar:1.7.0_55]

                Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@488b1a7b[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@526713d connection handles=0 lastUse=1450267876869 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@883d939 pool internal context=SemaphoreArrayListManagedConnectionPool@56fabeaa[pool=testmysql] xaResource=LocalXAResourceImpl@67fbb782[connectionListener=488b1a7b connectionManager=3ecced23 warned=false currentXid=null productName=MySQL productVersion=5.0.67-community-nt jndiName=java:/testmysql] txSync=null]

                  at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:151)

                  at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:281)

                  ... 41 more

                Caused by: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@488b1a7b[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@526713d connection handles=0 lastUse=1450267876869 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@883d939 pool internal context=SemaphoreArrayListManagedConnectionPool@56fabeaa[pool=testmysql] xaResource=LocalXAResourceImpl@67fbb782[connectionListener=488b1a7b connectionManager=3ecced23 warned=false currentXid=null productName=MySQL productVersion=5.0.67-community-nt jndiName=java:/testmysql] txSync=null]

                  at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:631) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]

                  at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:494) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]

                  at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:143)

                  ... 42 more

                Caused by: javax.resource.ResourceException: IJ000461: Could not enlist in transaction on entering meta-aware object

                  at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:490) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]

                  at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:626) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]

                  ... 44 more

                Caused by: javax.transaction.SystemException: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff0a1e7f4c:-52e6606c:56715397:21 status: ActionStatus.ABORT_ONLY >

                  at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:669) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]

                  at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:368) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]

                  at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:483) [ironjacamar-core-impl-1.0.28.Final-redhat-1.jar:1.0.28.Final-redhat-1]

                  ... 45 more

                 

                 

                Please help me out to resolve this  problem.

                  • Actions
                • 5. Re: Insert data into view in jboss data vertualization.
                  rareddy
                  rareddy Dec 16, 2015 9:12 AM (in response to nish18)

                  Nishant,

                   

                  Since there are two data sources involved to wrap a transaction around the both the data sources, the Teiid engine is trying wrap transaction around both sources. The options you have are

                  1. Create a "XA" data sources instead of the "local" data sources. Check the "docs/teiid/datasources" directory for respective data source on how to create a XA connection. Then replace the local ones with xa based connections.
                  2. On your JDBC url connection to the Teiid, you can supply "autoCommitTxn=OFF" , that will turn off transaction wrapping. But this is not recommended, as there is potential for loss of data in failed transaction.

                   

                  There are few threads on this subject in this forum, search for full details.

                   

                  Ramesh..

                  1 of 1 people found this helpful
                    • Actions
                  • 6. Re: Insert data into view in jboss data vertualization.
                    nish18
                    nish18 Dec 17, 2015 7:59 AM (in response to rareddy)

                    Hi Ramesh,

                     

                    Thanks for your feedback.

                     

                    I have tried the above options but it does not work I have replaced my local data source connection with XA cannection as shown below:

                     

                    Previous:

                    <datasources>

                    <datasource jndi-name="java:/mysql-ds" pool-name="mysqlDS">

                            <driver-class>com.mysql.jdbc.Driver</driver-class>

                            <connection-url>jdbc:mysql://XXXX:3306/aakash_test</connection-url>

                            <driver>test_mysql</driver>

                            <pool>

                                <prefill>false</prefill>

                                <use-strict-min>false</use-strict-min>

                                <flush-strategy>FailingConnectionOnly</flush-strategy>

                            </pool>

                            <security>

                                <user-name>root</user-name>

                                <password>root</password>

                            </security>

                        </datasource>

                    </datasources>

                    <drivers>

                    <driver name="test_mysql" module="com.mysql">

                                            <driver-class>com.mysql.jdbc.Driver</driver-class>

                                            <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                    </driver>

                    </drivers>

                     

                    This is working fine But When i am replacing the above code with below mentioned code it does not work.

                     

                    Currently:

                     

                    <datasources>

                      <xa-datasource jndi-name="java:/mysql-ds" pool-name="mysqlDS" enabled="true" use-java-context="true" use-ccm="true">

                      

                            <xa-datasource-property name="PortNumber">3306</xa-datasource-property>

                            <xa-datasource-property name="DatabaseName">aakash_test</xa-datasource-property>

                            <xa-datasource-property name="ServerName">XXXX</xa-datasource-property>      

                          

                            <driver>test_mysql</driver>

                          

                            <!--

                            <new-connection-sql>select 1/new-connection-sql>

                            <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                             -->

                                   

                            <xa-pool>

                                <min-pool-size>10</min-pool-size>

                                <max-pool-size>20</max-pool-size>

                                <is-same-rm-override>true</is-same-rm-override>

                                <prefill>false</prefill>

                                <use-strict-min>false</use-strict-min>

                                <flush-strategy>FailingConnectionOnly</flush-strategy>

                                <no-tx-separate-pools/>

                            </xa-pool>

                     

                     

                            <security>

                                <user-name>root</user-name>

                                <password>>root</password>

                                <!--

                                <security-domain>mysecurity-realm</security-domain>

                                -->

                            </security>

                           

                            <!--

                            <validation>

                                <check-valid-connection-sql>select 1</check-valid-connection-sql>

                            </validation>

                             -->

                        </xa-datasource>

                     

                    <drivers>

                    <driver name="test_mysql" module="com.mysql">

                                            <driver-class>com.mysql.jdbc.Driver</driver-class>

                                            <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                    </driver>

                    </drivers>

                     

                    I am getting below error

                    Error:

                     

                    18:23:57,071 ERROR [org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl] (Periodic Recovery)  IJ000906: Error during crash recovery: java:/mysql-ds (IJ031084: Unable to

                    create connection): javax.resource.ResourceException: IJ031084: Unable to create connection

                            at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:496)

                            at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1.run(XAManagedConnectionFactory.java:392)

                            at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1.run(XAManagedConnectionFactory.java:389)

                            at java.security.AccessController.doPrivileged(Native Method)

                            at javax.security.auth.Subject.doAs(Subject.java:415)

                            at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManagedConnectionFactory.java:388)

                            at org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl.open(XAResourceRecoveryImpl.java:355)

                            at org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl.getXAResources(XAResourceRecoveryImpl.java:193)

                            at com.arjuna.ats.internal.jbossatx.jta.XAResourceRecoveryHelperWrapper.getXAResources(XAResourceRecoveryHelperWrapper.java:51)

                            at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.resourceInitiatedRecoveryForRecoveryHelpers(XARecoveryModule.java:510)

                            at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:176)

                            at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:747)

                            at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:375)

                    Caused by: java.sql.SQLException: Access denied for user 'root'@'10.30.127.76' (using password: YES)

                            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)

                            at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)

                            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)

                            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)

                            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919)

                            at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3996)

                            at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1284)

                            at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2142)

                            at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:781)

                            at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)

                            at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

                            at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

                            at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

                            at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

                            at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

                            at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)

                            at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:284)

                            at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:439)

                            at com.mysql.jdbc.jdbc2.optional.MysqlDataSource.getConnection(MysqlDataSource.java:137)

                            at com.mysql.jdbc.jdbc2.optional.MysqlXADataSource.getXAConnection(MysqlXADataSource.java:58)

                            at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:479)

                            ... 12 more

                     

                     

                    Please can you suggest me any solution for that.

                      • Actions
                    • 7. Re: Insert data into view in jboss data vertualization.
                      rareddy
                      rareddy Dec 17, 2015 9:16 AM (in response to nish18)

                      Nishant,

                       

                      The error seems to happen during a recovery, not during the startup, that means you may have executed once. I think you need to check mysql instance for any errors. I can not spot any errors in above cnfiguration.

                       

                      Ramesh..

                        • Actions
                      • 8. Re: Insert data into view in jboss data vertualization.
                        nish18
                        nish18 Dec 18, 2015 4:55 AM (in response to rareddy)

                        Ramesh ,

                         

                        The above error has been resolved but i am facing another problem while i am going to insert data into view which was created using two different datasources(mysql and sqlserver):

                         

                        Following are my configuration files:

                         

                        standalone-teiid.xml:

                         

                         

                        <xa-datasource jndi-name="java:/mysqlXA" pool-name="mysqlXA" enabled="true" use-ccm="true" statistics-enabled="false">

                                            <xa-datasource-property name="PortNumber">

                                                3306

                                            </xa-datasource-property>

                                            <xa-datasource-property name="ServerName">

                                                xxxx

                                            </xa-datasource-property>

                                            <xa-datasource-property name="DatabaseName">

                                                aakash_test

                                            </xa-datasource-property>

                                            <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                                            <driver>test_mysql</driver>

                                            <xa-pool>

                                                <is-same-rm-override>false</is-same-rm-override>

                                                <interleaving>false</interleaving>

                                                <pad-xid>false</pad-xid>

                                                <wrap-xa-resource>false</wrap-xa-resource>

                                            </xa-pool>

                                            <security>

                                                <user-name>root</user-name>

                                                <password>root</password>

                                            </security>

                                            <validation>

                                                <validate-on-match>false</validate-on-match>

                                                <background-validation>false</background-validation>

                                            </validation>

                                            <timeout>

                                                <set-tx-query-timeout>false</set-tx-query-timeout>

                                                <blocking-timeout-millis>0</blocking-timeout-millis>

                                                <idle-timeout-minutes>0</idle-timeout-minutes>

                                                <query-timeout>0</query-timeout>

                                                <use-try-lock>0</use-try-lock>

                                                <allocation-retry>0</allocation-retry>

                                                <allocation-retry-wait-millis>0</allocation-retry-wait-millis>

                                                <xa-resource-timeout>0</xa-resource-timeout>

                                            </timeout>

                                            <statement>

                                                <share-prepared-statements>false</share-prepared-statements>

                                            </statement>

                                        </xa-datasource>

                         

                         

                                        <xa-datasource jndi-name="java:/sqlserverDS" pool-name="sqlserverDS" enabled="true" use-java-context="true" use-ccm="true">

                                            <xa-datasource-property name="PortNumber">

                                                1433

                                            </xa-datasource-property>

                                            <xa-datasource-property name="DatabaseName">

                                                xxxxx

                                            </xa-datasource-property>

                                            <xa-datasource-property name="ServerName">

                                              xxxxx

                                            </xa-datasource-property>

                                            <xa-datasource-property name="Instance">

                                                xxxxx

                                            </xa-datasource-property>

                                            <xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>

                                            <driver>sqlserver</driver>

                                            <xa-pool>

                                                <min-pool-size>10</min-pool-size>

                                                <max-pool-size>20</max-pool-size>

                                                <prefill>false</prefill>

                                                <use-strict-min>false</use-strict-min>

                                                <flush-strategy>FailingConnectionOnly</flush-strategy>

                                                <is-same-rm-override>true</is-same-rm-override>

                                                <no-tx-separate-pools>true</no-tx-separate-pools>

                                            </xa-pool>

                                            <security>

                                                <user-name>root</user-name>

                                                <password>root</password>

                                            </security>

                                        </xa-datasource>

                         

                                    <drivers>

                                             <driver name="test_mysql" module="com.mysql">

                                                <driver-class>com.mysql.jdbc.Driver</driver-class>

                                                <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                                            </driver>

                                            <driver name="sqlserver" module="com.sqlserver">

                                                <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>

                                                <xa-datasource-class>net.sourceforge.jtds.jdbcx.JtdsDataSource</xa-datasource-class>

                                            </driver>

                                        </drivers>

                         

                         

                        mysql-xa-vdb.xml :

                         

                        <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

                        <vdb name="EmployeeVDBXA" version="1">

                            <description>A Dynamic VDB</description>

                            <property name="UseConnectorMetadata" value="false"/>

                        <model name="join" type="VIRTUAL">

                                <metadata type="DDL"><![CDATA[

                                

                                   

                        CREATE VIEW SvcView (CustName string,CustAddress string, cust_id long,item string,amount long) OPTIONS (UPDATABLE true)AS

                        SELECT CustName,CustAddress, cust_id,item,amount

                        FROM "DQ_2012.dbo.Customer"  INNER JOIN "aakash_test.order" ON CustId = cust_id;

                            

                        CREATE TRIGGER ON SvcView INSTEAD OF INSERT AS 

                                      FOR EACH ROW 

                                      BEGIN ATOMIC 

                                        INSERT INTO DQ_2012.dbo.Customer (CustName,CustAddress) VALUES (NEW.CustName, NEW.CustAddress); 

                                        INSERT INTO aakash_test.order (cust_id,item,amount) VALUES (NEW.cust_id,NEW.item,NEW.amount); 

                        END 

                                ]]>

                                </metadata>

                            </model>

                          <model visible="true" type="PHYSICAL" name="mysqlModel">

                                <source name="sqlTest2" translator-name="mysql5" connection-jndi-name="java:/mysqlXA"/>

                            </model>

                          <model visible="true" type="PHYSICAL" name="sqlServerModel">

                                <source name="sqlTest" translator-name="sqlserver" connection-jndi-name="java:/sqlserverDS"/>

                            </model>

                           </vdb>       

                         

                         

                        Insert query:

                        select * from SvcView;

                         

                        Result :

                        Connie,706 Clyde Station,1,Item1,23120

                        Jackie,1353 Middleton Crescent,2,Item2,342342

                        Jeannie,814 Long Run,3,Item3,23423423

                         

                         

                        But when i am using below insert query it does not work:

                         

                        insert into SvcView(CustName,CustAddress, cust_id,item,amount)values('testCust101','testAdd101',101,'testItem101',34234)

                         

                        I am getting below error:

                         

                        org.teiid.jdbc.TeiidSQLException: Error Code:TEIID30504 Message:Remote org.teiid.core.TeiidProcessingException: TEIID30504 sqlTest2: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@324

                        54e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0 lastReturned=1450432017412 lastValidated=1450431

                        523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed634e4 mcp=SemaphoreArrayListManagedConnectionPool@

                        481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]

                                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:113)

                                at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:70)

                                at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:624)

                                at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:72)

                                at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:539)

                                at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:130)

                                at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:37)

                                at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:75)

                                at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:220)

                                at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:255)

                                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

                                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                                at java.lang.reflect.Method.invoke(Method.java:606)

                                at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:110)

                                at com.sun.proxy.$Proxy1.read(Unknown Source)

                                at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:349)

                                at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:548)

                                at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:347)

                                at JDBCClientInsert.execute(JDBCClientInsert.java:74)

                                at JDBCClientInsert.main(JDBCClientInsert.java:42)

                        Caused by: [TeiidProcessingException] TEIID30504: Error Code:TEIID30504 Message:Remote org.teiid.core.TeiidProcessingException: TEIID30504 sqlTest2: TEIID11009 java.sql.

                        SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnec

                        tionListener@32454e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0 lastReturned=1450432017412 lastV

                        alidated=1450431523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed634e4 mcp=SemaphoreArrayListManage

                        dConnectionPool@481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]

                        1 [TeiidException] TEIID11009: Error Code:TEIID11009 Message:Remote org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceEx

                        ception: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@32454e63[state=DESTROYED m

                        anaged connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0 lastReturned=1450432017412 lastValidated=1450431523940 lastCheckedOut=1

                        450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed634e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=mysqlXA]

                        xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]

                        2 [SQLException]Remote java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.con

                        nectionmanager.listener.TxConnectionListener@32454e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0

                        lastReturned=1450432017412 lastValidated=1450431523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed63

                        4e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]

                        3 [TeiidRuntimeException]IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0

                        :ffff0a1e7f4c:-2aee09e6:5673d41a:34 status: ActionStatus.ABORT_ONLY >

                                at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401)

                                at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161)

                                at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:374)

                                at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

                                at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)

                                at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

                                at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

                                at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69)

                                at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70)

                                at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84)

                                at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92)

                                at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:604)

                                at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:69)

                                at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:385)

                                at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:294)

                                at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:268)

                                at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

                                at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

                                at org.teiid.query.processor.proc.ForEachRowPlan.nextBatch(ForEachRowPlan.java:147)

                                at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118)

                                at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

                                at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)

                                at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

                                at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)

                                at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

                                at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

                                at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

                                at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

                                at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:470)

                                at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)

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

                                at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)

                                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

                                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

                                at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

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

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

                        Caused by: [TeiidException] TEIID11009: Error Code:TEIID11009 Message:Remote org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.R

                        esourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@32454e63[state=DE

                        STROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0 lastReturned=1450432017412 lastValidated=1450431523940 lastChe

                        ckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed634e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=

                        mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]

                        1 [SQLException]Remote java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.con

                        nectionmanager.listener.TxConnectionListener@32454e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b connection handles=0

                        lastReturned=1450432017412 lastValidated=1450431523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@5ed63

                        4e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=null]

                        2 [TeiidRuntimeException]IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0

                        :ffff0a1e7f4c:-2aee09e6:5673d41a:34 status: ActionStatus.ABORT_ONLY >

                                at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:271)

                                at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:68)

                                at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:194)

                                at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:313)

                                at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                                at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

                                at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                                at java.lang.reflect.Method.invoke(Method.java:606)

                                at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)

                                at com.sun.proxy.$Proxy56.execute(Unknown Source)

                                at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)

                                at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:142)

                                ... 36 more

                        Caused by: java.sql.SQLException: Remote java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.

                        jboss.jca.core.connectionmanager.listener.TxConnectionListener@32454e63[state=DESTROYED managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b con

                        nection handles=0 lastReturned=1450432017412 lastValidated=1450431523940 lastCheckedOut=1450432156963 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.stra

                        tegy.OnePool@5ed634e4 mcp=SemaphoreArrayListManagedConnectionPool@481818f0[pool=mysqlXA] xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3375f4b txSync=nul

                        l]

                                at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:146)

                                at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:67)

                                at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:267)

                                ... 47 more

                        Caused by: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffff0a1e7f4c:

                        -2aee09e6:5673d41a:34 status: ActionStatus.ABORT_ONLY >

                                at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:970)

                                at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:412)

                                at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:554)

                                at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:922)

                                at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:740)

                                at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)

                                ... 49 more

                        -------------------------------

                          • Actions
                        • 9. Re: Insert data into view in jboss data vertualization.
                          rareddy
                          rareddy Dec 18, 2015 10:09 AM (in response to nish18)

                          Nishant,

                           

                          As I mentioned believe there is an issue with your MySQL instance. All I see the transaction manager is unable to participate in transaction. Are your tables created in InnoDB engine to participate in XA? see http://dev.mysql.com/doc/refman/5.7/en/xa.html

                           

                          Ramesh..

                            • Actions
                          • 10. Re: Insert data into view in jboss data vertualization.
                            nish18
                            nish18 Dec 21, 2015 6:56 AM (in response to rareddy)

                            Ramesh.

                             

                            I have checked with other mysql instance also but still I am getting the same error.So I have taken another approach here,

                             

                            When I change the order of insertion in procedure,then I am bale to insert data in view successfully.

                             

                            CREATE TRIGGER ON SvcView INSTEAD OF INSERT AS

                                          FOR EACH ROW

                                          BEGIN ATOMIC

                             

                                  INSERT INTO aakash_test.order (cust_id,item,amount) VALUES (NEW.cust_id,NEW.item,NEW.amount);           //insertion in child table

                                  INSERT INTO DQ_2012.dbo.Customer (CustName,CustAddress) VALUES (NEW.CustName, NEW.CustAddress); //insertion in parent table

                                END

                             

                            But this is not the correct way because "Customer" is the parent table in SQL Server and "order" is the child table in mySQL data source therefore Insertion should happen in "Customer" table first then in "order" table.I am unable to identify the reason why it is happening .

                             

                            Nishant.

                              • Actions
                            • 11. Re: Insert data into view in jboss data vertualization.
                              rareddy
                              rareddy Dec 22, 2015 9:51 AM (in response to nish18)

                              So are they created with InnoDB engine? You can do describe on the tables and find out.

                               

                              not sure what is going on with change of statement order, it may be last optimization feature kicking in.

                               

                              Ramesh..

                                • Actions
                              • 12. Re: Insert data into view in jboss data vertualization.
                                nish18
                                nish18 Dec 23, 2015 1:29 AM (in response to rareddy)

                                Yes tables are created with InnoDB engine.Should I change the engine?

                                  • Actions
                                • 13. Re: Insert data into view in jboss data vertualization.
                                  shawkins
                                  shawkins Dec 23, 2015 10:39 AM (in response to nish18)

                                  > it may be last optimization feature kicking in.

                                   

                                  If this is run against the configuration with 2 xa sources, then that shouldn't apply.

                                   

                                  > Yes tables are created with InnoDB engine.Should I change the engine?

                                   

                                  InnoDB should work.  SQL Server may need some additional steps to enable XA transactions:

                                   

                                  https://msdn.microsoft.com/en-us/library/aa342335(v=sql.110).aspx

                                    • Actions
                                  • 14. Re: Insert data into view in jboss data vertualization.
                                    nish18
                                    nish18 Dec 24, 2015 12:59 AM (in response to shawkins)

                                    Steven,

                                     

                                    Thanks for your feedback.

                                    I am facing problem with mySQL instance ,there is no issue i had seen for sqlServer.

                                     

                                    I have tried two Scenarios:

                                     

                                    Scenario1:

                                     

                                    I have parent table as "Customer" in mySQL and child table as "Order" in sqlserver.I am creating a view using both these tables and able insert data in view successfully .

                                     

                                    Scenario2:

                                     

                                    I have parent table as "Customer" in sqlServer and child table as "Order" in mySQL. I am creating a view using both these tables but  unable to insert data into view.

                                     

                                    My confusion is that if there is a mySQL instance problem than How Scenario1 is working fine and Scenario2 is getting failed with the same configuration.

                                      • Actions
                                    1 2 Previous Next