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

Atomic block is ignored when working with execute immediate command

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 11.1, 10.3.4, 11.0.2
    • 10.2
    • Query Engine
    • None
    • 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 ;;

      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).

              rhn-engineering-shawkins Steven Hawkins
              dalex005 Dmitrii Pogorelov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: