MySQL Bugs: #102339: Adding Functional Index using ALTER TABLE ALGORITHM=INPLACE produces error

Bug #102339	Adding Functional Index using ALTER TABLE ALGORITHM=INPLACE produces error
Submitted:	22 Jan 2021 6:02	Modified:	22 Jan 2021 13:41
Reporter:	Robert Roland	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	8.0.23	OS:	Windows (10 x64)
Assigned to:		CPU Architecture:	Other (AMD 3900X)
Tags:	ALGORITHM INPLACE, CONCAT Functional Index, DDL, functional index

Description:
Creating a functional index using the syntax:

ALTER TABLE mytable ADD INDEX n12((concat(`n1`, `n2`)), ALGORITHM=INPLACE, LOCK=SHARED;

generates the following error:
1846 - ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.

"mytable" has an autoincrement primary key and n1,n2,n3...n9 are each varchar(30) utf8mb4/utf8mb4_0900_ai_ci.

Interestingly, the ALTER TABLE statement above works for other combinations of concat(n1,n2 .. n9) attempted except for concat(n2,n3,n4) which produces the error. This failure was discovered when the server was found rebuilding a 100GB table after having rapidly created several nearly identical functional indexes without having explicitly specified ALGORITHM=INPLACE.

How to repeat:
Create table with autoincrement primary key and at least 5 varchar columns.
Populate table.
Assuming varchar columns have names n1,n2,n3... then execute:

ALTER TABLE mytable ADD INDEX n345((concat(`n3`, `n4`, `n5`)), ALGORITHM=INPLACE, LOCK=SHARED;
(which should proceed without error)

next execute:

ALTER TABLE mytable ADD INDEX n234((concat(`n2`, `n3`, `n4`)), ALGORITHM=INPLACE, LOCK=SHARED;
(which will produce the error)

If you delete the first index, "n345", and then attempt again to create the second index, "n234", it will proceed without error. Furthermore, the "n345" index can then also be created without error.

Suggested fix:
A workaround for the issue appear to be that you must create the functional indexes that use CONCAT in a certain order starting with the index containing the lowest/leftmost column and working upwards.

After additional testing the issue is not always resolved by creating the functional indexes in a particular order. The error may still arise at an unpredictable point after as few as 4 or as many as 7 indexes have been created. This was further tested on another installation of Server 8.0.23 Win 10 x64 Intel i9-9900K 64GB with similar results.

Hello Robert,

Thank you for the report and feedback.

regards,
Umesh

-- 8.0.23

mysql> drop table mytable;
Query OK, 0 rows affected (0.05 sec)

mysql>  CREATE TABLE `mytable` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `n1` varchar(30) DEFAULT NULL,
    ->   `n2` varchar(30) DEFAULT NULL,
    ->   `n3` varchar(30) DEFAULT NULL,
    ->   `n4` varchar(30) DEFAULT NULL,
    ->   `n5` varchar(30) DEFAULT NULL,
    ->   `n6` varchar(30) DEFAULT NULL,
    ->   `n7` varchar(30) DEFAULT NULL,
    ->   `n8` varchar(30) DEFAULT NULL,
    ->   `n9` varchar(30) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql> ALTER TABLE mytable ADD INDEX n567((concat(`n5`,`n6`,`n7`))), ALGORITHM=INPLACE, LOCK=SHARED;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mytable ADD INDEX n12((concat(`n1`, `n2`))), ALGORITHM=INPLACE, LOCK=SHARED;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE mytable ADD INDEX n345((concat(`n3`, `n4`, `n5`))), ALGORITHM=INPLACE, LOCK=SHARED;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>  ALTER TABLE mytable ADD INDEX n234((concat(`n2`, `n3`, `n4`))), ALGORITHM=INPLACE, LOCK=SHARED;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY.