Why Did My Incremental Backup Fail? Uncovering the Binlog_pos Length Issue in Percona XtraBackup

Background

The DBaaS platform performs full and incremental backups of MySQL instances. Most incremental backups succeed, but a few fail, prompting a detailed root‑cause analysis.

Investigation Steps

1. Check backup logs

Both the false and success clusters show successful full backups. The incremental backup log for the false cluster contains the error:

Error while attempting to find history record for name 13_********_f

2. Examine PERCONA_SCHEMA.xtrabackup_history table

The table stores backup history when the --history option is used. The false cluster returns an empty result set, while the success cluster returns a row with detailed backup metadata.

select * from PERCONA_SCHEMA.xtrabackup_history where start_time like '2022-11-01%'
Empty set (0.00 sec)

select * from PERCONA_SCHEMA.xtrabackup_history where start_time like '2022-11-01%'
*************************** 1. row ***************************
uuid: 1c6f0f12-5956-11ed-8dde-b4969186078e
name: *************_f
... (additional columns omitted for brevity)

3. Parse binlog around the backup time

For the false cluster, the binlog contains only CREATE statements. The success cluster’s binlog also includes INSERT statements that populate the history table.

mysqlbinlog -vv binlog.003740 > /tmp/binlog.003740.sql

mysqlbinlog -vv --base64-output=DECODE-ROWS binlog.000654 \
--start-datetime="2022-11-01 03:40:00" --stop-datetime="2022-11-01 04:00:00" > binlog.000654.sql

4. Replay INSERT on the false cluster

Attempting to insert the successful INSERT statement into the false cluster’s history table yields:

ERROR 1406 (22001): Data too long for column 'binlog_pos' at row 1

The binlog_pos column is defined as VARCHAR(128), which is insufficient for the long GTID string.

5. Compare sql_mode

The false cluster runs with STRICT_TRANS_TABLES, causing the insert to fail. The success cluster uses a relaxed mode, allowing the oversized value to be truncated with only a warning.

6. Solution

Altering the column length resolves the issue:

ALTER TABLE PERCONA_SCHEMA.xtrabackup_history MODIFY binlog_pos VARCHAR(2000) DEFAULT NULL;

After the change, full‑backup records are inserted successfully and incremental backups complete without errors.

Final Thoughts

The failure was caused by an inadequate column size for binlog_pos combined with strict SQL mode. Properly sizing schema fields and aligning sql_mode with operational requirements prevents similar backup failures.