Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

xtrabackup 2.4.11, MySQL 5.7.21-21-log Percona Server partial backup and errors in MySQL log

Description

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.

 

 

 

Environment

None

Attachments

2

Smart Checklist

Activity

Show:

Sergei Glushchenko December 17, 2018 at 9:06 AM

This has been fixed: https://github.com/percona/percona-xtrabackup/pull/597 and the fix will come in the next 2.4 release.

Lalit Choudhary August 27, 2018 at 9:24 AM
Edited

Hi Maksim,

Thank you for the details.

Validated as described.

 

Test steps : 

 

started mysqld with this new prepared directory as datadir.

As per  xtarbackup Partial backup doc 

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.

And the error message you will see during the prepare phase.

 

Here the issue is, We are getting an error in mysql error log after restore and not in the prepare phase.

xtrabackup prepare and error log attached.

 

Maxim Fedotov August 24, 2018 at 2:19 PM

Hi Lalit, 

the steps are in the issue:

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

 

and you will see errors in mysql error log.

Lalit Choudhary August 7, 2018 at 11:44 AM

Hello Maksim,

Thank you for the report and details.

It will be great if you can provide clean steps to reproduce this.

Maxim Fedotov May 15, 2018 at 7:33 AM

Hello Vishal!

 

Thanks for your reply. I've just reproduced this one more time on CentOS 7 and Ubuntu 12.04. If you need more clean steps to reproduce this - will be glad to provide.

Thanks,

Maksim

Done

Details

Assignee

Reporter

Labels

Time tracking

2d logged

Fix versions

Affects versions

Priority

Smart Checklist

Created May 4, 2018 at 2:20 PM
Updated March 6, 2024 at 7:32 PM
Resolved December 17, 2018 at 9:07 AM
Configure