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

Errored-out CREATE-SELECT does not binlog results of any function modifying non-transactional table

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.4.34
    • None
    • Replication
    • None

    Description

      In either ROW and STATEMENT binlog format a failing at *commit* as

      --error ER_GTID_STRICT_OUT_OF_ORDER
      create table t_x (a int)  select f(/* function modifies a non-transaction table */) as a;
      

      CREATE-TABLE-SELECT query is not binlogged at all even when a function may have left
      side effects such as updates to non-transactional tables.

      How to reproduce

      There are two types of the error occurring either in the commit time or and the select time.
      The first find in the section 4.B of the following tests from MDEV-35506

      # Tests of commit time failures.
      # At committing of an auto-commit statement a failure to commit in its
      # binlog branch should rollback at least a transactional part of the statement.
      #
      # References:
      # MDEV-35506
       
      source include/have_innodb.inc;
      source include/have_log_bin.inc;
       
      set @@session.gtid_domain_id=1;
      set @save_gtid_stric_mode=@@global.gtid_strict_mode;
       
      create table ta (a int) engine=aria;
      create table ti (a int) engine=innodb;
      create table ti_pk (a int primary key) engine=innodb;
      create table t  (a int) engine=innodb;
      delimiter |;
      create function f_i()
      returns integer
      begin
        insert into ti set a=1;
      return 1;
      end |
      create function f_ia(arg int)
      returns integer
      begin
        insert into ti_pk set a=1;
        insert into ta set a=1;
        insert into ti_pk set a=arg;
        return 1;
      end |
      delimiter ;|
       
      call mtr.add_suppression("Error writing file");
       
      # Naturally all empty now
      select count(*) as zero from t;
      select count(*) as zero from ta;
      select count(*) as zero from ti;
       
      # Force manual value assignement to gtid::seq_no while in the strict mode
      # so that the value is rejected. Despite the errorred out statement
      # being at its commit phase it will eventually be rolled back.
      # Side effects of non-transactional engines, like Aria, are displayed.
      --echo # 1. simple Innodb test
      set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1;
      set @@global.gtid_strict_mode=1;
      # mask possible allowed seq_no shift
      --replace_regex /GTID 1-1-[0-9]+/GTID VALUE/
      --error ER_GTID_STRICT_OUT_OF_ORDER
      insert into t set a=1;
       
      --echo # observe effective rollback
      select count(*) as zero from t;
       
      --echo # 2. simple Aira test
      set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1;
      set @@global.gtid_strict_mode=1;
      --replace_regex /GTID 1-1-[0-9]+/GTID VALUE/
      --error ER_GTID_STRICT_OUT_OF_ORDER
      insert into ta values (1),(2);
       
      --echo # note no rollback
      select count(*) as '*NON-zero*' from ta;
      # local cleanup
      delete from ta;
       
      --echo # 3. multi-engine test
      # A. non-transactional top-level
      set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1;
      set @@global.gtid_strict_mode=1;
      --replace_regex /GTID 1-1-[0-9]+/GTID VALUE/
      --error ER_GTID_STRICT_OUT_OF_ORDER
      insert into ta set a=f_i();
      --echo # note no rollback..
      select count(*) as one from ta;
      --echo # ..incl transactional engine
      select count(*) as one from ti;
      delete from ti;
      delete from ta;
       
      # B. non-transactional in the leaf
      set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1;
      set @@global.gtid_strict_mode=1;
      --replace_regex /GTID 1-1-[0-9]+/GTID VALUE/
      --error ER_GTID_STRICT_OUT_OF_ORDER
      insert into t set a=f_ia(0);
       
      --echo # note no rollback..
      select count(*) as one from ta;
      --echo # ..incl transactional engine
      select count(*) as one from t;
      select count(*) as one from ti;
      delete from ti;
      delete from ta;
       
      --echo # 4. create-table-select-f()
      --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
      --let $binlog_start = query_get_value(SHOW MASTER STATUS, Position, 1)
      # 4.A. two phase commit branch
      set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1;
      set @@global.gtid_strict_mode=1;
      --replace_regex /GTID 1-1-[0-9]+/GTID VALUE/
      --error ER_GTID_STRICT_OUT_OF_ORDER
      create table f_x (a int) select f_i() as a;
      --echo # rollback indeed takes place in the pure transactional case
      select count(*) as zero from ti;
       
      # *** MDEV-36027 ***
       
      # # 4.B. one phase commit branch
      --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
      --let $binlog_start = query_get_value(SHOW MASTER STATUS, Position, 1)
      set @@global.gtid_strict_mode=0; set @@session.gtid_seq_no=1;
      set @@global.gtid_strict_mode=1;
      --replace_regex /GTID 1-1-[0-9]+/GTID VALUE/
      --error ER_GTID_STRICT_OUT_OF_ORDER
      create table t_x (a int) engine=aria select f_ia(0) as a;
      --error ER_NO_SUCH_TABLE
      select * from t_x;
       
      --echo # bug: in EITHER binlog_format no logging for create-table-select took place
      --source include/show_binlog_events.inc
      --echo # .. but non-transactional `ta` (and `t_x` sic!) are modified
      select count(*) as one from ta;
      select count(*) as zero from ti;
       
      delete from ta;
      --echo #.
       
      # cleanup
       
      set  @@global.gtid_strict_mode=@save_gtid_stric_mode;
      drop function f_i;
      drop function f_ia;
      drop table t, ta, ti, ti_pk;
      
      

      The 2nd error type takes place with STATEMENT binlog format when an error occurs with SELECT's execution, like a duplicate entry error is met inside the function here

      --error ER_DUP_ENTRY
      set statement binlog_format = STATEMENT for create table t_y (a int) engine=aria select f_ia(1 /* err */) as a;
      

      see the whole test in the # MDEV-36027 section of rpl_create_select_row.test of MDEV-35207 fixes.
      In the ROW format case the errored-in-select CREATE-SELECT is logged correctly which suggests
      a *workaround*.

      How to fix

      It is expected that when the non-transactional ta got affected its updates must be
      present in binlog.
      E.g for the ROW binlog format a non-transactonal group of events could be still logged
      headed with an automatic GTID value.
      In the STATEMENT format case, it seems an Incident event has to be logged.

      Attachments

        Issue Links

          Activity

            There are no comments yet on this issue.

            People

              Unassigned Unassigned
              Elkin Andrei Elkin
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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