[Proposal]: Precise and observable primary switchover in Group Replication / InnoDB Cluster #97

Pre-flight Checklist

• I have searched existing GitHub issues and did not find a duplicate proposal.
• I have removed or redacted sensitive information.

Primary Contact Name

Vinicius Grippa

Primary Contact Email

vgrippa@gmail.com

Company / Organization

Independent Contributor

Role

Database Engineer

Additional Authors / Contributors

No response

Component

Server

Target Release (Optional)

No response

Roadmap Section

Performance & Observability

Related Issues / Pull Requests / References (Optional)

No response

Executive Summary

When promoting a new primary in an InnoDB Cluster —
Cluster.setPrimaryInstance(target, {runningTransactionsTimeout: N}), i.e. the server UDF
group_replication_set_as_primary() — the primary election waits up to N seconds for in-flight
transactions on the current primary to drain. Two problems, both validated on 8.0.46 and 8.4.10:

1. Over-restriction. A transaction that is read-only or merely open-and-idle (a bare BEGIN,
   or an uncommitted SELECT) is treated as a "running transaction" and stalls the whole election
   for the full timeout — even though it cannot be lost by a switchover and is invisible in
   information_schema.INNODB_TRX. Measured election stall equals the timeout exactly
   (N=15→15.00 s, N=20→20.00 s, N=50→50.00 s), versus ~2 ms for a switch with no open transaction.
2. No observability. Even at log_error_verbosity=3, a timeout-forced switch is
   indistinguishable in the error log from a clean one — the same message sequence is emitted;
   only the gap between MY-013214 (Starting … Primary election change) and MY-011507
   (A new primary … elected) differs. When the timeout is reached, Transaction_monitor_thread
   disconnects binloggable transactions that have not committed via the internal
   mysql_close_connection_of_binloggable_transaction_not_reached_commit service (no SQL KILL;
   client sees ERROR 2013 Lost connection, transaction rolled back), with nothing logged.

This proposal (a) scopes the switchover wait to transactions that can actually be lost, and
(b) makes the wait and the timeout-forced disconnect observable in the error log and at runtime.

User / Developer Stories

User / Developer Stories

• As a DBA running a planned switchover, I want an idle monitoring connection or a read-only
  session on the primary to not delay promotion, so a controlled failover stays sub-second.
• As an operator, I want to see what is blocking a switchover (which transactions/threads),
  because INNODB_TRX is empty for these today.
• As an SRE reading logs, I want to distinguish a switch that waited/timed-out-and-forced from
  a clean instant switch, so post-incident analysis doesn't require a stopwatch.

Proposed Scope

Proposed Scope

• Restrict the switchover wait to transactions that can be lost by a primary change (have modified
  data / hold record locks / have an assigned GTID); ignore read-only and idle-open transactions.
• Emit an error-log note when the switchover starts waiting on N blocking transactions, and when
  runningTransactionsTimeout is reached and connections are force-closed.
• Optionally surface the blocking transactions/threads via Performance Schema and/or INNODB_TRX.

Out of Scope / Future Work

• The durability-vs-availability tradeoff for real uncommitted writes at the timeout boundary
  is unchanged (they are still rolled back when the timeout is reached).
• Router-side behavior and MySQL Shell UX beyond passing through the new information.
• Applying the same classification to the multi-primary migration action (shares the monitor
  thread) — candidate follow-up.

References

• bugs.mysql.com #121016, #119197.
• Validation: two 3-node clusters (8.0.46 and 8.4.10) — timing tables, error-log windows,
  counter-case (real writes behave correctly), tested write-rollback-on-timeout, source trace to
  Transaction_monitor_thread.

Functional Requirements

No response

Non-functional Requirements

No response

Impact Areas

• SQL syntax or statements
• Configuration options or system variables
• Command-line options or utilities
• User-visible behavior
• Observability
• Security or privilege model
• Protocol or replication behavior
• Upgrade / downgrade compatibility
• Performance or resource usage
• Files, persistence, or metadata formats
• APIs or internal interfaces
• Testing or QA coverage needs

Summary of the Approach

The wait is implemented by Transaction_monitor_thread (started by the primary-election action).
It blocks new transactions, then loops in 1-second waits until either the UDF finishes (transactions
drained → terminate()), or runningTransactionsTimeout elapses, at which point it stops
before-commit and calls
mysql_close_connection_of_binloggable_transaction_not_reached_commit->close().
Two changes: (1) feed the wait a classification so only losable transactions count as blocking;
(2) add error-log notes (and optional runtime visibility) at the wait-start and timeout-reached
points.

User Interface

No change to setPrimaryInstance() syntax or the runningTransactionsTimeout option. Observable
differences: faster completion when only read-only/idle transactions are open; new [Note] lines
in the error log; optionally new Performance Schema rows describing the blockers.

Configuration / Knobs

No response

Observability

No response

User Procedure

No response

Security Considerations

No new privileges. The connection-close path already exists internally. Any new Performance Schema
visibility must respect existing instrumentation privileges; no additional data exposure beyond what
processlist/INNODB_TRX already provide.

Compatibility and Behavior Changes

Read-only/idle transactions no longer delay a controlled switchover (behavior change; can be gated).
Committed transactions and the timeout-kill semantics for real uncommitted writes are unchanged.
No upgrade/downgrade data-format impact. Replication semantics unchanged. New log notes are additive.

Block Diagram

flowchart TD
  A[setPrimaryInstance / group_replication_set_as_primary] --> B[Primary_election_action]
  B --> C[Transaction_monitor_thread: stop new txns]
  C --> D{Blocking txns remain?<br/>modified rows / locks / assigned GTID}
  D -- none --> E[Proceed immediately]
  D -- yes --> F{timeout reached?}
  F -- no, drained --> E
  F -- yes --> G[NOTE: timeout reached, close M connections]
  G --> H[close binloggable txns -> rollback]
  H --> E
  E --> I[Elect new primary -> allow new txns]

Interface Specification

No response

Proposed Implementation Plan

1. Add the timeout-reached [Note] at the existing close branch (low-risk, immediate diagnosability
   win).
2. Add the wait-start [Note] with the blocking-transaction count.
3. Add the classification predicate so read-only/idle transactions are not counted as blocking.
4. (Optional) Expose the blocking set via Performance Schema / INNODB_TRX.
5. Add the compatibility option (if we decide to gate), documentation, and MTR tests.

QA Notes

No response