mirror of
https://github.com/MariaDB/server.git
synced 2025-02-12 00:15:35 +01:00
9e1fb104a3
-----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEF39AEP5WyjM2MAMF8WVvJMdM0dgFAmck77AACgkQ8WVvJMdM 0dgccQ/+Lls8fWt4D+gMPP7x+drJSO/IE/gZFt3ugbWF+/p3B2xXAs5AAE83wxEh QSbp4DCkb/9PnuakhLmzg0lFbxMUlh4rsJ1YyiuLB2J+YgKbAc36eQQf+rtYSipd DT5uRk36c9wOcOXo/mMv4APEvpPXBIBdIL4VvpKFbIOE7xT24Sp767zWXdXqrB1f JgOQdM2ct+bvSPC55oZ5p1kqyxwvd6K6+3RB3CIpwW9zrVSLg7enT3maLjj/761s jvlRae+Cv+r+Hit9XpmEH6n2FYVgIJ3o3WhdAHwN0kxKabXYTg7OCB7QxDZiUHI9 C/5goKmKaPB1PCQyuTQyLSyyK9a8nPfgn6tqw/p/ZKDQhKT9sWJv/5bSWecrVndx LLYifSTrFC/eXLzgPvCnNv/U8SjsZaAdMIKS681+qDJ0P5abghUIlGnMYTjYXuX1 1B6Vrr0bdrQ3V1CLB3tpkRjpUvicrsabtuAUAP65QnEG2G9UJXklOer+DE291Gsl f1I0o6C1zVGAOkUUD3QEYaHD8w7hlvyfKme5oXKUm3DOjaAar5UUKLdr6prxRZL4 ebhmGEy42Mf8fBYoeohIxmxgvv6h2Xd9xCukgPp8hFpqJGw8abg7JNZTTKH4h2IY J51RpD10h4eoi6WRn3opEcjexTGvZ+xNR7yYO5WxWw6VIre9IUA= =s+WW -----END PGP SIGNATURE----- Merge tag '11.4' into 11.6 MariaDB 11.4.4 release
319 lines
13 KiB
Text
319 lines
13 KiB
Text
create or replace table t1(
|
|
x int unsigned,
|
|
y int unsigned,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time (sys_start, sys_end))
|
|
with system versioning;
|
|
insert into t1(x, y) values(3, 4);
|
|
insert into t1(x, y) values(2, 3);
|
|
insert into t1 values(40, 33);
|
|
select x, y, sys_end < MAXVAL from t1;
|
|
x y sys_end < MAXVAL
|
|
3 4 0
|
|
2 3 0
|
|
40 33 0
|
|
create or replace table t1(
|
|
id int unsigned auto_increment primary key,
|
|
x int unsigned,
|
|
y int unsigned,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time (sys_start, sys_end))
|
|
with system versioning;
|
|
insert into t1(x, y) values(33, 44);
|
|
insert into t1(id, x, y) values(20, 33, 44);
|
|
insert into t1 values(40, 33, 44);
|
|
select id, x, y, sys_end < MAXVAL from t1;
|
|
id x y sys_end < MAXVAL
|
|
1 33 44 0
|
|
20 33 44 0
|
|
40 33 44 0
|
|
create or replace table t1(
|
|
x int unsigned,
|
|
y int unsigned,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time (sys_start, sys_end))
|
|
with system versioning;
|
|
create view vt1_1 as select x, y from t1;
|
|
insert into t1(x, y) values(8001, 9001);
|
|
insert into vt1_1(x, y) values(1001, 2001);
|
|
insert into vt1_1 values(1002, 2002);
|
|
select x, y, sys_end < MAXVAL from t1;
|
|
x y sys_end < MAXVAL
|
|
8001 9001 0
|
|
1001 2001 0
|
|
1002 2002 0
|
|
select x, y from vt1_1;
|
|
x y
|
|
8001 9001
|
|
1001 2001
|
|
1002 2002
|
|
drop view vt1_1;
|
|
create or replace table t1( id bigint primary key, a int, b int) with system versioning;
|
|
insert into t1 values(1, 1, 1);
|
|
select row_start, row_end from t1 into @sys_start, @sys_end;
|
|
select id, a, b from t1;
|
|
id a b
|
|
1 1 1
|
|
insert into t1 values(2, 2, 2);
|
|
select id, a, b, row_start > @sys_start as C, row_end = @sys_end as D from t1 where id = 2;
|
|
id a b C D
|
|
2 2 2 1 1
|
|
drop table t1;
|
|
create or replace table t1(
|
|
x int unsigned,
|
|
y int unsigned,
|
|
sys_start SYS_DATATYPE as row start invisible,
|
|
sys_end SYS_DATATYPE as row end invisible,
|
|
period for system_time (sys_start, sys_end))
|
|
with system versioning;
|
|
create or replace table t2 like t1;
|
|
insert into t1(x, y) values (1, 1000), (2, 2000), (3, 3000), (4, 4000), (5, 5000), (6, 6000), (7, 7000), (8, 8000), (9, 9000);
|
|
delete from t1 where x >= 1;
|
|
insert into t1(x, y) values (1, 1001), (2, 2001), (3, 3001), (4, 4001), (5, 5001), (6, 6001);
|
|
insert into t1(x, y, sys_start) values (7, 7001, DEFAULT);
|
|
insert into t1(x, y, sys_end) values (8, 8001, DEFAULT);
|
|
insert into t1(x, y, sys_start, sys_end) values (9, 9001, DEFAULT, DEFAULT);
|
|
insert into t2 select x, y from t1 for system_time all;
|
|
select x, y from t1;
|
|
x y
|
|
1 1001
|
|
2 2001
|
|
3 3001
|
|
4 4001
|
|
5 5001
|
|
6 6001
|
|
7 7001
|
|
8 8001
|
|
9 9001
|
|
select x, y from t2;
|
|
x y
|
|
1 1000
|
|
2 2000
|
|
3 3000
|
|
4 4000
|
|
5 5000
|
|
6 6000
|
|
7 7000
|
|
8 8000
|
|
9 9000
|
|
1 1001
|
|
2 2001
|
|
3 3001
|
|
4 4001
|
|
5 5001
|
|
6 6001
|
|
7 7001
|
|
8 8001
|
|
9 9001
|
|
drop table t1;
|
|
drop table t2;
|
|
#
|
|
# MDEV-16546 System versioning setting to allow history modification
|
|
#
|
|
set @@session.time_zone='+00:00';
|
|
create table t1(x int primary key) with system versioning;
|
|
create table t2(y int primary key,
|
|
row_start timestamp(6) as row start invisible,
|
|
row_end timestamp(6) as row end invisible,
|
|
period for system_time (row_start, row_end))
|
|
with system versioning;
|
|
create table t3(z int primary key,
|
|
row_start timestamp(6) as row start,
|
|
row_end timestamp(6) as row end,
|
|
period for system_time (row_start, row_end))
|
|
with system versioning;
|
|
insert into t1(x, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR 42S22: Unknown column 'row_start' in 'INSERT INTO'
|
|
insert into t2(y, row_start, row_end) values (2, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
|
|
set @@system_versioning_insert_history= 1;
|
|
show create table t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`x` int(11) NOT NULL,
|
|
PRIMARY KEY (`x`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
|
|
insert into t1(x, row_start, row_end) values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
insert into t2(y, row_start, row_end) values (4, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
insert into t3 values (5, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
insert into t3 values (5, '1980-01-02 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR HY000: Incorrect system-version range 'row_start' value: '1980-01-02 00:00:00.000000' and 'row_end' value: '1980-01-01 00:00:01.000000'
|
|
select x, row_start, row_end from t1 for system_time all;
|
|
x row_start row_end
|
|
3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
|
|
select y, row_start, row_end from t2 for system_time all;
|
|
y row_start row_end
|
|
4 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
|
|
select z, row_start, row_end from t3 for system_time all;
|
|
z row_start row_end
|
|
5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
|
|
insert into t1(x) values (1);
|
|
insert into t2(y) values (1);
|
|
update t1 set x= x + 1;
|
|
update t1 set row_start= '1971-01-01 00:00:00';
|
|
ERROR 42S22: Unknown column 'row_start' in 'SET'
|
|
update t2 set row_start= '1971-01-01 00:00:00';
|
|
ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
|
|
insert t1 (x) values (2) on duplicate key update x= 3, row_end= '1970-01-01 00:00:00';
|
|
ERROR 42S22: Unknown column 'row_end' in 'UPDATE'
|
|
insert t2 (y) values (1) on duplicate key update y= 3, row_end= '1970-01-01 00:00:00';
|
|
ERROR HY000: The value specified for generated column 'row_end' in table 't2' has been ignored
|
|
insert t2 (y,row_end) values (1, '1970-01-01 00:00:00') on duplicate key update y= 3;
|
|
ERROR HY000: The value specified for generated column 'row_end' in table 't2' has been ignored
|
|
insert into t1 values (4);
|
|
insert into t1 set x= 5, row_start= '1980-01-01 00:00:00', row_end= '1980-01-01 00:00:01';
|
|
insert into t1(x, row_start, row_end) values (6, '1980-01-01 00:00:01', '1980-01-01 00:00:00');
|
|
ERROR HY000: Incorrect system-version range 'row_start' value: '1980-01-01 00:00:01.000000' and 'row_end' value: '1980-01-01 00:00:00.000000'
|
|
insert into t1(x, row_start, row_end) values (7, '1980-01-01 00:00:11', '1980-01-01 00:00:11');
|
|
ERROR HY000: Incorrect system-version range 'row_start' value: '1980-01-01 00:00:11.000000' and 'row_end' value: '1980-01-01 00:00:11.000000'
|
|
insert into t1(x, row_start) values (8, '1980-01-01 00:00:22');
|
|
insert into t1(x, row_end) values (9, '1980-01-01 00:00:33');
|
|
ERROR HY000: Incorrect system-version range 'row_start' value: 'now'
|
|
insert into t1(x, row_end) values (10, MAX_TIMESTAMP);
|
|
select x, check_row_ts(row_start, row_end) from t1 for system_time all order by x;
|
|
x check_row_ts(row_start, row_end)
|
|
1 HISTORICAL ROW
|
|
2 CURRENT ROW
|
|
3 HISTORICAL ROW
|
|
4 CURRENT ROW
|
|
5 HISTORICAL ROW
|
|
8 CURRENT ROW
|
|
10 CURRENT ROW
|
|
select x, row_start, row_end from t1 for system_time all
|
|
where x > 1 and row_end < MAX_TIMESTAMP order by x, row_start, row_end;
|
|
x row_start row_end
|
|
3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
|
|
5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
|
|
# Direct insert is not possible for TRX_ID versioning
|
|
create or replace table t2(y int primary key,
|
|
row_start bigint unsigned as row start,
|
|
row_end bigint unsigned as row end,
|
|
period for system_time (row_start, row_end))
|
|
with system versioning engine innodb;
|
|
insert into t2(y, row_start, row_end) values (0, 1, 2);
|
|
ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
|
|
set @@system_versioning_insert_history= 0;
|
|
## INSERT..SELECT
|
|
create or replace table t2 like t1;
|
|
set @@system_versioning_insert_history= 1;
|
|
insert into t2 (x, row_start, row_end) select x, row_start, row_end from t1 for system_time all;
|
|
select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
|
|
x check_row_ts(row_start, row_end)
|
|
1 HISTORICAL ROW
|
|
2 CURRENT ROW
|
|
3 HISTORICAL ROW
|
|
4 CURRENT ROW
|
|
5 HISTORICAL ROW
|
|
8 CURRENT ROW
|
|
10 CURRENT ROW
|
|
select x, row_start, row_end from t2 for system_time all
|
|
where x > 1 and row_end < MAX_TIMESTAMP order by x, row_start, row_end;
|
|
x row_start row_end
|
|
3 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
|
|
5 1980-01-01 00:00:00.000000 1980-01-01 00:00:01.000000
|
|
set @@system_versioning_insert_history= 0;
|
|
# REPLACE / REPLACE .. SELECT
|
|
create or replace table t2(a int primary key,
|
|
row_start timestamp(6) as row start invisible,
|
|
row_end timestamp(6) as row end invisible,
|
|
period for system_time (row_start, row_end))
|
|
with system versioning;
|
|
replace into t2 (a, row_start, row_end) values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
|
|
replace into t2 (a, row_start, row_end) select x, row_start, row_end from t1;
|
|
ERROR HY000: The value specified for generated column 'row_start' in table 't2' has been ignored
|
|
create or replace table t2 (a int primary key) with system versioning;
|
|
replace into t2 (a, row_start, row_end) values (1, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR 42S22: Unknown column 'row_start' in 'INSERT INTO'
|
|
replace into t2 (a, row_start, row_end) select x, row_start, row_end from t1;
|
|
ERROR 42S22: Unknown column 'row_start' in 'INSERT INTO'
|
|
set @@system_versioning_insert_history= 1;
|
|
# REPLACE ignores system_versioning_insert_history
|
|
replace into t2 (a, row_end) values (0, '1980-01-01 00:00:00');
|
|
ERROR 42S22: Unknown column 'row_end' in 'INSERT INTO'
|
|
replace into t3 (z, row_start) values (0, '1980-01-01 00:00:00');
|
|
ERROR HY000: The value specified for generated column 'row_start' in table 't3' has been ignored
|
|
replace into t3 values (0, '1980-01-01 00:00:00', '1981-01-01 00:00:00');
|
|
ERROR HY000: The value specified for generated column 'row_start' in table 't3' has been ignored
|
|
# LOAD DATA
|
|
select x, row_start, row_end into outfile 'DATAFILE' from t1 for system_time all;
|
|
create or replace table t2 like t1;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`x` int(11) NOT NULL,
|
|
PRIMARY KEY (`x`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
|
|
set @@system_versioning_insert_history= 1;
|
|
show create table t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`x` int(11) NOT NULL,
|
|
PRIMARY KEY (`x`)
|
|
) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
|
|
load data infile 'DATAFILE' into table t2 (x, row_start, row_end);
|
|
select x, check_row_ts(row_start, row_end) from t2 for system_time all order by x;
|
|
x check_row_ts(row_start, row_end)
|
|
1 HISTORICAL ROW
|
|
2 CURRENT ROW
|
|
3 HISTORICAL ROW
|
|
4 CURRENT ROW
|
|
5 HISTORICAL ROW
|
|
8 CURRENT ROW
|
|
10 CURRENT ROW
|
|
select row_start = '1980-01-01 00:00:00', row_end = '1980-01-01 00:00:01' from t2 for system_time all where x = 3;
|
|
row_start = '1980-01-01 00:00:00' row_end = '1980-01-01 00:00:01'
|
|
1 1
|
|
# Honor secure_timestamp option
|
|
# restart: --secure-timestamp=YES
|
|
set @@system_versioning_insert_history= 1;
|
|
insert into t3(z, row_start, row_end) values (8, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR HY000: The MariaDB server is running with the --secure-timestamp=YES option so it cannot execute this statement
|
|
insert into t3 values (8, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR HY000: The MariaDB server is running with the --secure-timestamp=YES option so it cannot execute this statement
|
|
# restart: --secure-timestamp=REPLICATION
|
|
create user nobody;
|
|
grant all privileges on test.* to nobody;
|
|
set @@system_versioning_insert_history= 1;
|
|
insert into test.t3(z, row_start, row_end) values (9, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR 42000: Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation
|
|
insert into test.t3 values (9, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR 42000: Access denied; you need (at least one of) the BINLOG REPLAY privilege(s) for this operation
|
|
# restart: --secure-timestamp=SUPER
|
|
set @@system_versioning_insert_history= 1;
|
|
insert into test.t3(z, row_start, row_end) values (10, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
set @@system_versioning_insert_history= 1;
|
|
insert into test.t3(z, row_start, row_end) values (7, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation
|
|
insert into test.t3 values (7, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER, BINLOG REPLAY privilege(s) for this operation
|
|
use test;
|
|
# restart: --secure-timestamp=NO
|
|
drop tables t1, t2, t3;
|
|
#
|
|
# MDEV-29813 REPLACE/IGNORE does not work with historical records in InnoDB
|
|
#
|
|
set sql_mode='STRICT_ALL_TABLES';
|
|
create or replace table t1 (a int) with system versioning;
|
|
set system_versioning_insert_history= on;
|
|
insert into t1 (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
|
|
select a,row_start,row_end into outfile 'mdev29813.txt' from t1 for system_time all;
|
|
create or replace table t1 (a int primary key) with system versioning;
|
|
load data infile 'mdev29813.txt' ignore into table t1 (a,row_start,row_end);
|
|
Warnings:
|
|
Warning 1062 Duplicate entry '1' for key 'PRIMARY'
|
|
select a,row_start,row_end from t1 for system_time all;
|
|
a row_start row_end
|
|
1 2022-01-01 00:00:00.000000 2023-01-01 00:00:00.000000
|
|
create or replace table t1 (a int primary key) with system versioning;
|
|
insert ignore into t1 (a,row_start,row_end) values (1,'2022-01-01','2023-01-01'),(1,'2022-01-01','2023-01-01');
|
|
Warnings:
|
|
Warning 1062 Duplicate entry '1' for key 'PRIMARY'
|
|
select a,row_start,row_end from t1 for system_time all;
|
|
a row_start row_end
|
|
1 2022-01-01 00:00:00.000000 2023-01-01 00:00:00.000000
|
|
drop table t1;
|
|
set sql_mode=default;
|