Fix physical backup failure on MySQL 8.0.31 - ApsaraDB RDS

Full physical backups fail on ApsaraDB RDS instances running MySQL 8.0.31 (AliSQL version 20230630) when one or more tables have used the INSTANT ADD/DROP COLUMNS feature introduced in MySQL 8.0.29.

For background on this issue, see Percona Xtrabackup 8.0.29 Troubleshoot.

Solution

Step 1: Identify affected tables

Run the following query to find all tables that have used INSTANT ADD/DROP COLUMNS:

SELECT NAME FROM information_schema.innodb_tables WHERE TOTAL_ROW_VERSIONS != 0;

If the query returns rows, those tables must be rebuilt before the backup can succeed.

+----------------+
| NAME           |
+----------------+
| sbtest/sbtest2 |
+----------------+
1 row in set (0.01 sec)

If the query returns an empty set, INSTANT ADD/DROP COLUMNS is not the cause of the backup failure.
```
Empty set (0.00 sec)
```

Step 2: Rebuild the affected tables

For each affected table, run the following statement to rebuild it:

OPTIMIZE TABLE sbtest.sbtest2;

Replace sbtest.sbtest2 with the actual database and table name from Step 1. If multiple tables are listed, run the rebuild command for each one.

After rebuilding all affected tables, retry the backup.