Uploaded image for project: 'Percona XtraBackup'
  1. Percona XtraBackup
  2. PXB-1536

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

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 2.4.12
    • Fix Version/s: 2.4.13
    • Component/s: None
    • Labels:

      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.

       

       

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                sergei.glushchenko Sergei Glushchenko (Inactive)
                Reporter:
                mfedotov Maksim Fedotov
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 2 days
                  2d