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

ALTER UNDO TABLESPACE fails after restore when undo tablespace state is changed during backup

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 8.0.5
    • Component/s: None
    • Labels:
      None

      Description

      Start MS8.0.15
      Add data in MS using sysbench
      Create a new undo tablespace

      CREATE UNDO TABLESPACE new_undo_tablespace ADD DATAFILE 'new.ibu';

      Change the state of new_undo_tablespace to inactive/active continuously

      while true; do
       bin/mysqladmin ping --user=root --socket=${PWD}/socket.sock 2>/dev/null 1>&2
       if [ "$?" -ne 0 ]; then
       break
       fi
       echo "Change the state of new_undo_tablespace to inactive"
       bin/mysql -uroot --socket=${PWD}/socket.sock --force -e "ALTER UNDO TABLESPACE new_undo_tablespace SET INACTIVE;"
       echo "Change the state of new_undo_tablespace to active"
       bin/mysql -uroot --socket=${PWD}/socket.sock --force -e "ALTER UNDO TABLESPACE new_undo_tablespace SET ACTIVE;"
      done
      

      Take full backup with lock-ddl

      ./xtrabackup --user=root --password='' --backup --target-dir=${backup_dir}/full -S ${mysqldir}/socket.sock --datadir=${datadir} --lock-ddl

      Start a data load on the server using sysbench
      Take incremental backup with lock-ddl 

      ./xtrabackup --user=root --password='' --backup --target-dir=${backup_dir}/inc --incremental-basedir=${backup_dir}/full -S ${mysqldir}/socket.sock --datadir=${datadir} --lock-ddl

      Prepare the full backup

      ./xtrabackup --user=root --password='' --prepare --apply-log-only --target_dir=${backup_dir}/full

      Prepare the incremental backup

      ./xtrabackup --user=root --password='' --prepare --target_dir=${backup_dir}/full --incremental-dir=${backup_dir}/inc

      Stop the MS and move the data directory to another location
      Restore the backup

      ./xtrabackup --user=root --password='' --copy-back --target-dir=${backup_dir}/full --datadir=${datadir}

      Start MS and view the status of undo tablespaces

      8.0.15>SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%undo%';
      +---------------------+--------+
      | NAME                | STATE  |
      +---------------------+--------+
      | innodb_undo_001     | active |
      | innodb_undo_002     | active |
      | new_undo_tablespace | empty  |
      +---------------------+--------+
      

      Change the state of undo tablespace

      8.0.15>ALTER UNDO TABLESPACE new_undo_tablespace SET ACTIVE;
      ERROR 1533 (HY000): Failed to alter: UNDO TABLESPACE new_undo_tablespace
      

      View all the undo tablespaces

      8.0.15>SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
      +-----------------+------------+
      | TABLESPACE_NAME | FILE_NAME  |
      +-----------------+------------+
      | innodb_undo_001 | ./undo_001 |
      | innodb_undo_002 | ./undo_002 |
      +-----------------+------------+
      2 rows in set, 1 warning (0.00 sec)
      
      8.0.15>show warnings;
      +---------+------+------------------------------------------------------+
      | Level   | Code | Message                                              |
      +---------+------+------------------------------------------------------+
      | Warning | 1812 | Tablespace is missing for table new_undo_tablespace. |
      +---------+------+------------------------------------------------------+
      

      Issue: MS displays that the tablespace for new_undo_tablespace is missing.

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                sergei.glushchenko Sergei Glushchenko
                Reporter:
                manish.chawla Manish Chawla
              • Votes:
                0 Vote for this issue
                Watchers:
                2 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 - 6 hours
                  6h