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
|
-- source include/have_innodb.inc
delimiter ~~;
create or replace procedure drop_archives (in vtmd_name varchar(64))
begin
declare archive_name varchar(64);
declare cur_done bool default false;
declare cur cursor for
select cur_tmp.archive_name from cur_tmp;
declare continue handler for not found set cur_done = true;
set @tmp= concat('
create or replace temporary table
cur_tmp as
select vtmd.archive_name from ', vtmd_name, '
for system_time all as vtmd
where vtmd.archive_name is not null
group by vtmd.archive_name');
prepare stmt from @tmp; execute stmt; drop prepare stmt;
open cur;
fetch_loop: loop
fetch cur into archive_name;
if cur_done then
leave fetch_loop;
end if;
set @tmp= concat('drop table ', archive_name);
prepare stmt from @tmp; execute stmt; drop prepare stmt;
end loop;
drop table cur_tmp;
end~~
delimiter ;~~
delimiter ~~;
create or replace procedure check_vtmd (in vtmd_name varchar(64))
begin
set @tmp= concat('
create or replace temporary table
tmp_vtmd with system versioning as
select * from ', vtmd_name, '
for system_time all as vtmd');
prepare stmt from @tmp; execute stmt; drop prepare stmt;
set @inf= 0xFFFFFFFFFFFFFFFF + 0;
set @start= null;
select start from tmp_vtmd for system_time all order by start limit 1 into @start;
select @start > 0 and @start < @inf;
select
start >= @start as A_start,
(@start:= end) and end = @inf as B_end,
name,
substr(archive_name, 1, instr(archive_name, '_')) as C_archive_name
from tmp_vtmd for system_time all;
drop table tmp_vtmd;
end~~
delimiter ;~~
delimiter ~~;
create or replace procedure show_tables()
begin
show tables;
select table_name, table_schema from information_schema.tables
where table_schema not in ('mysql', 'performance_schema', 'information_schema', 'mtr')
order by table_name;
end~~
delimiter ;~~
# create
set versioning_alter_history= keep;
create table t0 (z int) with system versioning;
show tables;
set versioning_alter_history= survive;
create or replace table t0 (y int) with system versioning;
show tables;
show create table t0_vtmd;
call check_vtmd('t0_vtmd');
set versioning_alter_history= keep;
drop table t0;
set versioning_alter_history= survive;
--error ER_VERS_VTMD_ERROR
create table t0 (x int) with system versioning;
drop table t0_vtmd;
create table t0 (y int) with system versioning;
create or replace table t0 (x int) with system versioning;
# alter
insert into t0 values (1);
set @t0= now(6);
alter table t0 add column (y int);
select * from t0 for system_time as of @t0;
select * from t0;
call check_vtmd('t0_vtmd');
call drop_archives('t0_vtmd');
drop table t0_vtmd;
alter table t0 drop column y;
call check_vtmd('t0_vtmd');
call drop_archives('t0_vtmd');
set versioning_alter_history= keep;
drop tables t0, t0_vtmd;
set versioning_alter_history= survive;
# rename
set versioning_alter_history= keep;
create or replace table x0 (x int) with system versioning;
set versioning_alter_history= survive;
rename table x0 to d0;
show tables;
set versioning_alter_history= keep;
drop table d0;
set versioning_alter_history= survive;
create or replace table x0 (x int) with system versioning;
rename table x0 to d0;
show tables;
call check_vtmd('d0_vtmd');
set versioning_alter_history= keep;
drop table d0;
set versioning_alter_history= survive;
create or replace table x0 (x int) with system versioning;
--error ER_VERS_VTMD_ERROR
rename table x0 to d0;
show tables;
drop table x0_vtmd;
rename table x0 to d0;
show tables;
rename table d0 to duck;
rename table duck to bay;
rename table bay to sheer;
rename table sheer to t0;
call check_vtmd('t0_vtmd');
alter table t0 add column (y int);
call check_vtmd('t0_vtmd');
# rename to different schema
alter table t0 add column (z int);
alter table t0 drop column y;
alter table t0 drop column z;
create database db0;
rename table t0 to db0.t0;
show tables;
use db0;
show tables;
call test.check_vtmd('db0.t0_vtmd');
create database db1;
rename table t0 to db1.other_name;
show tables;
use db1;
show tables;
call test.check_vtmd('db1.other_name_vtmd');
# alter rename
alter table other_name rename to t1;
call test.check_vtmd('db1.t1_vtmd');
# alter rename and modify to different schema
alter table t1 rename to test.t2, add column (y int);
use test;
show tables;
call check_vtmd('t2_vtmd');
create or replace table t3 (x int) with system versioning;
alter table t3 change x x bigint;
alter table t3 change x x bigint after sys_trx_start;
call check_vtmd('t3_vtmd');
# hide archive tables
set versioning_hide= auto;
call show_tables();
set versioning_hide= implicit;
call show_tables();
set versioning_hide= full;
call show_tables();
set versioning_hide= never;
--replace_regex /\d{8}_\d{6}_\d{6}/TIMESTAMP_SUFFIX/
call show_tables();
# wrong VTMD handling
set versioning_hide= auto;
create or replace table u0_vtmd (x int) with system versioning;
show tables;
set versioning_alter_history= survive;
create or replace table t (x int) with system versioning;
select * from t for system_time all;
drop database db0;
drop database db1;
drop database test;
create database test;
|
