$ guides / proxysql / proxysql-query-rules-order-apply ▌

Operations Guides

ProxySQL query rule order and apply=1: how rule chains silently mis-route

ProxySQL query rules look deceptively simple: write a regex, point it at a hostgroup, done. But mysql_query_rules is an ordered chain, and the chain’s behavior depends on three interacting variables that most operators never think about together: rule_id order, the apply flag, and flagIN/flagOUT chaining. Get any of these wrong and queries route to the wrong backend silently, with no error.

The mis-route may not manifest until weeks after the configuration change. A rule chain can work correctly by accident, because a later rule happens to set the same hostgroup the operator intended. Then someone adds a new rule, the last match changes, and writes start landing on read-only replicas.

What it is and why it matters

ProxySQL routes every query through the Query Processor, which evaluates the incoming SQL against the mysql_query_rules table. Each rule has:

A rule_id that determines evaluation order (ascending)
Match criteria (match_digest, match_pattern, schemaname, username, client_addr, and others)
A destination_hostgroup to route matching queries to
An apply flag that controls whether evaluation stops after a match
Optional flagIN/flagOUT values for rule chaining

The critical behavior: rules are evaluated in rule_id order, and the first match with apply=1 terminates evaluation. If apply=0 (the default when not explicitly set), the rule’s destination_hostgroup and other settings are applied, but evaluation continues to subsequent rules. Any later matching rule overrides the earlier rule’s settings.

A rule without apply=1 is not a terminal rule. It is a tentative rule whose routing decision can be overridden by anything later in the chain.

How it works

The evaluation algorithm:

ProxySQL receives a query from the client session.
Starting from the lowest rule_id where active=1 and flagIN=0, the query is tested against each rule’s match criteria.
If a rule matches, its settings (destination_hostgroup, cache_ttl, timeout, etc.) are applied to the query.
If apply=1, evaluation stops. The query uses this rule’s settings.
If apply=0, evaluation continues. Any subsequent matching rule overrides the previous settings.
If no rule matches, the query falls through to the user’s default_hostgroup from mysql_users.

flowchart TD
    A[Incoming query] --> B{Match rule at
current rule_id?}
    B -- No --> C{More active rules
in chain?}
    C -- Yes --> B
    C -- No --> D[Route to
default_hostgroup]
    B -- Yes --> E[Apply destination_hostgroup,
cache_ttl, timeout, etc.]
    E --> F{apply = 1?}
    F -- Yes --> G[Route to
destination_hostgroup]
    F -- No --> H{flagOUT set?}
    H -- Yes --> I[Jump to rules with
matching flagIN]
    I --> B
    H -- No --> C

apply=0 is not an error, but it is usually wrong

When a rule matches with apply=0, the rule’s destination_hostgroup is recorded, but the query continues through the chain. If a later rule also matches (with a broader regex, for example), that later rule’s destination_hostgroup wins. The first rule effectively did nothing.

This is the classic silent mis-route. It happens most often when:

A read-routing rule is added with a broad pattern like ^SELECT and apply=0
A later, more general rule sends everything to the writer hostgroup
The read rule “works” in testing because the test query also matches the writer rule and the writer happens to accept reads

flagIN/flagOUT chaining

flagIN and flagOUT create rule pipelines. By default, flagIN=0 for all queries entering the chain. When a rule with flagOUT=N matches, evaluation jumps to rules with flagIN=N. This allows multi-stage routing: first classify the query type, then apply hostgroup-specific rules.

The mysql-query_processor_iterations variable (default 0) controls whether the query processor can loop back to the beginning of the rule set. If set greater than 0, a matching rule can restart processing from rule_id 1, up to the iteration limit. This is rarely used and makes the chain harder to reason about.

Chains progress in rule_id order. Even with flagIN/flagOUT, ProxySQL does not revisit rules with a lower rule_id than the current position (unless iterations are explicitly enabled).

Invalid regex fails silently

When LOAD MYSQL QUERY RULES TO RUNTIME compiles regex patterns, a malformed pattern does not produce an error. The rule is loaded, marked active, but never matches anything. The only evidence is stats_mysql_query_rules.hits staying at zero for that rule.

A typo in a critical write-routing rule can pass a config reload with no warning. Queries that should match the rule silently fall through to the default hostgroup, which may be a reader.

stats_mysql_query_rules resets on every reload

The hits counter in stats_mysql_query_rules resets to zero every time query rules are loaded to runtime. This means:

You cannot accumulate hit counts across rule changes
If you LOAD rules to fix a mis-route, you lose the baseline that would confirm the fix
Monitoring systems that sample hits will show a brief zero spike after every config change, which must be distinguished from a genuinely dead rule

Where it shows up in production

The deferred mis-route

This is the most common and most dangerous pattern:

Operator writes a read/write split rule set. The write rule (^INSERT, ^UPDATE, ^DELETE) routes to hostgroup 0. The read rule (^SELECT) routes to hostgroup 1.
The write rule has apply=1. The read rule does not.
During testing, SELECT queries land on hostgroup 1 (correct). The operator declares victory.
Months later, someone adds a new rule with a higher rule_id that catches SELECT FOR UPDATE and routes it to hostgroup 0 (the writer). This is intentional.
But the broad pattern also matches regular SELECT queries. Since the read rule has apply=0, those queries now override to hostgroup 0. All reads silently move to the writer.

No error is generated. ProxySQL is doing exactly what its rules say. The only symptoms are increased load on the writer and reduced load on read replicas.

Writes hitting read-only replicas

If a write-routing rule has apply=0 and a later rule routes the same query to a read-only replica, the application receives MySQL error 1290 (“The MySQL server is running with the –read-only option”). This error appears in stats_mysql_errors with the reader hostgroup as the source.

If replicas are not configured as read-only, the writes succeed on a non-authoritative backend. This causes silent data divergence with no error anywhere. The only detection is comparing data between primary and replica, or noticing that rows written via ProxySQL are missing from the primary.

transaction_persistent bypass

When a user has transaction_persistent=1 in mysql_users and a transaction is active, ProxySQL pins all subsequent queries in that transaction to the hostgroup where the transaction started, regardless of query rules. This is by design (a transaction must go to a single backend), but it can mask rule mis-configuration: queries inside transactions always route “correctly” because rules are bypassed entirely. The mis-route only surfaces for autocommit queries outside transactions.

Cluster checksum divergence

In a ProxySQL Cluster, different nodes can have different mysql_query_rules loaded if sync is lagging or a change was applied to only one node. The stats_proxysql_servers_checksums table reveals this. If nodes have different rules, the same query may route differently depending on which ProxySQL instance the client connects to. This produces intermittent symptoms that are extremely difficult to reproduce in testing, because the test client may hit a different proxy node than the one exhibiting the problem.

Common misuses

Missing apply=1 on terminal rules: Every rule that sets a destination_hostgroup and is meant to be the final routing decision should have apply=1. Leaving it unset (defaults to 0) creates a deferred mis-route that tests fine until a later rule change shifts the last match.
Broad regex early in the chain: A rule with rule_id=1 and pattern ^SELECT will match before more specific rules. If it has apply=1, it shadows everything. If it has apply=0, it sets a tentative destination that later rules override. Either way, specificity is lost.
Using flagIN/flagOUT without documentation: Multi-stage chains are powerful but extremely hard to debug. If you use them, document the pipeline explicitly: which flagOUT values exist, which rules have matching flagIN, and what each stage decides.
Assuming stats_mysql_query_rules persists: Hit counters reset on every LOAD MYSQL QUERY RULES TO RUNTIME. Do not rely on them for trend analysis across config changes. Export and persist hit counts externally if you need history.
Passing the string ‘NULL’ instead of SQL NULL for username: In automation (Ansible, scripts), ensure that NULL is passed as SQL NULL, not serialized as the string 'NULL'. The string 'NULL' causes ProxySQL to match a user literally named “NULL” rather than treating the field as a wildcard.

How to check your rule chain

These are read-only diagnostic queries and are safe to run against the admin interface in production:

-- Inspect rule order and apply flags as ProxySQL sees them at runtime
SELECT rule_id, active, match_digest, match_pattern,
       username, destination_hostgroup, apply,
       flagIN, flagOUT
FROM runtime_mysql_query_rules
ORDER BY rule_id;

-- Check which rules are actually being hit (resets on every LOAD TO RUNTIME)
SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;

-- Find write statements routed to unexpected hostgroups
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;

The first query shows the chain as ProxySQL sees it at runtime. Note the table is runtime_mysql_query_rules, not mysql_query_rules (the staging table). The second shows which rules are actually matching traffic. A critical rule with zero hits is either dead (invalid regex) or shadowed by an earlier rule. The third catches the symptom: write digests landing in a reader hostgroup.

Signals to watch in production

Signal	Why it matters	Warning sign
`stats_mysql_query_rules.hits` per rule	Reveals which rules match traffic and which are dead	Critical rule (e.g., write routing) with zero or declining hits
`stats_mysql_query_digest` hostgroup per digest	Shows where each query type actually lands	Write digests (INSERT, UPDATE, DELETE) appearing in a reader hostgroup
`stats_mysql_errors` error code 1290	Read-only replica rejecting writes routed to it	Error 1290 appearing from a reader hostgroup backend
Backend query distribution per hostgroup	Unexpected shift in traffic balance	Writer hostgroup absorbing read traffic, or reader hostgroup receiving writes
`stats_proxysql_servers_checksums` (cluster)	Config divergence between ProxySQL nodes	Checksum mismatch on `mysql_query_rules` module between peers

How Netdata helps

Per-second metric collection on Questions, Slow_queries, and backend connection pool metrics reveals routing shifts as they happen, not minutes later during the next polling interval.
Backend traffic distribution: correlating per-hostgroup query counts and connection usage shows when traffic shifts from readers to the writer (or vice versa) without an explicit rule review.
Error rate baselines: if stats_mysql_errors data is exported through Netdata, new MySQL error codes (such as 1290) appearing after a rule reload are surfaced even at low rates that coarser polling would miss.
Cluster checksum monitoring: in ProxySQL Cluster deployments, checksum divergence on mysql_query_rules between nodes is a leading indicator of split-brain routing, detectable before clients report inconsistent behavior.