Xtrabackup ,“INSTANT ADD/DROP columns” ，does it still exist in PXB 8.0.30

MySQL & MariaDB Percona XtraBackup
1

MySQL : 8.0.30 community
Xtrabackup : 8.0.30

I know there is an “INSTANT ADD/DROP columns” error in 8.0.29，does it still exist in 8.0.30。
When 8.0.30 is used, the error still exists。

> select version();
+-----------+
| version() |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)

> alter table sbtest3 add column ta varchar(10);
Query OK, 0 rows affected (0.06 sec)

do backup

xtrabackup --defaults-file=/data/my3306.cnf --socket=/data/mysql3306/mysql.sock --user=databak --password=123456 --backup --stream=xbstream --target-dir=/data/backup --ftwrl-wait-timeout=300 --compress --compress-threads=2 --parallel=2

error message:

2022-12-09T15:02:57.484758+08:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: databak, password: set, port: 3306, socket: /data/mysql3306/mysql.sock
2022-12-09T15:02:57.491459+08:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.30
2022-12-09T15:02:57.495139+08:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ...
2022-12-09T15:02:57.497382+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns
2022-12-09T15:02:57.497424+08:00 0 [ERROR] [MY-011825] [Xtrabackup] This feature is not stable and will cause backup corruption.
2022-12-09T15:02:57.497439+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please check https://docs.percona.com/percona-xtrabackup/8.0/em/instant.html for more details.
2022-12-09T15:02:57.497451+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Tables found:
2022-12-09T15:02:57.497463+08:00 0 [ERROR] [MY-011825] [Xtrabackup] yqtest/sbtest3
2022-12-09T15:02:57.497474+08:00 0 [ERROR] [MY-011825] [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHM=COPY on all listed tables to fix this issue.
1 Like
2

Did you follow the instructions to OPTIMIZE the table after altering? Please do this then try xtrabackup again.

1 Like
3

Whether it is necessary to execute OPTIMIZE ？
When I want to use xtrabackup, I cannot use the “INSTANT ADD/DROP columns” 。
The INSTANT problem in 8.0.29, 8.0.30 Community Edition, has been fixed?
At present, I feel that the INSTANT function is abandoned.
When I want to use xtrabackup, I cannot use the INSTANT function.

Thank you for your answer.

1 Like
4

The issue is that you must first upgrade your tables to 8.0.30. This is accomplished using OPTIMIZE / ALTER ENGINE=InnoDB. After you have done this, you don’t have to do it again and you can use xtrabackup without issue. This is a necessary step to fix the bug introduced in 8.0.29 by Oracle.

1 Like