Description:
ALTER TABLE ALGORITHM=INSTANT fails under certain conditions not specified in the online DDL manpage (https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-colum...).
Specifically, if there *was* (not "is") a fulltext column on a table, the adding a column with instant algorithm will fail.
It's not clear if this behavior is intended (therefore, it's a documentation bug), or if not (in this case, it's a server bug).
I'm aware that FT indexes add invisible data structures to the table, which are not removed with the FT index removal, so it's possible that this is a case of the former. If so, the manpage should be changed from:
> Columns cannot be added to tables that include a FULLTEXT index.
to:
> Columns cannot be added to tables that include or previously included a FULLTEXT index.
How to repeat:
CREATE TABLE mytable (
id int NOT NULL,
mycolumn text NOT NULL,
PRIMARY KEY (id),
FULLTEXT KEY mycolumn (mycolumn)
);
-- Remove the fulltext index
--
ALTER TABLE mytable DROP KEY mycolumn;
-- According to the documentation, this should succeed.
--
ALTER TABLE mytable ADD newcolumn json NOT NULL, ALGORITHM=INSTANT;
-- ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
-- Display the current table structure.
--
SHOW CREATE TABLE mytable;
-- *************************** 1. row ***************************
-- Table: mytable
-- Create Table: CREATE TABLE `mytable` (
-- `id` int NOT NULL,
-- `mycolumn` text COLLATE utf8mb4_general_ci NOT NULL,
-- PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
-- Recreate it from scratch.
--
CREATE TABLE mytable2 (
id int NOT NULL,
mycolumn text COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- Now the instant change succeeds.
--
ALTER TABLE mytable2 ADD newcolumn json NOT NULL, ALGORITHM=INSTANT;
-- Query OK, 0 rows affected (0,01 sec)
