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

Backup fails when undo truncation log is present

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • None
    • 8.0.30-23 (Q3 2022)
    • None
    • Yes

    Description

      We use standard MySQL primary => secondary replication schema.
      MySQL secondary instance is running with --super-read-only flag.
      MySQL secondary instance is used for long queries, thus have big undo tablespaces, which are periodically truncated.
      Due to some bugs in MySQL (still digging) truncation process does not complete, leaving MySQL instance alive, but with undo truncation logs:

       [DEV]root@myt-nza7ldt2gfco5nj6 ~ # ls -alh /var/lib/mysql/undo*
       -rw-r----- 1 mysql mysql 80M Feb 3 14:08 /var/lib/mysql/undo_001
       -rw-r----- 1 mysql mysql 80M Feb 3 14:08 /var/lib/mysql/undo_002
       -rw-r----- 1 mysql mysql 16K Feb 3 13:59 /var/lib/mysql/undo_1_trunc.log
       -rw-r----- 1 mysql mysql 16K Feb 3 14:01 /var/lib/mysql/undo_2_trunc.log
      

      These logs will exist for weeks, until MySQL instance restart.

      In such situation, PXB fails to create a backup on secondary instance:

       [DEV]root@myt-nza7ldt2gfco5nj6 ~ # /usr/bin/xtrabackup --backup --stream=xbstream --lock-ddl --lock-ddl-timeout=3600 --parallel=1 --datadir=/var/lib/mysql --port=3308 > /dev/null
       xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/var/lib/mysql/.tmp --innodb_page_size=16384 --innodb_buffer_pool_size=256M --innodb_file_per_table=1 --innodb_log_buffer_size=16777216 --innodb_log_file_size=268435456 --innodb_adaptive_hash_index=1 --innodb_use_native_aio=1 --innodb_flush_method=O_DIRECT --innodb_flush_log_at_trx_commit=1 --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --server-id=4 --log_bin=mysql-bin-log-myt-nza7ldt2gfco5nj6-db-yandex-net --log-bin-index=/var/lib/mysql/mysql-bin-log.index --datadir=/var/lib/mysql 
       xtrabackup: recognized client arguments: --port=3307 --socket=/var/run/mysqld/mysqld.sock --user=admin --password=* --host=myt-nza7ldt2gfco5nj6.db.yandex.net --port=3307 --backup=1 --stream=xbstream --lock-ddl=1 --lock-ddl-timeout=3600 --parallel=1 --port=3308 
       /usr/bin/xtrabackup.bak version 8.0.25-17 based on MySQL server 8.0.25 Linux (x86_64) (revision id: a460572)
       220203 14:02:31 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=myt-nza7ldt2gfco5nj6.db.yandex.net;port=3308;mysql_socket=/var/run/mysqld/mysqld.sock' as 'admin' (using password: YES).
       220203 14:02:31 version_check Connected to MySQL server
       220203 14:02:31 version_check Executing a version check against the server...
       220203 14:02:31 version_check Done.
       220203 14:02:31 Connecting to MySQL server host: myt-nza7ldt2gfco5nj6.db.yandex.net, user: admin, password: set, port: 3308, socket: /var/run/mysqld/mysqld.sock
       Using server version 8.0.25-15
       220203 14:02:32 Executing LOCK TABLES FOR BACKUP...
       xtrabackup: uses posix_fadvise().
       xtrabackup: cd to /var/lib/mysql
       xtrabackup: open files limit requested 0, set to 1024
       xtrabackup: using the following InnoDB configuration:
       xtrabackup: innodb_data_home_dir = .
       xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
       xtrabackup: innodb_log_group_home_dir = ./
       xtrabackup: innodb_log_files_in_group = 2
       xtrabackup: innodb_log_file_size = 268435456
       2022-02-03 14:02:32 139770467744832 InnoDB: Using Linux native AIO
       xtrabackup: using O_DIRECT
       Number of pools: 1
       xtrabackup: inititialize_service_handles suceeded
       220203 14:02:32 Connecting to MySQL server host: myt-nza7ldt2gfco5nj6.db.yandex.net, user: admin, password: set, port: 3308, socket: /var/run/mysqld/mysqld.sock
       xtrabackup: Redo Log Archiving is not set up.
       Starting to parse redo log at lsn = 66688617652
       Recovery parsing buffer extended to 4194304.
       Recovery parsing buffer extended to 8388608.
       220203 14:02:33 >> log scanned up to (66707951861)
       xtrabackup: Generating a list of tablespaces
       xtrabackup: Generating a list of tablespaces
       Scanning './'
       Directory '/var/lib/mysql/.tmp' will not be scanned because it is a hidden directory.
       Completed space ID check of 2 files.
       Allocated tablespace ID 2 for mysql/mdb_repl_mon, old maximum was 0
       Undo tablespace number 1 was being truncated when mysqld quit.
       Cannot recover a truncated undo tablespace in read-only mode
       xtrabackup: error: xb_load_tablespaces() failed with error code 57
      

       

      Attachments

        Activity

          People

            rahul.malik Rahul Malik (Inactive)
            mialinx Dmitry Smal
            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 - Not Specified
                Not Specified
                Logged:
                Time Spent - 45 minutes
                45m

                Smart Checklist