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
|
set @@session.time_zone='+00:00';
select ifnull(max(transaction_id), 0) into @start_trx_id from mysql.transaction_registry;
set @test_start=now(6);
create procedure if not exists verify_vtq()
begin
set @i= 0;
select
@i:= @i + 1 as No,
transaction_id > 0 as A,
commit_id > transaction_id as B,
begin_timestamp > @test_start as C,
commit_timestamp >= begin_timestamp as D
from mysql.transaction_registry
where transaction_id > @start_trx_id;
select ifnull(max(transaction_id), 0)
into @start_trx_id
from mysql.transaction_registry;
end~~
create function if not exists default_engine()
returns varchar(255)
deterministic
begin
declare e varchar(255);
select lower(engine) from information_schema.engines where support='DEFAULT' into e;
return e;
end~~
create function if not exists sys_datatype()
returns varchar(255)
deterministic
begin
if default_engine() = 'innodb' then
return 'bigint unsigned';
elseif default_engine() = 'myisam' then
return 'timestamp(6)';
end if;
return NULL;
end~~
create function if not exists sys_commit_ts(sys_field varchar(255))
returns varchar(255)
deterministic
begin
if default_engine() = 'innodb' then
return concat('vtq_commit_ts(', sys_field, ')');
elseif default_engine() = 'myisam' then
return sys_field;
end if;
return NULL;
end~~
create procedure if not exists innodb_verify_vtq(recs int)
begin
declare i int default 1;
if default_engine() = 'innodb' then
call verify_vtq;
elseif default_engine() = 'myisam' then
create temporary table tmp (No int, A bool, B bool, C bool, D bool);
while i <= recs do
insert into tmp values (i, 1, 1, 1, 1);
set i= i + 1;
end while;
select * from tmp;
drop table tmp;
end if;
end~~
create procedure concat_exec2(a varchar(255), b varchar(255))
begin
prepare stmt from concat(a, b);
execute stmt;
deallocate prepare stmt;
end~~
create procedure concat_exec3(a varchar(255), b varchar(255), c varchar(255))
begin
prepare stmt from concat(a, b, c);
execute stmt;
deallocate prepare stmt;
end~~
create function get_archive_table_name()
returns varchar(255)
begin
return (select archive_name from t_vtmd for system_time all where archive_name is not NULL
order by start desc limit 1);
end~~
create procedure drop_last_archive()
begin
call concat_exec2('drop table ', get_archive_table_name());
end~~
set versioning_alter_history= survive;
create or replace table t (a int) with system versioning;
insert into t values (1);
update t set a=2 where a=1;
select sys_trx_start from t where a=2 into @tm;
alter table t add column b int;
select * from t;
a b
2 NULL
call concat_exec3('select * from ', get_archive_table_name(), ' for system_time all');
a
2
1
call concat_exec3('select @tm=sys_trx_start from ', get_archive_table_name(), ' for system_time all where a=2');
@tm=sys_trx_start
1
select @tm<sys_trx_start from t where a=2;
@tm<sys_trx_start
1
select sys_trx_start from t where a=2 into @tm;
call concat_exec3('select @tm=sys_trx_end from ', get_archive_table_name(), ' for system_time all where a=2');
@tm=sys_trx_end
1
call drop_last_archive();
set versioning_alter_history= keep;
drop table t_vtmd;
drop table t;
set versioning_alter_history= survive;
create or replace table t (a int) with system versioning;
insert into t values (1);
update t set a=2 where a=1;
select sys_trx_start from t where a=2 into @tm;
alter table t add column b int;
select * from t;
a b
2 NULL
call concat_exec3('select * from ', get_archive_table_name(), ' for system_time all');
a
2
1
call concat_exec3('select @tm=sys_trx_start from ', get_archive_table_name(), ' for system_time all where a=2');
@tm=sys_trx_start
1
select @tm<sys_trx_start from t where a=2;
@tm<sys_trx_start
1
select sys_trx_start from t where a=2 into @tm;
call concat_exec3('select @tm=sys_trx_end from ', get_archive_table_name(), ' for system_time all where a=2');
@tm=sys_trx_end
1
call drop_last_archive();
set versioning_alter_history= keep;
drop table t_vtmd;
drop table t;
set versioning_alter_history= survive;
create or replace table t (a int) with system versioning engine innodb;
insert into t values (1);
update t set a=2 where a=1;
select sys_trx_start from t where a=2 into @tm;
alter table t add column b int;
select * from t;
a b
2 NULL
call concat_exec3('select * from ', get_archive_table_name(), ' for system_time all');
a
2
1
call concat_exec3('select @tm=sys_trx_start from ', get_archive_table_name(), ' for system_time all where a=2');
@tm=sys_trx_start
1
select @tm<sys_trx_start from t where a=2;
@tm<sys_trx_start
1
select sys_trx_start from t where a=2 into @tm;
call concat_exec3('select @tm=sys_trx_end from ', get_archive_table_name(), ' for system_time all where a=2');
@tm=sys_trx_end
1
call drop_last_archive();
set versioning_alter_history= keep;
drop table t_vtmd;
drop table t;
set versioning_alter_history= survive;
create or replace table t (a int) with system versioning engine innodb;
insert into t values (1);
update t set a=2 where a=1;
alter table t add column b int, algorithm=inplace;
set versioning_alter_history = keep;
drop function get_archive_table_name;
drop procedure drop_last_archive;
select * from mysql.vtmd_template;
start end name archive_name col_renames
show create table mysql.vtmd_template;
Table Create Table
vtmd_template CREATE TABLE `vtmd_template` (
`start` bigint(20) unsigned GENERATED ALWAYS AS ROW START COMMENT 'TRX_ID of table lifetime start',
`end` bigint(20) unsigned GENERATED ALWAYS AS ROW END NOT NULL COMMENT 'TRX_ID of table lifetime end',
`name` varchar(64) COLLATE utf8_bin NOT NULL COMMENT 'Table name during current lifetime period',
`archive_name` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT 'Name of archive table',
`col_renames` blob DEFAULT NULL COMMENT 'Column name mappings from previous lifetime',
PRIMARY KEY (`end`),
KEY `archive_name` (`archive_name`),
PERIOD FOR SYSTEM_TIME (`start`, `end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 WITH SYSTEM VERSIONING
call verify_vtq;
No A B C D
1 1 1 1 1
2 1 1 1 1
3 1 1 1 1
4 1 1 1 1
5 1 1 1 1
6 1 1 1 1
7 1 1 1 1
8 1 1 1 1
9 1 1 1 1
10 1 1 1 1
11 1 1 1 1
12 1 1 1 1
drop table t;
drop table t_vtmd;
drop procedure verify_vtq;
drop procedure innodb_verify_vtq;
drop function default_engine;
drop function sys_commit_ts;
drop function sys_datatype;
drop procedure concat_exec2;
drop procedure concat_exec3;
|
