Inc backup fails if a tablespace is created between full and inc backups

Reported by Laurynas Biveinis on 2012-07-09
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona XtraBackup
High
Laurynas Biveinis
2.0
High
Laurynas Biveinis
2.1
High
Laurynas Biveinis

Bug Description

If the xb_incremental_compressed test is extended to create a new table between the full and inc backups and insert some rows to it (did not check if inserting the rows is actually mandatory), then the 2nd prepare step will show an error:

InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Error: tablespace id 18446744073709551615 in file ./incremental_sample/t2.ibd is not sensible
120709 16:07:22 InnoDB: Error creating file './incremental_sample/t2.ibd'.
120709 16:07:22 InnoDB: Operating system error number 17 in a file operation.
InnoDB: Error number 17 means 'File exists'.

This error will cascade to a same error message on the 3rd prepare step and then to a restored from backup server error:

120709 16:07:30 [ERROR] Cannot find or open table incremental_sample/t2 from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.

Tests xb_incremental and ib_incremental pass with the same change. Tested with xtradb51.

The testcase changes:
=== modified file 'test/t/xb_incremental_compressed.sh'
--- test/t/xb_incremental_compressed.sh 2012-06-05 12:35:33 +0000
+++ test/t/xb_incremental_compressed.sh 2012-07-09 13:07:00 +0000
@@ -79,31 +79,45 @@

   vlog "Making changes to database"

+ ${MYSQL} ${MYSQL_ARGS} -e "CREATE TABLE t2 (a INT(11) DEFAULT NULL, \
+number INT(11) DEFAULT NULL) ENGINE=INNODB \
+ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=$page_size" incremental_sample
+
   let "count=numrow+1"
   let "numrow=15000"
   while [ "$numrow" -gt "$count" ]; do
- sql="INSERT INTO test VALUES ($count, $numrow)"
+ sql="VALUES ($count, $numrow)"
     let "count=count+1"
     for ((i=0; $i<99; i++)); do
       sql="${sql},($count, $numrow)"
       let "count=count+1"
     done
- ${MYSQL} ${MYSQL_ARGS} -e "$sql" incremental_sample
+ ${MYSQL} ${MYSQL_ARGS} -e "INSERT INTO test $sql" incremental_sample
+ ${MYSQL} ${MYSQL_ARGS} -e "INSERT INTO t2 $sql" incremental_sample
   done

   rows=`${MYSQL} ${MYSQL_ARGS} -Ns -e "SELECT COUNT(*) FROM test" \
 incremental_sample`
   if [ "$rows" != "15000" ]; then
- vlog "Failed to add more rows"
+ vlog "Failed to add more rows to 'test'"
     exit -1
   fi

+ rows=`${MYSQL} ${MYSQL_ARGS} -Ns -e "SELECT COUNT(*) FROM t2" \
+incremental_sample`
+ if [ "$rows" != "5000" ]; then
+ vlog "Failed to add more rows to 't2'"
+ exit -1
+ fi
+
   vlog "Changes done"

   # Saving the checksum of original table
- checksum_a=`checksum_table incremental_sample test`
+ checksum_test_a=`checksum_table incremental_sample test`
+ checksum_t2_a=`checksum_table incremental_sample t2`

- vlog "Table checksum is $checksum_a"
+ vlog "Table 'test' checksum is $checksum_test_a"
+ vlog "Table 't2' checksum is $checksum_t2_a"

   vlog "Making incremental backup"

@@ -127,8 +141,9 @@
   vlog "Data prepared for restore"

   # removing rows
- vlog "Table cleared"
   ${MYSQL} ${MYSQL_ARGS} -e "delete from test;" incremental_sample
+ ${MYSQL} ${MYSQL_ARGS} -e "delete from t2;" incremental_sample
+ vlog "Tables cleared"

   # Restore backup

@@ -144,12 +159,19 @@

   start_server ${mysqld_additional_args}

- vlog "Cheking checksums"
- checksum_b=`checksum_table incremental_sample test`
-
- if [ "$checksum_a" != "$checksum_b" ]
- then
- vlog "Checksums are not equal"
+ vlog "Checking checksums"
+ checksum_test_b=`checksum_table incremental_sample test`
+ checksum_t2_b=`checksum_table incremental_sample t2`
+
+ if [ "$checksum_test_a" != "$checksum_test_b" ]
+ then
+ vlog "Checksums of table 'test' are not equal"
+ exit -1
+ fi
+
+ if [ "$checksum_t2_a" != "$checksum_t2_b" ]
+ then
+ vlog "Checksums of table 't2' are not equal"
       exit -1
   fi

A reduced test case. It seems that the bug happens when innodb-file-per-table is enabled and a table is created and inserted to between full and incremental backups.

. inc/common.sh

mysqld_additional_args="--innodb_file_per_table"

start_server ${mysqld_additional_args}

load_dbase_schema incremental_sample

# Full backup

# Full backup folder
rm -rf $topdir/data/full
mkdir -p $topdir/data/full
# Incremental data
rm -rf $topdir/data/delta
mkdir -p $topdir/data/delta

vlog "Starting backup"

xtrabackup --datadir=$mysql_datadir --backup --target-dir=$topdir/data/full \
    $mysqld_additional_args

vlog "Full backup done"

# Changing data in sakila

vlog "Making changes to database"

${MYSQL} ${MYSQL_ARGS} -e "CREATE TABLE t2 (a INT(11) DEFAULT NULL, \
 number INT(11) DEFAULT NULL) ENGINE=INNODB" incremental_sample
${MYSQL} ${MYSQL_ARGS} -e "INSERT INTO t2 VALUES (1, 1)" incremental_sample

vlog "Changes done"

# Saving the checksum of original table
checksum_t2_a=`checksum_table incremental_sample t2`

vlog "Table 't2' checksum is $checksum_t2_a"

vlog "Making incremental backup"

# Incremental backup
xtrabackup --datadir=$mysql_datadir --backup \
    --target-dir=$topdir/data/delta --incremental-basedir=$topdir/data/full \
    $mysqld_additional_args

vlog "Incremental backup done"
vlog "Preparing backup"

# Prepare backup
xtrabackup --datadir=$mysql_datadir --prepare --apply-log-only \
    --target-dir=$topdir/data/full $mysqld_additional_args
vlog "Log applied to backup"

xtrabackup --datadir=$mysql_datadir --prepare --apply-log-only \
    --target-dir=$topdir/data/full --incremental-dir=$topdir/data/delta \
    $mysqld_additional_args
vlog "Delta applied to backup"

xtrabackup --datadir=$mysql_datadir --prepare --target-dir=$topdir/data/full \
    $mysqld_additional_args
vlog "Data prepared for restore"

# removing rows
${MYSQL} ${MYSQL_ARGS} -e "delete from t2;" incremental_sample
vlog "Table cleared"

# Restore backup

stop_server

vlog "Copying files"

cd $topdir/data/full/
cp -r * $mysql_datadir
cd -

vlog "Data restored"

start_server ${mysqld_additional_args}

vlog "Checking checksums"
checksum_t2_b=`checksum_table incremental_sample t2`

if [ "$checksum_t2_a" != "$checksum_t2_b" ]
then
    vlog "Checksums of table 't2' are not equal"
    exit -1
fi

vlog "Checksums are OK"

stop_server

summary: - Compressed InnoDB table inc backup fails when a tablespace is created
- between full and inc backups
+ Inc backup fails when a tablespace is created between full and inc
+ backups
summary: - Inc backup fails when a tablespace is created between full and inc
- backups
+ Inc backup fails if a tablespace is created between full and inc backups

The issue here is that the 1st page of the newly-created tablespace is not properly initialized. The delta of that tablespace is empty, thus all the changes are in the redo log. And the tablespace initialization is not properly redo-logged in InnoDB: the 1st page is written outside any MTR in fil_create_new_single_table_tablespace(). The XB creates a new single table table in xb_delta_open_matching_space() and does not set up the 1st page properly nor of course is it replayed from a redo log. Usually this issue is masked by restoring the 1st page from the delta.

Alexey Kopytov (akopytov) wrote :

I don't like the fix for this bug. The problem is that now incremental backups take 64 KB more per _every_ table, even if it has not been modified. We have customers with 100,000+ tables (see bug #950334). A simple calculation shows their incremental backups would now take 6+ GB more, even if there have been absolutely no data updates.

I think the fix should be re-implemented to properly initialize the 1st page when a new tablespace has to be created on prepare. It's quite easy to do, we can reuse the code from fil_create_new_single_table_tablespace().

Alexey Kopytov (akopytov) wrote :

Reported regression as bug #1043762.

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

Other bug subscribers