Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.0(EOL), 11.1(EOL), 11.2(EOL), 11.3(EOL), 11.4, 11.5(EOL)
-
Ubuntu 22.04, n.a.
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
The only thing which helped was restarting the database after the partial physical restore...
Side note: This problem does not happen with a competitive product (8.0.37 with backup tool 8.0.35).
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;
|
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...