MySQL Bugs: #96148: using Invisible Index when slave apply EVENT

Bug #96148	using Invisible Index when slave apply EVENT
Submitted:	10 Jul 2019 7:12	Modified:	17 Aug 2020 16:00
Reporter:	songlei wang	Email Updates:
Status:	Closed	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

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.

Hi,

Thanks for the report. Verified as described.

all best
Bogdan

Documented fix as follows in the MySQL 8.0.22 changelog:

    When using row-based replication, the replica was allowed to use
    an invisible index when searching for rows to synchronize.

Closed.

Thank you, Jon.