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

Record retains new value on DML when history row insert fails

    XMLWordPrintable

    Details

      Description

      Reproduce

      --source include/have_innodb.inc
       
      set timestamp= 1;
      create table t1 (pk int primary key, x int) engine=innodb with system versioning;
      create table t2 (pk int primary key, x int) engine=myisam with system versioning;
      insert into t1 values (1, 0), (2, 0), (3, 0);
      insert into t2 values (1, 0), (2, 0), (3, 0);
       
      --connect (con1,localhost,root,,test)
      set timestamp= 2;
      replace into t1 values (1, 1), (2, 1);
      replace into t2 values (1, 1), (2, 1);
       
      --connection default
      replace into t1 values (1, 2), (2, 2);
      replace into t2 values (1, 2), (2, 2);
       
      --connection con1
      --error ER_DUP_ENTRY
      replace into t1 values (3, 3), (1, 3), (2, 3);
      --error ER_DUP_ENTRY
      replace into t2 values (3, 3), (1, 3), (2, 3);
      select *, row_start, row_end from t1 for system_time all order by row_end, pk;
      select *, row_start, row_end from t2 for system_time all order by row_end, pk;
       
      # cleanup
      drop tables t1, t2;
      

      Result

      t2 changed pk=1 record to x=3, row_start=1.

      select *, row_start, row_end from t1 for system_time all order by row_end, pk;
      pk      x       row_start       row_end
      1       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      2       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      1       2       1970-01-01 03:00:01.000000      2038-01-19 06:14:07.999999
      2       2       1970-01-01 03:00:01.000000      2038-01-19 06:14:07.999999
      3       0       1970-01-01 03:00:01.000000      2038-01-19 06:14:07.999999
      select *, row_start, row_end from t2 for system_time all order by row_end, pk;
      pk      x       row_start       row_end
      1       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      2       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      3       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      1       3       1970-01-01 03:00:02.000000      2038-01-19 06:14:07.999999
      2       2       1970-01-01 03:00:01.000000      2038-01-19 06:14:07.999999
      3       3       1970-01-01 03:00:02.000000      2038-01-19 06:14:07.999999
      

      Expected

      t2 retains pk=1 record values.

      select *, row_start, row_end from t1 for system_time all order by row_end, pk;
      pk      x       row_start       row_end
      1       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      2       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      1       2       1970-01-01 03:00:01.000000      2038-01-19 06:14:07.999999
      2       2       1970-01-01 03:00:01.000000      2038-01-19 06:14:07.999999
      3       0       1970-01-01 03:00:01.000000      2038-01-19 06:14:07.999999
      select *, row_start, row_end from t2 for system_time all order by row_end, pk;
      pk      x       row_start       row_end
      1       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      2       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      3       0       1970-01-01 03:00:01.000000      1970-01-01 03:00:02.000000
      1       2       1970-01-01 03:00:01.000000      2038-01-19 06:14:07.999999
      2       2       1970-01-01 03:00:01.000000      2038-01-19 06:14:07.999999
      3       3       1970-01-01 03:00:02.000000      2038-01-19 06:14:07.999999
      

      Notes

      Applicable to non-transactional tables only since transactional tables rollback failed command by design. Note that we cannot rollback successfully processed rows, but this is ok since we have history rows for them and the behaviour is identical to non-versioned table.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              midenok Aleksey Midenkov
              Reporter:
              midenok Aleksey Midenkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.