WL#12892: Deprecate slave-rows-search-algorithms

Affects: Server-8.0   —   Status: Complete 

EXECUTIVE SUMMARY
=================

This worklog implements a deprecation warning when user engages the
command line option (or the corresponding system variable)
--slave_rows_search_algorithms.

BACKGROUND
==========

The option slave-rows-search-algorithms controls how the replication
applier looks up rows in tables, when applying UPDATE or DELETE row
events. The default for this option is HASH_SCAN,INDEX_SCAN as of 8.0,
and this is always best, both for performance and (in a corner case)
for correctness. So it is better to not give the possibility to use
any other algorithm.

User Stories
------------

U1. As a user, I don't want the server to have options that should
    never be changed, so that there is less to learn in order to use
    the server.

U2. As a user, I want replication to always be as fast as possible,
    and not have options to make it slower.

U3. As a user, I want replication to always work, and not have options
    that might break it.

Notes:

 1. For indexed tables, it is always faster to use INDEX_SCAN.

 2. For unindexed tables, it is always faster to use HASH_SCAN.

 3. For indexed tables, using TABLE_SCAN,HASH_SCAN can make the slave
    stop with an error (duplicate key) in cases like the following:

    CREATE TABLE t (a INT PRIMARY KEY, b INT UNIQUE NOT NULL);
    INSERT INTO t VALUES (1, 2), (2, 1);
    UPDATE t SET b = b - 1 WHERE b > 0;

    (I.e., rows are updated on master in the order of a secondary index, and
    the row updates are order-dependent in such a way that doing the same row
    updates in primary key order will create collisions in some index.)

 4. For indexed tables, using TABLE_SCAN,HASH_SCAN can make the slave
    stop with an error (key not found) in cases like the following:

    --delimiter |
    CREATE FUNCTION f1 () RETURNS INT BEGIN
      UPDATE t1 SET a = 2 WHERE a = 1;
      UPDATE t1 SET a = 3 WHERE a = 2;
      RETURN 0;
    END|
    --delimiter ;
    CREATE TABLE t1 (a INT);
    INSERT INTO t1 VALUES (1);
    SELECT f1();


Copyright (c) 2000, 2025, Oracle Corporation and/or its affiliates. All rights reserved.