MySQL Bugs: #113177: unexcepted explain when using invisiable index

Bug #113177	unexcepted explain when using invisiable index
Submitted:	22 Nov 2023 7:36	Modified:	24 Nov 2023 16:21
Reporter:	ding qi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
after locked by metadata lock, an explain result is unexcepted, is this a bug?

How to repeat:
CREATE TABLE `tb1` (
  `id` int NOT NULL,
  `c` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;
insert into tb1 values(1,1);
insert into tb1 values(2,2);

--thread 1
begin;
select * from tb1 where c=2;

--thread 2 
alter table tb1 alter index c invisible; 
//blocked here, waiting for thread 1

--thread 3
explain select * from tb1 where c=2;
//blocked here, waiting for thread 2

--thread 1
rollback;

** after this command, the alter table command continues, and after it release the metadatalock,  the explain query in thread3 continues.

As expected, thread3 executes after the index c changed to "invisible",
but the result of thread 3 is 

mysql> explain select * from tb1 where c=2;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb1   | NULL       | ref  | c             | c    | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+

Hi Mr. bryce,

Thanks a lot for your bug report.

However, it is not a bug.

If you look at the tables for data locks and data lock waits, you will notice that not a single InnoDB lock is set, nor is anything waiting for the InnoDB locks.

This is all only about table metadata locks.

The explain shows result with the index being visible, because it executes before table cache is updated, after the ALTER table.

This is all explained in our Reference Manual.

Not a bug.

The result of the third query will be different ,if I change the engine of tb1 to MyISAM (will be affected by the alter statement)

Is this by designed too?

Yes, definitely.

That is because MyISAM uses only table locks. No index locks, record locks. Nothing but the table locks.