Reproduce
--source include/have_innodb.inc
|
|
set timestamp= 1;
|
create table t1 (pk int primary key, x int) engine=innodb with system versioning;
|
create table t2 (pk int primary key, x int) engine=myisam with system versioning;
|
insert into t1 values (1, 0), (2, 0), (3, 0);
|
insert into t2 values (1, 0), (2, 0), (3, 0);
|
|
--connect (con1,localhost,root,,test)
|
set timestamp= 2;
|
replace into t1 values (1, 1), (2, 1);
|
replace into t2 values (1, 1), (2, 1);
|
|
--connection default
|
replace into t1 values (1, 2), (2, 2);
|
replace into t2 values (1, 2), (2, 2);
|
|
--connection con1
|
--error ER_DUP_ENTRY
|
replace into t1 values (3, 3), (1, 3), (2, 3);
|
--error ER_DUP_ENTRY
|
replace into t2 values (3, 3), (1, 3), (2, 3);
|
select *, row_start, row_end from t1 for system_time all order by row_end, pk;
|
select *, row_start, row_end from t2 for system_time all order by row_end, pk;
|
|
# cleanup
|
drop tables t1, t2;
|
Result
t2 changed pk=1 record to x=3, row_start=1.
select *, row_start, row_end from t1 for system_time all order by row_end, pk;
|
pk x row_start row_end
|
1 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
2 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
1 2 1970-01-01 03:00:01.000000 2038-01-19 06:14:07.999999
|
2 2 1970-01-01 03:00:01.000000 2038-01-19 06:14:07.999999
|
3 0 1970-01-01 03:00:01.000000 2038-01-19 06:14:07.999999
|
select *, row_start, row_end from t2 for system_time all order by row_end, pk;
|
pk x row_start row_end
|
1 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
2 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
3 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
1 3 1970-01-01 03:00:02.000000 2038-01-19 06:14:07.999999
|
2 2 1970-01-01 03:00:01.000000 2038-01-19 06:14:07.999999
|
3 3 1970-01-01 03:00:02.000000 2038-01-19 06:14:07.999999
|
Expected
t2 retains pk=1 record values.
select *, row_start, row_end from t1 for system_time all order by row_end, pk;
|
pk x row_start row_end
|
1 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
2 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
1 2 1970-01-01 03:00:01.000000 2038-01-19 06:14:07.999999
|
2 2 1970-01-01 03:00:01.000000 2038-01-19 06:14:07.999999
|
3 0 1970-01-01 03:00:01.000000 2038-01-19 06:14:07.999999
|
select *, row_start, row_end from t2 for system_time all order by row_end, pk;
|
pk x row_start row_end
|
1 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
2 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
3 0 1970-01-01 03:00:01.000000 1970-01-01 03:00:02.000000
|
1 2 1970-01-01 03:00:01.000000 2038-01-19 06:14:07.999999
|
2 2 1970-01-01 03:00:01.000000 2038-01-19 06:14:07.999999
|
3 3 1970-01-01 03:00:02.000000 2038-01-19 06:14:07.999999
|
Notes
Applicable to non-transactional tables only since transactional tables rollback failed command by design. Note that we cannot rollback successfully processed rows, but this is ok since we have history rows for them and the behaviour is identical to non-versioned table.
|
MDEV-17891.patch