I’m trying to use MySQL’s instant column drop functionality on a large/high traffic table and it’s not working the way I expect. My database is hosted in AWS RDS on MySQL version 8.0.34.

Here is the query:

ALTER TABLE old_big_table
DROP COLUMN not_needed,
ALGORITHM=INSTANT;

and the error message I get is:

SQL Error [1845] [0A000]: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

I checked the restrictions on this feature and it says it’s not supported for:

• Tables with FTS indexes – This table does not have one
• Tables having row_format=compressed. – Checked information_schema.tables and the row_format is Dynamic
• Temporary tables – Not a temporary table

What’s interesting is I can ADD a new column instantly and then drop that column just fine:

-- Works just fine
ALTER TABLE old_big_table
ADD COLUMN test_instant_col BIGINT NULL,
ALGORITHM=INSTANT;

-- Works just fine
ALTER TABLE old_big_table
DROP COLUMN test_instant_col,
ALGORITHM=INSTANT;

I’m wondering if there is any way to instantly drop columns that were not added instantly?