Understanding the HASH_SCAN Bug in MySQL Slave Rows Search Algorithms and Its Workarounds

Introduction MySQL has a long‑standing issue where tables without a primary key cause each row‑based DML event to trigger a full‑table or secondary‑index scan on the replica, leading to unacceptable overhead and latency.

MySQL 5.6 introduced the slave_rows_search_algorithms parameter to mitigate this problem by collecting all before‑images of a ROWS EVENT and performing a single scan, using a hash to match records.

Excerpt from the original article: https://yq.aliyun.com/articles/41058.

Confusing Behavior of the HASH_SCAN Bug This piece does not revisit the theory of slave_rows_search_algorithms but instead describes a pitfall encountered when using the parameter, which appears to be both a bug and a feature.

Problem Description With row‑based replication and consistent master‑slave data, the slave SQL thread reports "Can't find record".

Reproduction Steps

Configuration:

slave_rows_search_algorithms='INDEX_SCAN,HASH_SCAN'

binlog_format=ROW

On the master:

CREATE TABLE t1 (A INT UNIQUE KEY, B INT); INSERT INTO t1 VALUES (1,2);

REPLACE INTO t1 VALUES (1,3),(1,4);

After these statements, the replica throws the error.

On the replica:

SET GLOBAL slave_rows_search_algorithms='INDEX_SCAN,TABLE_SCAN'; START SLAVE; – the problem is resolved.

Alternative Work‑around:

1. Change the UNIQUE KEY to a PRIMARY KEY.

2. Split the REPLACE statement into separate statements:

REPLACE INTO tt.t1 VALUES (1,3); REPLACE INTO tt.t1 VALUES (1,4);

Analysis

The manual defines the default value as INDEX_SCAN,TABLE_SCAN , meaning indexed searches use indexes and others use table scans. To force hashing for searches without a primary or unique key, set INDEX_SCAN,HASH_SCAN . Using TABLE_SCAN,HASH_SCAN forces hashing for all searches and can cause "record not found" or duplicate‑key errors when multiple updates affect the same row within a single event.

According to the manual, the search order is INDEX_SCAN → HASH_SCAN → TABLE_SCAN. If a table lacks a primary key, HASH_SCAN can improve replica performance, but it may fail to locate rows when the same row is updated multiple times.

Bug Report An Oracle engineer confirmed this is a HASH_SCAN bug fixed in MySQL 8.0.17 (not yet released at the time). The bug occurs when a row is updated twice within a single Update_rows_log_event; HASH_SCAN puts both rows in a hash map, iterates once, applies the first update, misses the second, and then raises an error.

Why the fix is only in 8.0 and not 5.7 remains unclear, leading to speculation that the issue is considered a feature rather than a bug.

Solution

1. Add a primary key to the table (the recommended practice).

2. Change the parameter to slave_rows_search_algorithms='INDEX_SCAN,TABLE_SCAN' .

3. Upgrade to MySQL 8.0.17, though this may be impractical for many production environments.