MySQL Bugs: #118233: Fixed a bug in the DDL for invisible indexes.

Bug #118233	Fixed a bug in the DDL for invisible indexes.
Submitted:	20 May 2025 13:54	Modified:	1 May 14:19
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

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

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.

Hello Alex Xing,

Thank you for the report and contribution.

regards,
umesh

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

We observed that attached patch breaks the existing ALTER TABLE behavior when using ALGORITHM=INPLACE. 

Additionally, ha_alter_info->handler_flags is currently being set using Alter_inplace_info flags, whereas the patch incorrectly uses Alter_info flags. This should be updated to use the appropriate Alter_inplace_info flag.

Kindly address the above issues.

Before Patch:
-------------
mysql>  CREATE TABLE  t1 (a INT, b INT, KEY(a) INVISIBLE, KEY(b));
Query OK, 0 rows affected (0.077 sec)

mysql> ALTER TABLE t1 ALTER INDEX a VISIBLE, ALGORITHM = INPLACE;
Query OK, 0 rows affected (0.039 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE t1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                            |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

After patch:
-----------
mysql> CREATE TABLE  t1 (a INT, b INT, KEY(a) INVISIBLE, KEY(b));
Query OK, 0 rows affected (0.065 sec)

mysql> ALTER TABLE t1 ALTER INDEX a VISIBLE, ALGORITHM = INPLACE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.

Hello Prabakaran Thirumalai, 
thank you for your reply. I will provide a correct patch. Please wait a moment.

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

Contribution: 0001-Fixed-a-bug-in-the-DDL-for-invisible-indexes.patch (application/octet-stream, text), 12.23 KiB.

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

Contribution: 0001-Fixed-a-bug-in-the-DDL-for-invisible-indexes_new.patch (application/octet-stream, text), 13.28 KiB.