Skip to content
logo
Percona XtraBackup
Error Message: Found tables with row versions due to INSTANT ADD/DROP columns
Initializing search
    percona/pxb-docs
    percona/pxb-docs
    • Home
      • About Percona XtraBackup
      • How Percona XtraBackup works
      • Understand version numbers
      • Install Percona XtraBackup 8.0
      • Use an APT repo to install Percona XtraBackup
      • Use a YUM repo to install Percona XtraBackup
      • Use DEB or RPM downloaded packages to install Percona XtraBackup
      • Install Percona XtraBackup from a Binary Tarball
      • Compile and install Percona XtraBackup from source code
      • What’s in the packages
      • Uninstall Percona XtraBackup
      • Run Percona XtraBackup in a Docker container
      • Implementation details
      • Connection and privileges needed
      • Configure xtrabackup
      • Server version and backup version comparison
      • xtrabackup exit codes
      • The backup cycle - full backups
      • Incremental backup
      • Compressed backup
      • Partial backups
      • Percona XtraBackup user manual
      • Throttling backups
      • Encrypted InnoDB tablespace backups
      • Encrypt backups
      • LRU dump backup
      • Point-in-time recovery
      • Smart memory estimation
      • Work with binary logs
      • Improved log statements
      • Work with SELinux
      • Work with AppArmor
      • Enable the server to communicate via TCP/IP
      • Install and configure an SSH server
      • Analyze table statistics
      • FLUSH TABLES WITH READ LOCK option
      • lock-ddl-per-table option improvements
      • Take an incremental backup using page tracking
      • The xbcloud binary
      • Use the xbcloud binary with Swift
      • Use xbcloud Binary with Amazon S3
      • Use the xbcloud binary with MinIO
      • Use the xbcloud with Google Cloud Storage
      • Exponential backoff
      • Use the xbcloud binary with Microsoft Azure Cloud Storage
      • How-tos and recipes
      • Release notes index
      • Percona XtraBackup 8.0.30-23 (2022-11-14)
      • Percona XtraBackup 8.0.29-22 (2022-07-19)
      • Percona XtraBackup 8.0.28-21 (2022-05-25)
      • Percona XtraBackup 8.0.28-20
      • Percona XtraBackup 8.0.27-19
      • Percona XtraBackup 8.0.26-18.0
      • Percona XtraBackup 8.0.25-17.0
      • Percona XtraBackup 8.0.23-16.0
      • Percona XtraBackup 8.0.22-15.0
      • Percona XtraBackup 8.0.14
      • Percona XtraBackup 8.0.13
      • Percona XtraBackup 8.0.12
      • Percona XtraBackup 8.0.11
      • Percona XtraBackup 8.0.10
      • Percona XtraBackup 8.0.9
      • Percona XtraBackup 8.0.8
      • Percona XtraBackup 8.0.7
      • Percona XtraBackup 8.0.6
      • Percona XtraBackup 8.0.5
      • Percona XtraBackup 8.0.4
      • Percona XtraBackup 8.0-3-rc1
      • Error Message: Found tables with row versions due to INSTANT ADD/DROP columns
        • Percona XtraBackup Error Message
        • Summary
      • The xtrabackup option reference
      • The xbcrypt binary
      • The xbstream binary
      • Frequently asked questions
      • Glossary
      • Index of files created by Percona XtraBackup
      • Trademark policy
      • Copyright and licensing information
      • Version checking

    • Percona XtraBackup Error Message
    • Summary

    Error Message: Found tables with row versions due to INSTANT ADD/DROP columns¶

    MySQL 8.0.29 extended the support for ALTER TABLE … ALGORITHM=INSTANT to allow users to add a column in any table position or drop any column. As part of this update, the redo log format has changed for all server DML operations. This updated redo log format has a design flaw that can cause data corruption for tables with INSTANT ADD/DROP COLUMNS.

    The corruption happens when the crash recovery occurs. InnoDB applies redo logs at startup. Xtrabackup copies the redo log during the backup and applies the log as part of the --prepare step to bring the backup to a consistent state.

    Percona fixed the corruption issue and several other issues with the INSTANT ADD/DROP column feature in the upcoming Percona Server for MySQL 8.0.29.

    For more details, see the following:

    • PS-8291,

    • PS-8292

    • PS-8303

    Percona XtraBackup 8.0.29 is able to take backups of Percona Server for MySQL 8.0.29 with tables that have INSTANT ADD/DROP COLUMNS. However, the issues in MySQL 8.0.29 make this version unsafe to take backups.

    It is impossible for XtraBackup to deal with the corrupted redo log generated by MySQL 8.0.29 and, for this reason, XtraBackup 8.0.29 version will not take backups if it detects tables with INSTANT ADD/DROP COLUMNS and generates an error listing the affected tables and providing instructions to convert them to regular tables.

    Please avoid ALTER ADD/DROP COLUMN without an explicit ALGORITHM=INPLACE. The default ALGORITHM is INSTANT, so ALTER TABLE without the ALGORITHM keyword uses the newly introduced INSTANT algorithm.

    If you already have such tables (information below on how to find such tables), users are advised to do OPTIMIZE TABLE against these tables before taking backups.

    To find tables with INSTANT ADD/DROP COLUMNS run the following command:

    mysql> SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
    +---------+
    | NAME    |
    +---------+
    | test/t1 |
    | test/t2 |
    | test/t3 |
    +---------+
    3 rows in set (0.02 sec)
    

    If this query shows an empty result set, you have no issues. Percona Xtrabackup takes the backup of your MySQL 8.0.29 server. If the results are a list of tables, please run OPTIMIZE TABLE on the list before taking the backup.

    Percona XtraBackup Error Message¶

    If Percona XtraBackup detects that MySQL 8.0.29 server has tables with instant add/drop columns, it aborts with the following error message

    2022-07-01T15:18:35.127689+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
    2022-07-01T15:18:35.127714+05:30 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
    2022-07-01T15:18:35.127723+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
    2022-07-01T15:18:35.127730+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t1
    2022-07-01T15:18:35.127737+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t2
    2022-07-01T15:18:35.127744+05:30 0 [ERROR] [MY-011825] [Xtrabackup] test/t3
    2022-07-01T15:18:35.127752+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.
    

    Summary¶

    The option, ALGORITHM=INSTANT is the default value in MySQL 8.0.29. If you do not specify an algorithm, all ALTER TABLE ADD/DROP COLUMN statements use this algorithm.

    The INSTANT algorithm is unstable at this point.

    Percona XtraBackup refuses to take backups from MySQL 8.0.29 tables that have used this algorithm. Percona Server users do not suffer the same limitations as the corruption issues are fixed in the upcoming release of Percona Server for MySQL 8.0.29

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: 2022-11-10
    Back to top
    Previous Percona XtraBackup 8.0-3-rc1
    Next The xtrabackup option reference
    Percona LLC and/or its affiliates, © 2023
    Made with Material for MkDocs