Hi, Benjamin! First: please send questions like this to maria-discuss@lists.launchpad.net 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@tutanota.de 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-t...
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@mariadb.org