maria-discuss team mailing list archive

[serg@xxxxxxxxxxx]

Hi, Benjamin!

First: please send questions like this to
maria-discuss@xxxxxxxxxxxxxxxxxxx it's a public mailing list dedicated
to MariaDB. I am subscribed, so I'll see you mail there, and you may be
sure I will, because it won't be accidentally catched by my spam filter,
or sorted out in some obscure folder. Furthermore other subscribers will
see your question and could reply if I will be not available (e.g. I
could be travelling).

Thank you.

On Feb 05, bartels.benjamin@xxxxxxxxxxx wrote:
> Hello Mr. Golubchik,
> I have a couple of questions on MariaDB's implementation of the system
> versioned tables if you are not busy.
> 1. Is there any mechanism in the current implementation to insert rows
> with custom values for ROW START and ROW END? I know that since they
> are automatically implemented as auto generated columns that they are
> not allowed to be modified. But I feel like this would make backup
> migration very hard, since you cannot insert into these generated
> columns. Maybe I am missing something but from what I can tell it is
> not possible at the moment.MSSQL from my understanding doesn't
> restrict the ROW START and ROW END columns to generated columns so it
> is possible to insert some data into those columns and then alter the
> table to make it system versioned.See (Listing
> 1): http://www.itprotoday.com/microsoft-sql-server/first-look-system-versioned-temporal-tables-part-2-querying-data-and

No, there is none. It is intentional, to protect the integrity of the
history. SQL Standard, as far as I know, does not provide any way of
falsifying the history of system versioned tables.

There is another feature in the standard: application-time period
tables, in there one can provide arbitrary values for start and end
timestamps. But MariaDB 10.3 doesn't support application-time period
tables.

As for backups, yes, I agree, it's a problem. Binary backups will
preserve the history just fine, but mysqldump backups will not, they'll
reset the history.

> 2. What is supposed to be done in 2038 when NOW() will surpass the ROW
> END column? Will there be a way to have a bigger underlying datatype
> to represent the historic columns?

That's the common issue for any TIMESTAMP columns. We don't have any
solution specific to system versioned tables.

I could *guess* that closer to that date TIMESTAMP width will be
increased and, perhaps, there will be some migration procedure in
mysql_uprade. There are no specific plans yet.

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx