xtrabackup encounters assertion failure when taking incremental backup

[Lachlan Mulcahy]

Taking an incremental backup of a running InnoDB database causes xtrabackup to encounter an assertion failure due to tablespace id being allegedly mis-aligned.....

Multiple attempts to backup this database encounter similar assertions but on different tables..

My suspicion is that tables are being dropped and recreated during the backup, so the tablespace id in the data dictionary at the beginning of the backup has changed by the time the process comes to actually open the .ibd per-tablespace file later on in the backup…

I think the fact that the error happens on different tables each time is fairly good evidence to back up this theory, but you guys are the experts...

The assertion looks like:

incremental backup from 5250385178540 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mysqldb/data
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /mysqldb/data
xtrabackup: innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
xtrabackup: innodb_log_group_home_dir = /usr/local/mysql/iblogs
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 2097152000
xtrabackup: use O_DIRECT
110828 19:10:19 InnoDB: Warning: allocated tablespace 636, old maximum was 9
>> log scanned up to (5444207434065)
>> log scanned up to (5444210337603)
>> log scanned up to (5444213213911)
>> log scanned up to (5444217152139)
>> log scanned up to (5444220334845)
>> log scanned up to (5444245546572)
>> log scanned up to (5444259268795)
>> log scanned up to (5444266374104)
>> log scanned up to (5444279696651)
>> log scanned up to (5444279758349)
>> log scanned up to (5444280855357)
>> log scanned up to (5444280891607)
>> log scanned up to (5444280915322)
>> log scanned up to (5444280953222)
>> log scanned up to (5444280974395)
>> log scanned up to (5444285081215)
>> log scanned up to (5444286169399)
>> log scanned up to (5444286212068)
>> log scanned up to (5444286259826)
>> log scanned up to (5444286280585)
>> log scanned up to (5444286285925)
>> log scanned up to (5444286346477)
>> log scanned up to (5444286458853)
>> log scanned up to (5444286495127)
>> log scanned up to (5444286543033)
>> log scanned up to (5444286632303)
>> log scanned up to (5444286652743)
>> log scanned up to (5444286729806)
>> log scanned up to (5444286752276)
>> log scanned up to (5444286779789)
>> log scanned up to (5444286799640)
>> log scanned up to (5444286823282)
>> log scanned up to (5444286841309)
>> log scanned up to (5444286998233)
>> log scanned up to (5444287328521)
>> log scanned up to (5444287363801)
>> log scanned up to (5444287518640)
>> log scanned up to (5444287537379)
>> log scanned up to (5444287566289)
>> log scanned up to (5444287600592)
>> log scanned up to (5444287624330)
>> log scanned up to (5444287695968)
>> log scanned up to (5444287743204)
xtrabackup version 1.6.2 for Percona Server 5.1.55 unknown-linux-gnu (x86_64) (revision id: 274)
[01] Copying /mysqldb/data/ibdata1
    to /mysqldb/tmp/xbm-2736143/ibdata1.delta
[01] ...done
[01] Copying /mysqldb/data/ibdata2
    to /mysqldb/tmp/xbm-2736143/ibdata2.delta

…. <snip -- ..many .delta files are copied...> ….

[01] Copying ./marin_olap_staging/creative_fact_854_9817.ibd
    to /mysqldb/tmp/xbm-2736143/marin_olap_staging/creative_fact_854_9817.ibd.delta
[01] ...done
[01] Copying ./marin_olap_staging/keyword_instance_dim_854_5588.ibd
    to /mysqldb/tmp/xbm-2736143/marin_olap_staging/keyword_instance_dim_854_5588.ibd.delta
[01] ...done
[01] Copying ./marin_olap_staging/InnoDB: Error: tablespace id is 69059 in the data dictionary
InnoDB: but in file ./marin_olap_staging/creative_meta_dim_976_7441.ibd it is 69550!
110828 20:03:12 InnoDB: Assertion failure in thread 1378617664 in file fil/fil0fil.c line 740
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-innodb-recovery.html
InnoDB: about forcing recovery.
revenue_load_5483.ibd
    to /mysqldb/tmp/xbm-2736143/marin_olap_staging/revenue_load_5483.ibd.delta
[01] ...done
[01] Copying ./marin_olap_staging/keyword_instance_dim_854_5456.ibd
    to /mysqldb/tmp/xbm-2736143/marin_olap_staging/keyword_instance_dim_854_5456.ibd.delta
[01] ...done
[01] Copying ./marin_olap_staging/keyword_instance_dim_941_10519.ibd
    to /mysqldb/tmp/xbm-2736143/marin_olap_staging/keyword_instance_dim_941_10519.ibd.delta
[01] ...done
[01] Copying ./marin_olap_staging/creative_dim_941_6711.ibd
    to /mysqldb/tmp/xbm-2736143/marin_olap_staging/creative_dim_941_6711.ibd.delta
[01] ...done
[01] Copying ./marin_olap_staging/revenue_load_6831.ibd
    to /mysqldb/tmp/xbm-2736143/marin_olap_staging/revenue_load_6831.ibd.delta
[01] …done

another example backing up the same DB hours later..…

[01] Copying ./marin_olap_staging/creative_meta_dim_1292_8517.ibd
    to /mysqldb/tmp/xbm-9877536/marin_olap_staging/creative_meta_dim_1292_8517.ibd.delta
[01] InnoDB: Error: tablespace id is 67357 in the data dictionary
InnoDB: but in file ./marin_olap_staging/creative_meta_dim_1402_8860.ibd it is 69562!
110828 22:11:59 InnoDB: Assertion failure in thread 1378867520 in file fil/fil0fil.c line 740
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-innodb-recovery.html
InnoDB: about forcing recovery.
 …done

[Charl (charlretief)]

Yes dropping or creating tables (bug #722638) or even truncating certain tables (bug #826604) during a backup does cause backups to fail in this manner.

[Lachlan Mulcahy (lachlan-mulcahy)]

This appears to be a duplicate of bug 722638. Feel free to close it and mark it as such.