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

row_start is not updated in current row for InnoDB

Details

    Description

      After upgrading to MariaDB 10.6.4, we noticed an issue with system versioned tables where it inserts a new row for update commands that don't change anything. Previous versions such as 10.5.5 would ignore such dummy updates and not change the SVT history. Small test case to illustrate:

      • CREATE TABLE t(
        x INT,
        start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
        end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
        PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
        ) WITH SYSTEM VERSIONING;
      • "INSERT INTO t( x ) VALUES (1);"
      • "UPDATE t SET x = 1 WHERE x = 1;" # no change, dummy update

      With MariaDB 10.5.5, the above prints:
      Rows matched: 1 Changed: 0 Inserted: 0 Warnings: 0

      With MariaDB 10.6.4:
      Rows matched: 1 Changed: 0 Inserted: 1 Warnings: 0

      • "SELECT * FROM t for SYSTEM_TIME between (NOW() - INTERVAL 1 YEAR) AND NOW();"

      The above will print 2 records (with same start_time but different end_time) for MariaDB 10.6.4 and only 1 record for MariaDB 10.5.5

      Looks like the 10.6 version stores dummy updates which don't make any real change to the data. While that's okay, shouldn't the start time stamp also be updated for the new row? The current behavior is problematic because queries such as "SELECT * FROM t FOR SYSTEM_TIME AS OF ..." will print 2 records for certain timestamps which doesn't make sense.

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            Thank you for the report!
            Currently on MariaDB 10.3-10.6 update inserts a history row even if the data is the same (MDEV-23446)
            But it is supposed to update start_time, it works with Myisam, but not with InnoDB:

            MariaDB [test]> CREATE TABLE t(
                -> x INT,
                -> start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
                -> end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
                -> PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
                -> ) engine=innodb WITH SYSTEM VERSIONING;
            Query OK, 0 rows affected (0.042 sec)
             
            MariaDB [test]> INSERT INTO t( x ) VALUES (1);
            Query OK, 1 row affected (0.008 sec)
             
            MariaDB [test]> UPDATE t SET x = 1 WHERE x = 1;
            Query OK, 0 rows affected (0.006 sec)
            Rows matched: 1  Changed: 0  Inserted: 1  Warnings: 0
             
            MariaDB [test]> SELECT * FROM t for SYSTEM_TIME between (NOW() - INTERVAL 1 YEAR) AND NOW();
            +------+----------------------------+----------------------------+
            | x    | start_timestamp            | end_timestamp              |
            +------+----------------------------+----------------------------+
            |    1 | 2021-10-07 12:19:34.470303 | 2038-01-19 04:14:07.999999 |
            |    1 | 2021-10-07 12:19:34.470303 | 2021-10-07 12:19:34.480074 |
            +------+----------------------------+----------------------------+
            2 rows in set (0.002 sec)
             
            MariaDB [test]> UPDATE t SET x = 1 WHERE x = 1;
            Query OK, 0 rows affected (0.006 sec)
            Rows matched: 1  Changed: 0  Inserted: 1  Warnings: 0
             
            MariaDB [test]> SELECT * FROM t for SYSTEM_TIME between (NOW() - INTERVAL 1 YEAR) AND NOW();
            +------+----------------------------+----------------------------+
            | x    | start_timestamp            | end_timestamp              |
            +------+----------------------------+----------------------------+
            |    1 | 2021-10-07 12:19:34.470303 | 2038-01-19 04:14:07.999999 |
            |    1 | 2021-10-07 12:19:34.470303 | 2021-10-07 12:19:34.480074 |
            |    1 | 2021-10-07 12:19:34.470303 | 2021-10-07 12:19:39.752182 |
            +------+----------------------------+----------------------------+
            3 rows in set (0.003 sec)
             
            MariaDB [test]> CREATE or replace TABLE t(
                -> x INT,
                -> start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
                -> end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
                -> PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
                -> ) engine=myisam WITH SYSTEM VERSIONING;
            Query OK, 0 rows affected (0.041 sec)
             
            MariaDB [test]> INSERT INTO t( x ) VALUES (1);
            Query OK, 1 row affected (0.004 sec)
             
            MariaDB [test]> UPDATE t SET x = 1 WHERE x = 1;
            Query OK, 1 row affected (0.002 sec)
            Rows matched: 1  Changed: 1  Inserted: 1  Warnings: 0
             
            MariaDB [test]> 
            MariaDB [test]> SELECT * FROM t for SYSTEM_TIME between (NOW() - INTERVAL 1 YEAR) AND NOW();
            +------+----------------------------+----------------------------+
            | x    | start_timestamp            | end_timestamp              |
            +------+----------------------------+----------------------------+
            |    1 | 2021-10-07 12:20:02.429311 | 2038-01-19 04:14:07.999999 |
            |    1 | 2021-10-07 12:20:02.424017 | 2021-10-07 12:20:02.429311 |
            +------+----------------------------+----------------------------+
            2 rows in set (0.003 sec)
            

            alice Alice Sherepa added a comment - Thank you for the report! Currently on MariaDB 10.3-10.6 update inserts a history row even if the data is the same ( MDEV-23446 ) But it is supposed to update start_time, it works with Myisam, but not with InnoDB: MariaDB [test]> CREATE TABLE t( -> x INT, -> start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START, -> end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END, -> PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp) -> ) engine=innodb WITH SYSTEM VERSIONING; Query OK, 0 rows affected (0.042 sec)   MariaDB [test]> INSERT INTO t( x ) VALUES (1); Query OK, 1 row affected (0.008 sec)   MariaDB [test]> UPDATE t SET x = 1 WHERE x = 1; Query OK, 0 rows affected (0.006 sec) Rows matched: 1 Changed: 0 Inserted: 1 Warnings: 0   MariaDB [test]> SELECT * FROM t for SYSTEM_TIME between (NOW() - INTERVAL 1 YEAR) AND NOW(); +------+----------------------------+----------------------------+ | x | start_timestamp | end_timestamp | +------+----------------------------+----------------------------+ | 1 | 2021-10-07 12:19:34.470303 | 2038-01-19 04:14:07.999999 | | 1 | 2021-10-07 12:19:34.470303 | 2021-10-07 12:19:34.480074 | +------+----------------------------+----------------------------+ 2 rows in set (0.002 sec)   MariaDB [test]> UPDATE t SET x = 1 WHERE x = 1; Query OK, 0 rows affected (0.006 sec) Rows matched: 1 Changed: 0 Inserted: 1 Warnings: 0   MariaDB [test]> SELECT * FROM t for SYSTEM_TIME between (NOW() - INTERVAL 1 YEAR) AND NOW(); +------+----------------------------+----------------------------+ | x | start_timestamp | end_timestamp | +------+----------------------------+----------------------------+ | 1 | 2021-10-07 12:19:34.470303 | 2038-01-19 04:14:07.999999 | | 1 | 2021-10-07 12:19:34.470303 | 2021-10-07 12:19:34.480074 | | 1 | 2021-10-07 12:19:34.470303 | 2021-10-07 12:19:39.752182 | +------+----------------------------+----------------------------+ 3 rows in set (0.003 sec)   MariaDB [test]> CREATE or replace TABLE t( -> x INT, -> start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START, -> end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END, -> PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp) -> ) engine=myisam WITH SYSTEM VERSIONING; Query OK, 0 rows affected (0.041 sec)   MariaDB [test]> INSERT INTO t( x ) VALUES (1); Query OK, 1 row affected (0.004 sec)   MariaDB [test]> UPDATE t SET x = 1 WHERE x = 1; Query OK, 1 row affected (0.002 sec) Rows matched: 1 Changed: 1 Inserted: 1 Warnings: 0   MariaDB [test]> MariaDB [test]> SELECT * FROM t for SYSTEM_TIME between (NOW() - INTERVAL 1 YEAR) AND NOW(); +------+----------------------------+----------------------------+ | x | start_timestamp | end_timestamp | +------+----------------------------+----------------------------+ | 1 | 2021-10-07 12:20:02.429311 | 2038-01-19 04:14:07.999999 | | 1 | 2021-10-07 12:20:02.424017 | 2021-10-07 12:20:02.429311 | +------+----------------------------+----------------------------+ 2 rows in set (0.003 sec)
            alice Alice Sherepa added a comment -

            test case from MDEV-26783, where row_start is the same and it is not dependent on the engine:

            CREATE TABLE t1 ( id INT, ts datetime ON UPDATE current_timestamp()) WITH SYSTEM versioning;
            INSERT INTO t1 VALUES (1,now());
            UPDATE t1 SET id = 1 WHERE id = 1;
             
            SELECT *, ROW_START, ROW_END FROM t1 FOR SYSTEM_TIME ALL;
            

            MariaDB [test]> SELECT *, ROW_START, ROW_END FROM t1 FOR SYSTEM_TIME ALL;
            +------+---------------------+----------------------------+----------------------------+
            | id   | ts                  | ROW_START                  | ROW_END                    |
            +------+---------------------+----------------------------+----------------------------+
            |    1 | 2021-10-07 17:48:25 | 2021-10-07 17:48:25.313059 | 2038-01-19 04:14:07.999999 |
            |    1 | 2021-10-07 17:48:25 | 2021-10-07 17:48:25.313059 | 2021-10-07 17:48:25.319952 |
            |    1 | 2021-10-07 17:48:25 | 2021-10-07 17:48:25.313059 | 2021-10-07 17:48:27.908130 |
            +------+---------------------+----------------------------+----------------------------+
            3 rows in set (0.001 sec)
            

            alice Alice Sherepa added a comment - test case from MDEV-26783 , where row_start is the same and it is not dependent on the engine: CREATE TABLE t1 ( id INT , ts datetime ON UPDATE current_timestamp ()) WITH SYSTEM versioning; INSERT INTO t1 VALUES (1,now()); UPDATE t1 SET id = 1 WHERE id = 1;   SELECT *, ROW_START, ROW_END FROM t1 FOR SYSTEM_TIME ALL ; MariaDB [test]> SELECT *, ROW_START, ROW_END FROM t1 FOR SYSTEM_TIME ALL; +------+---------------------+----------------------------+----------------------------+ | id | ts | ROW_START | ROW_END | +------+---------------------+----------------------------+----------------------------+ | 1 | 2021-10-07 17:48:25 | 2021-10-07 17:48:25.313059 | 2038-01-19 04:14:07.999999 | | 1 | 2021-10-07 17:48:25 | 2021-10-07 17:48:25.313059 | 2021-10-07 17:48:25.319952 | | 1 | 2021-10-07 17:48:25 | 2021-10-07 17:48:25.313059 | 2021-10-07 17:48:27.908130 | +------+---------------------+----------------------------+----------------------------+ 3 rows in set (0.001 sec)

            Please review bb-10.3-midenok

            midenok Aleksey Midenkov added a comment - Please review bb-10.3-midenok

            The commit 5a70165ab8a97 is approved.

            sridhu88 row_start was remaining outdated in one corner case, this will be fixed with the commit above.

            However, missing history row problem wasn't found during validation by alice. Currently, we suppose a correct behavior to always insert a history row, even if the data wasn't modified (i don't remember whether the standard controls it). So one can report it separately, if they will experience it.

            nikitamalyavin Nikita Malyavin added a comment - The commit 5a70165ab8a97 is approved. sridhu88 row_start was remaining outdated in one corner case, this will be fixed with the commit above. However, missing history row problem wasn't found during validation by alice . Currently, we suppose a correct behavior to always insert a history row, even if the data wasn't modified (i don't remember whether the standard controls it). So one can report it separately, if they will experience it.

            People

              midenok Aleksey Midenkov
              sridhu88 Sridhar Sadasivam
              Votes:
              2 Vote for this issue
              Watchers:
              8 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.