Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.3.9
-
None
-
Windows 10 64Bit
Description
Good Morning,
If you run an update on a non-system versioned table, and the column(s) you update contain the same data, then no rows are updated. However, if you add system versioning to a table, then an update will always update the rows selected by the WHERE clause; regardless of if the values were updated or not.
This has the side effect of unnecessarily increasing the size of the history, as a new history row is created, despite there being no change. I am not certain if this is intended behaviour or not, so I believe as a minimum this should be documented (as SV tables behave differently to normal tables) - however ideally this would be changed, or a configuration option added to control if an no change UPDATE causes a new history row or not.
Test Script
/* Setup */
|
CREATE TABLE `bugTest`.`nsvTable` ( |
`i` INT UNSIGNED NOT NULL, |
PRIMARY KEY (`i`) |
) ENGINE = INNODB;
|
|
|
INSERT INTO `nsvTable` (`i`) VALUES(1); |
|
|
CREATE TABLE `bugTest`.`svTable` ( |
`i` INT UNSIGNED NOT NULL, |
PRIMARY KEY (`i`) |
) ENGINE = INNODB WITH SYSTEM VERSIONING; |
|
|
INSERT INTO `svTable` (`i`) VALUES(1); |
|
|
/* Test Updates */
|
UPDATE `nsvTable` SET `i` = 1 WHERE `i` = 1; |
/* 0 row(s) affected */
|
|
|
UPDATE `svTable` SET `i` = 1 WHERE `i` = 1; |
/* 1 row(s) affected */
|
|
|
/* Cleanup */
|
DROP TABLE `nsvTable`, `svTable`; |
Attachments
Issue Links
- causes
-
-
- Closed
-
- relates to
-
MDEV-16226 TRX_ID-based System Versioning refactoring
-
- Stalled
-
-
-
- Closed
-
Activity
This requires additional UPDATE on storage side. Currently it works like this:
1. update user and system fields;
2. detect if record is changed;
3. insert history row.
Because at p.1 we update row_start, we get always changed record at p.2.
So, we have to:
1. update user fields;
2. detect if record is changed;
3. if p.2 is true update system fields and insert history row.
The last approach have drawback of additional storage call for updating the system fields. So for the sake of artificial case of false UPDATE we must make slower any versioned UPDATE. The real numbers will highly depend on the underlying storage engine, but I guess for most of the cases the slowdown should not be critical. The performance should be compared before and after the patch.
There is another approach of ignoring the system fields at storage layer when comparing records. This is simpler, faster and less bug-prone solution. Though it's engine-dependent.
alice so this can be closed now along with https://github.com/MariaDB/server/pull/864 ?
danblack, I am not 100%sure, I wanted to find the commit which fixed it, but it was merge and then I gave up (if I remember correct)
I think this shouldn't have been done, and that history should always got a record when a value is updated, even if it's updated to its own value. Now we've got MDEV-23446
Please, reopen as a feature request configurable via session variable. An acknowledgement is needed that this feature is still required. Use case f.ex. is duplicate form posts from a browser.
I think it was previously discussed and said to be intentional, but I'm not 100% sure and can't find it in JIRA right away, so I'm leaving it to versioning experts to decide on it.