include/master-slave.inc
[connection master]
connection slave;
set @@session.time_zone='+00:00';
connection master;
CREATE TABLE t1 (x int) with system versioning;
insert into t1 values (1);
select * from t1 order by x;
x
1
delete from t1;
select * from t1 order by x;
x
select * from t1 for system_time all order by row_end, x;
x
1
connection slave;
select * from t1 order by x;
x
select * from t1 for system_time all order by row_end, x;
x
1
connection master;
insert into t1 values (2);
connection slave;
select * from t1 order by x;
x
2
connection master;
update t1 set x = 3;
connection slave;
select * from t1 order by x;
x
3
select * from t1 for system_time all order by row_end, x;
x
1
2
3
# check unversioned -> versioned replication
connection master;
create or replace table t1 (x int primary key);
connection slave;
alter table t1 with system versioning;
connection master;
insert into t1 values (1);
connection slave;
select * from t1 order by x;
x
1
select * from t1 for system_time all order by row_end, x;
x
1
connection master;
update t1 set x= 2 where x = 1;
connection slave;
select * from t1 order by x;
x
2
select * from t1 for system_time all order by row_end, x;
x
1
2
connection master;
delete from t1;
connection slave;
select * from t1 order by x;
x
select * from t1 for system_time all order by row_end, x;
x
1
2
# same thing (UPDATE, DELETE), but without PK
connection master;
create or replace table t1 (x int);
connection slave;
alter table t1 with system versioning;
connection master;
insert into t1 values (1);
update t1 set x= 2 where x = 1;
connection slave;
select * from t1 order by x;
x
2
select * from t1 for system_time all order by row_end, x;
x
1
2
connection master;
delete from t1;
connection slave;
select * from t1 order by x;
x
select * from t1 for system_time all order by row_end, x;
x
1
2
# multi-update
connection master;
create or replace table t1 (x int) with system versioning;
create or replace table t2 (x int) with system versioning;
insert into t1 values (1);
insert into t2 values (2);
update t1, t2 set t1.x=11, t2.x=22;
connection slave;
select * from t1 order by x;
x
11
select * from t2 order by x;
x
22
select * from t1 for system_time all order by row_end, x;
x
1
11
select * from t2 for system_time all order by row_end, x;
x
2
22
# MDEV-14767 system_versioning_alter_history breaks ALTER replication
## Case 1: KEEP on the master, ALTER will work on the slave
connection master;
create or replace table t1 (a int) with system versioning;
set system_versioning_alter_history= KEEP;
alter table t1 add column b int;
connection slave;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
## Case 2: ERROR on the master, it'll fail on the master, the slave won't see it
connection master;
set system_versioning_alter_history= ERROR;
alter table t1 drop column b;
ERROR HY000: Not allowed for system-versioned `test`.`t1`. Change @@system_versioning_alter_history to proceed with ALTER.
connection slave;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
## Case 3: table is not versioned on the master, ALTER will work on the slave
connection master;
create or replace table t1 (a int);
connection slave;
create or replace table t1 (a int) with system versioning;
connection master;
alter table t1 add column b int;
connection slave;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
connection master;
drop table t1, t2;
create table t1 (i int) with system versioning partition by system_time limit 8 ( partition p1 history, partition p2 history, partition pn current );
insert into t1 values (1);
update t1 set i = 1;
update t1 set i = 0;
connection slave;
connection master;
drop table t1;
# check versioned -> versioned replication without any keys on duplicate records
connection master;
create table t1 (a INT) with system versioning;
insert into t1 values (1);
insert into t1 values (1);
delete from t1;
connection slave;
include/diff_tables.inc [master:test.t1,slave:test.t1]
connection master;
drop table t1;
connection slave;
# check unversioned -> versioned replication with non-unique keys on duplicate records
connection master;
set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b));
connection slave;
set statement sql_log_bin=0 for create table t1 (a INT NOT NULL, b INT, INDEX(a,b)) with system versioning;
connection master;
insert into t1 values (1,1);
insert into t1 values (1,1);
delete from t1;
connection slave;
include/diff_tables.inc [master:test.t1,slave:test.t1]
connection master;
drop table t1;
#
# MDEV-31313 SYSTEM VERSIONING and FOREIGN KEY CASCADE create orphan rows on replica
#
create table parent (
id int(11) not null auto_increment,
processdate datetime default null,
primary key (id)
) engine=innodb with system versioning;
set timestamp= unix_timestamp('2000-01-01 00:00:00');
insert into parent values (1, now());
create table child (
id int(11) not null auto_increment,
ch_name varchar(30),
andreid int(11) default null,
primary key (id),
key andreid (andreid),
constraint fk_andreid foreign key (andreid) references parent (id) on delete cascade
) engine=innodb  with system versioning;
set timestamp= unix_timestamp('2000-01-01 00:00:01');
insert into child values (null, 'vimtomar', 1);
set timestamp= unix_timestamp('2000-01-01 00:00:02');
delete from parent where id = 1;
select check_row(row_start, row_end) from parent for system_time all;
check_row(row_start, row_end)
HISTORICAL ROW
select check_row(row_start, row_end) from child for system_time all;
check_row(row_start, row_end)
HISTORICAL ROW
select * from child;
id	ch_name	andreid
select * from parent;
id	processdate
connection slave;
select check_row_slave(row_start, row_end) from parent for system_time all;
check_row_slave(row_start, row_end)
HISTORICAL ROW
select check_row_slave(row_start, row_end) from child for system_time all;
check_row_slave(row_start, row_end)
HISTORICAL ROW
select * from child;
id	ch_name	andreid
select * from parent;
id	processdate
connection master;
set timestamp= default;
drop table child;
drop table parent;
connection slave;
connection master;
#
# MDEV-17554 Auto-create new partition for system versioned tables
#            with history partitioned by INTERVAL/LIMIT
#
set timestamp= unix_timestamp('2000-01-01 00:00:00');
create or replace table t1 (x int) with system versioning
partition by system_time interval 1 hour auto;
insert t1 values ();
set timestamp= unix_timestamp('2000-01-01 01:00:00');
delete from t1;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `x` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
PARTITIONS 3
connection slave;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `x` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2000-01-01 00:00:00' AUTO
PARTITIONS 3
connection master;
drop table t1;
set timestamp= default;
#
# MDEV-25477 Auto-create breaks replication when triggering event was not replicated
#
set timestamp= unix_timestamp('2001-01-01 01:00:00');
# ROLLBACK
create table t (a int) with system versioning
partition by system_time interval 1 hour auto;
insert into t values (1), (2);
set @@timestamp= @@timestamp + 3601;
start transaction;
delete from t;
rollback;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 01:00:00' AUTO
PARTITIONS 3
connection slave;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 01:00:00' AUTO
PARTITIONS 3
connection master;
alter table t drop partition p0;
connection slave;
# INSERT .. ODKU
connection master;
create or replace table t (a int primary key) with system versioning
partition by system_time interval 1 hour auto;
insert into t values (1), (2);
set @@timestamp= @@timestamp + 3601;
insert into t values (1) on duplicate key update a= a;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 02:00:00' AUTO
PARTITIONS 3
connection slave;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 02:00:00' AUTO
PARTITIONS 3
connection master;
alter table t drop partition p0;
connection slave;
# INSERT .. SELECT .. ODKU
connection master;
create or replace table t (a int primary key) with system versioning
partition by system_time interval 1 hour auto;
insert into t values (1), (2);
set @@timestamp= @@timestamp + 3601;
call mtr.add_suppression("Unsafe statement written to the binary log");
insert t select a from t where a = 1 limit 0 on duplicate key update a= 1;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 03:00:00' AUTO
PARTITIONS 3
connection slave;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 03:00:00' AUTO
PARTITIONS 3
connection master;
alter table t drop partition p0;
connection slave;
# UPDATE
connection master;
create or replace table t (a int) with system versioning
partition by system_time interval 1 hour auto;
insert into t values (1), (2);
set @@timestamp= @@timestamp + 3601;
update t set a= 3 limit 0;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 04:00:00' AUTO
PARTITIONS 3
connection slave;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 04:00:00' AUTO
PARTITIONS 3
connection master;
alter table t drop partition p0;
connection slave;
# DELETE
connection master;
create or replace table t (a int) with system versioning
partition by system_time interval 1 hour auto;
insert into t values (1), (2);
set @@timestamp= @@timestamp + 3601;
delete from t limit 0;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 05:00:00' AUTO
PARTITIONS 3
connection slave;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 05:00:00' AUTO
PARTITIONS 3
connection master;
alter table t drop partition p0;
connection slave;
# Multi-update
connection master;
create or replace table t (a int) with system versioning
partition by system_time interval 1 hour auto;
create or replace table t2 (b int);
insert into t values (0), (1);
insert into t2 values (10), (20);
set @@timestamp= @@timestamp + 3601;
update t left join t2 on a > b set a= 4;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 06:00:00' AUTO
PARTITIONS 3
connection slave;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 06:00:00' AUTO
PARTITIONS 3
connection master;
alter table t drop partition p0;
connection slave;
# Multi-delete
connection master;
create or replace table t (a int) with system versioning
partition by system_time interval 1 hour auto;
create or replace table t2 (b int);
insert into t values (0), (1);
insert into t2 values (10), (20);
set @@timestamp= @@timestamp + 3601;
delete t, t2 from t join t2 where a > b;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 07:00:00' AUTO
PARTITIONS 3
connection slave;
show create table t;
Table	Create Table
t	CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL
) ENGINE=ENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci WITH SYSTEM VERSIONING
 PARTITION BY SYSTEM_TIME INTERVAL 1 HOUR STARTS TIMESTAMP'2001-01-01 07:00:00' AUTO
PARTITIONS 3
connection master;
alter table t drop partition p0;
connection slave;
connection master;
drop tables t, t2;
set timestamp= default;
#
# MDEV-16546 System versioning setting to allow history modification
#
create table t1(x int) with system versioning;
insert into t1(x) values (1);
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 'field list'
set @@system_versioning_insert_history= 1;
insert into t1(x, row_start, row_end) values (3, '1980-01-01 00:00:00', '1980-01-01 00:00:01');
update t1 set x= x + 1;
connection slave;
set @@session.time_zone='+00:00';
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
select row_start = '1980-01-01 00:00:00', row_end = '1980-01-01 00:00:01' from t1 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
## INSERT..SELECT
connection master;
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;
connection slave;
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
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
# LOAD DATA
connection master;
select x, row_start, row_end into outfile 'DATAFILE' from t1 for system_time all;
create or replace table t3 like t1;
set @@system_versioning_insert_history= 1;
load data infile 'DATAFILE' into table t3 (x, row_start, row_end);
connection slave;
select x, check_row_ts(row_start, row_end) from t3 for system_time all order by x;
x	check_row_ts(row_start, row_end)
1	HISTORICAL ROW
2	CURRENT ROW
3	HISTORICAL ROW
select row_start = '1980-01-01 00:00:00', row_end = '1980-01-01 00:00:01' from t3 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
# why a slave cannot have system_versioning_insert_history always on
connection master;
set @@system_versioning_insert_history= 0;
set sql_mode='';
create or replace table t1 (a int,
rs timestamp(6) as row start, re timestamp(6) as row end,
period for system_time (rs,re)) with system versioning;
insert t1 values (1, '2000-01-01 02:03:04', '2001-01-01 02:03.04');
Warnings:
Warning	1906	The value specified for generated column 'rs' in table 't1' has been ignored
Warning	1906	The value specified for generated column 're' in table 't1' has been ignored
select a,check_row_ts(rs,re) from t1 for system_time all;
a	check_row_ts(rs,re)
1	CURRENT ROW
connection slave;
select a,check_row_ts(rs,re) from t1 for system_time all;
a	check_row_ts(rs,re)
1	CURRENT ROW
set sql_mode=default;
connection master;
drop tables t1, t2, t3;
include/rpl_end.inc