ApsaraDB RDS:What do I do if I fail to back up an ApsaraDB RDS instance that runs MySQL 8.0.31?

Problem description

I fail to perform a full physical backup on my ApsaraDB RDS instance that runs MySQL 8.0.31. MySQL 8.0.31 is equivalent to version 20230630 of AliSQL.

Causes

INSTANT ADD/DROP COLUMNS is introduced in MySQL 8.0.29. For more information, see Percona Xtrabackup 8.0.29 Troubleshoot.

Solutions

1. Execute the following statement to check whether tables with INSTANT ADD/DROP COLUMNS exist:

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

   • If the following result is returned, the sbtest2 table with INSTANT ADD/DROP COLUMNS exists in the sbtest database.

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

   • If the following result is returned, no tables with INSTANT ADD/DROP COLUMNS exist.

     Empty set (0.00 sec)

2. Execute the following statement to recreate the table with INSTANT ADD/DROP COLUMNS:

   mysql> OPTIMIZE TABLE sbtest.sbtest2;