Xtrabackup chokes on a specific file

MySQL & MariaDB Percona XtraBackup
1

Hello,
for the purpose of creating a new replica of a MySQL 5.7 server I use this command

xtrabackup --backup --stream=xbstream --parallel=4 /tmp | pigz -c --fast | nc -w 2 replica 2112

this is based on this document.
Normally it works fine (I have synced several replicas with the same method) but now it fails each time with such error after one hours

...
210428 10:56:59 Starting to backup non-InnoDB tables and files
210428 10:56:59 [01] Streaming ./db_187/twitter_scenarios.frm to <STDOUT>
210428 10:56:59 [01]        ...done
xtrabackup: Error writing file 'UNOPENED' (Errcode: 32 - Broken pipe)
[01] Error: copy_file() failed.
Failed to copy file ./db_187/twitter_scenarios.frm

The file ./db_187/twitter_scenarios.frm has no issue or permission problem.

Also it worth pointing I don’t understand why there is the line Starting to backup non-InnoDB tables and files before reaching this file because this table is innodb.

ls /var/lib/mysql/prod2_187/twitter_scenarios.* -al
-rw-r----- 1 mysql mysql   8762 Apr 28 14:36 /var/lib/mysql/db_187/twitter_scenarios.frm
-rw-r----- 1 mysql mysql 114688 Apr 28 12:53 /var/lib/mysql/db_187/twitter_scenarios.ibd

Do you have some advice to troubleshoot or even fix this issue ?

1 Like
2

I don’t understand why there is the line Starting to backup non-InnoDB tables and files before reaching

The table is InnoDB. The FRM file is not.

2 Likes
3

Did the nc connection break? Try without parallel? Have you tried doing a local backup? (replace the nc with >/path/to/backup.xbstream)

2 Likes
4

Judging by the message
“Error writing file ‘UNOPENED’ (Errcode: 32 - Broken pipe)”

I think the problem is on the receiving side.
Probably network connection get broken and nc breaks connection?
You use -w for timeout, what if you do not use this option?

2 Likes
5

I removed -w2 from nc and indeed it works mostly except the connexion never close, so I can hardly use that in a script. I don’t understand either why it used to work without a problem during previous weeks and now it breaks.
Perhaps I should replace nc by something else like socat.

Thanks

1 Like
6

I’m wondering if pigz is keeping the IO open. We use nc in our training and it works perfectly. Repeat the backup but remove pigz. xtrabackup should close the IO stream, which should tell nc to close it as well. You’ll have the same issue with socat if pigz is indeed keeping the stream open.

If you want compression, try using --compress on xtrabackup. Or check the pigz man pages for how to make it auto close when the IO stream terminates.

2 Likes
7

Actually I just did another run but I replaced -w 2 (no more timeout) by -N and it worked fine.
-N is

shutdown(2) the network socket after EOF on the input. Some servers require this to finish their work.

Thanks for the advices and directions you gave me.
really appreciated.

1 Like