1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
|
set system_versioning_alter_history=keep;
create or replace table t (a int);
alter table t add system versioning, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: Not implemented for system-versioned timestamp tables. Try LOCK=SHARED
alter table t add system versioning, algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Not implemented for system-versioned timestamp tables. Try ALGORITHM=COPY
alter table t add system versioning, lock=shared;
alter table t add column b int, change column a a int without system versioning, lock=none;
alter table t drop system versioning, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: Not implemented for system-versioned operations. Try LOCK=SHARED
alter table t drop system versioning, algorithm=inplace;
create or replace table t (
a int, b int,
row_start SYS_DATATYPE as row start invisible,
row_end SYS_DATATYPE as row end invisible,
period for system_time (row_start, row_end)
) with system versioning;
insert into t values (1, 0);
insert into t values (2, 0);
delete from t where a = 2;
alter table t drop column b, lock=none;
alter table t add index idx(a), lock=none;
select a, check_row(row_start, row_end) from t for system_time all order by a;
a check_row(row_start, row_end)
1 CURRENT ROW
2 HISTORICAL ROW
# MDEV-17038 ALTER TABLE CHANGE COLUMN c1 c1 bigint NOT NULL -
# generates error if table uses SYSTEM VERSIONING [tempesta-tech/mariadb#540]
create or replace table t1 (a int, key(a)) with system versioning;
create or replace table t2 like t;
alter table t2 add foreign key(a) references t1(a);
alter table t2 modify column a int not null, lock=none;
drop table t2;
drop table t1;
# MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute
create or replace table t1 (
a int,
b int,
row_start SYS_DATATYPE as row start invisible,
row_end SYS_DATATYPE as row end invisible,
period for system_time(row_start, row_end)
) with system versioning;
create or replace table t2 (
a int without system versioning,
b int,
row_start SYS_DATATYPE as row start invisible,
row_end SYS_DATATYPE as row end invisible,
period for system_time(row_start, row_end)
) with system versioning;
insert into t1 values (1,1);
insert into t2 values (1,1);
set @@system_versioning_alter_history=keep;
# without rebuild
alter table t1
change a a int without system versioning,
algorithm=instant;
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 0
alter table t2
change a a int with system versioning,
add primary key pk (a),
algorithm=instant;
ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=INPLACE
# with rebuild
alter table t2
change a a int with system versioning,
add primary key pk (a);
affected rows: 0
info: Records: 0 Duplicates: 0 Warnings: 1
Warnings:
Warning 1280 Name 'pk' ignored for PRIMARY key.
update t1 set a=2;
select count(*) from t1 for system_time all;
count(*)
1
update t2 set a=2;
select count(*) from t2 for system_time all;
count(*)
2
drop table t1, t2;
# rollback ALTER TABLE: nothing should change
create or replace table t (
a int,
b int,
row_start SYS_DATATYPE as row start invisible,
row_end SYS_DATATYPE as row end invisible,
period for system_time(row_start, row_end)
) with system versioning;
insert into t values (1, 1);
select c.prtype from information_schema.innodb_sys_columns as c
join information_schema.innodb_sys_tables as t on c.table_id=t.table_id
where t.name='test/t' and c.name='b';
prtype
50179
set @@system_versioning_alter_history=keep;
select c.prtype from information_schema.innodb_sys_columns as c
join information_schema.innodb_sys_tables as t on c.table_id=t.table_id
where t.name='test/t' and c.name='b';
prtype
50179
show create table t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`row_start` SYS_DATATYPE GENERATED ALWAYS AS ROW START INVISIBLE,
`row_end` SYS_DATATYPE GENERATED ALWAYS AS ROW END INVISIBLE,
PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
select count(*) from t for system_time all;
count(*)
1
update t set b=11;
select count(*) from t for system_time all;
count(*)
2
drop table t;
|
