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

mariadb-backup fails on database which was partially restored with mariadb-backup

Details

    Description

      Szenario:

      1. Physical full backup with mariadb-backup (--backup)
      2. Prepare of this backup (--prepare)
      3. Prepare with export for partial physical restore (--prepare --export --database=test)
      4. DISCARD/cp/chown/IMPORT of tablespace
      Up to here everything is fine. NO DB restart is done.
      5. Physical full backup with mariadb-backup (--backup) again.

      *.ibd file is missing in backup:
      rw-rw--- 1 mysql mysql 65 Jun 18 17:33 /tmp/bck/daily/bck_full_2024-06-18/test/db.opt
      rw-rw--- 1 mysql mysql 1100 Jun 18 17:33 /tmp/bck/daily/bck_full_2024-06-18/test/test.frm

      and we get in mariadb-backup output the following message:

      ...
      [00] 2024-06-18 17:33:02 mariabackup: Generating a list of tablespaces
      [00] 2024-06-18 17:33:02 DDL tracking : delete 1595 "./test/test.ibd"
      ...
      [01] 2024-06-18 17:33:05 Warning: We assume the table was dropped during xtrabackup execution and ignore the tablespace ./test/test.ibd
      ...
      [00] 2024-06-18 17:33:20 Redo log (from LSN 52224057258 to 52224510260) was copied.
      [00] 2024-06-18 17:33:20 completed OK!

      That means, that this backup is broken now an cannot be used for future partial or full restore!!!

      Steps to reproduce:

      PORT=3321
      DATADIR=/home/mysql/database/mariadb-1011/data
      BACKUPNAME=bck_full_2024-06-18
      BACKUPDIR=/tmp/bck

      rm -rf ${BACKUPDIR}/daily/${BACKUPNAME}

      /home/mysql/product/mariadb-10.11/bin/mariabackup --user=brman --password=secret --host=127.0.0.1 --port=3321 --backup --target-dir=/tmp/bck/daily/bck_full_2024-06-18
      /home/mysql/product/mariadb-10.11/bin/mariabackup --user=brman --password=secret --host=127.0.0.1 --port=3321 --prepare --target-dir=/tmp/bck/daily/bck_full_2024-06-18
      /home/mysql/product/mariadb-10.11/bin/mariabackup --user=brman --host=127.0.0.1 --port=3321 --prepare --export --databases=test --target-dir=/tmp/bck/daily/bck_full_2024-06-18

      ALTER TABLE `test`.`test` DISCARD TABLESPACE;
      cp /tmp/bck/daily/bck_full_2024-06-18/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.cfg
      cp /tmp/bck/daily/bck_full_2024-06-18/test/test.ibd /home/mysql/database/mariadb-1011/data/test/test.ibd
      chown mysql: /home/mysql/database/mariadb-1011/data/test/test.cfg /home/mysql/database/mariadb-1011/data/test/test.ibd
      ALTER TABLE `test`.`test` IMPORT TABLESPACE;
      rm /home/mysql/database/mariadb-1011/data/test/test.cfg
      rm /tmp/bck/daily/bck_full_2024-06-18/test/test.cfg

      no restart

      /home/mysql/product/mariadb-10.11/bin/mariabackup --user=brman --password=secret --host=127.0.0.1 --port=3321 --backup --target-dir=/tmp/bck/daily/bck_full_2024-06-18

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          oli Oli Sennhauser added a comment -

          I tried now various different things to workaround this problem:

          1.)
          start transaction;
          SELECT 1 from test.test LIMIT 0;
          commit;

          2.)
          start transaction;
          delete from mysql.innodb_table_stats limit 1;
          rollback;

          3.)
          mariadb-backup --lock-ddl-per-table

          4.)
          flush tables with read lock;
          unlock tables;

          5.)
          start transaction;
          delete from test.test limit 10;
          rollback;

          6.)
          start transaction;
          delete from test.test limit 1;
          commit;

          7.)
          FLUSH TABLES test.test FOR EXPORT;
          unlock tables;

          None of those helped to workaround the problem. So I am really happy if you have a least a hint so we can continue to develop our application...

          oli Oli Sennhauser added a comment - I tried now various different things to workaround this problem: 1.) start transaction; SELECT 1 from test.test LIMIT 0; commit; 2.) start transaction; delete from mysql.innodb_table_stats limit 1; rollback; 3.) mariadb-backup --lock-ddl-per-table 4.) flush tables with read lock; unlock tables; 5.) start transaction; delete from test.test limit 10; rollback; 6.) start transaction; delete from test.test limit 1; commit; 7.) FLUSH TABLES test.test FOR EXPORT; unlock tables; None of those helped to workaround the problem. So I am really happy if you have a least a hint so we can continue to develop our application...
          oli Oli Sennhauser added a comment -

          The only thing which helped was restarting the database after the partial physical restore...

          oli Oli Sennhauser added a comment - The only thing which helped was restarting the database after the partial physical restore...
          oli Oli Sennhauser added a comment -

          Side note: This problem does not happen with a competitive product (8.0.37 with backup tool 8.0.35).

          oli Oli Sennhauser added a comment - Side note: This problem does not happen with a competitive product (8.0.37 with backup tool 8.0.35).
          alice Alice Sherepa added a comment -

          Thank you for the report! 

          --source include/have_innodb.inc
          		 
           
          		 
          CREATE TABLE t1(i INT) ENGINE INNODB;
          		 
          INSERT INTO t1 VALUES(1);
          		 
           
          		 
          let $targetdir=$MYSQLTEST_VARDIR/tmp/backup;
          		 
           
          		 
          --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir 
          --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --prepare --target-dir=$targetdir
          		 
          --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --prepare --export --databases=test  --target-dir=$targetdir
          		 
           
          		 
          let $MYSQLD_DATADIR= `select @@datadir`;
          		 
          ALTER TABLE t1 DISCARD TABLESPACE;
          		 
          copy_file $targetdir/test/t1.ibd $MYSQLD_DATADIR/test/t1.ibd;
          		 
          copy_file $targetdir/test/t1.cfg $MYSQLD_DATADIR/test/t1.cfg;
          		 
          ALTER TABLE t1 IMPORT TABLESPACE;
          		 
           
          		 
          select * from t1;
          		 
           
          		 
          rmdir $targetdir;
          		 
          --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir 
          --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --prepare --target-dir=$targetdir
          		 
          --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --prepare --export --databases=test  --target-dir=$targetdir
          		 
           
          		 
           
          		 
           --list_files $targetdir/test
          		 
           ## !no .idb and .cfg file
          		 
           
          		 
          DROP TABLE t1;
          		 
          rmdir $targetdir;

          alice Alice Sherepa added a comment - Thank you for the report! --source include/have_innodb.inc   CREATE TABLE t1(i INT ) ENGINE INNODB; INSERT INTO t1 VALUES (1);   let $targetdir=$MYSQLTEST_VARDIR/tmp/backup;   --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --prepare --target-dir=$targetdir --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --prepare --export --databases=test --target-dir=$targetdir   let $MYSQLD_DATADIR= ` select @@datadir`; ALTER TABLE t1 DISCARD TABLESPACE; copy_file $targetdir/test/t1.ibd $MYSQLD_DATADIR/test/t1.ibd; copy_file $targetdir/test/t1.cfg $MYSQLD_DATADIR/test/t1.cfg; ALTER TABLE t1 IMPORT TABLESPACE;   select * from t1;   rmdir $targetdir; --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --prepare --target-dir=$targetdir --exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --prepare --export --databases=test --target-dir=$targetdir     --list_files $targetdir/test ## ! no .idb and .cfg file   DROP TABLE t1; rmdir $targetdir;

          People

            vlad.lesin Vladislav Lesin
            oli Oli Sennhauser
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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