A critical query rule (write routing, read/write splitting, query caching) shows zero hits in stats_mysql_query_rules. Queries that should match it are falling through to the default hostgroup or a catch-all rule instead. The rule exists in runtime_mysql_query_rules, it looks correct, and there are no errors in the ProxySQL log.

The proxy parsed your rule, compiled the regex, and loaded it to runtime. It just never matches any query. Meanwhile, writes may be landing on read-only replicas (MySQL error 1290) or reads may be piling onto the writer hostgroup unnecessarily. If replicas are not configured read_only, writes can silently succeed on a non-primary backend and never reach the authoritative source.

A rule with zero hits is not necessarily broken. stats_mysql_query_rules resets every time you run LOAD MYSQL QUERY RULES TO RUNTIME. A rule that was just loaded will show zero hits until traffic flows. The real signal is a rule that should be matching high-volume queries showing zero or near-zero hits while the default or fallback rule absorbs traffic that belongs to it.

What this means

ProxySQL evaluates mysql_query_rules in ascending rule_id order. The first rule that matches and has apply=1 terminates evaluation and determines the destination hostgroup, cache TTL, timeout, and any query rewrite. If no rule matches, the query goes to the default hostgroup configured for the user.

When a specific rule shows zero hits, one of these is happening:

  1. The regex compiled silently but never matches. LOAD MYSQL QUERY RULES TO RUNTIME succeeds without error even when a regex pattern in match_digest or match_pattern is invalid. The rule loads, becomes active, and matches nothing. No error is logged. This is the single most common cause.

  2. match_digest is matching against something that was stripped. match_digest matches the normalized query digest: parameters replaced with ?, and SQL comments removed. A pattern that expects comment text or literal parameter values will never match a digest.

  3. A lower rule_id is shadowing your rule. A broadly-matching rule earlier in the chain catches queries before they reach your more specific rule. If that earlier rule has apply=1, your rule is dead code.

  4. apply is missing, so a later rule overrides the destination. The query matches your rule, but evaluation continues. A subsequent rule with different destination_hostgroup wins.

  5. The username field contains the literal string 'NULL' instead of SQL NULL. ProxySQL treats SQL NULL in the username field as “match any user.” The literal string 'NULL' matches only a user literally named “NULL.” Both display identically in query output, which is why this goes unnoticed.

flowchart TD
    A["Critical rule shows
zero hits"] --> B{"Rule exists in
runtime_mysql_query_rules?"}
    B -- No --> C["Missing from runtime:
LOAD MYSQL QUERY RULES TO RUNTIME"]
    B -- Yes --> D{"Default/catch-all rule
absorbing traffic?"}
    D -- Yes --> E{"Lower rule_id
catching first?"}
    E -- Yes --> F["Rule shadowing"]
    E -- No --> G{"Regex pattern valid
against actual digest?"}
    G -- No --> H["Silent compile failure"]
    G -- Yes --> I{"match_digest used
for comments/literals?"}
    I -- Yes --> J["Digest strips comments:
use match_pattern"]
    I -- No --> K["Check apply flag,
username field, flagIN/flagOUT"]

Common causes

CauseWhat it looks likeFirst thing to check
Invalid regex (silent compile failure)Rule loads without error, zero hits, no log entryTest the regex pattern against actual query text or digest
match_digest matching stripped contentPattern references comments or literal values that the digest normalizes awayCompare the pattern against digest_text in stats_mysql_query_digest
Rule shadowing by lower rule_idDefault or broad rule has high hits; specific rule has zeroList all rules ordered by rule_id, check for overlap
Missing apply=1Rule has hits but queries still land in wrong hostgroupCheck apply column in runtime_mysql_query_rules
username field set to literal 'NULL'Rule matches nothing despite correct regexCheck if username column is SQL NULL vs the string 'NULL'
flagIN/flagOUT chain brokenRule has flagIN > 0 but no prior rule sets the matching flagOUTTrace the flag chain from flagIN=0

Quick checks

All commands below query the ProxySQL admin interface on port 6032. Substitute your actual credentials; the examples use ProxySQL defaults. Passing the password on the command line exposes it in the process list, so use a defaults file or MYSQL_PWD in production.

# Check rule hit counts
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;"

# Inspect active rule configuration (runtime layer, not memory)
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, active, match_digest, match_pattern, \
      destination_hostgroup, apply, username, schemaname, \
      flagIN, flagOUT, cache_ttl, re_modifiers \
      FROM runtime_mysql_query_rules ORDER BY rule_id;"

# See where queries are actually landing (top 20 by frequency)
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 \
      ORDER BY count_star DESC LIMIT 20;"

# Check for read-only errors (1290) indicating writes on replicas
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT * FROM stats_mysql_errors ORDER BY last_seen DESC LIMIT 20;"

# Verify config layer consistency: memory vs runtime
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT COUNT(*) AS memory_rules FROM mysql_query_rules; \
      SELECT COUNT(*) AS runtime_rules FROM runtime_mysql_query_rules;"

# Check for username field set to literal string 'NULL' instead of SQL NULL
mysql -u admin -padmin -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, username, username IS NULL as is_sql_null \
      FROM runtime_mysql_query_rules WHERE username = 'NULL';"

How to diagnose it

  1. Confirm you are reading from runtime, not memory. Query runtime_mysql_query_rules, not mysql_query_rules. The memory layer is staging. A rule can exist in memory but never have been loaded to runtime. If the rule count differs between the two tables, someone made a change without running LOAD MYSQL QUERY RULES TO RUNTIME.

  2. Check whether hits were just reset. stats_mysql_query_rules resets to zero every time rules are loaded to runtime. If someone recently reloaded rules, zero hits is expected until traffic flows. Compare the hit timestamp against ProxySQL_Uptime and any recent config changes.

  3. Identify where the target queries actually land. Look in stats_mysql_query_digest for the query patterns you expect your rule to catch. Check the hostgroup column. If writes are landing in a reader hostgroup or SELECTs are hitting the writer, the routing rule is not matching.

  4. Test the regex manually. Copy the match_digest or match_pattern value and test it against the actual digest_text from stats_mysql_query_digest. ProxySQL does not provide a built-in regex tester in the admin interface, so test externally:

# Extract the digest text and pattern, then test with grep -P or python re
# Example: does the pattern match the digest?
python3 -c "
import re
digest = 'SELECT * FROM users WHERE id=?'
pattern = r'^SELECT \* FROM users WHERE id = 42'
print('MATCH' if re.search(pattern, digest) else 'NO MATCH')
"

The most common mistake: the digest normalizes literals to ? and strips comments, so a pattern like ^SELECT \* FROM users WHERE id = 42 will never match a digest that reads SELECT * FROM users WHERE id=?.

  1. Review rule ordering for shadowing. List all rules by rule_id. Look for broad patterns (like ^SELECT or .*) at lower rule_id values that would catch queries before your specific rule. If a broad rule has apply=1, everything after it is dead code for the queries it catches.

  2. Trace flagIN/flagOUT chains. A rule with flagIN=0 is evaluated for all queries. A rule with flagIN=N is only evaluated if a prior rule set flagOUT=N. If your rule has flagIN > 0 but no earlier rule produces that flag, your rule is unreachable.

  3. Check the username field for SQL NULL vs the string 'NULL'. SQL NULL in this field means “match any user.” The literal string 'NULL' matches only a user named “NULL.” Distinguish them with username IS NULL. Set the field to NULL (not an empty string) to match all users.

Fixes

Invalid regex pattern (silent compile failure)

LOAD MYSQL QUERY RULES TO RUNTIME does not error on an invalid regex. The rule loads and becomes active, but it never matches.

To fix: simplify the regex and reload. Remove complex quantifiers, character classes, or backreferences that may not be supported by the configured engine. The mysql-query_processor_regex variable controls which engine is used. Check your current setting:

SELECT variable_value FROM global_variables WHERE variable_name='mysql-query_processor_regex';

RE2 has different capabilities than PCRE: no backreferences, no lookahead. If your pattern relies on features the configured engine does not support, it will silently fail to match.

After fixing:

LOAD MYSQL QUERY RULES TO RUNTIME;
-- Then verify hits are accumulating
SELECT rule_id, hits FROM stats_mysql_query_rules WHERE rule_id = <your_rule_id>;

match_digest stripping comments and parameters

match_digest matches the normalized digest. The digest has:

  • All literal values replaced with ?
  • All SQL comments (/* ... */, --, #) removed
  • Whitespace normalized

If your rule uses match_digest to match on a comment hint (like /* route=writer */) or on a specific literal value, it will always get zero hits. Switch the same pattern to match_pattern, which matches the raw query text as received from the client.

Note: match_digest cannot be used for query rewriting. Query rewriting requires match_pattern plus replace_pattern.

Rule ordering shadowing

Rules are evaluated in ascending rule_id order. A catch-all rule at rule_id=1 with apply=1 will prevent every subsequent rule from ever being evaluated. More specific rules must have lower rule_id values than the broad rules they specialize.

To fix: either reorder rule_id values so specific rules come first, or remove apply=1 from the broad rule (but then verify the intended rule has apply=1 so evaluation stops there).

Missing apply=1

If a matching rule lacks apply=1, evaluation continues to the next rule. The query uses the settings of the last matching rule in the chain, not the first. This means a read-routing rule at rule_id=100 and a write-routing rule at rule_id=200 where both match the same query will route to rule_id=200’s destination. If that is the reader hostgroup, writes silently land on replicas.

To fix: add apply=1 to the rule that should terminate evaluation for its matched queries.

username field containing literal string NULL

Setting the username column to the string 'NULL' causes ProxySQL to match only connections from a user literally named “NULL”. To match all users, set the field to SQL NULL:

UPDATE mysql_query_rules SET username=NULL WHERE rule_id=<your_rule_id>;
LOAD MYSQL QUERY RULES TO RUNTIME;

Version-specific edge cases

Two known issues can produce zero-hit rules:

  • ProxySQL 2.7.1 through 2.7.3 with caching_sha2_password: The first query after a ProxySQL restart may bypass query rules entirely because schema/database information is lost during the full authentication handshake. Fixed by PR #4941. If you see zero hits only on the first query after restart, check this.
  • ProxySQL 2.6.6: LOAD MYSQL QUERY RULES TO RUNTIME can trigger a SIGSEGV (signal 11) crash immediately after loading. If ProxySQL crashes when you load rules, check your version.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
stats_mysql_query_rules.hits per rule_idDirectly shows which rules are matching trafficCritical rule at zero hits; default rule with unexpectedly high hits
stats_mysql_query_digest.hostgroupShows where each query pattern actually landsWrite digests (INSERT, UPDATE, DELETE) in reader hostgroup
stats_mysql_errors (errno 1290)Read-only errors from replicas indicate writes routed wrongAny 1290 errors from reader hostgroup backends
Default/catch-all rule hit rateMeasures how much traffic falls through without matching specific rulesSustained increase indicates queries not matching intended rules
runtime_mysql_query_rules vs mysql_query_rules row countsDetects uncommitted config changesCounts differ means someone changed memory without loading to runtime
Query_Processor_time_nsecComplex regex rules add CPU overhead per querySustained increase after adding rules may indicate regex complexity

Prevention

  • Validate regex before loading. Test every match_digest pattern against actual digest_text values from stats_mysql_query_digest. Test every match_pattern against actual client query text. Use the digest column (exact match on digest hash like 0x...) instead of regex when you need to match a specific query shape, as it is the fastest matching method.

  • Enforce rule_id ordering in config management. Place specific rules at lower rule_id values than broad catch-all rules. Make the catch-all rule the highest rule_id.

  • Always set apply=1 on terminal rules. Rules that should determine the final routing decision must terminate evaluation. Leaving apply=0 creates a silent routing time bomb where a future rule addition can override the intended destination.

  • Monitor rule hit distribution as routing validation. Track stats_mysql_query_rules.hits per rule over time. A critical write-routing rule with zero hits while the default rule has high hits is a routing failure, not a monitoring curiosity.

  • Track the default rule hit rate. The catch-all or fallback rule should handle a small fraction of total queries. If it starts absorbing a large share, rules upstream are broken.

  • Use SAVE MYSQL QUERY RULES TO DISK after verifying. A rule that works in runtime but was never saved to disk will vanish on the next ProxySQL restart.

How Netdata helps

  • Per-second rule hit tracking. Netdata collects stats_mysql_query_rules at per-second resolution. A rule that drops to zero hits is visible within seconds, not at the next 5-minute polling interval.
  • Correlation with backend traffic and errors. When a rule stops matching, the immediate downstream effects are a shift in per-hostgroup query counts (stats_mysql_connection_pool) and potentially MySQL 1290 errors from reader backends. Netdata surfaces all three signals on the same timeline.
  • Anomaly detection on rule hit patterns. Netdata flags when a rule’s hit count deviates from its established baseline, which catches gradual degradation before it becomes a complete outage.
  • Runtime table collection. Per-second collection of runtime tables (runtime_mysql_query_rules, stats_mysql_query_digest) makes divergence between the memory and runtime layers detectable without manual polling.