Bug #96148 using Invisible Index when slave apply EVENT
Submitted: 10 Jul 7:12 Modified: 15 Jul 16:58
Reporter: songlei wang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.16 OS:CentOS
Assigned to: CPU Architecture:Any
Tags: INVISIBLE INDEX, replication

[10 Jul 7:12] songlei wang
Description:
During the slave apply the UPDATE_ROWS_EVENT or DELETE_ROWS_EVENT event, the non-null unique index which is set invisible may be chosen to scan the table.

How to repeat:
1. build replication with following parameters:
   log_bin=on
   binlog_format=row
   binlog_row_image=minimal

2. create different struct table in master and slave
master:
set sql_log_bin=OFF;
create table testa (A int not null, B int not null, C int, primary key (A));
set sql_log_bin=ON;

slave:
set sql_log_bin=OFF;
create table testa (A int not null, B int not null, C int, primary key (B), unique key (A));
alter table testa ALTER INDEX A INVISIBLE;
set sql_log_bin=ON;

3. insert data in master
master:
insert into testa values (1,1,1),(2,2,2),(3,3,3);

4. update data in master
update testa set C=100 where A=1;

5. trace the slave source code

Rows_log_event::do_apply_event()
| do_before_row_operations()
  | row_operations_scan_and_key_setup()

it will choose the index A to scan the table even if it is set to invisible

Suggested fix:
In function search_key_in_table,  it should consider the flag of invisible when check non-null unique key.
[15 Jul 16:58] Bogdan Kecman
Hi,

Thanks for the report. Verified as described.

all best
Bogdan