Percona XtraBackup 8.0.29 and INSTANT ADD/DROP Columns_please run optimize table or alter table algorithm-优快云博客

July 19, 2022

Oracle’s MySQL 8.0.29 release extended the support for ALTER TABLE … ALGORITHM=INSTANT to 1) allow users to instantly add columns in any position of the table, and 2) instantly drop columns. As part of this work, the InnoDB redo log format has changed for all DML operations on the server. This new redo log format introduced a design flaw that can cause data corruption for tables with INSTANT ADD/DROP COLUMNS.

The corruption happens when InnoDB crash recovery takes place. InnoDB applies redo logs at startup. Percona XtraBackup copies the redo log during backup and applies it 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 (check PS-8291 / PS-8292 / PS-8303 for more details) we also raised and provided patches for those issues as a contribution to Community MySQL (see 107613 / 107611 / 107854 for details). Percona XtraBackup 8.0.29 can take backups of Percona Server for MySQL 8.0.29 with tables that have INSTANT ADD/DROP COLUMNS. However, the current version of Community MySQL 8.0.29 still has this flaw, making it unsafe to take backups.

It is impossible for XtraBackup to deal with the corrupted redo log generated by Community 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 will create an error with a list of the affected tables and provide 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 added INSTANT algorithm. For example:

Working:

Shell

1	ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=INPLACE/COPY;

Not Working:

Shell

ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=INSTANT;

ALTER TABLE tb1 ADD/DROP COLUMN [...], ALGORITHM=DEFAULT;

ALTER TABLE tb1 ADD/DROP COLUMN [...];

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

Find all tables with INSTANT ADD/DROP COLUMNS:

Shell

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 are all good. Percona XtraBackup will take backups of your MySQL 8.0.29 servers. If not, please run OPTIMIZE TABLE on the list of tables before taking a 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

Shell

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.127714+05:30 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.

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

Algorithm INSTANT is the new default in 8.0.29. If you do not specify an algorithm, all ALTER TABLE ADD/DROP COLUMN statements will use the default algorithm.

The INSTANT algorithm is considered unstable at this point.

Percona XtraBackup will refuse to take backups from MySQL 8.0.29 tables that have been modified using this algorithm. Running OPTIMIZE TABLE on affected tables will bring them back to a safe state.

Percona XtraBackup is able to take backups seamlessly from Percona Server for MySQL, as the corruption issues have been fixed in the upcoming release of Percona Server for MySQL 8.0.29

 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
 OPTIMIZE TABLE xxxx;