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
-
After the fix 6ff8151111, mysqldump throws an error if it runs with --dump-history and encounters a precision-versioned table, and aborts or, in --force mode, proceeds and eventually exits with a non-zero code.
This all is fine, but when it proceeds with --force, it still dumps the history of a precision-versioned table, so the final dump will still have the same initial problem. It leaves no usable way to dump/load a database which contains a mix of temporal and precision-transaction versioned tables, especially if there are a lot of each kind and it's impractical to list them all explicitly.
I think it would make more sense if in --force mode, after throwing the error, mysqldump treated the precision-versioned table as a usual non-versioned table, doing normal SELECT and only dumping the current data.
Also, I think the error message could be improved. Now it is
bb-10.11-MDEV-16546 32090722c
mysqldump: Cannot use --dump-history for table `t1`
Since it's thrown in a specific case of precision-versioned table, there is no reason to make the user guess what the problem is, it would be better just say so, that it cannot use --dump-history because the table is precision-versioned (or whatever they are officially named). Also, a schema name would be nice.