Exploring MySQL 8.0.29 Instant ADD/DROP COLUMN Feature and Its Impact on Online DDL

1. Introduction

Prior to MySQL 8.0.29, online DDL could only add columns instantly at the end of a table, making it inconvenient to insert a column after a specific existing column. MySQL 8.0.29 extends ALTER TABLE … ALGORITHM=INSTANT to support instant addition, deletion, and row‑size evaluation of columns at any position.

Each instant add or drop creates a new row version; the new INFORMATION_SCHEMA.INNODB_TABLES column TOTAL_ROW_VERSIONS tracks the number of versions, with a maximum of 64 per table.

2. New Feature Experience

2.1 Instant column addition at any position

A 20‑million‑row table sbtest1 is created using sysbench:

sysbench ./oltp_read_write.lua --mysql-host=10.186.61.168 \
--mysql-user=root \
--mysql-password='XXXXXX' \
--mysql-port=3388 \
--mysql-socket=/data/mysql8.0.29/data/mysqld.sock \
--mysql-db=test_a --tables=1 \
--table-size=20000000 \
--report-interval=2 \
--threads=10 prepare

Instantly add column k2 after column k :

ALTER TABLE sbtest1 ADD COLUMN k2 int(10) AFTER k, ALGORITHM=INSTANT;

The operation completes within seconds, whereas the same operation on MySQL 8.0.27 (which only supports addition at the end) takes over 7 minutes.

2.2 Instant column deletion

MySQL 8.0.29 also allows instant column removal:

ALTER TABLE sbtest1 ADD COLUMN c4 int(10), ADD COLUMN c5 int(10), ALGORITHM=INSTANT;
ALTER TABLE sbtest1 DROP COLUMN c4, DROP COLUMN c5, ALGORITHM=INSTANT;

In versions prior to 8.0.29 this results in error ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation .

2.3 Row‑size limit evaluation when adding columns

From 8.0.29 onward, MySQL checks the row‑size limit during column addition. Adding a VARCHAR(4990) column that exceeds the limit triggers:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535...

Earlier versions allowed the addition without error, potentially causing future DML failures.

2.4 XtraBackup 8.0.29 backup issue

Backing up a MySQL 8.0.29 instance that contains tables with instant add/drop columns causes XtraBackup to fail because the InnoDB redo‑log format changes, leading to corrupted redo logs. The community version still has this defect, though the Percona build fixes it.

Before backup, query INFORMATION_SCHEMA.INNODB_TABLES for tables with TOTAL_ROW_VERSIONS > 0 and run OPTIMIZE TABLE on them to convert instant‑column tables to regular tables.

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
OPTIMIZE TABLE test_a.sbtest1;

3. Conclusion

The ALTER TABLE … ALGORITHM=INSTANT feature in MySQL 8.0.29 dramatically improves online DDL efficiency by allowing instant column addition or removal at any position and by enforcing row‑size limits, while also introducing backup considerations that administrators must address.