$ guides / proxysql / proxysql-server-read-only-option ▌

Operations Guides

ProxySQL error 1290 The MySQL server is running with the --read-only option

MySQL error 1290 in a ProxySQL environment is almost always a read/write split misconfiguration: a write query that should have gone to the primary was routed to a read-only replica, and the replica refused it. In ProxySQL, this surfaces in stats_mysql_errors with errno=1290, attributed to a reader hostgroup backend. The proxy is not malfunctioning. It is routing queries exactly as its mysql_query_rules dictate. The rules are wrong.

The fix has two phases: stop the bleeding by deactivating the offending rule, then fix the rule chain so writes route correctly.

What this means

Error 1290 is a MySQL server-side error. It occurs when a backend with read_only=1 receives a write operation (INSERT, UPDATE, DELETE, CREATE, ALTER, and others) and refuses it.

In a ProxySQL read/write split, the monitor module checks each backend’s read_only status and places it in the appropriate hostgroup via mysql_replication_hostgroups. Without that configuration, the read_only monitor check is disabled, and backends stay wherever they were manually assigned in mysql_servers.

When a write query reaches a reader hostgroup backend, the backend returns 1290. ProxySQL forwards this error to the client and records it in stats_mysql_errors. The routing was correct from ProxySQL’s perspective: it followed its rules. The rules were wrong.

flowchart TD
    A["Write query arrives"] --> B["Rules evaluated\nin rule_id order"]
    B --> C{"Write rule matches\nwith apply=1?"}
    C -->|"No: missing, shadowed,\nor no apply=1"| D["Falls through to reader HG\nor default hostgroup"]
    C -->|"Yes"| E["Routed to writer HG"]
    D --> F["read_only replica returns\nerror 1290"]
    E --> G["Primary accepts write"]
    F --> H["Recorded in stats_mysql_errors"]

The classic trigger is the generic ^SELECT routing pattern from the official ProxySQL documentation. It sends all SELECT queries to readers and everything else to the writer. The docs warn against production use because SELECT ... FOR UPDATE is a locking read that must go to the writer but matches ^SELECT and lands on a replica.

Common causes

Cause	What it looks like	First thing to check
Broad read rule shadows write rule	`SELECT ... FOR UPDATE` matches a `^SELECT` rule before a write-specific rule catches it	`stats_mysql_query_rules`: write or FOR UPDATE rule has zero hits
Missing `apply=1` on write rule	Write rule matches but does not stop evaluation; a later read rule overrides the destination hostgroup	`apply` column in `runtime_mysql_query_rules`
Default hostgroup set to reader	Queries not matching any rule fall through to the user’s `default_hostgroup`, which is a reader hostgroup	`default_hostgroup` in `runtime_mysql_users`
`transaction_persistent=1` pinning to reader	First query in a transaction routed to reader; subsequent writes stay on that backend, bypassing all query rules	`transaction_persistent` in `runtime_mysql_users`
New rule inserted with lower `rule_id`	A recently added rule catches queries before the existing write rule, redirecting them	Compare `rule_id` ordering before and after the change
`writer_is_also_reader` masking	Writes succeed intermittently: some hit the primary (also present in reader HG), some hit actual replicas	Check `mysql-writer_is_also_reader` global variable

Quick checks

All commands query the ProxySQL admin interface (default port 6032). Replace default credentials (admin/admin) with your actual admin user and password in production.

# Find error 1290 occurrences with hostgroup context
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, username, schemaname, errno, count_star, last_error, last_seen \
      FROM stats_mysql_errors WHERE errno=1290 ORDER BY last_seen DESC LIMIT 20;"

# Check active query rules in evaluation order
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 which rules are actually matching traffic
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;"

# Find write queries routed to the reader hostgroup
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 default hostgroup and transaction_persistent per user
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT username, default_hostgroup, transaction_persistent FROM runtime_mysql_users;"

# Verify replication hostgroup mapping (runtime view shows active config)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT writer_hostgroup, reader_hostgroup, comment FROM runtime_mysql_replication_hostgroups;"

# Check monitor read_only check results
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global \
      WHERE Variable_Name LIKE '%read_only%';"

How to diagnose it

Confirm error 1290 is from a reader hostgroup. Query stats_mysql_errors WHERE errno=1290. The hostgroup column shows which hostgroup returned the error. If it is your reader hostgroup, the query was routed there by a rule or by the default hostgroup.
Identify the affected write statements. Check stats_mysql_query_digest for INSERT, UPDATE, or DELETE digests with a hostgroup value matching your reader hostgroup. Note count_star to gauge blast radius.
Check rule hit distribution before reloading. Query stats_mysql_query_rules for hit counts per rule. A write-routing rule with zero hits is either shadowed by an earlier rule or its regex does not match the incoming query text. This table resets when you reload rules to runtime, so check it before making changes.
Review rule evaluation order. Examine runtime_mysql_query_rules ordered by rule_id. ProxySQL evaluates rules in ascending order. The first matching rule with apply=1 determines the destination hostgroup. If a broad read rule sits before the write rule and matches the query, the write rule is never reached.
Check apply flags. A matching rule without apply=1 does not terminate evaluation. A later rule with a different destination_hostgroup can override it. This is a common source of silent mis-routing: the write rule works in testing because a later read rule happens to send traffic to the writer, but any change to that later rule breaks the chain.
Verify the default hostgroup. If no rule matches, ProxySQL sends the query to the user’s default_hostgroup. If that is a reader hostgroup, every unmatched write goes to a replica.
Check transaction_persistent. When enabled for a user, all queries within a transaction stay on the same backend connection, bypassing query rules entirely. If the first query in a transaction is a SELECT routed to a reader, subsequent writes in that transaction also execute on the reader and return 1290.
Check writer_is_also_reader. If enabled, the primary appears in both the writer and reader hostgroups. Writes routed to the reader hostgroup may succeed when they hit the primary and fail when they hit an actual replica. This makes the error appear intermittent.

Fixes

Immediate: deactivate the offending rule

If you have identified the rule sending writes to the reader hostgroup, deactivate it now:

# Deactivate the offending rule
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "UPDATE mysql_query_rules SET active=0 WHERE rule_id=<problem_rule_id>; \
      LOAD MYSQL QUERY RULES TO RUNTIME;"

Queries that previously hit this rule now fall through to the next matching rule or the default hostgroup. Verify that the fallback destination sends writes to the writer hostgroup, or you trade one routing error for another.

After confirming the fix works, persist it:

mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SAVE MYSQL QUERY RULES TO DISK;"

Fix rule ordering

If a new rule was inserted with a lower rule_id than the write-routing rule, it shadows the write rule. Either move the write rule to a lower rule_id or move the new rule to a higher one.

ProxySQL requires unique rule_id values. Use a temporary high ID for swaps. Verify that ID 9000 (or whatever temporary ID you choose) is not already in use before running this:

# Swap rule_id values to fix evaluation order
# WARNING: verify rule_id 9000 does not exist before running
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "UPDATE mysql_query_rules SET rule_id=9000 WHERE rule_id=<write_rule_id>; \
      UPDATE mysql_query_rules SET rule_id=<write_rule_id> WHERE rule_id=<shadowing_rule_id>; \
      UPDATE mysql_query_rules SET rule_id=<shadowing_rule_id> WHERE rule_id=9000; \
      LOAD MYSQL QUERY RULES TO RUNTIME;"

Add `apply=1` to terminal rules

Every routing rule that is meant to be the final destination for a query must have apply=1. Without it, evaluation continues and a later rule can override the destination hostgroup.

# Ensure terminal routing rules stop evaluation
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "UPDATE mysql_query_rules SET apply=1 WHERE rule_id=<rule_id>; \
      LOAD MYSQL QUERY RULES TO RUNTIME;"

Use `match_digest` for write routing

Instead of enumerating every SELECT variant that must go to the writer (FOR UPDATE, stored function calls, DDL), route by write type. Create explicit rules for write statements using match_digest, then let everything else fall through to the reader hostgroup as the default.

# Route write statements to the writer hostgroup
# Note: DDL (CREATE, ALTER, DROP, TRUNCATE) also needs explicit rules
# or a writer default_hostgroup to avoid 1290 on schema changes.
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) \
      VALUES (10, 1, '^INSERT', <writer_hg>, 1); \
      INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) \
      VALUES (20, 1, '^UPDATE', <writer_hg>, 1); \
      INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) \
      VALUES (30, 1, '^DELETE', <writer_hg>, 1); \
      INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) \
      VALUES (40, 1, '^CREATE', <writer_hg>, 1); \
      INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) \
      VALUES (50, 1, '^ALTER', <writer_hg>, 1); \
      INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) \
      VALUES (60, 1, '^DROP', <writer_hg>, 1); \
      LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;"

match_digest matches against the normalized digest text (literals replaced with ?), which is more predictable than matching the full query text with regex. A query like UPDATE users SET name='alice' WHERE id=42 normalizes to UPDATE users SET name=? WHERE id=?, and ^UPDATE matches cleanly.

For SELECT FOR UPDATE specifically, add a rule before the generic SELECT rule:

# Route SELECT FOR UPDATE to the writer
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) \
      VALUES (5, 1, '^SELECT .* FOR UPDATE', <writer_hg>, 1); \
      LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;"

Verify the fix

After applying changes, verify that writes now route to the writer hostgroup:

# Check that write digests now appear in the writer hostgroup
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup, digest_text, count_star, last_seen \
      FROM stats_mysql_query_digest \
      WHERE digest_text LIKE 'INSERT%' OR digest_text LIKE 'UPDATE%' OR digest_text LIKE 'DELETE%' \
      ORDER BY last_seen DESC LIMIT 20;"

If stats_mysql_query_digest was reset by a rule reload, wait for new traffic to populate it. Alternatively, run a test write query through ProxySQL and check its hostgroup assignment.

Confirm no new 1290 errors appear:

# Confirm error 1290 has stopped accumulating
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT count_star, last_seen FROM stats_mysql_errors WHERE errno=1290;"

count_star should stop increasing after the fix is live.

Prevention

Always set apply=1 on terminal routing rules. Without it, a later rule can silently override the destination hostgroup. This is the single most common cause of routing bugs that pass review.
Route by write type, not by read type. Pattern rules around INSERT/UPDATE/DELETE/DDL routing to the writer, with reads as the catch-all. This avoids the fragility of enumerating every SELECT variant that should go to the writer.
Test rule changes against production digests. Before deploying a new rule, check stats_mysql_query_digest to understand which queries it will match. A rule that looks correct in isolation can shadow existing rules in production.
Audit rule_id ordering after any rule addition. Verify a new rule does not sit before an existing rule that handles the same traffic.
Persist changes to disk. After LOAD MYSQL QUERY RULES TO RUNTIME, always run SAVE MYSQL QUERY RULES TO DISK. A restart without the save reverts your fix silently.
Monitor stats_mysql_query_rules hit counts. A write-routing rule with zero hits is a leading indicator of a shadowed rule.
Set default_hostgroup to the writer hostgroup. If a query matches no rule, sending it to the writer (where it executes correctly, even if less efficient) is safer than sending it to a reader (where writes fail with 1290).

Metrics and signals to monitor

Signal	Why it matters	Warning sign
`stats_mysql_errors` errno=1290	Direct count of read-only rejections	Any count > 0 from reader hostgroup
`stats_mysql_query_rules` hits per rule_id	Reveals shadowed or dead rules	Write-routing rule with 0 hits; catch-all rule with unexpectedly high volume
`stats_mysql_query_digest` hostgroup for write digests	Shows actual routing of write statements	INSERT/UPDATE/DELETE with reader hostgroup value
`MySQL_Monitor_read_only_check_OK` / `MySQL_Monitor_read_only_check_ERR`	Validates monitor can detect backend read/write roles	ERR increasing means monitor cannot determine which backends are read-only

stats_mysql_errors and stats_mysql_query_rules are not collected by standard monitoring setups. They require explicit queries against the ProxySQL admin interface. Without collection, you are blind to this failure mode until applications report the error.

Monitoring with Netdata

Netdata collects the ProxySQL admin interface tables that expose this failure mode, with per-second granularity:

Error tracking by errno. stats_mysql_errors is collected with per-errno breakdown, so error 1290 surfaces immediately. This matters when writer_is_also_reader causes intermittent failures.
Rule hit distribution. stats_mysql_query_rules hit counts are tracked continuously, making it visible the moment a write-routing rule stops matching after a rule chain change.
Digest hostgroup assignment. stats_mysql_query_digest data shows which hostgroup each query type lands on, making it straightforward to spot writes routed to reader hostgroups.
Monitor check correlation. Read-only check results are collected alongside backend status transitions, so you can correlate monitor health with routing errors.
Change correlation. Timeline correlation between ProxySQL rule reloads and error count changes helps pinpoint when a misconfiguration was introduced.