[MDEV-14794] Limitations which the row end as a part of PK imposes due to CURRENT_TIMESTAMP behavior and otherwise Created: 2017-12-28  Updated: 2018-02-22  Resolved: 2018-02-22

Status: Closed
Project: MariaDB Server
Component/s: Versioned Tables
Affects Version/s: 10.3.4
Fix Version/s: 10.3.5

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Eugene Kosov (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-14829 Assertion `0' failed in Protocol::end... Closed

 Description   

Note: Possibly it's the matter of documentation, but before we proceed with documenting it, it needs to be confirmed as intended behavior.

CURRENT_TIMESTAMP freezes its value upon the beginning of the statement/function/trigger execution. Given that the row end column is added automatically to the PK, it means that certain DML statements that work with non-versioned tables will become impossible with versioning. Here is basic example for a single statement, it can of course become more elaborate and less degenerate within triggers or functions.

MariaDB [test]> create table t1 (pk int primary key, i int) with system versioning;
 
Query OK, 0 rows affected (0.15 sec)
 
 
 
MariaDB [test]> replace into t1 values (1,10),(1,100),(1,1000);
 
ERROR 1062 (23000): Duplicate entry '1-2038-01-19 05:14:07.999999' for key 'PRIMARY'

That is, everything that removes a row which is represented by the non-"row end" part of PK more than once within a statement/function/trigger will fail.

Although, maybe in the case above the reason is actually different, not related to CURRENT_TIMESTAMP behavior as such, since it claims that it can't insert 2038-01-19 05:14:07.999999, which is just wrong. But even if it goes as expected, it will still attempt to insert (1,<current timestamp>) twice and fail.

 Comments   
Comment by Elena Stepanova [ 2018-01-14 ]

Here is another kind of limitation which enforced addition of row end to the PK causes, also quite unexpected and without any obvious workaround:

CREATE OR REPLACE TABLE t1 (
 
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `User` char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
 
  `Timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
 
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
 
) ENGINE=MyISAM;

Adding system versioning to the table causes the error:

MariaDB [test]> ALTER TABLE t1 ADD SYSTEM VERSIONING;
 
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Same happens if versioning is added explicitly, through creating a table and a period, because even in this case row end is added to the PK, which is even stranger.

Comment by Sergei Golubchik [ 2018-01-29 ]

The first case (with REPLACE) is a bug, should be fixed. The second, with "key was too long" — that's a limitation, nothing we can do...

Comment by Eugene Kosov (Inactive) [ 2018-02-14 ]

In `tt-1.3.5` it works like this:

create table t1 (pk int primary key, i int) with system versioning;
 
replace into t1 values (1,10),(1,100),(1,1000);
 
select *, row_start, row_end from t1 for system_time all;
 
pk	i	row_start	row_end
 
1	1000	2018-02-14 14:22:04.358922	2038-01-19 06:14:07.999999

Relevant commit is https://github.com/tempesta-tech/mariadb/commit/d95c74d8280f93b73995935a5dcf2359ad375daa

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