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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
|
call mtr.add_suppression("need more HISTORY partitions");
create table t (a int);
delete history from t before system_time now();
ERROR HY000: Table `t` is not system-versioned
create or replace table t (
a int,
row_start SYS_TYPE as row start invisible,
row_end SYS_TYPE as row end invisible,
period for system_time (row_start, row_end))
with system versioning;
insert into t values (1);
update t set a=2;
set @test = 'correct';
create trigger trg_before before delete on t for each row set @test = 'incorrect';
create trigger trg_after after delete on t for each row set @test = 'incorrect';
delete history from t;
select @test from t;
@test
correct
drop table t;
create or replace table t (
a int,
row_start SYS_TYPE as row start invisible,
row_end SYS_TYPE as row end invisible,
period for system_time (row_start, row_end))
with system versioning;
insert into t values (1), (2);
update t set a=11 where a=1;
set @ts1=now(6);
update t set a=22 where a=2;
select * from t for system_time all;
a
11
22
1
2
delete history from t before system_time timestamp @ts1;
select * from t for system_time all;
a
11
22
2
prepare stmt from 'delete history from t';
execute stmt;
drop prepare stmt;
select * from t for system_time all;
a
11
22
delete from t;
create or replace procedure truncate_sp()
begin
delete history from t before system_time timestamp now(6);
end~~
call truncate_sp;
select * from t for system_time all;
a
drop procedure truncate_sp;
# Truncate partitioned
create or replace table t (a int) with system versioning
partition by system_time limit 1 partitions 3;
insert into t values (1);
update t set a= 2;
update t set a= 3;
Warnings:
Warning 4114 Versioned table `test`.`t`: last HISTORY partition (`p1`) is out of LIMIT, need more HISTORY partitions
# You see warning above ^
delete history from t;
select * from t for system_time all;
a
3
# VIEW
create or replace table t (
i int,
row_start SYS_TYPE as row start invisible,
row_end SYS_TYPE as row end invisible,
period for system_time (row_start, row_end))
with system versioning;
delete history from t;
create or replace view v as select * from t;
delete history from v;
ERROR 42S02: 'v' is a view
create or replace table t (i int);
delete history from t;
ERROR HY000: Table `t` is not system-versioned
create or replace view v as select * from t;
delete history from v;
ERROR 42S02: 'v' is a view
prepare stmt from 'delete history from t';
ERROR HY000: Table `t` is not system-versioned
drop table t;
drop view v;
create or replace table t (i int);
create or replace view v as select * from t;
drop table v;
ERROR 42S02: 'test.v' is a view
lock table v write;
delete history from v before system_time now(6);
ERROR 42S02: 'v' is a view
unlock tables;
drop view v;
drop table t;
create table t1 (i int) with system versioning;
create procedure pr() delete history from t1 before system_time now();
call pr;
call pr;
drop procedure pr;
drop table t1;
# MDEV-15966 Behavior for TRUNCATE versioned table is not documented and not covered by tests
create or replace table t1 (id int);
create or replace table t2 (id int) with system versioning;
# force cleaning table shares
flush tables t1, t2;
truncate table t1;
truncate table t2;
ERROR HY000: System-versioned tables do not support TRUNCATE TABLE
# fetch table shares
describe t1;
Field Type Null Key Default Extra
id int(11) YES NULL
describe t2;
Field Type Null Key Default Extra
id int(11) YES NULL
truncate table t1;
truncate table t2;
ERROR HY000: System-versioned tables do not support TRUNCATE TABLE
# enter locked tables mode
lock tables t1 WRITE, t2 WRITE;
truncate t1;
truncate t2;
ERROR HY000: System-versioned tables do not support TRUNCATE TABLE
unlock tables;
drop table t2;
#
# MDEV-19814 Assertion `update->n_fields < ulint(table->n_cols + table->n_v_cols)' on DELETE HISTORY
#
create or replace table t1 (
f varchar(1),
row_start SYS_TYPE as row start,
row_end SYS_TYPE as row end,
period for system_time (row_start, row_end))
with system versioning;
insert into t1 (f) values ('a'), ('b'), ('c'), ('d'), ('e'), ('f'), ('g'), ('h');
delete from t1;
delete history from t1;
drop table t1;
#
# MDEV-20186 Wrong result or Assertion on INSERT after DELETE HISTORY
#
create or replace table t1 (a int check (a > 0)) with system versioning;
delete history from t1;
insert into t1 values (1);
select * from t1;
a
1
drop table t1;
#
# MDEV-25468 DELETE HISTORY may delete current data on system-versioned table
#
create or replace table t1 (x int) with system versioning;
insert into t1 values (1);
delete history from t1 before system_time '2039-01-01 23:00';
select * from t1;
x
1
explain extended delete history from t1 before system_time '2039-01-01 23:00';
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 1 100.00 Using where
create or replace procedure p() delete history from t1 before system_time '2039-01-01 23:00';
call p;
select * from t1;
x
1
call p;
select * from t1;
x
1
drop procedure p;
prepare stmt from "delete history from t1 before system_time '2039-01-01 23:00'";
execute stmt;
select * from t1;
x
1
execute stmt;
select * from t1;
x
1
drop prepare stmt;
drop table t1;
#
# MDEV-28201 Server crashes upon SHOW ANALYZE/EXPLAIN FORMAT=JSON
#
CREATE TABLE t1 (a INT) WITH SYSTEM VERSIONING;
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM t1;
SET optimizer_trace= 'enabled=on';
DELETE HISTORY FROM v1 BEFORE SYSTEM_TIME '2021-01-01';
ERROR HY000: The target table v1 of the DELETE is not updatable
DELETE HISTORY FROM v1;
ERROR HY000: The target table v1 of the DELETE is not updatable
DROP VIEW v1;
DROP TABLE t1;
#
# MDEV-17554 Auto-create new partition for system versioned tables with history partitioned by INTERVAL/LIMIT
#
# Don't auto-create new partition on DELETE HISTORY:
set timestamp= unix_timestamp('2000-01-01 00:00:00');
create or replace table t (a int) with system versioning
partition by system_time interval 1 hour auto;
set timestamp= unix_timestamp('2000-01-01 10:00:00');
delete history from t;
set timestamp= default;
show create table t;
Table Create Table
t CREATE TABLE `t` (
`a` int(11) DEFAULT NULL
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
PARTITIONS 2
drop table t;
|