[MDEV-26778] row_start is not updated in current row for InnoDB Created: 2021-10-06  Updated: 2022-10-26  Resolved: 2022-01-13

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.6.4, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.3.33, 10.4.23, 10.5.14, 10.6.6, 10.7.2, 10.8.1

Type: Bug Priority: Major
Reporter: Sridhar Sadasivam Assignee: Aleksey Midenkov
Resolution: Fixed Votes: 2
Labels: None

Issue Links:
PartOf
includes MDEV-26783 System-versioned table writes histori... Closed
Relates
relates to MDEV-452 Add full support for auto-initialized... Closed
relates to MDEV-23446 UPDATE does not insert history row if... Closed

 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.



 Comments   
Comment by Alice Sherepa [ 2021-10-07 ]

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)

Comment by Alice Sherepa [ 2021-10-07 ]

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)

Comment by Aleksey Midenkov [ 2021-12-27 ]

Please review bb-10.3-midenok

Comment by Nikita Malyavin [ 2022-01-13 ]

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.

Generated at Thu Feb 08 09:47:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.