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

UPDATE does not insert history row if the row is not changed

    XMLWordPrintable

Details

    Description

      UPDATE queries that do not change any values in the rows they match on versioned tables do not insert historical rows.

      Example:

      CREATE TABLE test (a INT) WITH SYSTEM VERSIONING;
      		 
      INSERT INTO test (a) VALUES (1);
      		 
      UPDATE test SET a = 1 WHERE a = 1;
      		 
      SELECT *, row_start, row_end FROM test FOR SYSTEM_TIME ALL;

      Disclaimer: I don't know whether this is a bug or was purposefully designed like this. In the latter case, I want to make a case to reconsider it:

      • This behavior contrasts with triggers: UPDATE queries that do not change any values in the rows they match still carry out registered triggers for each row.
      • Now some UPDATEs cannot be logged historically. Versioned table fail to capture that they happened. Yet logging such queries and the timestamps at which they were issued can still be valuable information.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24522 Assertion `inited==NONE' fails upon UPDATE on versioned table with unique blob

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25644 UPDATE not working properly on transaction precise system versioned table

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31944 UPDATE creates new row in system-versioned tables even if there is no value to change

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-32124 System-Versioned Tables, extra rows with UPDATE

          • Major - Major loss of function.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23644 Assertion on evaluating foreign referential action for self-reference in system versioned table

          • Major - Major loss of function.
          • Closed
          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17089 Updating a System Versioned Table always causes a row to be updated, regardless if the data is the same or not

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24451 Record retains new value on DML when history row insert fails

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-16226 TRX_ID-based System Versioning refactoring

          • Major - Major loss of function.
          • Stalled

          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-23100 ODKU of non-versioning column inserts history row

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-24064 Utility columns for system-versioned tables

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26778 row_start is not updated in current row for InnoDB

          • Major - Major loss of function.
          • Closed

          Activity

            People

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