Bug #88847 Invisible Indexes are not respected by slave under ROW format

Description:
Invisible indexes are not respected by slave sql applier under row binlog format, invisible index will be  chose to do updating.

Although optimizer is not invoked for slave row applying, I think INVISIBLE attribute should be treat as DISABLED KEYS(which is respected by SQL applier and skipped). Besides, it can provide a chance to DBA for control index picking up.

How to repeat:
### test case as below:

--source include/master-slave.inc
--source include/have_binlog_format_row.inc

create table t1(id int auto_increment, name varchar(30), key idx_id(id)) engine=innodb;
insert into t1(name) values('MySQL');
insert into t1(name) values('InnoDB');
insert into t1(name) select a.name from t1 a, t1 b;
insert into t1(name) select a.name from t1 a, t1 b;
insert into t1(name) select a.name from t1 a, t1 b;
sync_slave_with_master;

## change idx_id index on slave invisible
connection slave;
alter table t1 alter index idx_id invisible;

connection master;
delete from t1 limit 100;
sync_slave_with_master;

## check whether invisible attribute is respected
connection slave;
select table_name, index_name, is_visible from information_schema.statistics where index_name = 'idx_id';
select * from sys.schema_unused_indexes where index_name = 'idx_id';
select * from sys.schema_index_statistics where index_name = 'idx_id';

==== below is partial result

select table_name, index_name, is_visible from information_schema.statistics where index_name = 'idx_id';
TABLE_NAME      INDEX_NAME      IS_VISIBLE
t1      idx_id  NO
select * from sys.schema_unused_indexes where index_name = 'idx_id';
object_schema   object_name     index_name
select * from sys.schema_index_statistics where index_name = 'idx_id';
table_schema    table_name      index_name      rows_selected   select_latency  rows_inserted   insert_latency  rows_updated    update_latency  rows_deleted    delete_latency
test    t1      idx_id  199     21.83 ms        0       0 ps    0       0 ps    100     16.63 ms

From the result we can see that although is invisible on slave, it is still used anyway.

Suggested fix:

      /*
        - Skip innactive keys
        - Skip unique keys without nullable parts
        - Skip indices that do not support ha_index_next() e.g. full-text
        - Skip primary keys
      */
      if (!(table->s->keys_in_use.is_set(key)) ||
          ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME) ||
          !(table->file->index_flags(key, 0, true) & HA_READ_NEXT) ||
          (key == table->s->primary_key))
        continue;

I think the fix is simple, we should use table->s->usable_indexes(thd) instead of keys_in_use bitmap to do index filter.

Hello Fungo Wang,

Thank you for the report and test case.

Thanks,
Umesh

Documented fix in the MySQL 8.0.11 changelog as follows:

    Row-based replication used the wrong set of indexes on the
    slave.

Closed.