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:
Percona XtraBackup 8.0.32-25 allows instant columns in the backup for MySQL 8.0.32. This limitation remains for backing up MySQL 8.0.31 and lower versions. This limitation does not exist for backing up Percona Server for any version.
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