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

Bug #722638 reported by Erkan Yanar
60
This bug affects 9 people
Affects Status Importance Assigned to Milestone
Percona XtraBackup moved to https://jira.percona.com/projects/PXB
Fix Released
Critical
Alexey Kopytov
1.6
Fix Released
Critical
Alexey Kopytov
Trunk
Fix Released
Critical
Alexey Kopytov

Bug Description

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.

Related branches

Changed in percona-xtrabackup:
assignee: nobody → Valentine Gostev (core-longbow)
Changed in percona-xtrabackup:
importance: Undecided → High
Revision history for this message
David Corley (david-corley) wrote :

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

Revision history for this message
Peter Zaitsev (pz-percona) wrote :

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

Revision history for this message
David Corley (david-corley) wrote :

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

Revision history for this message
David Corley (david-corley) wrote :

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

Changed in percona-xtrabackup:
status: New → Confirmed
Stewart Smith (stewart)
Changed in percona-xtrabackup:
importance: High → Critical
status: Confirmed → Triaged
assignee: Valentine Gostev (longbow) → nobody
Stewart Smith (stewart)
Changed in percona-xtrabackup:
milestone: none → 1.7
Revision history for this message
Vadim Tkachenko (vadim-tk) wrote :

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

If innodb_file_per_table disabled it should not be problem.

Revision history for this message
juliet draper (juliet-draper) wrote :

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

Revision history for this message
Charl (charlretief) wrote :

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

Revision history for this message
Alexey Kopytov (akopytov) wrote :

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.

Revision history for this message
Stewart Smith (stewart) wrote : Re: [Bug 722638] Re: xtrabackup: taking backup while tables are droped and created breaks backup

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

Changed in percona-xtrabackup:
assignee: nobody → Alexey Kopytov (akopytov)
status: Triaged → In Progress
Changed in percona-xtrabackup:
status: In Progress → Fix Committed
Changed in percona-xtrabackup:
status: Fix Committed → Fix Released
Revision history for this message
Stewart Smith (stewart) wrote :

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

Revision history for this message
Vojtech Kurka (vojtech-kurka) wrote :

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`

Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Duplicates of this bug

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.