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

Atomic block is ignored when working with execute immediate command

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 10.2
    • Fix Version/s: 11.1, 10.3.4, 11.0.2
    • Component/s: Query Engine
    • Labels:
      None
    • Environment:

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

    • Steps to Reproduce:
      Hide

      1. Create in MySQL the test_a table by the following script:

      CREATE TABLE `test`.`test_a` (
        `a` int(11) DEFAULT NULL,
        `b` int(11) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (1,1);
      INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (1,2);
      INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (2,1);
      INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (2,2);
      INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (3,2);
      INSERT INTO `test`.`test_a` (`a`,`b`) VALUES (3,10);
      

      2. Add MySQL database configuration in standalone-teiid.xml:

      <datasource jndi-name="java:/test" pool-name="test" enabled="true" use-java-context="true">
                          <connection-url>jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull</connection-url>
                          <driver>mysql</driver>
                          <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
                          <pool>
                              <min-pool-size>2</min-pool-size>
                              <max-pool-size>70</max-pool-size>
                          </pool>
                          <security>
                              <user-name>root</user-name>
                              <password>xxxxxx</password>
                          </security>
                          <validation>
                              <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                              <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
                          </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 configured in previous step as datasource:

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

      using "myLobs" as following:

         <translator name="mylobs" type="mysql5">
              <property name="CopyLobs" value="true" />
              <property name="SupportsNativeQueries" value="true"/>
          </translator>
      

      4. Add the following virtual procedure in test-vdb.xml:

          <model visible = "true" type = "VIRTUAL" name = "test_upd">
              <metadata type = "DDL"><![CDATA[
                create virtual procedure upd() as
                begin
      	    execute immediate 'begin update test.test_a t set t.b = -1 where t.a = 3 and t.b = 10; error ''Test error''; end';
                end
              ]]>
              </metadata>
          </model>
      

      5. Running the following query we get an expected result - changed rows will be rolled back because of the thrown test exception:

      begin atomic
      update test.test_a t
      set t.b = -1
      where t.a = 3 and t.b = 10;
      error 'Test error';
      end ;;
      

      but putting the same script in the test_upd.upd() virtual procedure and running it we get a unexpected result - changed rows won't be rolled back:

      begin atomic
      call test_upd.upd();
      end ;;
      
      Show
      1. Create in MySQL the test_a table by the following script: CREATE TABLE `test`.`test_a` ( ` a ` int (11) DEFAULT NULL , `b` int (11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (1,1); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (1,2); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (2,1); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (2,2); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (3,2); INSERT INTO `test`.`test_a` (` a `,`b`) VALUES (3,10); 2. Add MySQL database configuration in standalone-teiid.xml: <datasource jndi-name= "java:/test" pool-name= "test" enabled= "true" use-java-context= "true" > <connection-url> jdbc:mysql://localhost:3306/test?zeroDateTimeBehavior=convertToNull </connection-url> <driver> mysql </driver> <new-connection-sql> set SESSION sql_mode = 'ANSI' </new-connection-sql> <pool> <min-pool-size> 2 </min-pool-size> <max-pool-size> 70 </max-pool-size> </pool> <security> <user-name> root </user-name> <password> xxxxxx </password> </security> <validation> <valid-connection-checker class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker" /> <exception-sorter class-name= "org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter" /> </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 configured in previous step as datasource: <model name= "test" > <property name= "importer.useFullSchemaName" value= "false" /> <property name= "importer.tableTypes" value= "TABLE,VIEW" /> <property name= "importer.importKeys" value= "false" /> <source name= "test" translator-name= "mylobs" connection-jndi-name= "java:/test" /> </model> using "myLobs" as following: <translator name= "mylobs" type= "mysql5" > <property name= "CopyLobs" value= "true" /> <property name= "SupportsNativeQueries" value= "true" /> </translator> 4. Add the following virtual procedure in test-vdb.xml: <model visible = "true" type = "VIRTUAL" name = "test_upd" > <metadata type = "DDL" > <![CDATA[ create virtual procedure upd() as begin execute immediate 'begin update test.test_a t set t.b = -1 where t.a = 3 and t.b = 10; error ' 'Test error' '; end' ; end ]]> </metadata> </model> 5. Running the following query we get an expected result - changed rows will be rolled back because of the thrown test exception: begin atomic update test.test_a t set t.b = -1 where t. a = 3 and t.b = 10; error 'Test error' ; end ;; but putting the same script in the test_upd.upd() virtual procedure and running it we get a unexpected result - changed rows won't be rolled back: begin atomic call test_upd.upd(); end ;;

      Description

      When calling in atomic block a proc which is throwing an exception after updating some rows these changed rows won't be rolled back. That is running the following query:

      begin atomic
      call test_upd.upd();
      end ;;
      

      all changes done in the test_upd.upd virtual procedure won't be rolled back in case of a thrown exception there. If I'm not mistaken such behavior was introduced in scope of TEIID-4504 issue (after introducing the Program.instructionsRequireTransaction method).

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                dalex005 Dmitrii Pogorelov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: