Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.6.4, 10.3(EOL), 10.4(EOL), 10.5, 10.6
-
None
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
- includes
-
MDEV-26783 System-versioned table writes historical rows on update without any changed data
- Closed
- relates to
-
MDEV-452 Add full support for auto-initialized/updated timestamp and datetime
- Closed
-
MDEV-23446 UPDATE does not insert history row if the row is not changed
- Closed
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)