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:
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 2019 7:12] songlei wang
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:

2. create different struct table in master and slave
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;

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
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

| 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 2019 16:58] MySQL Verification Team

Thanks for the report. Verified as described.

all best
[17 Aug 2020 16:00] Jon Stephens
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.

[18 Aug 2020 12:18] MySQL Verification Team
Thank you, Jon.