mariadb-system-versioned-tables

Create a system-versioned table by appending WITH SYSTEM VERSIONING to your CREATE TABLE statement. MariaDB automatically adds hidden ROW_START and ROW_END timestamp columns, and every INSERT, UPDATE, or DELETE generates a history row. For existing tables, run ALTER TABLE t ADD SYSTEM VERSIONING.

Use the FOR SYSTEM_TIME clause directly after the table name, such as SELECT * FROM t FOR SYSTEM_TIME AS OF '2026-01-01'. Variants include BETWEEN, FROM ... TO, and ALL for full history. You can also set system_versioning_asof to apply a snapshot to all session queries.

No, MySQL has no equivalent to system-versioned tables. This feature is unique to MariaDB among MySQL-compatible databases and has been available since MariaDB 10.3. SQL Server temporal syntax also differs and does not translate directly.

TRUNCATE is prohibited on system-versioned tables and raises error 4137 to protect history integrity. Use DELETE HISTORY FROM t BEFORE SYSTEM_TIME 'date' to purge old history, or manage historical partitions with ALTER TABLE DROP PARTITION instead.

By default, ALTER TABLE on a versioned table raises an error to protect history integrity. Set system_versioning_alter_history = KEEP before altering, then restore it to ERROR afterward. Historical rows for newly added columns will contain NULL values.

No, mysqldump and mariadb-dump skip historical rows by default. Use mariadb-dump --dump-history (MariaDB 10.11+) to include them, and set system_versioning_insert_history=ON plus secure_timestamp on restore so the loader can write ROW_START and ROW_END values.