Bug #118233 Fixed a bug in the DDL for invisible indexes.
Submitted: 20 May 2025 13:54 Modified: 20 May 2025 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 2025 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 2025 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 2025 15:01] MySQL Verification Team 
Hello Alex Xing,

Thank you for the report and contribution.

regards,
umesh
[6 Apr 6:40] Prabakaran Thirumalai 
Posted by developer:
 
Hello Alex Xing,

Thank you for your contribution. The patch appears to address the issue outlined in the bug report. We are currently validating it to ensure there is no impact on existing functionality. We will keep you updated with our findings.

Thanks,
Prabakaran