[MDEV-30670] Partial backup/restore operation fail with mariabackup after mariadb upgrade Created: 2023-02-17  Updated: 2023-12-14

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.10.3
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Antoine Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: corruption, recovery
Environment:

Debian 11
Virtual server on proxmox 7 env
NVME disks


Issue Links:
Duplicate
duplicates MDEV-23394 mariadb --prepare --export fails with... Open
Relates
relates to MDEV-29050 mariabackup issues error messages dur... Closed

 Description   

Since mariadb upgrade from 10.5 to 10.10.3 on our server, partial backup/restore script not working anymore.

The script is used to restore db to another host (dev station).

Assume that:

  • mariadb version is the same on 2 hosts
  • source host have slave functionnality enabled.
  • mysql_upgrade was executed on source and destination host
  • all tables of databases have innodb engine

Our script have several phases that run successfully on mariadb 10.5 :

  • we import schema from source host and import it for destination host to be up to date
    on source : mysqldump -u$GX_USER --password=$GX_PASS --no-data $schema > /var/tmp/dump_schema_$schema.sql"
    on destination : mysql -uroot --password=$LOCAL_ROOT_PASS -e "CREATE DATABASE $schema;";mysql -uroot --password=$LOCAL_ROOT_PASS $schema < /var/tmp/dump_schema_$schema.sql
  • for every db to backup and restore, we retrieve table list and discard tablespace is enabled by : SET FOREIGN_KEY_CHECKS=0;ALTER TABLE ${DB}.${TABLE} DISCARD TABLESPACE;SET FOREIGN_KEY_CHECKS=1;
  • for every db to backup we execute mariabackup tools to backup all needed databases:
    mariabackup --binlog-info=OFF --backup --target-dir=${PARTIAL_BACKUP_DIR} --socket /var/run/mysqld/mysqld.sock --user=${USER} --password=${PASS} --databases=${DB} --tables-exclude=${GX_EXCLUDED_TABLES};
    mariabackup --prepare --export --target-dir=${PARTIAL_BACKUP_DIR};
  • mariadb stoped on destination host
  • for every db to backup, we copy idb and cfg to destination host : cp -rf ${PARTIAL_BACKUP_DIR}/${DB}/*. {cfg,ibd}

    /var/lib/mysql/${DB}/

  • ensure mariadb list dir permissions fixed by chown -R mysql:mysql /var/lib/mysql
  • mariadb started on destination host
  • On destination host, we import tablespace by executing command : echo "set FOREIGN_KEY_CHECKS=0;ALTER TABLE ${DB}.${TABLE} IMPORT TABLESPACE;set FOREIGN_KEY_CHECKS=1;ALTER TABLE ${DB}.${TABLE} engine = innodb" | mysql -S /var/run/mysqld/mysqld.sock -uroot --password=$LOCAL_ROOT_PASS -D ${DB}
  • remove all cfg files on destination host rm /var/lib/mysql/${DB}/*.cfg

Some of db tables generate errors when we import tablespace (not all) : ERROR 1034 (HY000) at line 1: Index for table 'acces' is corrupt; try to repair it.

We think the problem is during mariabackup -export -prepare phase because mariabackup -backup seems to be ok (we saw [00] 2023-02-17 08:09:26 completed OK!) and prepare stage seems not completing successfully :Not show ok status at then end of process :

mariabackup based on MariaDB server 10.10.3-MariaDB debian-linux-gnu (x86_64)
 
[00] 2023-02-17 08:09:26 mariabackup: auto-enabling --innodb-file-per-table due to the --export option
 
[00] 2023-02-17 08:09:26 cd to /var/tmp/gestixi_data/
 
[00] 2023-02-17 08:09:26 open files limit requested 0, set to 1024
 
[00] 2023-02-17 08:09:26 This target seems to be not prepared yet.
 
[00] 2023-02-17 08:09:26 mariabackup: using the following InnoDB configuration for recovery:
 
[00] 2023-02-17 08:09:26 innodb_data_home_dir = .
 
[00] 2023-02-17 08:09:26 innodb_data_file_path = ibdata1:12M:autoextend
 
[00] 2023-02-17 08:09:26 innodb_log_group_home_dir = .
 
[00] 2023-02-17 08:09:26 InnoDB: Using Linux native AIO
 
[00] 2023-02-17 08:09:26 Starting InnoDB instance for recovery.
 
[00] 2023-02-17 08:09:26 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
 
2023-02-17  8:09:26 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
 
2023-02-17  8:09:26 0 [Note] InnoDB: Number of transaction pools: 1
 
2023-02-17  8:09:26 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
 
2023-02-17  8:09:26 0 [Note] InnoDB: Using Linux native AIO
 
2023-02-17  8:09:26 0 [Note] InnoDB: Initializing buffer pool, total size = 100.000MiB, chunk size = 100.000MiB
 
2023-02-17  8:09:26 0 [Note] InnoDB: Completed initialization of buffer pool
 
2023-02-17  8:09:26 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
 
2023-02-17  8:09:26 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=7212435476868
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
 
2023-02-17  8:09:27 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Cannot open datafile for read-only: './gestixi/paylineWebPayment.ibd' OS error: 71
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
 
2023-02-17  8:09:27 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Could not find a valid tablespace file for gestixi/paylineWebPayment. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
 
2023-02-17  8:09:27 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Cannot open datafile for read-only: './mysql/gtid_slave_pos.ibd' OS error: 71
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Error number 2 means 'No such file or directory'
 
2023-02-17  8:09:27 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
 
2023-02-17  8:09:27 0 [ERROR] InnoDB: Could not find a valid tablespace file for mysql/gtid_slave_pos. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue.
 
2023-02-17  8:09:27 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 2 row operations to undo
 
2023-02-17  8:09:27 0 [Note] InnoDB: Trx id counter is 2440583605
 
2023-02-17  8:09:27 0 [Note] InnoDB: Starting final batch to recover 85 pages from redo log.
 
2023-02-17  8:09:27 0 [Note] InnoDB: Last binlog file './binlog.000028', position 5012056
 
[00] 2023-02-17 08:09:28 Last binlog file ./binlog.000028, position 5012056
 
[00] 2023-02-17 08:09:28 Prepare export : executing "/usr/bin/mariabackup" --mysqld --defaults-file=./backup-my.cnf --defaults-group-suffix= --datadir=. --innodb --innodb-fast-shutdown=0 --loose-partition --innodb_purge_rseg_truncate_frequency=1 --innodb-buffer-pool-size=104857600 --console --log-error= --skip-log-bin --bootstrap < mariabackup_prepare_for_export.sql

No error reported in /var/log/mysql/error.log when tablespace are imported

We tried to :

  • lauch mysqlcheck on dest host to verify table status without errors but optimize report an index error
  • test on mariadb 10.11 on 2 hosts if issue concern mariabackup version (same result)
  • reset dest host mariadb configuration
  • innodb param tuning in my.cnf configuration on source host
  • increase free space on source and destination hosts for temporary files when mariabackup is executed
  • check file system issue

But the error still remain.

Do you have any idea about our problem ?
Do you think its a bug because script working perfectly before upgrade ?

Thanks

 Comments   
Comment by Marko Mäkelä [ 2023-12-14 ]

This is a duplicate of MDEV-23394.

Backups are supposed to be processed with the same major version of backup as the originating server. It is a good practice to prepare backups after they have been taken, to have an additional consistency check. The backup could be incomplete for various reasons, such as a scenario like MDEV-19492 or MDEV-31399. Starting with the redo log file format change MDEV-14425 (10.8), such log overwriting is not always noticed before the backup is prepared; see MDEV-31410.

Generated at Thu Feb 08 10:18:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.