Issues with renaming/rotating tables during the backup stage

[Alexey Kopytov]

Reporting https://bugs.launchpad.net/percona-xtrabackup/+bug/932623/comments/6 as a separate bug:

RENAME TABLE run during the backup breaks our backup even it is not incremental:

-- 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`

[Alexey Kopytov (akopytov)]

The reason is that even though we iterate and copy .ibd files based on a "data dictionary snapshot" (actually, a list of tablespaces represented by a fil_system_t structure) taken before starting a backup, we don't validate if the files being copied are consistent with that snapshot.

Consider the following case.

- before taking a backup there are tables t1 and t2 in fil_system.
- after t1 has been copied by xtrabackup, we do RENAME TABLE t1 TO temp, t2 TO t1, temp TO t2;
- xtrabackup then copies t2 (which is in fact the original t1)

As a result, the backup will contain two identical (i.e. with same ID) tables with different names, and the original table t2 will be lost.

I think the only way to guarantee that files being copied are consistent with the snapshot created before starting the backup is to reuse file handles created when populating the list in fil_system_struct, rather than reopen files by their names as we do currently.

However, there's still a possibility of table rotation occurring while fil_system is being populated before the backup. But in this case xtrabackup will fail with an InnoDB error message about duplicate tablespace IDs before copying any files. I.e. at least it will not create a broken backup.

[Alexey Kopytov (akopytov)]

A partial case of this bug is when a tablespace is renamed after it is scanned by XtraBackup on startup and before XtraBackup attempts to copy it. In this case XtraBackup assumes the table has been dropped, which is of course not necessarily the case.

[Shahriyar Rzayev (rzayev-sehriyar)]

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