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

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

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

          is caused by

          Task - A task that needs to be done. MDEV-16029 mysqldump: dump and restore historical data

          • Major - Major loss of function.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-16546 System versioning setting to allow history modification

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment - - edited

            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.

            elenst Elena Stepanova added a comment - - edited 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.
            serg Sergei Golubchik added a comment -
            • 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.
            serg Sergei Golubchik added a comment - 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.

            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.