Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6945

SET STATEMENT autocommit=... has no effect and causes warnings in the error log

    XMLWordPrintable

Details

    Description

      Note: I couldn't find a test so I don't know whether it should work. If not, probably the attempt should cause a warning (not the one described below, but a normal one, "not supported" or something).

      Results of the test case below

      create table t1 (i int) engine=InnoDB;
      set autocommit = 1;
      # Insert '1' with autocommit enabled
      insert into t1 values (1);
      connect  con1,localhost,root,,;
      set session transaction isolation level read committed;
      # Make sure the value '1' is visible right away
      select * from t1;
      i
      1
      connection default;
      # Disable autocommit for inserting the value '2'
      set statement autocommit=0 for insert into t1 values (2);
      connection con1;
      # The value '2' should not be visible
      select * from t1;
      i
      1
      2
      # ... but it is
      connection default;
      # Disable autocommit in general
      set autocommit = 0;
      # Insert '3' with autocommit disabled
      insert into t1 values (3);
      connection con1;
      # Make sure the value '3' is not visible 
      select * from t1;
      i
      1
      2
      connection default;
      rollback;
      # Enable autocommit for inserting the value '4'
      set statement autocommit=1 for insert into t1 values (4);
      connection con1;
      # The value '4' should be visible
      select * from t1;
      i
      1
      2
      # ... but it is not.
      disconnect con1;

      The part SET STATEMENT autocommit=1 FOR .. also causes warnings in the error log:

      [Warning] MySQL is closing a connection that has an active InnoDB transaction.  1 row modifications will roll back.

      Test case

      --enable_connect_log
       
      --source include/have_innodb.inc
       
      create table t1 (i int) engine=InnoDB;
      set autocommit = 1;
       
      --echo # Insert '1' with autocommit enabled
      insert into t1 values (1);
       
      --connect (con1,localhost,root,,)
      set session transaction isolation level read committed;
       
      --echo # Make sure the value '1' is visible right away
      select * from t1;
       
      --connection default
       
      --echo # Disable autocommit for inserting the value '2'
      set statement autocommit=0 for insert into t1 values (2);
       
      --connection con1
       
      --echo # The value '2' should not be visible
      select * from t1;
       
      --echo # ... but it is
       
      --connection default
       
      --echo # Disable autocommit in general
      set autocommit = 0;
       
      --echo # Insert '3' with autocommit disabled
      insert into t1 values (3);
       
      --connection con1
       
      --echo # Make sure the value '3' is not visible 
      select * from t1;
       
      --connection default
      rollback;
       
      --echo # Enable autocommit for inserting the value '4'
      set statement autocommit=1 for insert into t1 values (4);
       
      --connection con1
       
      --echo # The value '4' should be visible
      select * from t1;
       
      --echo # ... but it is not.
       
      # Cleanup
      --disconnect con1

      It's all the same in Percona server.

      Attachments

        Issue Links

          Activity

            People

              sanja Oleksandr Byelkin
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.