Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5200

INSERT INTO query fails in BEGIN ATOMIC block if data is obtained from atomic procedure which catches some exception

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Critical
    • Resolution: Done
    • Affects Version/s: 10.0.1
    • Fix Version/s: 10.1, 10.0.2, 9.3.7
    • Component/s: Query Engine
    • Labels:
      None
    • Environment:

      teiid-10.0.1 on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)

    • Steps to Reproduce:
      Hide

      1. In postgresql create the batch_test table by the following script:

      create table batch_test (a varchar);
      

      2. Add postgresql database configuration in standalone-teiid.xml

                      <datasource jndi-name="java:/test_pg" pool-name="test_pg" enabled="true" use-java-context="true">
                          <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url>
                          <driver-class>org.postgresql.Driver</driver-class>
                          <driver>org.postgresql</driver>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                              <prefill>false</prefill>
                              <use-strict-min>false</use-strict-min>
                              <flush-strategy>FailingConnectionOnly</flush-strategy>
                          </pool>
                          <security>
                              <user-name>postgres</user-name>
                              <password>xxxxxx</password>
                          </security>
                          <validation>
                              <check-valid-connection-sql>select 0</check-valid-connection-sql>
                          </validation>
                          <timeout>
                              <blocking-timeout-millis>120000</blocking-timeout-millis>
                              <idle-timeout-minutes>5</idle-timeout-minutes>
                          </timeout>
                      </datasource>
      

      3. Add in test-vdb.xml java:/test_pg configured in previous step as datasource:

          <model name="test_pg">
              <property name="importer.useFullSchemaName" value="false"/>
      	<property name="importer.tableTypes" value="TABLE,VIEW"/>
      	<property name="importer.importKeys" value="false"/>
              <source name="test_pg" translator-name="myPg" connection-jndi-name="java:/test_pg"/>
          </model>
      

      4. Configure in the test-vdb.xml the following virtual procs:

          <model visible = "true" type = "VIRTUAL" name = "procs">
              <metadata type = "DDL"><![CDATA[
                create procedure px1() returns (a string) as
                begin atomic
                  begin
                    error 'aaaa';
                  end
                  exception e
                  select 'bbbbb';
                end;
                create procedure px2() returns (a string) as
                begin atomic
                  select 'bbbbb';
                end
              ]]>
              </metadata>
          </model>
      

      5. The following queries fail:

      -- fails with IJ000460: Error checking for a transaction
      begin 
        loop on (select s.a as a from (call procs.px1()) as s) as x 
        begin 
          insert into test_pg.batch_test (a) values (x.a); 
        end 
      end;
      -- fails with IJ000460: Error checking for a transaction
      begin atomic 
        loop on (select s.a as a from (call procs.px1()) as s) as x 
        begin 
          insert into test_pg.batch_test (a) values (x.a); 
        end 
      end;
      

      6. But the following queries work:

      begin 
        execute immediate 'begin loop on (select s.a as a from (call procs.px1()) as s) as x begin insert into test_pg.batch_test (a) values (x.a); end end'; 
      end;
       
      begin 
        loop on (select s.a as a from (call procs.px2()) as s) as x 
        begin 
          insert into test_pg.batch_test (a) values (x.a); 
        end 
      end;
      

      Show
      1. In postgresql create the batch_test table by the following script: create table batch_test (a varchar ); 2. Add postgresql database configuration in standalone-teiid.xml < datasource jndi-name = "java:/test_pg" pool-name = "test_pg" enabled = "true" use-java-context = "true" > < connection -url>jdbc:postgresql://localhost:5432/test?charSet=utf8</ connection -url> < driver -class>org.postgresql.Driver</ driver -class> < driver >org.postgresql</ driver > < pool > < min -pool-size>2</ min -pool-size> < max -pool-size>70</ max -pool-size> < prefill >false</ prefill > < use -strict-min>false</ use -strict-min> < flush -strategy>FailingConnectionOnly</ flush -strategy> </ pool > < security > < user -name>postgres</ user -name> < password >xxxxxx</ password > </ security > < validation > < check -valid-connection-sql>select 0</ check -valid-connection-sql> </ validation > < timeout > < blocking -timeout-millis>120000</ blocking -timeout-millis> < idle -timeout-minutes>5</ idle -timeout-minutes> </ timeout > </ datasource > 3. Add in test-vdb.xml java:/test_pg configured in previous step as datasource: <model name = "test_pg" > <property name = "importer.useFullSchemaName" value= "false" /> <property name = "importer.tableTypes" value= "TABLE,VIEW" /> <property name = "importer.importKeys" value= "false" /> <source name = "test_pg" translator- name = "myPg" connection -jndi- name = "java:/test_pg" /> </model> 4. Configure in the test-vdb.xml the following virtual procs: < model visible = "true" type = "VIRTUAL" name = "procs" > < metadata type = "DDL" > <![CDATA[ create procedure px1() returns (a string) as begin atomic begin error 'aaaa'; end exception e select 'bbbbb'; end; create procedure px2() returns (a string) as begin atomic select 'bbbbb'; end ]]> </ metadata > </ model > 5. The following queries fail: -- fails with IJ000460: Error checking for a transaction begin loop on ( select s.a as a from (call procs.px1()) as s) as x begin insert into test_pg.batch_test (a) values (x.a); end end ; -- fails with IJ000460: Error checking for a transaction begin atomic loop on ( select s.a as a from (call procs.px1()) as s) as x begin insert into test_pg.batch_test (a) values (x.a); end end ; 6. But the following queries work: begin execute immediate 'begin loop on (select s.a as a from (call procs.px1()) as s) as x begin insert into test_pg.batch_test (a) values (x.a); end end' ; end ;   begin loop on ( select s.a as a from (call procs.px2()) as s) as x begin insert into test_pg.batch_test (a) values (x.a); end end ;
    • Workaround:
      Workaround Exists
    • Workaround Description:
      Hide

      Use "set autoCommitTxn off" to disable the command level transaction.

      Show
      Use "set autoCommitTxn off" to disable the command level transaction.

      Description

      Running the query:

      begin atomic 
        loop on (select s.a as a from (call procs.px1()) as s) as x 
        begin 
          insert into test_pg.batch_test (a) values (x.a); 
        end 
      end;
      

      leads to the following error in stacktrace:

      2018-01-04 16:58:34,494 WARN  [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue75) AIlKbjKNf5Ju TEIID30020 Processing exception for request AIlKbjKNf5Ju.28 'TEIID30504 test_pg: TEII
      D11009 java.sql.SQLException: javax.resource.ResourceException: IJ000460: Error checking for a transaction'. Originally TeiidProcessingException 'IJ000459: Transaction is not active:
       tx=Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffac100003:3cbb95f1:5a4e46c6:b3 status: ActionStatus.ABORT_ONLY >, owner=Local transaction context for provide
      r JBoss JTA transaction provider)' TxConnectionManagerImpl.java:409. Enable more detailed logging to see the entire stacktrace.
      2018-01-04 16:58:39,134 WARN  [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue76) AIlKbjKNf5Ju Connector worker process failed for atomic-request=AIlKbjKNf5Ju.29.3.26: org.teiid.tr
      anslator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000460: Error checking for a transaction
              at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:278)
              at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:65)
              at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:201)
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:322)
              at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
              at java.lang.reflect.Method.invoke(Method.java:498)
              at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:216)
              at com.sun.proxy.$Proxy36.execute(Unknown Source)
              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
              at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
              at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:398)
              at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
              at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
              at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
              at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
              at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:65)
              at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:66)
              at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:80)
              at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:88)
              at org.teiid.query.processor.proc.ProcedurePlan.executePlan(ProcedurePlan.java:611)
              at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:65)
              at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:388)
              at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:297)
              at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:269)
              at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
              at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
              at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
              at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
              at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:480)
              at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:350)
              at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
              at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:276)
              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:280)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
              at java.lang.Thread.run(Thread.java:745)
      Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000460: Error checking for a transaction
              at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:146)
              at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:64)
              at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:274)
              ... 38 more
      Caused by: javax.resource.ResourceException: IJ000460: Error checking for a transaction
              at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:425)
              at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:789)
              at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)
              ... 40 more
      Caused by: javax.resource.ResourceException: IJ000459: Transaction is not active: tx=Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffac100003:3cbb95f1:5a4e46c6:
      b6 status: ActionStatus.ABORT_ONLY >, owner=Local transaction context for provider JBoss JTA transaction provider)
              at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:409)
              ... 42 more
       
      2018-01-04 16:58:39,137 WARN  [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue76) AIlKbjKNf5Ju TEIID30020 Processing exception for request AIlKbjKNf5Ju.29 'TEIID30504 test_pg: TEII
      D11009 java.sql.SQLException: javax.resource.ResourceException: IJ000460: Error checking for a transaction'. Originally TeiidProcessingException 'IJ000459: Transaction is not active:
       tx=Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffac100003:3cbb95f1:5a4e46c6:b6 status: ActionStatus.ABORT_ONLY >, owner=Local transaction context for provide
      r JBoss JTA transaction provider)' TxConnectionManagerImpl.java:409. Enable more detailed logging to see the entire stacktrace.
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 dalex dalex
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: