Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3(EOL), 10.4(EOL)
Description
An ALTER TABLE operation that specifies WITH SYSTEM VERSIONING or WITHOUT SYSTEM VERSIONING on a column will set the flag ALTER_COLUMN_UNVERSIONED, which currently causes the table to be rebuilt.
As far as I can tell, the change should not require rebuilding anything. The data in the table remains the same.
Enabling WITH SYSTEM VERSIONING for a column should be an instantaneous change, assuming that system versioning is already enabled on the table. This would only set some metadata so that future UPDATE on that column will cause the versioning information to be stored.
Specifying WITHOUT SYSTEM VERSIONING might involve removing old history data for that column. That could be done by a table scan and update, which is cheaper than a full rebuild. We could also redefine the semantics of the operation, documenting that any old historical data for the column would survive.
Attachments
Issue Links
- blocks
-
MDEV-11424 Instant ALTER TABLE of failure-free record format changes
-
- Closed
-
Activity
Apart from table rebuildint there is a related bug:
set @@system_versioning_alter_history=keep;
|
create table t (
|
a int,
|
b int,
|
row_start bigint unsigned as row start invisible,
|
row_end bigint unsigned as row end invisible,
|
period for system_time(row_start, row_end)
|
) with system versioning engine=innodb;
|
|
|
insert into t values (1,1);
|
|
|
alter table t
|
change a a int without system versioning;
|
|
|
update t set a=11;
|
select *,row_start,row_end from t for system_time all;
|
|
Here historical row is created on UPDATE.
Yes, history is stored for rows, not columns. I meant that it would be technically possible to remove historical data for a particular column by updating the value of the column in the history rows to the current value.
Come to think of it, if versioning is disabled for some columns, some historical values of non-versioned columns can be persisted in historical rows as ‘collateral damage’ if these historical rows are created due to updating versioned columns.
So, I would guess that adding the WITHOUT SYSTEM VERSIONING attribute to a column could be an instantaneous operation, only affecting subsequent versioned operations. (SELECT should only report the current value of non-versioned columns, and treat any values in historical rows as garbage.)
Specifying WITHOUT SYSTEM VERSIONING might involve removing old history data for that column.
History is stored for rows, not columns. Thus it's not possible to remove history for some columns. And changing WITH/WITHOUT SYSTEM VERSIONING doens't need to read table data at all.