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

Record retains new value on DML when history row insert fails

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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17891 Assertion failures in select_insert::abort_result_set and mysql_load upon attempt to replace into a full table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22540 ER_DUP_ENTRY upon REPLACE or Assertion `transactional_table || !changed || thd->transaction.stmt.modified_non_trans_table' failed

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23446 UPDATE does not insert history row if the row is not changed

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            midenok Aleksey Midenkov created issue -
            midenok Aleksey Midenkov made changes -
            Field Original Value New Value
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            midenok Aleksey Midenkov made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            midenok Aleksey Midenkov made changes -
            Component/s Versioned Tables [ 14303 ]
            midenok Aleksey Midenkov made changes -
            Labels system_versioned_tables
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            midenok Aleksey Midenkov made changes -
            Description h3. Reproduce
            {code:sql}
            --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);
            insert into t2 values (1, 0), (2, 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 (1, 3), (2, 3);
            --error ER_DUP_ENTRY
            replace into t2 values (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;
            {code}             		h3. Reproduce
            {code:sql}
            --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);
            insert into t2 values (1, 0), (2, 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 (1, 3), (2, 3);
            --error ER_DUP_ENTRY
            replace into t2 values (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;
            {code}

            h3. Result
            t2 changed value of x to 3, row_start to 2.

            {code:sql}
            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
            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
            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
            {code}

            h3. Expected
            t2 retains same values as t1.

            {code:sql}
            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
            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
            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
            {code}
            midenok Aleksey Midenkov made changes -
            Description h3. Reproduce
            {code:sql}
            --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);
            insert into t2 values (1, 0), (2, 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 (1, 3), (2, 3);
            --error ER_DUP_ENTRY
            replace into t2 values (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;
            {code}

            h3. Result
            t2 changed value of x to 3, row_start to 2.

            {code:sql}
            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
            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
            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
            {code}

            h3. Expected
            t2 retains same values as t1.

            {code:sql}
            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
            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
            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
            {code}             		h3. Reproduce
            {code:sql}
            --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;
            {code}

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

            {code:sql}
            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
            {code}

            h3. Expected
            t2 retains pk=1 record values.

            {code:sql}
            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
            {code}

            h3. 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.
            midenok Aleksey Midenkov made changes -
            Attachment MDEV-17891.patch [ 55415 ]
            midenok Aleksey Midenkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 117241 ] MariaDB v4 [ 142445 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.4 [ 22408 ]
            midenok Aleksey Midenkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            midenok Aleksey Midenkov added a comment -

            Theoretically it is possible to revert back all the previously replaced records, even the deleted ones, but practically I don't see the point. It is the nature of non-transactional table to not recover the state in case of error and REPLACE does not recover it for other kinds of errors. Since there is no watchers/voters in 4 years I'm closing the bug. It should be reopened as feature in case this behaviour is found useful.

            midenok Aleksey Midenkov added a comment - Theoretically it is possible to revert back all the previously replaced records, even the deleted ones, but practically I don't see the point. It is the nature of non-transactional table to not recover the state in case of error and REPLACE does not recover it for other kinds of errors. Since there is no watchers/voters in 4 years I'm closing the bug. It should be reopened as feature in case this behaviour is found useful.
            midenok Aleksey Midenkov made changes -
            Attachment MDEV-24451.patch [ 74904 ]
            midenok Aleksey Midenkov made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Won't Fix [ 2 ]
            Status In Progress [ 3 ] Closed [ 6 ]

            People

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