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

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

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

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

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

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

If innodb_file_per_table disabled it should not be problem.

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

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

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.

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

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."

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`

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-212