Percona Xtrabackup Undo Log Truncation

Xtrabackup failed with below error :
2022-11-02T12:41:20.587275+05:30 0 [Note] [MY-012902] [InnoDB] Undo tablespace number 1 was being truncated when mysqld quit.
2022-11-02T12:41:20.587323+05:30 0 [ERROR] [MY-012903] [InnoDB] Cannot recover a truncated undo tablespace in read-only mode
2022-11-02T12:41:20.587351+05:30 0 [ERROR] [MY-011825] [Xtrabackup] xb_load_tablespaces() failed with error code 57

Server version: 8.0.28 MySQL Community Server - GPL
Xtrabackup Version : 8.0.29

Undo log report
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES
→ WHERE FILE_TYPE LIKE ‘UNDO LOG’ \G
*************************** 1. row ***************************
FILE_ID: 4294958516
FILE_NAME: ./undo_001
FILE_TYPE: UNDO LOG
TABLESPACE_NAME: innodb_undo_001
TABLE_CATALOG:
TABLE_SCHEMA: NULL
TABLE_NAME: NULL
LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
ENGINE: InnoDB
FULLTEXT_KEYS: NULL
DELETED_ROWS: NULL
UPDATE_COUNT: NULL
FREE_EXTENTS: 14
TOTAL_EXTENTS: 32
EXTENT_SIZE: 1048576
INITIAL_SIZE: 16777216
MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 16777216
CREATION_TIME: NULL
LAST_UPDATE_TIME: NULL
LAST_ACCESS_TIME: NULL
RECOVER_TIME: NULL
TRANSACTION_COUNTER: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: 18874368
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
*************************** 2. row ***************************
FILE_ID: 4294956610
FILE_NAME: ./undo_002
FILE_TYPE: UNDO LOG
TABLESPACE_NAME: innodb_undo_002
TABLE_CATALOG:
TABLE_SCHEMA: NULL
TABLE_NAME: NULL
LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
ENGINE: InnoDB
FULLTEXT_KEYS: NULL
DELETED_ROWS: NULL
UPDATE_COUNT: NULL
FREE_EXTENTS: 4
TOTAL_EXTENTS: 32
EXTENT_SIZE: 1048576
INITIAL_SIZE: 16777216
MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 16777216
CREATION_TIME: NULL
LAST_UPDATE_TIME: NULL
LAST_ACCESS_TIME: NULL
RECOVER_TIME: NULL
TRANSACTION_COUNTER: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: 20971520
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
2 rows in set (0.35 sec)

We have also tried manually purge undo log:
set global innodb_undo_log_truncate=off
set global innodb_max_undo_log_size=50M
set global innodb_undo_log_truncate=ON
Before :
-rw-r----- 1 mysql mysql 704M Nov 2 11:56 undo_002
-rw-r----- 1 mysql mysql 368M Nov 2 11:56 undo_001

After :
-rw-r----- 1 mysql mysql 16K Nov 2 12:10 undo_2_trunc.log
-rw-r----- 1 mysql mysql 16M Nov 2 12:10 undo_002
-rw-r----- 1 mysql mysql 16K Nov 2 12:10 undo_1_trunc.log
-rw-r----- 1 mysql mysql 16M Nov 2 12:10 undo_001

No Luck still backup failed.

This is production server we will hardly get downtime for Instance Restart.

Any help would be highly appreciated.

Regards,
Jyoti Rajai

1 Like

Hi Jyothi,

It looks like you are facing [PXB-2702] Backup fails when undo truncation log is present - Percona JIRA

MySQL server is supposed to remove the undo*_trunc.log after resuming from crash. For some reason, it leaves this file (orphan). This confuses backup.

We will be fixing in the upcoming release of PXB 8.0.30

As a workaround, you can try to rename them the undo_trunc.log files in server data directory to undo_trunc.log.backup(or any other extension except .log)

2 Likes

@Jyoti_Rajai I hope my suggestion worked for you. If not, please let us know

2 Likes

YEs it did helped. Thanks a lot.

2 Likes