Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
It is obvious from MDEV-16546 specification (although unfortunately not from the variable name) that system_versioning_insert_history only applies to timestamp-based versioning.
However, --dump-history option in mysqldump applies to both timestamp-based and precision versioning, and it dumps the history for all versioned tables.
So, the dump would contain a mix of current and historical records. Upon loading it, the values for row start/row end in precision-versioned tables will be ignored, and depending on the structure and data, the table will either end up with outdated values, or INSERT will fail due to duplicate keys.
--source include/have_innodb.inc
|
|
create table t1 (a int, f varchar(8), s bigint unsigned as row start, e bigint unsigned as row end, period for system_time(s,e)) engine=InnoDB with system versioning; |
insert into t1 (a,f) values (1,'foo'); |
update t1 set f = 'bar'; |
create table t2 (pk int primary key, f varchar(8), s bigint unsigned as row start, e bigint unsigned as row end, period for system_time(s,e)) engine=InnoDB with system versioning; |
insert into t2 (pk,f) values (1,'foo'); |
update t2 set f = 'bar'; |
|
--exec $MYSQL_DUMP test --dump-history --tables t1 > $MYSQL_TMP_DIR/t1.dump
|
--exec $MYSQL_DUMP test --dump-history --tables t2 > $MYSQL_TMP_DIR/t2.dump
|
|
select * from t1; |
drop table t1; |
--echo # Loading t1 dump
|
--exec $MYSQL test < $MYSQL_TMP_DIR/t1.dump
|
select * from t1; |
|
select * from t2; |
drop table t2; |
--echo # Loading t2 dump
|
--exec $MYSQL test < $MYSQL_TMP_DIR/t2.dump
|
select * from t2; |
|
# Cleanup
|
drop table t1, t2; |
bb-10.11-MDEV-16546 2b1d3242 |
select * from t1; |
a f s e
|
1 bar 25 18446744073709551615
|
drop table t1; |
# Loading t1 dump
|
select * from t1; |
a f s e
|
1 bar 50 18446744073709551615
|
1 foo 50 18446744073709551615
|
select * from t2; |
pk f s e
|
1 bar 38 18446744073709551615
|
drop table t2; |
# Loading t2 dump
|
mysqltest: At line 22: exec of '<basedir>/client//mysql --defaults-file=<basedir>/mysql-test/var/my.cnf test < <basedir>/mysql-test/var/tmp/t2.dump' failed, error: 256, status: 1, errno: 11 |
|
select * from t2; |
pk f s e
|
Attachments
Issue Links
- is caused by
-
MDEV-16029 mysqldump: dump and restore historical data
- Closed
- relates to
-
MDEV-16546 System versioning setting to allow history modification
- Closed