[MDEV-15951] system versioning by trx id doesn't work with partitioning Created: 2018-04-20  Updated: 2022-03-29  Resolved: 2019-03-29

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3.6
Fix Version/s: 10.3.14, 10.4.4

Type: Bug Priority: Major
Reporter: Axel Schwenke Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-23773 Cannot store the history separately f... Open
Relates
relates to MDEV-20271 Partitioned versioned tables do not a... Open
relates to MDEV-15690 benchmark the overhead caused by syst... Stalled
relates to MDEV-25173 paritioning not supported on transac... Open

 Description   

Creating a system-versioned table with row version by trx id works as described in the KB. However when I add partitions to separate current and historic data, I get an error.

The CREATE TABLE statement:

CREATE TABLE sbtest1 (
 
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, 
k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
 
c CHAR(120) DEFAULT '' NOT NULL,
 
pad CHAR(60) DEFAULT '' NOT NULL,
 
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),
 
PRIMARY KEY (id),
 
INDEX k_ (k)
 
) ENGINE=InnoDB WITH SYSTEM VERSIONING PARTITION BY SYSTEM_TIME (
 
  PARTITION p_hist HISTORY,
 
  PARTITION p_cur CURRENT
 
)

and the error message:

ERROR 4110 (HY000): `start_trxid` must be of type TIMESTAMP(6) 
for system-versioned table `sbtest1`

If partitioning isn't supposed to work with trx ids, this should be documented.

 Comments   
Comment by Elena Stepanova [ 2018-04-20 ]

What's worse, it appears that PARTITION BY RANGE also doesn't work.

Comment by Sergei Golubchik [ 2018-05-24 ]

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.

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