mariabackup issues error messages during InnoDB tablespaces export on partial backup preparing

User upgraded from MariaDB Community Server 10.2 to 10.4. User had setup automation to check output of mariabackup --prepare to ensure no errors occurred which could imperil the health of the backup.

The basic procedure is to take a partial backup using tables-exclude or similar. Prepare is then performed with export.

In general, mariabackup does not seem to explicitly track what tables were excluded between backup and prepare steps. In 10.2, during prepare export we see-

2022-07-06 18:01:51 140608802916480 [Warning] InnoDB: Tablespace 5 was not found at ./mariadbslap/Slap2.ibd when restoring a (partial?) backup. All redo log for this file will be ignored!

To be clear, the backup stage for the above log snippet used tables-exclude='Slap2' successfully. The good news for 10.2 is this is flagged as a Warning, recognizing that prepare export simply does not know if this is a problem or not and it may be legitimate/intended behavior.

10.4 (possibly 10.3; most explicitly, possibly 10.3.5) changes this for the worse-

2022-07-06 17:00:51 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.

2022-07-06 17:00:51 0 [ERROR] InnoDB: The error means the system cannot find the path specified.

2022-07-06 17:00:51 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.

2022-07-06 17:00:51 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mariadbslap/Slap2.ibd' OS error: 71

2022-07-06 17:00:51 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.

2022-07-06 17:00:51 0 [ERROR] InnoDB: The error means the system cannot find the path specified.

2022-07-06 17:00:51 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.

2022-07-06 17:00:51 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``mariadbslap`.`Slap2``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.

So now in 10.4, it is referencing the same table which 10.2 identified, but instead of throwing a WARNING, 10.4 is putting an ERROR into its output. While it seems both 10.2 and 10.4 return 0 indicating a successful process exit, users practicing due diligence are rightly going to see an ERROR entry and figure out what to do about it. Between the 10.2 and 10.4 behaviors, the 10.2 behavior of flagging this as a WARNING is preferable because it differentiates the issue from breaking errors, especially given this is just expected partial backup operation. And while the specific concern being expressed here is for how this makes life difficult for automation, it is also worth pointing out that 10.4's behavior makes it less clear than 10.2 does for what is going on and what may be the cause, such that it is likely to cause even experienced DBAs who have not encountered this situation yet cause to panic and worry that their partial backup is broken.

In an ideal case though, no errors or warnings should be thrown for table files which are missing due to exclusions made during mariabackup backup. According to our KB documentation for --tables-exclude-

If a backup is a partial backup, then Mariabackup will record that detail in the xtrabackup_info file.

In short, mariabackup currently advertises retaining this information for it to use later. This is the behavior we would like to see mariabackup prepare export provide.

The problematic behavior can be reproduced via the following steps on MariaDB Community Server 10.6.8:

# Note that binary logging must be enabled and active in the MariaDB Server instance for this to work

mariabackup_password='securePasswordHere';

backup_path='/where/i/want/my/backup/to/go;

backup_log='/where/i/want/my/prepre/log/to/go;

mariadb -e "CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY '${mariabackup_password}';";

mariadb -e "GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariabackup'@'localhost';";

# Create database and tables

mariadb -e 'CREATE DATABASE mariadbslap';

mariadb mariadbslap -e 'CREATE TABLE Slap (i int);';

mariadb mariadbslap -e 'CREATE TABLE Slap2 (i int);';

# Create some writes on the table

for ((i=1; i<2000; i++)) ; do

  # Once the workload has gotten a little bit in...

  if (($i == 100)) ; then

    # Start a backup in parallel so writes can continue to roll in during the backup (forces mariabackup to need to leverage logs to obtain point in time consistency during prepare later on)

   mariabackup --backup -u mariabackup -p"${mariabackup_password}" --target-dir="${backup_path}" --tables-exclude='Slap2' &

  fi;

  # Generate writes on both the included and excluded tables

  mariadb mariadbslap -e 'INSERT INTO Slap VALUES ('"${i}"');';

  mariadb mariadbslap -e 'INSERT INTO Slap2 VALUES ('"${i}"');';

done;

# Prepare the backup

mariabackup --export --prepare -u mariabackup -p"${mariabackup_password}" --target-dir="${backup_path}" 2>&1 | tee "${backup_log}";

In the output for the last mariabackup command you should see [ERROR] InnoDB: Operating system error number 2 in a file operation..

You can adapt the above procedure for Community Server 10.2 by adjusting the mariabackup user GRANT to-

GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';

The process is otherwise the same and should output a WARNING instead of an ERROR.

A .zip is attached with log output from backup and {{prepare}] commands run from the above procedure for MariaDB versions 10.2.27, 10.2.43, 10.4.20, and 10.6.8.