[MDEV-39393] Adding system versioning with existing row_start/row_end data discards data without warning - Jira

XML

Word

Printable

Migrating an existing data with a ROW_START/ROW_END to SYSTEM VERSIONED tables should be possible without discarding rows.

This is a useful migration strategy for users with existing data.

e.g:

create table t (id int not null primary key auto_increment, validFrom DATETIME, validTo DATETIME);

insert into t (validFrom, validTo) values ('2021-03-04 11:30', '2022-04-04 23:00');

select * from t;

SET @@system_versioning_alter_history = KEEP;

SET @@system_versioning_insert_history=ON;

ALTER TABLE t MODIFY validFrom  TIMESTAMP(6) GENERATED ALWAYS AS ROW START,

MODIFY validTo TIMESTAMP(6) GENERATED ALWAYS AS ROW END,

ADD PERIOD FOR SYSTEM_TIME(validFrom, validTo),

ADD SYSTEM VERSIONING;

SHOW WARNINGS;

Resulting table contents:

SELECT * FROM t FOR SYSTEM_TIME ALL;

|----|----------------------------|----------------------------|

| id | validFrom                  | validTo                    |

|----|----------------------------|----------------------------|

| 1  | 2026-04-21 01:43:05.014747 | 2106-02-07 06:28:15.999999 |

The existing validFrom/validTo fields have had their data dropped without warning.

It is possible by creating a new table as answered.

links to

SO User requested migration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.