-
Type:
Bug
-
Status: Done
-
Priority:
Medium
-
Resolution: Fixed
-
Affects Version/s: 2.4.12
-
Fix Version/s: 2.4.13
-
Component/s: None
-
Labels:
Hello,
we are using the using xtrabackup 5.7.19 and MySQL 5.7.21-21-log Percona Server in order to make partial backups of databases. According to docs:
When you use the --prepare option on a partial backup, you will see warnings about tables that don’t exist. That is because these tables exist in the data dictionary inside InnoDB, but the corresponding .ibd files don’t exist. They were not copied into the backup directory. These tables will be removed from the data dictionary, and when you restore the backup and start InnoDB, they will no longer exist and will not cause any errors or warnings to be printed to the log file.
But seems like this is not working.
For example:
mysql> show databases;
--------------------
Database |
--------------------
information_schema |
akila |
mysql |
performance_schema |
sakila |
sys |
world |
--------------------
xtrabackup --backup --databases='sakila mysql sys' --target-dir=test
xtrabackup --prepare --target-dir=test
service mysql stop
rm -rf /var/lib/mysql/*
xtrabackup -copy-back -target-dir=test
chown -R mysql:mysql /var/lib/mysql
service mysql start
get a lot of errors in MySQL error log like:
2018-05-04T13:50:09.143130Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-05-04T13:50:09.143149Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-05-04T13:50:09.143153Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-05-04T13:50:09.143156Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './akila/actor.ibd' OS error: 71
2018-05-04T13:50:09.143161Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-05-04T13:50:09.143164Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-05-04T13:50:09.143166Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-05-04T13:50:09.143170Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `akila/actor`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to reso
lve the issue.
2018-05-04T13:50:09.143175Z 0 [Warning] InnoDB: Ignoring tablespace `akila/actor` because it could not be opened.
2018-05-04T13:50:09.143188Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-05-04T13:50:09.143192Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-05-04T13:50:09.143194Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-05-04T13:50:09.143196Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './akila/address.ibd' OS error: 71
2018-05-04T13:50:09.143199Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-05-04T13:50:09.143201Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-05-04T13:50:09.143203Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-05-04T13:50:09.143206Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `akila/address`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to re
solve the issue.
2018-05-04T13:50:09.143209Z 0 [Warning] InnoDB: Ignoring tablespace `akila/address` because it could not be opened.
2018-05-04T13:50:09.143219Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-05-04T13:50:09.143222Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-05-04T13:50:09.143224Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-05-04T13:50:09.143227Z 0 [ERROR] InnoDB: Cannot open datafile for read-only: './akila/category.ibd' OS error: 71
2018-05-04T13:50:09.143229Z 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2018-05-04T13:50:09.143231Z 0 [ERROR] InnoDB: The error means the system cannot find the path specified.
2018-05-04T13:50:09.143234Z 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2018-05-04T13:50:09.143236Z 0 [ERROR] InnoDB: Could not find a valid tablespace file for `akila/category`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
so seems like information about this databases wasn't deleted from data dictionary.
Same issue appears if use xtrabackup 2.4.11 and MySQL 5.6.39-83.1-log Percona Server (GPL)
If use xtrabackup 2.3.10 and MySQL 5.6.39-83.1-log Percona Server (GPL), then during --prepare phase you got messages like:
InnoDB: Table world/country in the InnoDB data dictionary has tablespace id 39, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
and databases are correctly removed from data dictionary.