Mysql, failing xtrabackup and weird undo tablespaces metrics

Question

I'm trying to set up a MySQL replica server, but, since the database size is quite large, I'm using xtrabackup. The latter one constantly fails on start (though I used to use it earlier):

xtrabackup: recognized server arguments: --innodb_log_buffer_size=32M --innodb_log_file_size=4096M --innodb_buffer_pool_size=64G --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=256M --innodb_io_capacity=3000 --innodb_checksum_algorithm=none --innodb_flush_method=O_DIRECT --innodb_read_io_threads=64 --innodb_write_io_threads=64 --server-id=5 --log_bin=mysql-bin 
xtrabackup: recognized client arguments: --port=3306 --socket=/tmp/mysql.sock --backup=1 --user=XXXX --password=XXXXXX --target-dir=/usr/local/public/backups 
xtrabackup version 8.0.14 based on MySQL server 8.0.21 FreeBSD12.2 (amd64) (revision id: 113f3d7)
220225 18:59:41  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
220225 18:59:41  version_check Connected to MySQL server
220225 18:59:41  version_check Executing a version check against the server...
220225 18:59:41  version_check Done.
220225 18:59:41 Connecting to MySQL server host: localhost, user: XXXXX, password: set, port: 3306, socket: /tmp/mysql.sock
Using server version 8.0.22
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/db/mysql/
xtrabackup: open files limit requested 0, set to 5621859
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 4294967296
xtrabackup: using O_DIRECT
Number of pools: 1
220225 18:59:41 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /tmp/mysql.sock
xtrabackup: Redo Log Archiving is not set up.
Starting to parse redo log at lsn = 83173920975378
Recovery parsing buffer extended to 4194304.
Recovery parsing buffer extended to 8388608.
Recovery parsing buffer extended to 16777216.
Recovery parsing buffer extended to 33554432.
Recovery parsing buffer extended to 67108864.
Recovery parsing buffer extended to 134217728.
220225 18:59:59 >> log scanned up to (83174734573031)
xtrabackup: Generating a list of tablespaces
xtrabackup: Generating a list of tablespaces
Scanning './'
Completed space ID check of 4 files.
Allocated tablespace ID 8533 for wimc_test/mp_group, old maximum was 0
220225 19:00:00 >> log scanned up to (83174734978857)
Using undo tablespace './undo003.ibu'.
Using undo tablespace './undo004.ibu'.
Opened 2 existing undo tablespaces.
Cannot create undo tablespaces since innodb_read_only has been set. Using 0 existing undo tablespaces.
Cannot continue InnoDB startup in read_only mode because there are no existing undo tablespaces found.
xtrabackup: error: xb_load_tablespaces() failed with error code 11

After some time digging into the mysql internals, I noticed that undo tablespaces have weird zero metrics FS_BLOCK_SIZE, FILE_SIZE and ALLOCATED_SIZE (although the sizes of all the four files aren't 0):

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where row_format='Undo';
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
| SPACE      | NAME            | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE  |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
| 4294898191 | innodb_undo_001 |    0 | Undo       |     16384 |             0 | Undo       |             0 |         0 |              0 | 8.0.14         |             1 | N          | active |
| 4294899206 | innodb_undo_002 |    0 | Undo       |     16384 |             0 | Undo       |             0 |         0 |              0 | 8.0.14         |             1 | N          | active |
| 4294967277 | undo_003        |    0 | Undo       |     16384 |             0 | Undo       |             0 |         0 |              0 | 8.0.22         |             1 | N          | active |
| 4294967276 | undo_004        |    0 | Undo       |     16384 |             0 | Undo       |             0 |         0 |              0 | 8.0.22         |             1 | N          | active |
+------------+-----------------+------+------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
4 rows in set (0.04 sec)

This is the only server where they do look like this. Am I right assuming it's the root cause of crashing xtrabackup ?

How can I fix this ? I tried to create manually two non-system undo tablespaces, xtrabackup tried to use them instead, but still crashes. And these two new undo tablespaces also have these two metrics set to 0.

Answer 1

So, long story short. There is a bug in xtrabackup that can be hit when meeting certain conditions on the database size/layout/qps; for those who meet it it's 100% reproducible (on the other hand this version/certain binaries can successfully work with other instances). In the same time I managed to find a thread on a chinese server with half-chinese/half-english discussion describing the root cause of this (no traces in the english part of the world though).

This bug has nothing to do with undo tablespaces metrics that can be seen. In fact, despite the zeroes, these tablespaces are fully functional.

I was unable to reproduce the issue with xtrabackup 8.0.26.