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

mysqldump: dump and restore historical data

Details

    Description

      Backup through mysqldump loses historical data from versioned tables.

      mysqldump does not read historical rows from the versioned tables, it runs SELECT, not SELECT .. FOR SYSTEM_TIME ALL. But even if we fixed it, it would still be impossible to load this data back, since we are dealing either with invisible columns into which we can't insert at all, or with virtual columns for which values are ignored. So, for now it becomes a known limitation which needs to be documented.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29730 mysqldump --dump-history creates broken dump if there are precision-versioned tables

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29736 mysqldump sets system_versioning_insert_history=1 twice and doesn't restore previous value

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29737 mysqldump doesn't check for --tz-utc when it accepts --dump-history

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29828 Indicate that --dump-history only applies to timestamp-based versioning

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          duplicates

          Task - A task that needs to be done. MDEV-23125 Backfilling System Versioned Tables

          • Major - Major loss of function.
          • Closed
          includes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16733 mysqldump --tab and --xml options are conflicting

          • Trivial - Cosmetic problem like misspelt words or misaligned text.
          • Closed

          Task - A task that needs to be done. MDEV-16734 LOAD DATA and system_versioning_modify_history

          • Major - Major loss of function.
          • Closed
          is blocked by

          Task - A task that needs to be done. MDEV-15923 option to control who can set session @@timestamp

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

          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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16684 Wrong timestamps on restore from mysqldump --compact

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Task - A task that needs to be done. MDEV-16798 need mysqldump option to include SYSTEM VERSIONING historical records in backups

          • Major - Major loss of function.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-29547 prepare 10.11.0 preview releases

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-16355 Add option for mysqldump to read data as of specific timestamp from system-versioned tables

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

          Task - A task that needs to be done. MDEV-16766 mysqldump: dump history in XML

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29741 SHOW BINLOG EVENTS shows garbage with system_versioning_insert_history=on

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29750 Triggers can modify history

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

          Activity

            Ascending order - Click to sort in descending order
            View 20 older comments
            rdyas Robert Dyas added a comment -

            I was not aware of that... how do you restore with history records?

            rdyas Robert Dyas added a comment - I was not aware of that... how do you restore with history records?
            serg Sergei Golubchik added a comment -

            in bb-10.11-MDEV-16546 branch

            serg Sergei Golubchik added a comment - in bb-10.11- MDEV-16546 branch
            elenst Elena Stepanova added a comment -

            I have no objections against pushing bb-10.11-MDEV-16546 as of 87fca0525 into 10.11 branch and releasing it with 10.11.1.

            Some notes in no particular order (greenman FYI):

            • the option --dump-history only applies to timestamp-based versioning; an error is thrown and mysqldump aborts if any precision-versioned tables are found upon dumping with the option;
            • the abort can be avoided with --force flag, then transaction-precise tables will be dumped without history;
            • combination of --dump-history and --as-of options is forbidden;
            • combination of --dump-history and --replace options is forbidden (see notes in MDEV-16456, REPLACE is not supported);
            • combination of --dump-history and --compact or --skip-tz-utc (in the usual INSERT format, without --tab option) is allowed, but should be used with caution due to a possible inadvertent timezone conversion;
            • combination of --dump-history} and --tab is allowed, but should be used with extreme caution due to a very likely inadvertent timezone conversion. One way to avoid it would be using --skip-tz-utc, which in this case (unlike the previous one) is possibly less dangerous than the default --tz-utc;
            • while re-loading history into a versioned table with limit-based partitioning, the user must be aware of MDEV-29674, all historical rows will be loaded into the first partition. It can be important if the goal of partitioning is to periodically drop oldest historical partitions, after reloading the next such drop may remove the whole previous history;
            • see also notes in MDEV-16546.
            elenst Elena Stepanova added a comment - I have no objections against pushing bb-10.11- MDEV-16546 as of 87fca0525 into 10.11 branch and releasing it with 10.11.1. Some notes in no particular order ( greenman FYI): the option --dump-history only applies to timestamp-based versioning; an error is thrown and mysqldump aborts if any precision-versioned tables are found upon dumping with the option; the abort can be avoided with --force flag, then transaction-precise tables will be dumped without history; combination of --dump-history and --as-of options is forbidden; combination of --dump-history and --replace options is forbidden (see notes in MDEV-16456 , REPLACE is not supported); combination of --dump-history and --compact or --skip-tz-utc (in the usual INSERT format, without --tab option) is allowed, but should be used with caution due to a possible inadvertent timezone conversion; combination of --dump-history } and --tab is allowed, but should be used with extreme caution due to a very likely inadvertent timezone conversion. One way to avoid it would be using --skip-tz-utc , which in this case (unlike the previous one) is possibly less dangerous than the default --tz-utc ; while re-loading history into a versioned table with limit-based partitioning, the user must be aware of MDEV-29674 , all historical rows will be loaded into the first partition. It can be important if the goal of partitioning is to periodically drop oldest historical partitions, after reloading the next such drop may remove the whole previous history; see also notes in MDEV-16546 .
            rembrandt Aliaksei added a comment - - edited

            Hello, any plans to support precision versioning as well (transaction based)?

            I'd very like to be able to get dumps with history from my AWS RDS instance, but it looks like it just not possible for now.
            I cannot get physical dump using `mariabackup` since RDS is a managed service and I don't have access to it's disk.
            And `mariadb-dump` isn't able to capture and recreate tables with precision versioning (which btw isn't mentioned on it's documentation page).

            rembrandt Aliaksei added a comment - - edited Hello, any plans to support precision versioning as well (transaction based)? I'd very like to be able to get dumps with history from my AWS RDS instance, but it looks like it just not possible for now. I cannot get physical dump using `mariabackup` since RDS is a managed service and I don't have access to it's disk. And `mariadb-dump` isn't able to capture and recreate tables with precision versioning (which btw isn't mentioned on it's documentation page ).
            midenok Aleksey Midenkov added a comment -

            rembrandt I wouldn't recommend precise versioning until MDEV-16226 as it has many deficiencies/bugs. Please watch that ticket and add vote as this can influence task priority.

            midenok Aleksey Midenkov added a comment - rembrandt I wouldn't recommend precise versioning until MDEV-16226 as it has many deficiencies/bugs. Please watch that ticket and add vote as this can influence task priority.

            People

              elenst Elena Stepanova
              elenst Elena Stepanova
              Votes:
              9 Vote for this issue
              Watchers:
              20 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.