Backup corrupted for PS 5.7 when DDL statements running

[Przemek]

Apply-log stage fails for a backup taken during some DDL workload, with example error:

InnoDB: Ignoring data file './db1/sb1.ibd' with space ID 112. Another data file called ./db1/#sql-ib123-1282040285.ibd exists with the same space ID.
InnoDB: Cannot rename './db1/#sql-ib123-1282040285.ibd' to './db1/sb1.ibd' for space ID 112 because the target file exists. Remove the target file and try again.
InnoDB: Cannot replay file rename. Remove either file and try again.
InnoDB: Plugin initialization aborted with error Generic error
xtrabackup: innodb_init(): Error occured.

How to reproduce:

mysql [localhost] {msandbox} ((none)) > select @@version,@@version_comment;
+-----------+---------------------------------------------------+
| @@version | @@version_comment |
+-----------+---------------------------------------------------+
| 5.7.10-3 | Percona Server (GPL), Release 3, Revision 63dafaf |
+-----------+---------------------------------------------------+
1 row in set (0.00 sec)

Prepare two simple sysbench tables:
sysbench --db-driver=mysql --test=oltp --mysql-table-engine=InnoDB --mysql-db=db1 --mysql-user=msandbox --mysql-password=msandbox --oltp-table-size=2000000 --mysql-socket=/tmp/mysql_sandbox25710.sock --test=/usr/share/doc/sysbench/tests/db/oltp.lua prepare
sysbench --db-driver=mysql --test=oltp --mysql-table-engine=InnoDB --mysql-db=db2 --mysql-user=msandbox --mysql-password=msandbox --oltp-table-size=2000000 --mysql-socket=/tmp/mysql_sandbox25710.sock --test=/usr/share/doc/sysbench/tests/db/oltp.lua prepare

Prepare simple bash script (inside sandbox instance):
cat test.sh
#!/bin/bash

echo "drop table if exists db1.sb1"|./use
echo "create table sb1 as select id,c from sbtest1 where id < 150000;"|./use db1
echo "create unique index ix on sb1 (id)"|./use db1
sleep 1
echo "drop table if exists db2.sb1"|./use
echo "create table sb1 as select id,c from sbtest1 where id < 150000;"|./use db2
echo "create unique index ix on sb1 (id)"|./use db2

Run the script in loop while the backup is taken:
> while true; do bash test.sh; done

Run standard backup:
percona-xtrabackup-2.4.1-Linux-x86_64/bin/innobackupex --user=msandbox --password=msandbox --socket=/tmp/mysql_sandbox25710.sock backups/

Prepare the backup
percona-xtrabackup-2.4.1-Linux-x86_64/bin/innobackupex--apply-log backups/...

Full example prepare-log in attachment.

[Przemek (pmalkowski)]

I could not reproduce the problem doing the same tests against Percona Server 5.6.29.

[Shahriyar Rzayev (rzayev-sehriyar)]

Could reproduce with PS 5.7.11-4.
Slightly changed test.sh and added alter encryption:

root@percona-XPS-15:~# cat test.sh
#!/bin/bash

echo "drop table if exists db1.sb1" | /opt/percona-5.7.11-4/bin/mysql -u root -pBaku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711
echo "create table db1.sb1 as select id,c from db1.sbtest1 where id < 150000;"| /opt/percona-5.7.11-4/bin/mysql -u root -pBaku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711
echo "alter table db1.sb1 encryption='Y'" | /opt/percona-5.7.11-4/bin/mysql -u root -pBaku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711
echo "create unique index ix on db1.sb1 (id)" | /opt/percona-5.7.11-4/bin/mysql -u root -pBaku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711
sleep 1
echo "drop table if exists db2.sb1" | /opt/percona-5.7.11-4/bin/mysql -u root -pBaku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711
echo "create table db2.sb1 as select id,c from db2.sbtest1 where id < 150000;" | /opt/percona-5.7.11-4/bin/mysql -u root -pBaku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711
echo "alter table db2.sb1 encryption='Y'" | /opt/percona-5.7.11-4/bin/mysql -u root -pBaku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711
echo "create unique index ix on db2.sb1 (id)" | /opt/percona-5.7.11-4/bin/mysql -u root -pBaku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711

Take Backup:

root@percona-XPS-15:/usr/local/xtrabackup/bin# cat run_backup.sh
./xtrabackup --defaults-file=/opt/percona-5.7.11-4/my.cnf --backup --datadir=/opt/percona-5.7.11-4/datadir --target-dir=/home/backup_dir/full/ --user=root --password=Baku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711 --no-version-check --keyring-file-data=/opt/percona-5.7.11-4/keyring/keyring --server-id=0

sleep 10

./xtrabackup --defaults-file=/opt/percona-5.7.11-4/my.cnf --backup --target-dir=/home/backup_dir/inc/inc1 --incremental-basedir=/home/backup_dir/full --datadir=/opt/percona-5.7.11-4/datadir --user=root --password=Baku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711 --no-version-check --keyring-file-data=/opt/percona-5.7.11-4/keyring/keyring --server-id=0

sleep 10

./xtrabackup --defaults-file=/opt/percona-5.7.11-4/my.cnf --backup --target-dir=/home/backup_dir/inc/inc2 --incremental-basedir=/home/backup_dir/inc/inc1 --datadir=/opt/percona-5.7.11-4/datadir --user=root --password=Baku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711 --no-version-check --keyring-file-data=/opt/percona-5.7.11-4/keyring/keyring --server-id=0

sleep 10

./xtrabackup --defaults-file=/opt/percona-5.7.11-4/my.cnf --backup --target-dir=/home/backup_dir/inc/inc3 --incremental-basedir=/home/backup_dir/inc/inc2 --datadir=/opt/percona-5.7.11-4/datadir --user=root --password=Baku12345# --socket=/opt/percona-5.7.11-4/datadir/mysqld.sock --port=5711 --no-version-check --keyring-file-data=/opt/percona-5.7.11-4/keyring/keyring --server-id=0

Prepare backups:

root@percona-XPS-15:/usr/local/xtrabackup/bin# cat prepare_backup.sh
./xtrabackup --defaults-fil...

[Sergei Glushchenko (sergei.glushchenko)]

https://github.com/percona/percona-xtrabackup/pull/202

[Shahriyar Rzayev (rzayev-sehriyar)]

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