WL#11123: Group Replication: hold reads and writes when the new primary has replication backlog to apply

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Executive Summary

This worklog implements a fencing mechanism when a new primary is being
promoted in Group Replication (GR). The fencing will hold connections from
writing *and* reading from the new primary until it has applied all the pending
backlog of changes that came from the old primary.
Applications will not read stale data or do writes for a short period of time
(during the new primary promotion).


There is an expectation that if an application reads from the primary, it
will always read its own writes. This expectation can even be subconsciously
enforced when GR is deployed in single primary mode.

However, on primary fail-over, the new primary will not reject or hold
reads after being promoted. Instead, it will turn on conflict detection while
the backlog is being applied. This means that if the application wrote
A to the previous primary and then reads A from the new primary, it may
see a stale value (i.e., miss its previous write, because it is still in
the backlog to be applied).

For example:

1. session1 @ server1 (primary) : W(a) C
2. server1 fails, server2 is promoted and is now the new primary
3. server2 is still applying the backlog (thence W(a) has not been applied
   yet to server2)
4. router fails over application to server2 (new primary)
5. session1 @ server2 (new primary) : R(a) C
6. Application reads an old version of "a", even though it was always
   connected to the primary (!)

The user of GR not expect this behavior, the primary member always shall have
the most recent data from the group.

- In async master-slave replication there is no provision in the system
  to prevent anything like this -- meaning that MySQL users have always
  faced this issue and they choose how to deal with it. The same techniques
  that users use today in async master-slave promotion can be applied
  to GR single primary promotion;
- And the current GR behavior is already better than the case in async
  master-slave promotions;
- But, GR is raising the bar and expectations are higher when it comes
  to synchrony and consistency;
- The user/middleware/router can hold incoming read/writes to the new
  primary until it finishes applying the backlog or we can do it in the
  server itself.

BIG FAT NOTE: This is not about implementing read your writes consistency,
but rather making sure that the following simple and straightforward
assumption holds: if one reads from the primary, at any point in time, one
will always read one's own writes.

Potential Solutions

There are three approaches to fix this (though 2 of them are pretty
much the same):

1. Fix it in the router, so that fences the new primary until the backlog
   is flushed;
2. Fix it in GR, so that the plugin **rejects** incoming traffic, until its
   backlog is flushed;
3. Fix it in GR, so that the plugin **holds** incoming traffic, until its
   backlog is flushed.

- Something similar to #3 will have to be implemented for "WL#10379: Group
  Replication: consistent reads";
- #1 is valid only for InnoDB Cluster setups, i.e., when MySQL Router is
- #2 is just a variation of #3. #1 vs #2,#3 is really what the decision boils
  down to.

Chosen Solution

Incoming transit when a member is being elected as Primary will be put on hold
until it applies all backlog. After this it will resume and retrieve the most
recent data from the group.

The option can be enabled on a GLOBAL or SESSION scope. The goal is to allow
create a session that can be used to do operations without cause impact on
others sessions that are on hold until backlog is applied.

Functional Requirements

- FR1: In single-primary mode, if an application reads from the primary, it must
  always read the most up to date snapshot.

- FR2: Any write statement, listed in the HLD, SHALL be hold on the primary
  when: (a) the primary is being promoted; (b) the option to wait for the
  backlog to be installed is set; and (c) there is replication backlog to

- FR3: Any read statement, listed in the HLD, SHALL be hold on the primary
  when: (a) the primary is being promoted; (b) the option to wait for the
  backlog to be installed is set; and (c) there is replication backlog to

- FR4: Any write statement that is on hold waiting for the backlog
  to be applied SHALL be released, and thus allowed to resume
  the execution, once the replication backlog has been applied.

- FR5: Any read statement that is on hold waiting for the backlog
  to be applied SHALL be released, and thus allowed to resume
  the execution, once the replication backlog has been applied.

- FR6: There will be a server option to specify consistency of primary
  failover, allowing be set on GLOBAL or SESSION scope

- FR7: An group action executed to elect a new primary shall hold all reads and
  writes when backlog is being applied

- FR8: A server that is being promoted, applying backlog and is stopped, either
  by STOP GROUP_REPLICATION or server shutdown, statements on hold will resume
  and throw error ER_GR_KILLED to the client session.

- FR9: The system variable wait_timeout will be used to set a limit of time a
  statement can be on hold. When the period on hold is greater than
  wait_timeout the statement will abort and throw error ER_GR_HOLD_WAIT_TIMEOUT
  to the client session.

- FR10: If a server that is being elected as the group primary member faces an
  issue and moves to ERROR state while applying its replication backlog then
  statements, listed on HLD, SHALL be aborted with
  ER_GR_HOLD_MEMBER_STATUS_ERROR. The hook will retrieve error for all
  connections until STOP group replication is executed.

- FR11: There is no impact of setting group_replication_consistency as

- FR12: There is no impact due to this WL on the SECONDARIES when

- FR13: A group of statements that belong to the same transaction shall run the
  hook only on the first statement, preventing multiple calls to plugin on same

- FR14: During the period of time a statement is on hold, the stage on process
  list for his connection shall be "Executing hook on transaction begin".

Even when there is no backlog to apply, its check is still done and
transactions are held until then.
In practice this means that an empty backlog is still considered as a valid
backlog for the above functional requirements

Non-Functional Requirements

- NFR1: This WL must have no impact on transaction execution performance when
  no single primary election is being executed.

Summary of the approach

It was added a hook on server side to be triggered when a statement begins.

On server side we will validate if the command is eligible to be put on hold
and if so the hook will be called and by consequence its observers.

If GR plugin observer  is installed, the statement is intercepted and put on
hold if a primary election is ongoing.
When no observer is registered, no action is taken to minimize the performance

The server won't fence the statements that:

* don't modify data. So the command don't use stored routines and is one of:
   - SHOW
   - DO
   - EMPTY
   - USE
* SELECT command to database performance_schema
* SELECT command to table PROCESSLIST from database infoschema
* SELECT command to database sys
* SELECT command that don't use tables
* SELECT command that don't execute user defined functions
* SHUTDOWN command

After the server validation, if the command need to be fenced we will call the

The Group Replication observer will verify that:

- group_replication_single_primary_mode is set
- the server is the primary
- group_replication_consistency is set with BEFORE_ON_PRIMARY_FAILOVER option
- incoming transactions arriving through group_replication_applier or
  group_replication_applier channels are never hold, since there are
  transactions already under group control.
- backlog is being applied

When the new primary applied all backlog it will send a signal for all
statements on hold, unlocking the transactions.

If the primary being elected is stopped while applying backlog the hold
statements are released by throwing an error to the client session.

A statement on hold for a period of time greater than the wait_timeout [1]
value will abort and retrieve an error to the client. The timeout prevents
client statement being on hold indefinitely.

[1] - https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout

Security context

From a point of view of malicious attack to the group, if someone is able to
prevent a secondary to apply his backlog when being promoted it will cause
statements being resumed with an error due to timeout until the attack is

Upgrade/downgrade and cross-version replication

The worklog don't cause any impact on upgrades or cross-version replication.

This mechanism depends solely on the new primary server having or not this
feature, and for that reason no cross version issues exist.

User interface

On this worklog we will introduce a option to specify which data consistency
the user wants when failover of primary happens.

A option will be added to server to specify the failover guarantees.

 - name: group_replication_consistency
 - default: EVENTUAL
 - scope: global, session
 - dynamic: yes

BEFORE_ON_PRIMARY_FAILOVER means that new queries to a new elected primary that
it is applying backlog from the old primary will be hold before execution until
the backlog is applied

EVENTUAL allows executing reads from newer primary even when the backlog isn't
applied, the client will return old values. Writes to new primary will fail due
super read only mode being enabled.


The state "Executing hook on transaction begin" is displayed on
INFORMATION_SCHEMA.PROCESSLIST to allow user see if transaction is on hold.

Deployment and installation

The worklog won't introduce any modification on plugin deployment or installation.


This worklog don't introduce any change to protocols.

Failure model specification

When a group is electing a new primary, if the option
BEFORE_ON_PRIMARY_FAILOVER is enabled, the group will wait for apply all
backlog to resume the statements.

A statement on hold can be abort and throw an error to the client  before
applied all backlog in two cases:

- group replication plugin is stopped (including on server restart and shutdown)
- period of time that statement is on hold is bigger than wait_timeout system

If the member during the election change the state to ERROR all the commands
will be aborted until STOP GROUP_REPLICATION is invoked.
Summary of Changes

Server core changes

1. Add a new hook call on server side whenever a statement starts, more
   precisely when its being parsed.

2. Create a new 'begin' hook function on rpl_handler.cc and make sql command and
   table validations

Group Replication changes

1. Add a new server variable, group_replication_consistency to select the
   behavior when the primary is being promoted.

2. Create a new observer transaction hook that will call group
   transaction observation manager registered observers

3. On plugin it was added functions that subscribe and unsubscribe the
   observation manager observer for when primary election occur or plugin is

4. A class was created created to hold the statements when backlog isn't
   applied on a primary election.

Sequence Diagram

When a statement is called, it will be intercepted:

      |- server code ...
      |- launch_hook_trans_begin() @ sql/rpl_handler.cc
      `- server code

On rpl_handler it makes validations to SQL command and the tables:

      `- if (server validations)
            group_replication_trans_begin() @ plugin/group_replication/src/observer_trans.cc

The function group_replication_trans_begin validates if group replication is
running and if there are group transaction observation manager observers

    `- group_replication_trans_begin()
        if ( plugin is running && observers registered)
           before_transaction_begin() @ plugin/group_replication/src/plugin_handlers/primary_election_handler.cc

When a primary election is occurring the observer is registered and the hold of
transactions is enabled.

    `- internal_primary_election()

When a primary election finishes we resume the hold transactions and unregister
the transaction observer. It can be unregistered when STOP GROUP_REPLICATION is

    `- primary_election_process_handler()

The hold transaction was implemented on a new class, Hold_transactions at

The methods are:

- enable : start hold transactions

- disable : stop hold transactions and send signal to resume held transactions

- wait_until_primary_failover_complete : if hold mechanism is enable, transaction will be
  held until backlog applied or a error condition occur.