Error reading delta files during incremental backup and restore

MySQL & MariaDB Percona XtraBackup
1

I’m attempting to use a combination of --export and --incremental to create a rolling table-independent backup of our server. I’ve tried this on a small scale test database with constantly changing data and manage to emulate the process successfully that I want to run on our much bigger server.

The setup is this:

  • Create a full backup
  • Prepare the full backup with --redo-only
  • Create an incremental backup
  • Apply the incremental with --redo-only

This way we can be taking incremental backups every hour for example and have a constantly prepared backup ready to restore in to a server pretty much immediately (thanks to the incremental prepare).

However, on our larger server, when applying the incremental backup to the full one for the preparation step, I’m getting errors with the delta files whereby innobackupex complains that it can’t read certain delta files. It seems to be a different delta file every time I try the process, there doesn’t seem to be any consistency to the .ibd files that cause the problem other than that they are for tables whose data doesn’t change very often (usually the delta files for these tables are 16K in size which shows they haven’t had any data changed for that incremental update).

Process:

  1. Create a full backup
    $ innobackupex --export --no-timestamp /backup/full

  2. Prepare the full backup
    $ innobackupex --defaults-file=/backup/full/backup-my.cnf --export --apply-log --redo-only /backup/full

  3. Create an incremental backup
    $ innobackupex --export --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1

  4. Prepare the incremental backup on the full backup
    $ innobackupex --defaults-file=/backup/inc1/backup-my.cnf --export --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full

This final step is what fails, with the following error:

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2011. All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints “completed OK!”.120208 16:20:21 innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-file=“/backup/inc1/backup-my.cnf” --prepare --target-dir=/backup/full --export --apply-log-only --incremental-dir=/backup/inc1/xtrabackup_55 version 1.6.4 for Percona Server 5.5.16 Linux (x86_64) (revision id: 314)incremental backup from 9155775058777 is enabled.xtrabackup: cd to /backup/fullxtrabackup: This target seems to be already prepared.xtrabackup: xtrabackup_logfile detected: size=435552256, start_lsn=(9156818549117)xtrabackup: page size for /backup/inc1//ibdata1.delta is 16384 bytesApplying /backup/inc1//ibdata1.delta …xtrabackup: page size for /backup/inc1//test/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//test/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//test/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//test/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//test/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//test/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …xtrabackup: page size for /backup/inc1//database/MY_TABLE.ibd.delta is 16384 bytesApplying /backup/inc1//database/MY_TABLE.ibd.delta …InnoDB: Error: tried to read 67108864 bytes at offset 0 0.InnoDB: Was only able to read 16384.120208 16:20:25 InnoDB: Operating system error number 0 in a file operation.InnoDB: Error number 0 means ‘Success’.InnoDB: Some operating system error numbers are described atInnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.htmlInnoDB: File operation call: ‘read’.InnoDB: Cannot continue operation.innobackupex: Error: innobackupex: ibbackup failed at /usr/bin/innobackupex line 341.

Versions: (using latest Percona YUM repo)

$ innobackupex --versionInnoDB Backup Utility v1.5.1-xtrabackup;$ xtrabackup --versionxtrabackup version 1.6.4 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 314)$ mysql --versionmysql Ver 14.14 Distrib 5.5.14, for Linux (x86_64) using readline 5.1$ uname -aLinux ***** 2.6.18-194.32.1.el5 #1 SMP Mon Dec 20 10:52:42 EST 2010 x86_64 x86_64 x86_64 GNU/Linux$ cat /etc/redhat-releaseRed Hat Enterprise Linux Server release 5.6 (Tikanga)

2

To simplify things I’m trying to run the backup process in standard fashion, as opposed to using --export to produce independent IBD files. I’m now running in to a different problem where I still can’t apply the incremental backup to the full one, but for a different reason. It will apply loads of tables without error in the following form:

xtrabackup: page size for /backup/inc01//database/table.ibd.delta is 16384 bytesApplying /backup/inc01//database/table.ibd.delta …

But eventually it comes across one that causes the following exception:

xtrabackup: page size for /backup/inc01//database/table.ibd.delta is 16384 bytesApplying /backup/inc01//database/table.ibd.delta …120209 15:42:01 InnoDB: Assertion failure in thread 47019566936160 in file xtrabackup.c line 5241InnoDB: Failing assertion: last_buffer || page_in_buffer == page_size / 4InnoDB: We intentionally generate a memory trap.InnoDB: Submit a detailed bug report to http://bugs.mysql.com.InnoDB: If you get repeated assertion failures or crashes, evenInnoDB: immediately after the mysqld startup, there may beInnoDB: corruption in the InnoDB tablespace. Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.htmlInnoDB: about forcing recovery.innobackupex: Error: innobackupex: ibbackup failed at /usr/bin/innobackupex line 341.

If I completely remove the incremental backup and start a brand new one, I’ll have the same problem but with a completely different table, whereby the offending table from the previous run sometimes applies cleanly if it’s been applied before the point at which the error occurs.

Interestingly a hex dump of the offending delta file shows that it begins with a slightly different 4-byte header:

HEX: 0x78 0x74 0x72 0x61ASCII: xtra

to files that apply cleanly:

HEX: 0x58 0x54 0x52 0x41ASCII: XTRA

What’s even more interesting is that if I edit the offending file in hexedit and change it to the uppercase version of the header, sometimes the file will apply without xtrabackup complaining. This seems to work about half of the time, so is probably just another symptom.

The lowercase version of the header exists in files that do apply OK before a table that fails, but the table that fails always has the lowercase header in the tests that I’ve done. Our dataset is pretty huge (about 180GB) and this is from a slave that’s constantly performing updates to a lot of the tables, so I’m just wondering whether we’ve hit a bug that’s due to this kind of volume?

3

Latest version of Percona seems to have resolved the delta issues; I’m now on to tablespace problems, but think that could be faulty replication. Not sure if the previous issues where from the same cause but just pushed further up the line and manifesting themselves with weird errors.

4

I’m not familiar enough with what’s going on here to understand without investigating the XtraBackup source code, but if this is still a problem I’d suggest filing a bug on Launchpad.