Bug #104899 ALTER TABLE ALGORITHM=INSTANT fails if there *was* a fulltext index
Submitted: 10 Sep 7:33 Modified: 10 Sep 7:56
Reporter: Saverio Miroddi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:8.0.25, 8.0.26, 8.0.13 OS:Any
Assigned to: CPU Architecture:Any
Tags: fulltext alter_table add_column

[10 Sep 7:33] Saverio Miroddi
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)
[10 Sep 7:56] MySQL Verification Team
Hello Saverio Miroddi,

Thank you for the report and test case.
Verified as described.

regards,
Umesh