Bug #118233 Fixed a bug in the DDL for invisible indexes.
Submitted: 20 May 13:54 Modified: 20 May 15:01
Reporter: alex xing (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:8.0.42 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[20 May 13:54] alex xing 
Description:
The original code had a bug: when executing DROP INDEX + ADD INDEX while altering the INVISIBLE property, the operation would have no effect, leaving the INVISIBLE state unchanged.

How to repeat:
CREATE TABLE normal_users1 (
 user_id INT PRIMARY KEY AUTO_INCREMENT, 
 user_name VARCHAR (50), 
 age INT, 
 city VARCHAR (50), 
 KEY idx_age(age)
);
alter table normal_users1 drop index idx_age, add index idx_age(age) invisible;

show create table normal_users1;

| normal_users1 | CREATE TABLE `normal_users1` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

Suggested fix:
fix as the below patch
[20 May 13:55] alex xing 
a simple patch to describe the bugfix

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: alter_invisible_index_ddl_bugfix.patch (application/octet-stream, text), 587 bytes.
[20 May 15:01] MySQL Verification Team 
Hello Alex Xing,

Thank you for the report and contribution.

regards,
umesh