mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
597 lines
20 KiB
Text
597 lines
20 KiB
Text
set default_storage_engine= innodb;
|
|
create or replace table t1 (
|
|
x int,
|
|
sys_trx_start bigint(20) unsigned as row start invisible,
|
|
sys_trx_end bigint(20) unsigned as row end invisible,
|
|
period for system_time (sys_trx_start, sys_trx_end)
|
|
) with system versioning;
|
|
# No history inside the transaction
|
|
start transaction;
|
|
insert into t1 (x) values (1);
|
|
update t1 set x= x + 1;
|
|
update t1 set x= x + 1;
|
|
commit;
|
|
select *, sys_trx_start > 1, sys_trx_end from t1 for system_time all;
|
|
x sys_trx_start > 1 sys_trx_end
|
|
3 1 18446744073709551615
|
|
# ALTER ADD SYSTEM VERSIONING should write to mysql.transaction_registry
|
|
set @@system_versioning_alter_history=keep;
|
|
create or replace table t1 (x int);
|
|
insert into t1 values (1);
|
|
alter table t1
|
|
add column s bigint unsigned as row start,
|
|
add column e bigint unsigned as row end,
|
|
add period for system_time(s, e),
|
|
add system versioning,
|
|
algorithm=inplace;
|
|
select s from t1 into @trx_start;
|
|
select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start;
|
|
count(*) = 1
|
|
1
|
|
create or replace table t1 (x int);
|
|
select count(*) from mysql.transaction_registry into @tmp;
|
|
alter table t1
|
|
add column s bigint unsigned as row start,
|
|
add column e bigint unsigned as row end,
|
|
add period for system_time(s, e),
|
|
add system versioning,
|
|
algorithm=inplace;
|
|
select count(*) = @tmp from mysql.transaction_registry;
|
|
count(*) = @tmp
|
|
1
|
|
create or replace table t1 (x int);
|
|
insert into t1 values (1);
|
|
alter table t1
|
|
add column s bigint unsigned as row start,
|
|
add column e bigint unsigned as row end,
|
|
add period for system_time(s, e),
|
|
add system versioning,
|
|
algorithm=copy;
|
|
select s from t1 into @trx_start;
|
|
select count(*) = 1 from mysql.transaction_registry where transaction_id = @trx_start;
|
|
count(*) = 1
|
|
1
|
|
create or replace table t1 (x int);
|
|
select count(*) from mysql.transaction_registry into @tmp;
|
|
alter table t1
|
|
add column s bigint unsigned as row start,
|
|
add column e bigint unsigned as row end,
|
|
add period for system_time(s, e),
|
|
add system versioning,
|
|
algorithm=copy;
|
|
select count(*) = @tmp + 1 from mysql.transaction_registry;
|
|
count(*) = @tmp + 1
|
|
1
|
|
# TRX_ID to TIMESTAMP versioning switch
|
|
create or replace table t1 (
|
|
x int,
|
|
sys_start bigint unsigned as row start invisible,
|
|
sys_end bigint unsigned as row end invisible,
|
|
period for system_time (sys_start, sys_end)
|
|
) with system versioning;
|
|
insert into t1 values (1);
|
|
alter table t1 drop column sys_start, drop column sys_end;
|
|
select row_end = 18446744073709551615 as transaction_based from t1 for system_time all;
|
|
transaction_based
|
|
1
|
|
# Simple vs SEES algorithms
|
|
create or replace table t1 (
|
|
x int,
|
|
sys_start bigint(20) unsigned as row start invisible,
|
|
sys_end bigint(20) unsigned as row end invisible,
|
|
period for system_time (sys_start, sys_end)
|
|
) with system versioning;
|
|
INSERT INTO t1 VALUES(100);
|
|
set transaction isolation level read committed;
|
|
start transaction;
|
|
insert into t1 values (1);
|
|
connect con1,localhost,root,,test;
|
|
set transaction isolation level read committed;
|
|
start transaction;
|
|
insert into t1 values (2);
|
|
connect con2,localhost,root,,test;
|
|
set transaction isolation level read committed;
|
|
start transaction;
|
|
insert into t1 values (3);
|
|
commit;
|
|
disconnect con2;
|
|
connection default;
|
|
set @ts1= sysdate(6);
|
|
connection con1;
|
|
commit;
|
|
disconnect con1;
|
|
connection default;
|
|
set @ts2= sysdate(6);
|
|
commit;
|
|
set @ts3= sysdate(6);
|
|
select sys_start from t1 where x = 1 into @trx_id1;
|
|
select sys_start from t1 where x = 2 into @trx_id2;
|
|
select sys_start from t1 where x = 3 into @trx_id3;
|
|
select @trx_id1 < @trx_id2, @trx_id2 < @trx_id3;
|
|
@trx_id1 < @trx_id2 @trx_id2 < @trx_id3
|
|
1 1
|
|
select @ts1 < @ts2, @ts2 < @ts3;
|
|
@ts1 < @ts2 @ts2 < @ts3
|
|
1 1
|
|
# MVCC is resolved
|
|
select * from t1 for system_time as of transaction @trx_id1;
|
|
x
|
|
100
|
|
1
|
|
2
|
|
3
|
|
select * from t1 for system_time as of timestamp @ts1;
|
|
x
|
|
100
|
|
3
|
|
select * from t1 for system_time as of transaction @trx_id2;
|
|
x
|
|
100
|
|
2
|
|
3
|
|
select * from t1 for system_time as of timestamp @ts2;
|
|
x
|
|
100
|
|
2
|
|
3
|
|
select * from t1 for system_time as of transaction @trx_id3;
|
|
x
|
|
100
|
|
3
|
|
select * from t1 for system_time as of timestamp @ts3;
|
|
x
|
|
100
|
|
1
|
|
2
|
|
3
|
|
#
|
|
# MDEV-15427 IB: TRX_ID based operations inside transaction generate history
|
|
#
|
|
create or replace table t1(
|
|
x int(10),
|
|
row_start bigint(20) unsigned as row start,
|
|
row_end bigint(20) unsigned as row end,
|
|
period for system_time(row_start, row_end)
|
|
) with system versioning;
|
|
begin;
|
|
insert into t1 (x) values (1);
|
|
delete from t1;
|
|
commit;
|
|
select x from t1 for system_time all;
|
|
x
|
|
insert into t1 (x) values (2);
|
|
begin;
|
|
update t1 set x= 3;
|
|
update t1 set x= 4;
|
|
commit;
|
|
select x, row_start < row_end from t1 for system_time all;
|
|
x row_start < row_end
|
|
4 1
|
|
2 1
|
|
#
|
|
# MDEV-15951 system versioning by trx id doesn't work with partitioning
|
|
# currently trx_id does not support partitioning by system_time
|
|
#
|
|
create or replace table t1(
|
|
i int,
|
|
row_start bigint unsigned generated always as row start,
|
|
row_end bigint unsigned generated always as row end,
|
|
period for system_time(row_start, row_end)
|
|
) engine=InnoDB with system versioning partition by system_time (
|
|
partition p0 history,
|
|
partition pn current
|
|
);
|
|
ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1`
|
|
create or replace table t1(
|
|
i int,
|
|
row_start bigint unsigned generated always as row start,
|
|
row_end bigint unsigned generated always as row end,
|
|
period for system_time(row_start, row_end)
|
|
) engine=InnoDB with system versioning;
|
|
alter table t1 partition by system_time (
|
|
partition p0 history,
|
|
partition pn current
|
|
);
|
|
ERROR HY000: `row_start` must be of type TIMESTAMP(6) for system-versioned table `t1`
|
|
drop table t1;
|
|
create or replace table t (
|
|
a int primary key,
|
|
row_start bigint unsigned as row start invisible,
|
|
row_end bigint unsigned as row end invisible,
|
|
period for system_time(row_start, row_end)
|
|
) engine=innodb with system versioning
|
|
partition by key() (
|
|
partition p1,
|
|
partition p2
|
|
);
|
|
ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
|
|
create or replace table t (
|
|
a int primary key,
|
|
row_start bigint unsigned as row start invisible,
|
|
row_end bigint unsigned as row end invisible,
|
|
period for system_time(row_start, row_end)
|
|
) engine=innodb with system versioning
|
|
partition by key(a, row_start) (
|
|
partition p1,
|
|
partition p2
|
|
);
|
|
ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
|
|
create or replace table t (
|
|
a int primary key,
|
|
row_start bigint unsigned as row start invisible,
|
|
row_end bigint unsigned as row end invisible,
|
|
period for system_time(row_start, row_end)
|
|
) engine=innodb with system versioning
|
|
partition by hash(a + row_end * 2) (
|
|
partition p1,
|
|
partition p2
|
|
);
|
|
ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
|
|
create or replace table t (
|
|
a int primary key,
|
|
row_start bigint unsigned as row start invisible,
|
|
row_end bigint unsigned as row end invisible,
|
|
period for system_time(row_start, row_end)
|
|
) engine=innodb with system versioning
|
|
partition by range columns (a, row_start) (
|
|
partition p1 values less than (100, 100)
|
|
);
|
|
ERROR HY000: Transaction-precise system-versioned tables do not support partitioning by ROW START or ROW END
|
|
#
|
|
# MDEV-16010 Too many rows with AS OF point_in_the_past_or_NULL
|
|
#
|
|
create or replace table t1 (
|
|
x int,
|
|
row_start bigint unsigned as row start invisible,
|
|
row_end bigint unsigned as row end invisible,
|
|
period for system_time (row_start, row_end)
|
|
) with system versioning engine innodb;
|
|
insert into t1 (x) values (1);
|
|
delete from t1;
|
|
select * from t1 for system_time as of timestamp'1990-1-1 00:00';
|
|
x
|
|
select * from t1 for system_time as of NULL;
|
|
x
|
|
# MDEV-16024 transaction_registry.begin_timestamp is wrong for explicit transactions
|
|
create or replace table t1 (
|
|
x int(11) default null,
|
|
row_start bigint(20) unsigned generated always as row start invisible,
|
|
row_end bigint(20) unsigned generated always as row end invisible,
|
|
period for system_time (row_start, row_end)
|
|
) engine=innodb with system versioning;
|
|
begin;
|
|
set @ts1= now(6);
|
|
insert into t1 values (1);
|
|
commit;
|
|
select row_start from t1 into @trx_id;
|
|
select trt_begin_ts(@trx_id) <= @ts1 as BEGIN_TS_GOOD;
|
|
BEGIN_TS_GOOD
|
|
1
|
|
drop table t1;
|
|
#
|
|
# MDEV-16100 FOR SYSTEM_TIME erroneously resolves string user variables as transaction IDs
|
|
#
|
|
CREATE TABLE t1 (
|
|
x INT,
|
|
sys_trx_start BIGINT UNSIGNED AS ROW START,
|
|
sys_trx_end BIGINT UNSIGNED AS ROW END,
|
|
PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
|
|
) WITH SYSTEM VERSIONING ENGINE=INNODB;
|
|
INSERT INTO t1 (x) VALUES (1);
|
|
SET @ts= DATE_ADD(NOW(), INTERVAL 1 YEAR);
|
|
EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION @ts;
|
|
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
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME AS OF TRANSACTION @`ts` where trt_trx_sees(`test`.`t1`.`sys_trx_end`,@`ts`) and trt_trx_sees_eq(@`ts`,`test`.`t1`.`sys_trx_start`)
|
|
EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF TIMESTAMP @ts;
|
|
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
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts` where trt_trx_sees(`test`.`t1`.`sys_trx_end`,<cache>(trt_trx_id(@`ts`))) and trt_trx_sees_eq(<cache>(trt_trx_id(@`ts`)),`test`.`t1`.`sys_trx_start`)
|
|
EXPLAIN EXTENDED SELECT x FROM t1 FOR SYSTEM_TIME AS OF @ts;
|
|
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
|
|
Warnings:
|
|
Note 1003 select `test`.`t1`.`x` AS `x` from `test`.`t1` FOR SYSTEM_TIME AS OF TIMESTAMP @`ts` where trt_trx_sees(`test`.`t1`.`sys_trx_end`,<cache>(trt_trx_id(@`ts`))) and trt_trx_sees_eq(<cache>(trt_trx_id(@`ts`)),`test`.`t1`.`sys_trx_start`)
|
|
DROP TABLE t1;
|
|
#
|
|
# Testing AS OF with expressions of various kinds and data types
|
|
#
|
|
CREATE TABLE t1
|
|
(
|
|
x INT,
|
|
sys_trx_start BIGINT UNSIGNED AS ROW START INVISIBLE,
|
|
sys_trx_end BIGINT UNSIGNED AS ROW END INVISIBLE,
|
|
PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
|
|
) WITH SYSTEM VERSIONING;
|
|
INSERT INTO t1 VALUES (1);
|
|
CREATE TABLE t2
|
|
(
|
|
x INT,
|
|
sys_trx_start TIMESTAMP(6) AS ROW START INVISIBLE,
|
|
sys_trx_end TIMESTAMP(6) AS ROW END INVISIBLE,
|
|
PERIOD FOR SYSTEM_TIME (sys_trx_start, sys_trx_end)
|
|
) WITH SYSTEM VERSIONING;
|
|
INSERT INTO t2 VALUES (1);
|
|
#
|
|
# ROW is not supported
|
|
#
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF (1,1);
|
|
ERROR HY000: Illegal parameter data type row for operation 'FOR SYSTEM_TIME'
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF (1,1);
|
|
ERROR HY000: Illegal parameter data type row for operation 'FOR SYSTEM_TIME'
|
|
#
|
|
# DOUBLE is not supported, use explicit CAST
|
|
#
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
|
|
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
|
|
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
|
|
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION RAND();
|
|
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION (RAND());
|
|
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(RAND());
|
|
ERROR HY000: Illegal parameter data type double for operation 'FOR SYSTEM_TIME'
|
|
#
|
|
# DECIMAL is not supported, use explicit CAST
|
|
#
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
|
|
ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
|
|
ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION 10.1;
|
|
ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION COALESCE(10.1);
|
|
ERROR HY000: Illegal parameter data type decimal for operation 'FOR SYSTEM_TIME'
|
|
#
|
|
# YEAR is not supported, use explicit CAST
|
|
#
|
|
BEGIN NOT ATOMIC
|
|
DECLARE var YEAR;
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
|
|
END;
|
|
$$
|
|
ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME'
|
|
BEGIN NOT ATOMIC
|
|
DECLARE var YEAR;
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
|
|
END;
|
|
$$
|
|
ERROR HY000: Illegal parameter data type year for operation 'FOR SYSTEM_TIME'
|
|
#
|
|
# ENUM is not supported, use explicit CAST
|
|
#
|
|
BEGIN NOT ATOMIC
|
|
DECLARE var ENUM('xxx') DEFAULT 'xxx';
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
|
|
END;
|
|
$$
|
|
ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME'
|
|
BEGIN NOT ATOMIC
|
|
DECLARE var ENUM('xxx') DEFAULT 'xxx';
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
|
|
END;
|
|
$$
|
|
ERROR HY000: Illegal parameter data type enum for operation 'FOR SYSTEM_TIME'
|
|
#
|
|
# SET is not supported, use explicit CAST
|
|
#
|
|
BEGIN NOT ATOMIC
|
|
DECLARE var SET('xxx') DEFAULT 'xxx';
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF TRANSACTION var;
|
|
END;
|
|
$$
|
|
ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME'
|
|
BEGIN NOT ATOMIC
|
|
DECLARE var SET('xxx') DEFAULT 'xxx';
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
|
|
END;
|
|
$$
|
|
ERROR HY000: Illegal parameter data type set for operation 'FOR SYSTEM_TIME'
|
|
BEGIN NOT ATOMIC
|
|
DECLARE var BIT(10);
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF TRANSACTION var;
|
|
END;
|
|
$$
|
|
ERROR HY000: Transaction-precise system versioning for `t2` is not supported
|
|
#
|
|
# String literals resolve to TIMESTAMP
|
|
#
|
|
SELECT * FROM t1 FOR SYSTEM_TIME AS OF '2107-12-30 00:00:00';
|
|
x
|
|
1
|
|
SELECT * FROM t2 FOR SYSTEM_TIME AS OF '2107-12-30 00:00:00';
|
|
x
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# MDEV-16094 Crash when using AS OF with a stored function
|
|
#
|
|
CREATE FUNCTION fts() RETURNS DATETIME RETURN '2001-01-01 10:20:30';
|
|
CREATE FUNCTION ftx() RETURNS BIGINT UNSIGNED RETURN 1;
|
|
CREATE TABLE ttx
|
|
(
|
|
x INT,
|
|
start_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
|
|
end_timestamp BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
|
|
PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
|
|
) ENGINE=InnoDB WITH SYSTEM VERSIONING;
|
|
CREATE TABLE tts
|
|
(
|
|
x INT,
|
|
start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
|
|
end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
|
|
PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
|
|
) ENGINE=InnoDB WITH SYSTEM VERSIONING;
|
|
SELECT * FROM tts FOR SYSTEM_TIME AS OF fts();
|
|
x start_timestamp end_timestamp
|
|
SELECT * FROM tts FOR SYSTEM_TIME AS OF TRANSACTION ftx();
|
|
ERROR HY000: Transaction-precise system versioning for `tts` is not supported
|
|
SELECT * FROM ttx FOR SYSTEM_TIME AS OF fts();
|
|
x start_timestamp end_timestamp
|
|
SELECT * FROM ttx FOR SYSTEM_TIME AS OF TRANSACTION ftx();
|
|
x start_timestamp end_timestamp
|
|
DROP TABLE tts;
|
|
DROP TABLE ttx;
|
|
DROP FUNCTION fts;
|
|
DROP FUNCTION ftx;
|
|
#
|
|
# MDEV-16330 Allow instant change of WITH SYSTEM VERSIONING column attribute
|
|
#
|
|
SET @@SYSTEM_VERSIONING_ALTER_HISTORY=KEEP;
|
|
CREATE TABLE t (
|
|
a INT,
|
|
b INT,
|
|
row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
|
|
row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
|
|
PERIOD FOR SYSTEM_TIME(row_start, row_end)
|
|
) WITH SYSTEM VERSIONING ENGINE=INNODB;
|
|
INSERT INTO t VALUES (1,1);
|
|
# without table rebuild
|
|
SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
|
|
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
|
|
ON c.table_id=t.table_id
|
|
WHERE t.name='test/t' AND c.name='a';
|
|
prtype
|
|
50179
|
|
ALTER TABLE t
|
|
CHANGE a a INT WITHOUT SYSTEM VERSIONING;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
|
|
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
|
|
ON c.table_id=t.table_id
|
|
WHERE t.name='test/t' AND c.name='a';
|
|
prtype
|
|
1027
|
|
UPDATE t SET a=11;
|
|
SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
|
|
COUNT(*)
|
|
1
|
|
# with table rebuild
|
|
SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
|
|
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
|
|
ON c.table_id=t.table_id
|
|
WHERE t.name='test/t' AND c.name='a';
|
|
prtype
|
|
1027
|
|
ALTER TABLE t
|
|
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.
|
|
SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
|
|
INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_TABLES AS t
|
|
ON c.table_id=t.table_id
|
|
WHERE t.name='test/t' AND c.name='a';
|
|
prtype
|
|
50435
|
|
UPDATE t SET a=1;
|
|
SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
|
|
COUNT(*)
|
|
2
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
`row_start` bigint(20) unsigned GENERATED ALWAYS AS ROW START INVISIBLE,
|
|
`row_end` bigint(20) unsigned GENERATED ALWAYS AS ROW END INVISIBLE,
|
|
PRIMARY KEY (`a`,`row_end`),
|
|
PERIOD FOR SYSTEM_TIME (`row_start`, `row_end`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci WITH SYSTEM VERSIONING
|
|
# handles VIRTUAL columns too
|
|
CREATE OR REPLACE TABLE t (
|
|
a INT AS (b + 1),
|
|
b INT,
|
|
row_start BIGINT UNSIGNED AS ROW START INVISIBLE,
|
|
row_end BIGINT UNSIGNED AS ROW END INVISIBLE,
|
|
PERIOD FOR SYSTEM_TIME(row_start, row_end)
|
|
) WITH SYSTEM VERSIONING ENGINE=INNODB;
|
|
INSERT INTO t VALUES (DEFAULT, 1);
|
|
SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
|
|
INNER 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
|
|
ALTER TABLE t
|
|
CHANGE b b INT WITHOUT SYSTEM VERSIONING;
|
|
affected rows: 0
|
|
info: Records: 0 Duplicates: 0 Warnings: 0
|
|
SELECT c.prtype FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS AS c
|
|
INNER 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
|
|
1027
|
|
UPDATE t SET b=11;
|
|
SELECT COUNT(*) FROM t FOR SYSTEM_TIME ALL;
|
|
COUNT(*)
|
|
1
|
|
DROP TABLE t;
|
|
SET @@SYSTEM_VERSIONING_ALTER_HISTORY=ERROR;
|
|
SELECT count(*) from mysql.transaction_registry where begin_timestamp>=commit_timestamp;
|
|
count(*)
|
|
0
|
|
# MDEV-18875 Assertion `thd->transaction.stmt.ha_list == __null ||
|
|
# trans == &thd->transaction.stmt' failed or bogus ER_DUP_ENTRY upon
|
|
# ALTER TABLE with versioning
|
|
create or replace table t (x int) engine=innodb;
|
|
set autocommit= 0;
|
|
alter table t
|
|
algorithm=copy,
|
|
add column row_start bigint unsigned as row start,
|
|
add column row_end bigint unsigned as row end,
|
|
add period for system_time(row_start,row_end),
|
|
with system versioning;
|
|
set autocommit= 1;
|
|
# MDEV-18865 Assertion `t->first->versioned_by_id()'
|
|
# failed in innodb_prepare_commit_versioned
|
|
create or replace table t (x int) engine=innodb;
|
|
insert into t values (0);
|
|
alter table t add `row_start` bigint unsigned as row start,
|
|
add `row_end` bigint unsigned as row end,
|
|
add period for system_time(`row_start`,`row_end`),
|
|
modify x int after row_start,
|
|
with system versioning;
|
|
drop table t;
|
|
#
|
|
# MDEV-20842 Crash using versioning plugin functions after plugin was removed from server
|
|
#
|
|
uninstall plugin test_versioning;
|
|
select trt_begin_ts(0);
|
|
ERROR 42000: FUNCTION test.trt_begin_ts does not exist
|
|
#
|
|
# MDEV-21650 Non-empty statement transaction on global rollback after TRT update error
|
|
#
|
|
create table t1 (s date, e date, period for app(s,e)) engine=innodb;
|
|
alter table t1
|
|
add row_start bigint unsigned as row start,
|
|
add row_end bigint unsigned as row end,
|
|
add period for system_time(row_start,row_end),
|
|
with system versioning,
|
|
add period if not exists for app(x,y);
|
|
Warnings:
|
|
Note 1060 Duplicate column name 'app'
|
|
set transaction isolation level serializable;
|
|
start transaction;
|
|
insert into t1 (s,e) values ('2021-07-04','2024-08-18');
|
|
connect con1,localhost,root,,test;
|
|
start transaction;
|
|
insert into t1 (s,e) values ('2018-06-01','2021-09-15');
|
|
connection default;
|
|
select * from t1 for system_time as of now();
|
|
ERROR HY000: TRX_ID ... not found in `mysql.transaction_registry`
|
|
connection con1;
|
|
set innodb_lock_wait_timeout= 1, lock_wait_timeout= 1;
|
|
alter table xx;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
disconnect con1;
|
|
connection default;
|
|
drop table t1;
|