MySQL Bugs: #118088: Optimize DDL handling for redundant invisible index changes

Bug #118088	Optimize DDL handling for redundant invisible index changes
Submitted:	29 Apr 14:06	Modified:	29 Apr 14:15
Reporter:	alex xing (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S5 (Performance)
Version:	8.0.42	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
Problem:
The original implementation would execute in-place DDL operations (acquiring MDL X locks) even when altering index visibility to the same state. This unnecessarily impacts workload performance for redundant operations.

Example:

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;

-- First change (valid)
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

-- Second redundant change (should be rejected)
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

Original behavior:
Both statements would execute full in-place DDL with MDL X locks.

Optimization:
Now we detect and reject redundant visibility changes immediately with error:

ERROR 8020 (HY000): Key 'i_idx' in table 't1' is already visible

Benefits:

Avoids unnecessary MDL X lock acquisition

Reduces DDL overhead for redundant operations

Provides clearer feedback to users about invalid operations

How to repeat:
just read the code

Suggested fix:
optimize as the below patch

a simple patch to describe the optimization (*) 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_optimize.patch (application/octet-stream, text), 924 bytes.

Hello Alex Xing,

Thank you for the report and contribution.

regards,
umesh