xtrabackup: taking backup while tables are droped and created breaks backup

[Erkan Yanar]

Hi,
taking a backup with innobackup/xtrabackup the backup breaks.
with:
InnoDB: Error: tablespace id is 849127 in the data dictionary
InnoDB: but in file ./magento/catalog_product_index_eav_idx.ibd it is 849130!
110221 12:17:52 InnoDB: Assertion failure in thread 140051784468208 in file fil/fil0fil.c line 726
InnoDB: 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, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
innobackupex-1.5.1: Error: ibbackup child process has died at ./innobackupex-1.5.1 line 534.

The table catalog_product_index_eav_idx is dropped and created while doing the backup. Stopping this the backup was doing well.
So innodbacup/xtrabackup can't handle dropping and creating tables while backup is done.

Regards
Erkan
Using latest stable 1.4 even if verson reports:
./innobackupex-1.5.1 --version
Version string '' contains invalid data; ignoring: '' at ./innobackupex-1.5.1 line 1708.
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.

[David Corley (david-corley)]

We're getting hit by this one too. 5.1.47 with XtraBackup 1.4 rev 193

[Peter Zaitsev (pz-percona)]

Can you try with Xtrabackup 1.5
It is called beta but it has a lot of bugs fixed

[David Corley (david-corley)]

Hey Peter,
I tried the latest beta but the issue is still present.

[David Corley (david-corley)]

I tried the newly released 1.6 version, and the issue remains.

[Vadim Tkachenko (vadim-tk)]

To clarify - the problem appears only with innodb_file_per_table=1.

If innodb_file_per_table disabled it should not be problem.

[juliet draper (juliet-draper)]

Is there a patch for this? I can't change out file per table settings and I would love to use xtrabackup!

[Charl (charlretief)]

I reported a new issue related to this bug #826604. It is however not a duplicate.

[Alexey Kopytov (akopytov)]

The root cause of the problem is that xtrabackup iterates .ibd files
based on a data dictionary snapshot taken at the very start of a
backup process. If a tablespace id changes due to dropping and
recreating the table with the same name, or an ALTER TABLE that
requires a table copy, or TRUNCATE (if it is performed as DROP +
CREATE TABLE), InnoDB complains about id mismatch, and crashes with an
assertion failure.

The idea of the fix is to ignore tablespaces with wrong ids at the
backup stage. They will be (re)created when doing recovery at the prepare
stage, since the corresponding delete/create log records will be
replayed on recovery.

All updates to the "old" version(s) of the tablespace (i.e. any log
records before the table was recreated) will be ignored on recovery,
because buf_read_recv_pages() just does nothing when it cannot find
the tablespace with a specified id.

Originally I thought that table renaming needs special handling,
because we may end up with multiple tablespace with the same id, if
renaming happens after the source tablespace has been
copied. However, that can never be the case with xtrabackup because
it considers only tablespaces that were present at start.

So what needs to be done is to modify fil_node_open_file() in all
XtraBackup patches to InnoDB/XtraDB source to signal id mismatches
back to the caller (possibly with a warning) rather than crash with
an error, similarly to how OS_FILE_NOT_FOUND is ignored in the fix for
bug #713799. The warning message in xtrabackup.c has to be adjusted
to take this new case into account.

The fix has to be tested manually, because the xtrabackup test suite
does not have a DEBUG_SYNC analog which is required in this case to
synchronize file copying by xtrabackup and DDL operations in the
server.

[Stewart Smith (stewart)]

On Wed, 07 Sep 2011 17:15:41 -0000, Alexey Kopytov <email address hidden> wrote:
> The fix has to be tested manually, because the xtrabackup test suite
> does not have a DEBUG_SYNC analog which is required in this case to
> synchronize file copying by xtrabackup and DDL operations in the
> server.

What about some perl and gdb?

--
Stewart Smith

[Stewart Smith (stewart)]

As mentioned in https://bugs.launchpad.net/percona-xtrabackup/+bug/855035

"We should also document that the fix for bug #722638 may not work for old MySQL or Percona Server releases. If this limitation proves to be problematic, we may invest more time into fixing it."

[Vojtech Kurka (vojtech-kurka)]

We are facing similar problem when renaming InnoDB tables in cycle:
-- versions ---------------------
mysql --version
mysql Ver 14.14 Distrib 5.5.23, for Linux (x86_64) using readline 5.1

xtrabackup_55 -v
xtrabackup_55 version 2.0.3 for Percona Server 5.5.16 Linux (x86_64) (revision id: 470)

-- The problem occurs when applying log ------------

innobackupex --apply-log --use-memory=48GB /data/db_restore

InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Error: trying to add tablespace 2788 of name './db2/ShopOfferAvgPositionCategoryTemp.ibd'
InnoDB: to the tablespace memory cache, but tablespace
InnoDB: 2788 of name './db2/ShopOfferAvgPositionCategory.ibd' already exists in the tablespace
InnoDB: memory cache!
innobackupex: Error:
innobackupex: ibbackup failed at /usr/bin/innobackupex line 378

-- --------------------------------------------------

The problem occurs always when this RENAME TABLE is done while the backup is being taken:

RENAME TABLE `ShopOfferAvgPositionCategory` TO `ShopOfferAvgPositionCategorySwitch`
                             , `ShopOfferAvgPositionCategoryTemp` TO `ShopOfferAvgPositionCategory`
                             , `ShopOfferAvgPositionCategorySwitch` TO `ShopOfferAvgPositionCategoryTemp`