owned this note
owned this note
Published
Linked with GitHub
# MariaDB Versioning - without versioning - Feets in the hole.
###### tags: `kyc-right`
- Related article: MariaDB Temporal Table https://hackmd.io/Iwe0MVlwRpOMZ70W1ZwPtQ
---
[TOC]
## Description
With versioned table (timestamp based) including one non-versioned column, using upsert (insert … on duplicate update) to update the unversioning column will still create new version records, which shouldn't.
- upstream tracking number https://jira.mariadb.org/browse/MDEV-23100
## Environment
Docker image from docker hub
> Server version: 10.4.12-MariaDB-1:10.4.12+maria~bionic
## Reproduce
### Create versioned table
Create in 3 different ways, inclusive, exclusive, and set by each column.
```sql=
CREATE TABLE t1 (
id INT auto_increment primary key,
x INT unique,
y INT WITHOUT SYSTEM VERSIONING
)WITH SYSTEM VERSIONING;
CREATE TABLE t2 (
id INT auto_increment primary key,
x INT unique WITH SYSTEM VERSIONING,
y INT
);
CREATE TABLE t3 (
id INT auto_increment primary key,
x INT unique WITH SYSTEM VERSIONING,
y INT WITHOUT SYSTEM VERSIONING
);
```
### Upsert
Upsert with `insert on duplicate key update`. The 2nd and 3rd SQL query for each table are doing UPDATE to non-versioned column.
```sql
insert into t1 (x, y) values ('1', '123')
on duplicate key update x = values(x), y = values(y);
insert into t1 (x, y) values ('1', '1234')
on duplicate key update x = values(x), y = values(y);
insert into t1 (x, y) values ('1', '12345')
on duplicate key update x = values(x), y = values(y);
insert into t2 (x, y) values ('1', '123')
on duplicate key update x = values(x), y = values(y);
insert into t2 (x, y) values ('1', '1234')
on duplicate key update x = values(x), y = values(y);
insert into t2 (x, y) values ('1', '12345')
on duplicate key update x = values(x), y = values(y);
insert into t3 (x, y) values ('1', '123')
on duplicate key update x = values(x), y = values(y);
insert into t3 (x, y) values ('1', '1234')
on duplicate key update x = values(x), y = values(y);
insert into t3 (x, y) values ('1', '12345')
on duplicate key update x = values(x), y = values(y);
```
#### Output
There should be only 1 version, but we see 3 instead.
```sql=
select *, ROW_START, ROW_END from t1 for system_time all;
select *, ROW_START, ROW_END from t2 for system_time all;
select *, ROW_START, ROW_END from t3 for system_time all;
```
![](https://i.imgur.com/Tsl95of.png)
### Simple UPDATE works fine
```sql=
update t1 set y = '123456' where x = 1;
select *, ROW_START, ROW_END from t1 for system_time all;
```
![](https://i.imgur.com/uPe4lWJ.png)
> With `on duplicate key update`,
> even we only update the column `WITHOUT VERSIONING`,
> MariaDB still create new version record for the row.
### Replace?
```sql=
replace into t1 (x, y) values ('1', '123456'); -- try this few times
```
![](https://i.imgur.com/u44RWBX.png)
- same result as upsert
- tried setting `id` to `WITHOUT SYSTEM VERSIONING` makes no difference
- removing auto-increment id makes no difference
### trxid-based versioning
#### Does not support partition
> https://jira.mariadb.org/browse/MDEV-15951
> That's because "partition" storage engine doesn't say that it supports trxid-based versioning.
I think it should support it at least for PARTITION BY RANGE and other not BY SYSTEM_TIME partitioning.
#### Testing
```sql=
CREATE TABLE t1 (
id INT auto_increment primary key,
x INT unique,
y INT,
z timestamp without system versioning,
create_at timestamp default CURRENT_TIMESTAMP(),
start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
)WITH SYSTEM VERSIONING;
insert into t1 (x, y, z) values ('1', '1', '2020-07-02 07:36:00'), ('2', '2', '2020-07-02 07:36:00'), ('3', '3', '2020-07-02 07:36:00')
on duplicate key update y = values(y), z = values(z);
insert into t1 (x, y, z) values ('2', '3', '2020-07-04 07:36:00'), ('3', '4', '2020-07-04 07:36:00'), ('4', '4', '2020-07-04 07:36:00'), ('5', '5', '2020-07-04 07:36:00')
on duplicate key update y = values(y), z = values(z);
insert into t1 (x, y, z) values ('2', '4', '2020-07-06 07:36:00'), ('3', '5', '2020-07-06 07:36:00'), ('4', '5', '2020-07-06 07:36:00'), ('5', '5', '2020-07-06 07:36:00')
on duplicate key update y = values(y), z = values(z);
delete from t1 where z < '2020-07-04 07:36:00';
select * from t1 for system_time all;
-- # last update z: 2020-07-03 07:36:00, current z: 2020-07-06 07:36:00
-- # delete: 取得所有最新資料(包含刪除)後,last_show_at 大於等於上次更新時間且 max_trx 不為最大值者
select *, MAX(end_trxid) as max_trx from t1 for system_time all group by id;
select * from
(select *, MAX(end_trxid) as max_trx from t1 for system_time all group by id) as latest
where latest.z >= '2020-07-02 07:36:00' and latest.max_trx < 18446744073709551615;
-- # update: end_trx = MAX and z = 這次時間
select * from t1 where id in (select *, MAX(z) from t1 group by id);
select * from t1 for system_time all as max_2
where max_2.z < (select MAX(z) from t1 for system_time all as max_1 where max_2.id = max_1.id group by id)
-- # 找出有第二大的 z 值的紀錄
and max_2.z >= '2020-07-04 07:36:00'
-- # 且第二大的 z 值大於等於上次更新時間 (上次做 change-set 時還沒有被更新到)
order by id;
select MAX(z) as latest from t1 for system_time all as max_1 group by id
-- # insert: create_at > 上次更新時間
select * from t1 for system_time all where create_at > '2020-07-03 09:05:57'
-- # 2 versions
-- # deleted
-- #
-- # select * from t1;
-- # show create table t1
```
### on duplicate key update with primary key + multi unique key
#### Test1
```sql=
CREATE TABLE `com_supervisor` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`business_uid` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_id` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_title` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_rep_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_rep_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_contribution` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_show_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `business_uid` (`business_uid`,`sup_id`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SET @@system_versioning_alter_history = 1;
ALTER TABLE com_supervisor ADD COLUMN start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
ADD COLUMN end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
ADD PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid),
ADD SYSTEM VERSIONING;
ALTER TABLE com_supervisor CHANGE last_show_at last_show_at DATETIME WITHOUT SYSTEM VERSIONING;
INSERT INTO com_supervisor (business_uid, sup_id, sup_title, sup_name, sup_rep_id, sup_rep_name, sup_contribution, last_show_at)
VALUES ('91734707', '0008', '', '黃秋松', '', '', '750,000', CURRENT_TIMESTAMP()), ('91734707', '0007', '', '黃秋松', '', '', '750,000', CURRENT_TIMESTAMP())
ON DUPLICATE KEY UPDATE sup_name = VALUES(sup_name), sup_title = VALUES(sup_title), sup_rep_id = VALUES(sup_rep_id),
sup_rep_name = VALUES(sup_rep_name), sup_contribution = VALUES(sup_contribution), last_show_at = VALUES(last_show_at);
-- ok
INSERT INTO com_supervisor (business_uid, sup_id, sup_title, sup_name, sup_rep_id, sup_rep_name, sup_contribution, last_show_at)
VALUES ('91734707', '0008', '1', '黃秋松', '', '', '750,000', CURRENT_TIMESTAMP()), ('91734707', '0007', '2', '黃秋松', '', '', '750,000', CURRENT_TIMESTAMP())
ON DUPLICATE KEY UPDATE sup_name = VALUES(sup_name), sup_title = VALUES(sup_title), sup_rep_id = VALUES(sup_rep_id),
sup_rep_name = VALUES(sup_rep_name), sup_contribution = VALUES(sup_contribution), last_show_at = VALUES(last_show_at);
-- ok for first time, but after the versioning created, it's will cause an error
-- `[23000][1062] Duplicate entry '91734707-0008-18446744073709551615' for key 'business_uid'`
```
#### Test2
```sql
CREATE TABLE `com_supervisor` (
`id` INT primary key auto_increment,
`business_uid` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_title` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_rep_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_rep_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_contribution` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_show_at` datetime DEFAULT NULL WITHOUT SYSTEM VERSIONING,
`start_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW START,
`end_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW END,
UNIQUE KEY `business_uid` (`business_uid`,`sup_id`,`end_trxid`) USING HASH,
PERIOD FOR SYSTEM_TIME (`start_trxid`, `end_trxid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci WITH SYSTEM VERSIONING;
-- same as test1
```
#### Test3
```sql
CREATE TABLE `com_supervisor` (
`id` INT primary key auto_increment,
`business_uid` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_title` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_rep_id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_rep_name` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sup_contribution` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`last_show_at` datetime DEFAULT NULL WITHOUT SYSTEM VERSIONING,
`start_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW START,
`end_trxid` bigint(20) unsigned GENERATED ALWAYS AS ROW END,
UNIQUE KEY `business_uid` (`business_uid`,`sup_id`,`end_trxid`),
PERIOD FOR SYSTEM_TIME (`start_trxid`, `end_trxid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci WITH SYSTEM VERSIONING;
-- it works
```