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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
|
set system_versioning_alter_history=keep;
create or replace table t (a int);
alter table t add system versioning, lock=none;
alter table t drop system versioning;
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: DROP SYSTEM VERSIONING. 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.
# restart
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=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci 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;
# Start of 10.4 tests
create or replace table t (a int, b int) engine=innodb;
alter table t
add s bigint unsigned as row start,
add e bigint unsigned as row end,
add period for system_time(s, e),
add system versioning;
alter table t drop column b, algorithm=instant;
alter table t add index idx(a), lock=none;
alter table t drop column s, drop column e;
alter table t drop system versioning, lock=none;
ERROR 0A000: LOCK=NONE is not supported. Reason: DROP SYSTEM VERSIONING. Try LOCK=SHARED
#
# MDEV-17697 Broken versioning info after instant drop column
#
set @@system_versioning_alter_history= keep;
create or replace table t1 (a int, b int) with system versioning;
insert into t1 values (1, 1);
alter table t1 drop column b, algorithm=instant;
alter table t1 drop system versioning;
create or replace table t1 (a int, b int) with system versioning;
insert into t1 values (1, 1);
alter table t1 drop system versioning;
#
# MDEV-18173 Assertion `o->ind == vers_end' or `o->ind == vers_start' failed in dict_table_t::instant_column
#
set @@system_versioning_alter_history= keep;
create or replace table t1 (pk integer primary key, a int, b int, v int as (a))
with system versioning;
alter table t1 force;
alter table t1 drop column b;
#
# MDEV-18122 Assertion `table->versioned() == m_prebuilt->table->versioned()' failed in ha_innobase::open
#
create or replace table t1 (
x int,
v int as (x) virtual,
y int
) with system versioning;
alter table t1 drop system versioning;
drop tables t, t1;
|
