$ guides / proxysql / proxysql-read-write-split-misrouting ▌

Operations Guides

ProxySQL read/write split misrouting: writes silently reaching replicas

ProxySQL read/write split routes queries based on mysql_query_rules evaluated in ascending rule_id order. When that chain is misconfigured, write queries can match a read-routing rule and land on a replica hostgroup. If the replica enforces super_read_only, the write fails with MySQL error 1290 and the client sees an error. If it does not, the write succeeds on the replica, never reaches the primary, and the client receives a success response. Data diverges silently with no error at any layer.

The proxy is routing queries exactly as its rules dictate. This is a configuration problem, not a bug. No single metric in stats_mysql_global flags it directly. You detect it by examining stats_mysql_query_rules hit distribution and stats_mysql_query_digest for write digests appearing in reader hostgroups.

What this means

ProxySQL evaluates mysql_query_rules in ascending rule_id order. Only rules with active=1 are evaluated. When a rule matches and has apply=1 (the default), evaluation stops and the query routes to that rule’s destination_hostgroup. If a matching rule has apply=0, evaluation continues and a subsequent matching rule can override the destination_hostgroup.

The canonical read/write split uses three rules, all with apply=1:

A low rule_id for ^SELECT.*FOR UPDATE (or equivalent locking reads) routing to the writer hostgroup.
A middle rule_id for ^SELECT routing to the reader hostgroup.
A catch-all .* rule routing to the writer hostgroup.

When any rule is missing, mis-ordered, lacks apply=1, or has a regex that matches unintended queries, writes can fall through to the reader hostgroup.

flowchart TD
    Q["Incoming query"] --> R1{"rule_id=100
SELECT.*FOR UPDATE
apply=1?"}
    R1 -- "no match" --> R2{"rule_id=200
^SELECT
apply=1?"}
    R2 -- "write matches
unanchored pattern
or apply=0" --> RH["reader hostgroup"]
    RH --> RO{"replica
super_read_only?"}
    RO -- "OFF" --> SD["write succeeds
silent divergence
no error to client"]
    RO -- "ON" --> E1290["error 1290
client sees failure"]
    R1 -- "match, apply=1" --> WH["writer hostgroup"]
    R2 -- "no match" --> R3{"rule_id=300
catch-all .*
apply=1"}
    R3 --> WH

Common causes

Cause	What it looks like	First thing to check
Missing `apply=1` on a routing rule	Rule matches but a later read rule overrides the destination. `stats_mysql_query_rules` shows hits on both rules for overlapping traffic.	`SELECT rule_id, apply FROM runtime_mysql_query_rules ORDER BY rule_id;`
Unanchored read regex	A `SELECT` pattern without the `^` anchor in `match_pattern` matches any query containing SELECT, including `INSERT INTO ... SELECT ...` or `UPDATE ... = (SELECT ...)`.	`SELECT rule_id, match_digest, match_pattern, destination_hostgroup FROM runtime_mysql_query_rules ORDER BY rule_id;`
Rule_id ordering error	A newly added rule with a lower `rule_id` shadows existing rules. Hit distribution shifts after a config change.	`SELECT rule_id, match_digest, destination_hostgroup, apply, active FROM runtime_mysql_query_rules ORDER BY rule_id;`
`transaction_persistent=1` with backend `autocommit=0`	A SELECT routed to a reader backend with `autocommit=0` opens an implicit transaction. With `transaction_persistent=1`, the backend connection is pinned to the client session. Subsequent writes go to that reader.	Check `@@autocommit` on backend MySQL and `transaction_persistent` in `mysql_users`.
`^SELECT.*FOR UPDATE` rule never matches	The locking-read rule has zero hits in `stats_mysql_query_rules`. SELECT FOR UPDATE queries route to the reader hostgroup instead.	`SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;`

Quick checks

All of these are read-only queries against the ProxySQL admin interface (default port 6032). They do not modify configuration or affect traffic. Adjust credentials if your deployment does not use the defaults.

# Check rule ordering, match patterns, apply flags, and active status
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, active, match_digest, match_pattern, destination_hostgroup, apply FROM runtime_mysql_query_rules ORDER BY rule_id;"

# Check per-rule hit counts to see where queries are actually going
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;"

# Look for write digests routed to reader hostgroups
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, schemaname, username, digest_text, count_star FROM stats_mysql_query_digest WHERE digest_text LIKE 'INSERT%' OR digest_text LIKE 'UPDATE%' OR digest_text LIKE 'DELETE%' ORDER BY count_star DESC LIMIT 20;"

# Check for error 1290 from reader hostgroups (indicates super_read_only is catching misroutes)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, errno, count_star, last_seen FROM stats_mysql_errors WHERE errno=1290 ORDER BY last_seen DESC LIMIT 20;"

# Verify transaction_persistent setting per user
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT username, transaction_persistent FROM mysql_users;"

# Check autocommit_false_is_transaction variable
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT variable_name, variable_value FROM global_variables WHERE variable_name='mysql-autocommit_false_is_transaction';"

# Verify which backends are in which hostgroups
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup_id, hostname, port, status FROM runtime_mysql_servers ORDER BY hostgroup_id;"

Note: stats_mysql_errors is available in ProxySQL 2.0.6 and later. On older versions, rely on stats_mysql_query_digest and stats_mysql_query_rules.

How to diagnose it

Establish the expected rule chain. Write down the intended rule_id order, which hostgroup each rule targets, and whether apply=1 is set. The canonical pattern: locking reads to writer, plain SELECTs to reader, catch-all to writer. All with apply=1.
Compare expected vs actual hit distribution. Query stats_mysql_query_rules for per-rule hit counts. If the write-routing rule (or catch-all) has unexpectedly low or zero hits while the read rule has inflated hits, queries are falling through. Note: stats_mysql_query_rules resets when rules are loaded to runtime, so hit counts only reflect traffic since the last reload.
Search for write digests in reader hostgroups. Query stats_mysql_query_digest filtering for INSERT, UPDATE, and DELETE where the hostgroup is a reader. Any row here is evidence of misrouting. The count_star column tells you how many times it has happened.
Check for error 1290. If super_read_only is enabled on replicas, misrouted writes produce error 1290. Check stats_mysql_errors for errno=1290 from reader hostgroups. If you see zero 1290 errors but write digests appear in reader hostgroups, your replicas likely do not have super_read_only enforced and writes are succeeding silently.
Inspect regex patterns. If writes are matching a read rule, the pattern may be missing the ^ anchor or matching too broadly. A SELECT pattern without ^ in match_pattern matches any query containing SELECT anywhere in the text, including INSERT INTO ... SELECT ... or UPDATE ... = (SELECT ...). Using match_digest normalizes the query text (parameter values replaced with ?) and reduces false matches, but the pattern still needs proper anchoring. Consider explicitly routing INSERT, UPDATE, DELETE, and other write statements to the writer hostgroup with dedicated rules at a lower rule_id than the read rule.
Check transaction pinning behavior. If misrouting happens only after a SELECT within the same session, transaction_persistent=1 combined with backend autocommit=0 may be pinning the connection to the reader. Verify backend @@autocommit settings and the transaction_persistent value in mysql_users.
Verify runtime vs disk. If you recently changed rules, confirm the change reached runtime. Query runtime_mysql_query_rules (not mysql_query_rules) to see what is actually live. A fix applied to mysql_query_rules but not loaded to runtime has no effect.

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`stats_mysql_query_rules` hit distribution	Shows which rules are matching traffic. Shifts indicate routing changes.	Write-routing rule hits drop to zero or catch-all rule hits spike.
`stats_mysql_query_digest` hostgroup per digest	Shows where each query type is actually routed.	INSERT, UPDATE, or DELETE digests with a reader hostgroup.
`stats_mysql_errors` errno=1290 count	The loud signal that `super_read_only` is catching misroutes.	Any 1290 errors from reader hostgroups.
`stats_mysql_global` error packet counters	ProxySQL-generated error count. Correlates with routing failures.	Sustained increase after a rule change.
`mysql_users.transaction_persistent`	Determines whether transactions pin backend connections.	Set to 1 on users whose backends have `autocommit=0`.
Backend `super_read_only` status	The safety net that turns silent divergence into loud failure.	Any replica with `super_read_only=OFF`.

Fixes

Warning: LOAD MYSQL QUERY RULES TO RUNTIME takes effect immediately for all live traffic and resets stats_mysql_query_rules hit counters to zero.

Missing or incorrect `apply=1`

Every routing rule in the read/write split chain must have apply=1. Without it, evaluation continues and a later rule can override the destination.

UPDATE mysql_query_rules SET apply=1 WHERE rule_id=<rule_id>;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Verify hit counts after the reload and confirm writes are accumulating on the writer rule.

Unanchored or too-broad read regex

Replace broad patterns with explicit write-routing rules. Instead of relying solely on a catch-all, add dedicated rules for write statements at a lower rule_id than the read rule:

-- Route writes explicitly before the SELECT rule
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES
  (110, 1, '^INSERT', <writer_hg>, 1),
  (120, 1, '^UPDATE', <writer_hg>, 1),
  (130, 1, '^DELETE', <writer_hg>, 1),
  (140, 1, '^CREATE', <writer_hg>, 1),
  (150, 1, '^DROP',   <writer_hg>, 1),
  (160, 1, '^ALTER',  <writer_hg>, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Use match_digest rather than match_pattern. Digest-based matching normalizes query text (parameters become ?) and avoids false matches from parameter values or formatting.

Rule_id ordering error

List all rules in rule_id order and trace the evaluation path manually. A common mistake is inserting a new rule at a rule_id between existing rules, inadvertently shadowing a rule that was previously the first match for certain queries.

Transaction pinning with autocommit=0

If backend MySQL servers have autocommit=0 set globally, every SELECT starts an implicit transaction. With transaction_persistent=1 on the ProxySQL user, the backend connection is pinned to the client session and subsequent writes go to the same reader backend.

Two options:

Enable autocommit=1 on backend MySQL servers. This is the root fix. ProxySQL expects backends to have autocommit=1 so that queries outside explicit transactions (BEGIN/COMMIT) can be multiplexed freely.

Emergency: deactivate the offending rule

During an active incident with silent data divergence, deactivate the misrouting rule immediately to stop further damage:

-- Disarm the offending rule (takes effect immediately)
UPDATE mysql_query_rules SET active=0 WHERE rule_id=<problem_rule_id>;
LOAD MYSQL QUERY RULES TO RUNTIME;

This is a stopgap. It does not repair data divergence on affected replicas.

Prevention

Enforce super_read_only=ON on every replica. This is the single most important safety net. It turns silent divergence into error 1290 that clients and monitoring catch immediately. Without it, misrouted writes succeed and you have no signal until corruption surfaces.
Use explicit write-routing rules. Add dedicated rules for INSERT, UPDATE, DELETE, CREATE, DROP, ALTER at rule_id values lower than the read rule. All with apply=1.
Prefer match_digest over match_pattern. Digest matching normalizes query text and reduces false matches from parameter values.
Audit rule changes. A single rule change can redirect writes to replicas. Treat query rule modifications with the same scrutiny as firewall changes. Log who changed what and when.
Monitor hit distribution. Establish a baseline for expected hit counts per rule. Alert on significant deviations, especially when write-routing or catch-all rule hits drop.
Verify the three-layer config model after changes. Confirm changes are in runtime (runtime_mysql_query_rules) and persisted to disk. A restart with stale disk config silently reverts a fix.
Check backend autocommit. Ensure backends have autocommit=1 unless you have a specific reason otherwise. Combined with transaction_persistent=1, autocommit=0 silently pins writes to reader hostgroups.

How Netdata helps

Netdata’s ProxySQL collector surfaces the signals that reveal misrouting:

Per-second query rule hit counts from stats_mysql_query_rules detect routing shifts within seconds of a rule change.
Per-hostgroup query digest data from stats_mysql_query_digest makes write digests in reader hostgroups immediately visible on dashboards and charts.
Error tracking via stats_mysql_errors catches error 1290 bursts from reader hostgroups when super_read_only is enforced.
Connection pool metrics (ConnUsed, ConnFree, ConnERR) distinguish routing problems from backend saturation.
ML-based anomaly detection on query rule hit ratios flags unusual shifts even when no explicit threshold is crossed.

Correlate rule hit distribution changes with write digests in reader hostgroups and any spike in error 1290 counts within a single timeline.