MariaDB Versioning - without versioning - Feets in the hole.

tags: `kyc-right`

Related article: MariaDB Temporal Table https://hackmd.io/Iwe0MVlwRpOMZ70W1ZwPtQ

MariaDB Versioning - without versioning - Feets in the hole.

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.

















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.

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.





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;

Simple UPDATE works fine


update t1 set y = '123456' where x  = 1;
select *, ROW_START, ROW_END from t1 for system_time all;

With on duplicate key update,
even we only update the column WITHOUT VERSIONING,
MariaDB still create new version record for the row.

Replace?


replace into t1 (x, y) values ('1', '123456'); -- try this few times

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


























































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





































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

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

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

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`	在筆記中貼入程式碼
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.

MariaDB Versioning - without versioning - Feets in the hole.

tags: kyc-right

Description

Environment

Reproduce

Create versioned table

Upsert

Output

Simple UPDATE works fine

Replace?

trxid-based versioning

Does not support partition

Testing

on duplicate key update with primary key + multi unique key

Test1

Test2

Test3

tags: `kyc-right`