[MDEV-17089] Updating a System Versioned Table always causes a row to be updated, regardless if the data is the same or not Created: 2018-08-29  Updated: 2020-12-25  Resolved: 2020-10-19

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update, Versioned Tables
Affects Version/s: 10.3.9
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Robert Humphries Assignee: Aleksey Midenkov
Resolution: Won't Fix Votes: 1
Labels: None
Environment:

Windows 10 64Bit

Issue Links:
Problem/Incident
causes MDEV-23446 UPDATE does not insert history row if... Closed
Relates
relates to MDEV-16226 TRX_ID-based System Versioning refact... Stalled
relates to MDEV-23100 ODKU of non-versioning column inserts... Closed

 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`;



 Comments   
Comment by Elena Stepanova [ 2018-08-29 ]

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.

Comment by Aleksey Midenkov [ 2018-09-10 ]

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.

Comment by Aleksey Midenkov [ 2018-09-12 ]

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.

Comment by Alice Sherepa [ 2020-07-06 ]

not reproducible after 10.3.18

Comment by Daniel Black [ 2020-07-29 ]

alice so this can be closed now along with https://github.com/MariaDB/server/pull/864 ?

Comment by Alice Sherepa [ 2020-07-29 ]

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)

Comment by Sergei Golubchik [ 2020-09-10 ]

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

Comment by Aleksey Midenkov [ 2020-10-19 ]

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.

Generated at Thu Feb 08 08:33:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.