Failed recover table data if backup was taken while 'optimize table'

[Shahriyar Rzayev]

Tested with Percona Server 5.7.10-rc1

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=3567048 DEFAULT CHARSET=latin1 COMPRESSION='lz4'

While running optimize table run the full backup:

optimize table sbtest1

./xtrabackup --defaults-file=/etc/my.cnf --backup --datadir=/var/lib/mysql/ --target-dir=/home/backup_dir/full/ --user=root --password=Baku12345# --no-version-check

Prepare:

./xtrabackup --defaults-file=/home/backup_dir/full/backup-my.cnf --prepare --target-dir=/home/backup_dir/full

InnoDB: Log scan progressed past the checkpoint lsn 1695165482
InnoDB: Ignoring data file './dbtest/#sql-ib42-3719846336.ibd' with space ID 24. Another data file called ./dbtest/sbtest1.ibd exists with the same space ID.
InnoDB: Ignoring data file './dbtest/sbtest1.ibd' with space ID 25. Another data file called ./dbtest/#sql-ib41-3719846335.ibd exists with the same space ID.
InnoDB: Ignoring data file './dbtest/sbtest1.ibd' with space ID 24. Another data file called ./dbtest/#sql-ib42-3719846336.ibd exists with the same space ID.
InnoDB: Ignoring data file './dbtest/#sql-ib41-3719846335.ibd' with space ID 25. Another data file called ./dbtest/sbtest1.ibd exists with the same space ID.
InnoDB: Doing recovery: scanned up to log sequence number 1696707558 (20%)

InnoDB: Shutdown completed; log sequence number 1696708136
160204 04:43:33 completed OK!

Copy-back:

./xtrabackup --copy-back --target-dir=/home/backup_dir/full/
160204 04:45:58 completed OK!

Start Server:

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMPRESSION='lz4'

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

All data was lost.

[Roel Van de Paar (roel11)]

Also see http://bugs.mysql.com/bug.php?id=80263

[Roel Van de Paar (roel11)]

Upstream fix will only come in 5.8.