[MDEV-29730] mysqldump --dump-history creates broken dump if there are precision-versioned tables Created: 2022-10-06  Updated: 2022-10-26  Resolved: 2022-10-26

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients, Versioned Tables
Affects Version/s: N/A
Fix Version/s: 10.11.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-16029 mysqldump: dump and restore historica... Closed
Relates
relates to MDEV-16546 System versioning setting to allow hi... Closed

 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



 Comments   
Comment by Elena Stepanova [ 2022-10-16 ]

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.

Comment by Sergei Golubchik [ 2022-10-17 ]
  • treat as a usual non-versioned table — done
  • no reason to make the user guess what the problem is ­— done
  • schema name would be nice — not done. there are many error messages in mysqldump and in get_table_structure() specifically, none includes a schema name. I can change them all at once, but only only one out of many.
Generated at Thu Feb 08 10:10:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.