RENAME TABLE causes incremental prepare to fail

[Matt]

Issue we're seeing is that renaming a table after taking and preparing a full backup but before taking and applying an incremental backup is causing the incremental prepare step to fail with the following error. Backup in total is about 180GB in size, and the tables here are ~3MB and ~70,000 rows. We use the renames to rotate a set of tables in order to introduce some new data to the system. Our workaround for this at the moment is to convert the tables to MyISAM so that they're not part of the incremental step.

Relevant tables:
---
mytable
mytable_old

Rotate process:
---
CREATE TABLE mytable_temp [ ... ]
LOAD DATA INFILE data INTO TABLE mytable_temp
DROP TABLE IF EXISTS mytable_old
RENAME TABLE mytable TO mytable_old
RENAME TABLE mytable_temp TO mytable
ANALYZE TABLE mytable

Backup process:
---
$ innobackupex --no-timestamp /backup/full/
...
$ innobackupex --apply-log --redo-only --use-memory=16GB /backup/full/
...

[ ROTATE HAPPENS HERE ]

$ innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/inc01/
...
$ innobackupex --apply-log --redo-only --use-memory=16GB --incremental-dir=/backup/inc01/ /backup/full/
[ ... APPLYING DELTA FILES UP TO HERE ... ]
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /backup/inc01/
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 562397184
120214 15:25:58 InnoDB: Using Linux native AIO
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 17179869184 bytes for buffer pool (set by --use-memory parameter)
120214 15:25:58 InnoDB: The InnoDB memory heap is disabled
120214 15:25:58 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120214 15:25:58 InnoDB: Compressed tables use zlib 1.2.3
120214 15:25:58 InnoDB: Using Linux native AIO
120214 15:25:58 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
120214 15:25:58 InnoDB: Initializing buffer pool, size = 16.0G
120214 15:25:59 InnoDB: Completed initialization of buffer pool
120214 15:25:59 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 9195483965206
120214 15:25:59 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Error: trying to add tablespace 6558 of name './database/mytable.ibd'
InnoDB: to the tablespace memory cache, but tablespace
InnoDB: 6558 of name './database/mytable_old.ibd' already exists in the tablespace
InnoDB: memory cache!
innobackupex: Error:
innobackupex: ibbackup failed at /usr/bin/innobackupex line 349.

[Alexey Kopytov (akopytov)]

Verified with a bit different symptoms. Will attach a test case.

[Sergei Glushchenko (sergei.glushchenko)]

When we rename table after the full backup been taken but before the incremental backup been taken, we get a mismatch in tablespace's names.
I think we should match tablespaces by id when applying deltas. If we can't find matching id (ALTER TABLE), then match by name. Also we should rename tablespaces in older backup to match the new name. I propose to store table id in .meta file.

[Alexey Kopytov (akopytov)]

Looks good, except that when we can't find a matching tablespace by id, we should recreate an existing tablespace with the same name, if any. So we don't apply incremental changes to a completely different table that was DROPed + CREATEed between full and incremental backups.

Storing ids in .meta files is okay.

[Laurynas Biveinis (laurynas-biveinis)]

Bug 1044398 is another regression.

[Vojtech Kurka (vojtech-kurka)]

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)]

Vojtech,

Thanks for the report. It appears to be a different issue, so I reported it as bug #1079700.

[Vojtech Kurka (vojtech-kurka)]

OK, thank you Alexey :)

Best regards,

Vojtech

On Fri, Nov 16, 2012 at 3:37 PM, Alexey Kopytov
<email address hidden> wrote:
> Vojtech,
>
> Thanks for the report. It appears to be a different issue, so I reported
> it as bug #1079700.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/932623
>
> Title:
> RENAME TABLE causes incremental prepare to fail
>
> Status in Percona XtraBackup:
> Fix Released
> Status in Percona XtraBackup 2.0 series:
> Fix Released
> Status in Percona XtraBackup 2.1 series:
> Fix Released
>
> Bug description:
> Issue we're seeing is that renaming a table after taking and preparing
> a full backup but before taking and applying an incremental backup is
> causing the incremental prepare step to fail with the following error.
> Backup in total is about 180GB in size, and the tables here are ~3MB
> and ~70,000 rows. We use the renames to rotate a set of tables in
> order to introduce some new data to the system. Our workaround for
> this at the moment is to convert the tables to MyISAM so that they're
> not part of the incremental step.
>
> Relevant tables:
> ---
> mytable
> mytable_old
>
> Rotate process:
> ---
> CREATE TABLE mytable_temp [ ... ]
> LOAD DATA INFILE data INTO TABLE mytable_temp
> DROP TABLE IF EXISTS mytable_old
> RENAME TABLE mytable TO mytable_old
> RENAME TABLE mytable_temp TO mytable
> ANALYZE TABLE mytable
>
> Backup process:
> ---
> $ innobackupex --no-timestamp /backup/full/
> ...
> $ innobackupex --apply-log --redo-only --use-memory=16GB /backup/full/
> ...
>
> [ ROTATE HAPPENS HERE ]
>
> $ innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/inc01/
> ...
> $ innobackupex --apply-log --redo-only --use-memory=16GB --incremental-dir=/backup/inc01/ /backup/full/
> [ ... APPLYING DELTA FILES UP TO HERE ... ]
> xtrabackup: Temporary instance for recovery is set as followings.
> xtrabackup: innodb_data_home_dir = ./
> xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
> xtrabackup: innodb_log_group_home_dir = /backup/inc01/
> xtrabackup: innodb_log_files_in_group = 1
> xtrabackup: innodb_log_file_size = 562397184
> 120214 15:25:58 InnoDB: Using Linux native AIO
> xtrabackup: Starting InnoDB instance for recovery.
> xtrabackup: Using 17179869184 bytes for buffer pool (set by --use-memory parameter)
> 120214 15:25:58 InnoDB: The InnoDB memory heap is disabled
> 120214 15:25:58 InnoDB: Mutexes and rw_locks use GCC atomic builtins
> 120214 15:25:58 InnoDB: Compressed tables use zlib 1.2.3
> 120214 15:25:58 InnoDB: Using Linux native AIO
> 120214 15:25:58 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
> 120214 15:25:58 InnoDB: Initializing buffer pool, size = 16.0G
> 120214 15:25:59 InnoDB: Completed initialization of buffer pool
> 120214 15:25:59 InnoDB: highest supported file format is Barracuda.
> InnoDB: Log scan progressed past the checkpoint lsn 9195483965206
> 120214 15:25:59 InnoDB: Da...

[Shahriyar Rzayev (rzayev-sehriyar)]

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