Xtrabackup --copy-back fail Can't create/write to file './undo_001' (OS errno 17 - File exists)

Hi,
I took an xtrabackup successfully and also the
–prepare was okay.

I created an empty directory and set the permission:
mkdir /export/ssdraid/mysql
chown -R mysql:mysql /export/ssdraid/mysql

When I tried to restore I got error:

xtrabackup: Can't create/write to file './undo_001' (OS errno 17 - File exists)

This is the command to restore:
xtrabackup --copy-back --target-dir=/export/ssdraid/mysqlbackup/2022/07Oct --datadir=/export/ssdraid/mysql

Here is the full error message:

time xtrabackup --copy-back --target-dir=/export/ssdraid/mysqlbackup/2022/07Oct --datadir=/export/ssdraid/mysql
2022-09-07T05:51:59.191458-04:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/export/ssdraid/mysql --innodb_checksum_algorithm=none --innodb_io_capacity=500000 --innodb_log_group_home_dir=/export/mysql-logs --innodb_read_io_threads=16 --innodb_undo_directory=. --innodb_write_io_threads=16 --open_files_limit=32768 --tmpdir=/export/ssdraid/tmp --innodb_file_per_table=1 --innodb_buffer_pool_size=19G --innodb_log_file_size=2000M --innodb_log_buffer_size=1024M --innodb_flush_log_at_trx_commit=0 --datadir=/export/ssdraid/mysql
2022-09-07T05:51:59.191632-04:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --socket=/export/ssdraid/mysql/mysql.sock --copy-back=1 --target-dir=/export/ssdraid/mysqlbackup/2022/07Oct
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
2022-09-07T05:51:59.191664-04:00 0 [Note] [MY-011825] [Xtrabackup] cd to /export/ssdraid/mysqlbackup/2022/07Oct/
xtrabackup: Can’t create/write to file ‘./undo_001’ (OS errno 17 - File exists)
2022-09-07T05:51:59.192270-04:00 0 [ERROR] [MY-011825] [Xtrabackup] cannot open the destination stream for undo_001
2022-09-07T05:51:59.192292-04:00 0 [ERROR] [MY-011825] [Xtrabackup] copy_file() failed.

With rsync I can copy the files and was able to start the database.

This is the version of the MySQL and xtrabackup :
mysql Ver 8.0.29-21 for Linux on x86_64 (Percona Server (GPL), Release 21, Revision c59f87d2854)
xtrabackup version 8.0.29-22.

Please help me.
Thanks

1 Like

Hi @sg4 .

Can you please try the copy-back operating ignoring your my.cnf:

xtrabackup --no-defaults --copy-back --target-dir=/export/ssdraid/mysqlbackup/2022/07Oct --datadir=/export/ssdraid/mysql
1 Like

Hi Marcelo,

It working!
I was able to restore and start the MySQL.

xtrabackup --no-defaults --copy-back --target-dir=/export/ssdraid/mysqlbackup/2022/07Oct --datadir=/export/ssdraid/mysql

2022-09-08T09:01:36.838300-04:00 1 [Note] [MY-011825] [Xtrabackup] Creating directory ./#innodb_temp
2022-09-08T09:01:36.838318-04:00 1 [Note] [MY-011825] [Xtrabackup] Done: creating directory ./#innodb_temp
2022-09-08T09:01:36.884869-04:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

real    1m49.326s
user    0m0.074s
sys     0m47.029s

Can you explain what was the problem?

Thanks

1 Like

Problem is its reading the live server / my.cnf values, and it was trying to move the logs according to your my.cnf (innodb_log_group_home_dir / innodb_undo_directory ) and you already had a file with the same name there.

1 Like

Hi Marcelo,

This is the :
–innodb_log_group_home_dir=/export/mysql-logs

Should I delete all the files there before the restore
and omit --no-defaults ? does it will still work?
Thanks

1 Like

Hi Marcelo,

I tried to delete the files under
–innodb_log_group_home_dir:
/export/mysql-logs

and the restore fail.

The --innodb_undo_directory=. is absolutely empty since it’s the datadir - please correct me if I wrong.

Thanks

1 Like

Hi @sg4

Can you please run xtrabackup under strace so I can see exactly where is the problem:

strace -f -s8192 -ttt -o/tmp/pxb.strace xtrabackup ...

Then, please send me /tmp/pxb.strace file

Thanks

1 Like

Hi Marcelo,

Sure please see the attached pxb.strace

Thanks

1 Like

Hi, I see no attachment

1 Like

Sorry, now I changed the extension to log
pxb.log (128.6 KB)

1 Like
22138 1662643994.366973 chdir("/export/ssdraid/mysqlbackup/2022/07Oct/") = 0
22138 1662643994.367017 write(2, "2022-09-08T09:33:14.367003-04:00 0 [Note] [MY-011825] [Xtrabackup] cd to /export/ssdraid/mysqlbackup/2022/07Oct/\n", 113) = 113
22138 1662643994.367058 open("./xtrabackup_checkpoints", O_RDONLY) = 3
 . . .
22138 1662643994.369083 mkdir(".", 0750) = -1 EEXIST (File exists)
22138 1662643994.369120 open("./xtrabackup_punch_hole", O_WRONLY|O_CREAT|O_EXCL|O_NOFOLLOW, 0640) = 3
22138 1662643994.369182 fallocate(3, FALLOC_FL_KEEP_SIZE|FALLOC_FL_PUNCH_HOLE, 0, 1) = 0
22138 1662643994.369220 close(3)        = 0
22138 1662643994.369256 unlink("./xtrabackup_punch_hole") = 0
22138 1662643994.369308 stat("undo_001", {st_mode=S_IFREG|0640, st_size=67108864, ...}) = 0
22138 1662643994.369361 open("undo_001", O_RDONLY) = 3
22138 1662643994.369400 fstat(3, {st_mode=S_IFREG|0640, st_size=67108864, ...}) = 0
22138 1662643994.369438 fadvise64(3, 0, 0, POSIX_FADV_SEQUENTIAL) = 0
22138 1662643994.369471 mmap(NULL, 10489856, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f76d419b000
22138 1662643994.369514 stat(".", {st_mode=S_IFDIR|0755, st_size=4096, ...}) = 0
22138 1662643994.369551 mkdir("./", 0750) = -1 EEXIST (File exists)
22138 1662643994.369586 open("./undo_001", O_WRONLY|O_CREAT|O_EXCL|O_NOFOLLOW, 0640) = -1 EEXIST (File exists)
22138 1662643994.369630 write(2, "xtrabackup: ", 12) = 12
22138 1662643994.369671 write(2, "Can't create/write to file './undo_001' (OS errno 17 - File exists)", 67) = 67
22138 1662643994.369712 write(2, "\n", 1) = 1
22138 1662643994.369769 write(2, "2022-09-08T09:33:14.369748-04:00 0 [ERROR] [MY-011825] [Xtrabackup] cannot open the destination stream for undo_001\n", 116) = 116
22138 1662643994.369809 close(3)        = 0
22138 1662643994.369842 munmap(0x7f76d419b000, 10489856) = 0
22138 1662643994.369888 write(2, "2022-09-08T09:33:14.369874-04:00 0 [ERROR] [MY-011825] [Xtrabackup] copy_file() failed.\n", 88) = 88
22138 1662643994.370472 exit_group(1)   = ?
22138 1662643994.371167 +++ exited with 1 +++

Your problem is --innodb_undo_directory=. as we change dir chdir to --target-dir and not --data-dir
In fact I can reproduce the same error by setting --innodb_undo_directory=.

1 Like

Hi Marcelo,

I remove
innodb_undo_directory=.
from my.cnf
I don’t know why it’s there.

Now the restore is working,
Highly appreciate your assistance.

Thanks

2 Likes