MySQL Bugs: #116930: Deleting indexes from partition tables with function indexes is not online ddl

Bug #116930	Deleting indexes from partition tables with function indexes is not online ddl
Submitted:	11 Dec 2024 0:39	Modified:	13 Jan 6:40
Reporter:	chong zhang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0, 8.0.40, 8.4.3, 9.1.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Deleting indexes from partition tables with function indexes is not  online ddl

How to repeat:
-- Deleting indexes from partition tables with function indexes is not  online ddl

mysql> CREATE TABLE `hash_t1` (
    ->   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    ->   `r1` int DEFAULT NULL,
    ->   `log_date` datetime DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_date_format` ((date_format(`log_date`,_utf8mb3'%Y%m%d')))
    -> ) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    -> /*!50100 PARTITION BY HASH (`id`)
    -> PARTITIONS 1024 */;

Query OK, 0 rows affected, 1 warning (12.28 sec)

mysql>  
mysql>  ALTER TABLE hash_t1 DROP INDEX idx_date_format, ALGORITHM = INPLACE, LOCK = NONE;
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.
mysql> 

--

Hello chong zhang,

Thank you for the report and feedback.

regards,
Umesh

Development concluded this as Not a bug. Closing it now.

======================

This is not a bug.

Dropping a virtual column from a partitioned table can't be done INPLACE. See the doc :

https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html#online-ddl-colum...
 - Dropping a VIRTUAL column 

   Dropping a VIRTUAL column can be performed instantly or in place for non-partitioned tables. 

Now, functional indexes, when created, have (invisible) virtual columns created. 

Therefore, when we try to drop the index, those virtual columns are being dropped, and it isn't allowed.