Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29730

mysqldump --dump-history creates broken dump if there are precision-versioned tables

    XMLWordPrintable

Details

    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

          Activity

            People

              serg Sergei Golubchik
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.