Body
MySQL HA Lab MySQL HA Lab · MySQL · Group Replication

MySQL HA Lab, Lesson 6: Stale Reads, Read/Write Splitting and group_replication_consistency

Make a stale read happen on a MySQL secondary on purpose, then fix it with group_replication_consistency BEFORE and AFTER, and try MySQL Router's read/write splitting port 6450.

MySQL HA Lab Lesson 6: Stale Reads and Read/Write Splitting
📘
This post is part of the MySQL HA Lab series: a hands-on MySQL 8.4 InnoDB Cluster on Docker. Code for this lesson: branch 05-consistency on GitHub. Previous: Lesson 5: Highly available routing with HAProxy.

Group Replication commits a transaction on the primary once a majority has received it, not once the secondaries have applied it. So a read sent to a secondary straight after a write can return old data. This lesson makes that happen on purpose, then fixes it with group_replication_consistency, and shows MySQL Router's read/write splitting port, which sends reads to secondaries automatically.

What changes in this lesson

FileChange
haproxy/haproxy.cfgNew frontend mysql_rwsplit on :6450 and backend routers_rwsplit (router port 6450)
docker-compose.ymlrouter-lb publishes 6450:6450
scripts/helpers.shNew helpers mstall (make a secondary lag) and r (SQL through port 6450)

HAProxy ports after this lesson

router-lb (container mysql-router) now publishes four ports. Each maps the same number on your laptop to HAProxy; the three MySQL ports are passed in TCP mode to the same port on mysql-router1 or mysql-router2, and the router picks the MySQL node.

    ports:
      - "6446:6446"
      - "6447:6447"
      - "6450:6450"
      - "8404:8404"
PortHAProxy frontend → backendRouter portWhere the connection ends upUse it for
6446mysql_rw → routers_rw6446 read/writeAlways the primaryWrites, and reads that must see the latest data
6447mysql_ro → routers_ro6447 read-onlyA secondary, spread across themReads that can lag slightly: reports, lists
6450mysql_rwsplit → routers_rwsplit6450 read/write splittingChosen per statement: plain SELECT → secondary; writes and transactions → primary; START TRANSACTION READ ONLY → secondaryOne connection for the app, Router does the routing
8404stats (HTTP, not MySQL)noneHAProxy itself/stats page for router health, /metrics for Prometheus
  • HAProxy only chooses a healthy router (check inter 2s fall 2 rise 2, leastconn); it never sees SQL. Routing to primary or secondary is done by the router.
  • The routers' X Protocol ports 6448/6449 (MySQL Shell, X DevAPI) are not published, because nothing in the lab uses them.
  • From your laptop use localhost:<port>; inside the Docker network (the helpers) use mysql-router:<port>.

Apply it:

docker compose up -d --force-recreate router-lb
source scripts/helpers.sh
r "SELECT @@hostname"

6.1 Why reads can be stale

sequenceDiagram
    participant C as Client
    participant P as Primary
    participant S as Secondary
    C->>P: INSERT (v = x)
    P->>S: transaction into the relay log
    S-->>P: received (majority)
    P-->>C: COMMIT OK
    C->>S: SELECT ... WHERE v = x
    alt consistency EVENTUAL
        S-->>C: 0 rows (applier not there yet)
    else consistency BEFORE
        S->>S: wait until the applier has caught up
        S-->>C: 1 row
    end
    S->>S: applier writes the row
Why a read can be stale: COMMIT returns once a majority has received the transaction, not once the secondaries have applied it.
client ── INSERT ──> primary ──(binlog event)──> secondary: relay log ─> applier ─> table
                        │                                       ▲
                        └── COMMIT returns once a majority      └── a read here can run
                            has the event in its relay log          before the applier has
                                                                    reached the new row

Normally the applier is milliseconds behind, so you rarely see it. Under load, a long query, or a lock on the secondary, the gap grows to seconds.

To make the gap visible, mstall holds LOCK TABLES app.t READ on a secondary. Reads still work, but the applier cannot write to app.t until the lock is released:

mstall mysql3 30 &      # mysql3 lags for 30 seconds

mstall refuses to run on the primary: locking the primary blocks client writes, which tests something else.

6.2 Read/write splitting (port 6450)

flowchart TD
    q(["Statement on port 6450"]) --> tx{"Inside a transaction?"}
    tx -- "START TRANSACTION READ ONLY" --> sec[("a SECONDARY")]
    tx -- "START TRANSACTION / BEGIN" --> pri[("the PRIMARY")]
    tx -- no --> kind{"Statement type"}
    kind -- "plain SELECT" --> sec
    kind -- "INSERT / UPDATE / DELETE / DDL" --> pri
How router port 6450 decides where each statement goes.

MySQL Router 8.4 has a read/write splitting port, 6450. It looks at each statement and decides:

StatementGoes to
A plain SELECT outside a transactiona secondary
INSERT, UPDATE, DELETE, DDLthe primary
Anything in START TRANSACTION / BEGINthe primary, for the whole transaction
START TRANSACTION READ ONLYa secondary, for the whole transaction

It only exists on the classic protocol, and it relies on Router's connection sharing. Router 8.2+ creates it at bootstrap, so the routers in this lab already listen on 6450; this lesson only publishes it through HAProxy. Ports 6446 and 6447 still work as before.

Tests:

r "SELECT @@hostname"                                                  # mysql2 or mysql3
r "START TRANSACTION; SELECT @@hostname; COMMIT"                       # mysql1 (primary)
r "START TRANSACTION READ ONLY; SELECT @@hostname; COMMIT"             # a secondary

Read/write splitting does not fix stale reads. A SELECT right after an INSERT on the same connection can land on a lagging secondary, which is exactly the problem from 6.1.

6.3 group_replication_consistency

This setting decides who waits, and for what. It can be set globally or per session, so one critical query can ask for strong consistency while the rest stays fast.

LevelWhat waitsUse it for
EVENTUALNothing. Reads on a secondary may be staleHigh read throughput where a little lag is fine
BEFORE_ON_PRIMARY_FAILOVER (default in 8.4)A newly elected primary waits to apply its backlog before taking new transactionsSafe default: no stale reads on a new primary after failover
BEFOREThe read waits until the node has applied everything committed before it"Read your writes" on a secondary, paid only by the reads that ask for it
AFTERThe write waits until every ONLINE member has applied itWrites that must be visible everywhere before the client continues
BEFORE_AND_AFTERBothStrictest; slowest

The cost moves: BEFORE makes reads slow, AFTER makes writes slow. With AFTER, one lagging member slows every write by the lag of the slowest node.

6.4 Test: see a stale read, then fix it

Lock both secondaries, otherwise a read might land on the healthy one and hide the problem:

mstall mysql2 30 & mstall mysql3 30 &
sleep 2
x=$RANDOM$RANDOM            # unique value, so we count exactly our row

Use COUNT(*) WHERE v=$x, not MAX(v): older test rows can already hold a higher value and make every read look fresh.

EVENTUAL: stale read

w "INSERT INTO app.t (v) VALUES ($x)"
r "SET SESSION group_replication_consistency='EVENTUAL'; SELECT @@hostname, COUNT(*) FROM app.t WHERE v=$x"

Result in this lab: mysql3 0. The row is committed but the secondary does not have it yet.

BEFORE: the read waits

mstall mysql2 30 & mstall mysql3 30 &
sleep 2; x=$RANDOM$RANDOM
w "INSERT INTO app.t (v) VALUES ($x)"
time ( MH_TIMEOUT=60 r "SET SESSION group_replication_consistency='BEFORE'; SELECT @@hostname, COUNT(*) FROM app.t WHERE v=$x" )

Result: mysql2 1 after 28.7 s. The read blocked until the lock was gone and the applier had caught up, then returned the correct row.

AFTER: the write waits

mstall mysql2 30 & mstall mysql3 30 &
sleep 2; x=$RANDOM$RANDOM
time ( MH_TIMEOUT=60 m -e "SET SESSION group_replication_consistency='AFTER'; INSERT INTO app.t (v) VALUES ($x)" )
r "SELECT @@hostname, COUNT(*) FROM app.t WHERE v=$x"     # 1 immediately, on any node

Result: the INSERT took 28.8 s. It returned only after both secondaries had applied it, so every read afterwards is fresh.

In zsh, time does not measure shell functions; wrap them in ( ... ) as above. Raise MH_TIMEOUT for these tests, because the default 15 s would kill the waiting call.

6.5 How long can a read or write wait?

A BEFORE read or an AFTER write waits until the lagging members catch up, bounded by the session's wait_timeout (8 hours by default). After that the statement fails with an error. Hours is far too long for an application, so:

  • set the level per session on the queries that need it, not globally;
  • give the client its own timeout (Lesson 3), as MH_TIMEOUT does here.

What you learned

  • A committed write can be invisible on a secondary for as long as its applier lags.
  • Router port 6450 sends plain reads to secondaries and transactions to the primary; it does not guarantee fresh reads.
  • BEFORE makes the read wait, AFTER makes the write wait; measured here at about 29 s each with a 30 s lag.
  • Choose the level per session, and always give clients a timeout.

The MySQL HA Lab series

  1. Series introduction
  2. Lesson 1: Environment and SQL helpers
  3. Lesson 2: A self-bootstrapping InnoDB Cluster
  4. Lesson 3: Failover, quorum and recovery
  5. Lesson 4: Monitoring with Prometheus and Grafana
  6. Lesson 5: Highly available routing with HAProxy
  7. Lesson 6: Stale reads and read/write splitting (you are here)
  8. Lesson 7: Failover tuning, switchover and partitions
  9. Lesson 8: A Spring Boot app that survives failover
  10. Lesson 9: The same app in Go
  11. Bonus: Operations and troubleshooting

All code is on GitHub: mytechbytes/mysql-ha-lab. Questions or a different result in your lab? Leave a comment below.

← Previous MySQL HA Lab, Lesson 5: Highly Available MySQL Router with HAProxy Next → MySQL HA Lab, Lesson 7: Failover from 25 s to 6 s, Switchover and Network Partitions
Part 7 of 11 · Collection MySQL HA Lab Back to collection →
Membership

Go deeper.

Members get the full technical guides, production checklists and the source repos behind every article.

You may also like