Preparing partial backup does not drop excluded tables using XtraBackup 2.4

[Geoff Montee]

According to the documentation, when a partial backup is prepared, innobackupex should use xtrabackup to drop the excluded tables from the data dictionary:

"You may see warnings in the output about tables that don’t exists. This is because InnoDB-based engines stores its data dictionary inside the tablespace files besides the .frm files. innobackupex will use xtrabackup to remove the missing tables (those that haven’t been selected in the partial backup) from the data dictionary in order to avoid future warnings or errors:"

https://www.percona.com/doc/percona-xtrabackup/2.4/howtos/recipes_ibkx_partition.html#preparing-the-backup

The following steps can reproduce the issue with MariaDB 10.2.7 and XtraBackup 2.4.8:

sudo systemctl stop mariadb
sudo rm -fr /var/lib/mysql/*
sudo mysql_install_db --user=mysql
sudo systemctl start mariadb
mysql -u root <<EOF
create database db1;
create table db1.tab (id int primary key, str varchar(50));
insert into db1.tab values (1, 'str1');
create database db2;
create table db2.tab (id int primary key, str varchar(50));
insert into db2.tab values (1, 'str1');
EOF
mkdir -p /home/ec2-user/mariadb_backup/
sudo rm -fr /home/ec2-user/mariadb_backup/*
sudo innobackupex \
--no-timestamp \
--version-check \
--parallel=30 \
--ftwrl-wait-query-type=all \
--databases="db1 mysql performance_schema" \
--compress --compress-threads=30 \
--ftwrl-wait-timeout=0 \
/home/ec2-user/mariadb_backup/
sudo xtrabackup --decompress --parallel=20 --remove-original --target-dir=/home/ec2-user/mariadb_backup/
sudo innobackupex --apply-log --export --parallel=20 /home/ec2-user/mariadb_backup/
sudo systemctl stop mariadb
sudo rsync -av --delete /home/ec2-user/mariadb_backup/ /var/lib/mysql/
sudo chown -R mysql:mysql /var/lib/mysql/
sudo systemctl start mariadb

The error log after the final startup shows that the table in db2 is still in the data dictionary:

2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Uses event mutexes
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Compressed tables use zlib 1.2.7
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Using Linux native AIO
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Number of pools: 1
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Using SSE2 crc32 instructions
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Completed initialization of buffer pool
2017-08-07 15:20:39 140385957512960 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Highest supported file format is Barracuda.
2017-08-07 15:20:39 140386652305536 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2017-08-07 15:20:39 140386652305536 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-08-07 15:20:39 140386652305536 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-08-07 15:20:39 140386652305536 [ERROR] InnoDB: Cannot open datafile for read-only: './db2/tab.ibd' OS error: 71
2017-08-07 15:20:39 140386652305536 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2017-08-07 15:20:39 140386652305536 [ERROR] InnoDB: The error means the system cannot find the path specified.
2017-08-07 15:20:39 140386652305536 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2017-08-07 15:20:39 140386652305536 [ERROR] InnoDB: Could not find a valid tablespace file for `db2/tab`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2017-08-07 15:20:39 140386652305536 [Warning] InnoDB: Ignoring tablespace for `db2`.`tab` because it could not be opened.
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: 128 out of 128 rollback segments are active.
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: Waiting for purge to start
2017-08-07 15:20:39 140386652305536 [Note] InnoDB: 5.7.18 started; log sequence number 1631784
2017-08-07 15:20:39 140385661724416 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-08-07 15:20:39 140385661724416 [Note] InnoDB: Buffer pool(s) load completed at 170807 15:20:39
2017-08-07 15:20:39 140386652305536 [Note] Plugin 'FEEDBACK' is disabled.
2017-08-07 15:20:39 140386652305536 [Note] Server socket created on IP: '::'.
2017-08-07 15:20:39 140386652305536 [Note] Reading of all Master_info entries succeded
2017-08-07 15:20:39 140386652305536 [Note] Added new Master_info '' to hash table
2017-08-07 15:20:39 140386652305536 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.2.7-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server

The tablespace file does not exist:

$ ls -l /var/lib/mysql/db2
ls: cannot access /var/lib/mysql/db2: No such file or directory

[Shahriyar Rzayev (rzayev-sehriyar)]

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