Originally published April 2024. Substantially updated October 2025 with expanded analysis and debugging insights.

I spent 3 days debugging why ProxySQL wasn’t redirecting queries to read replicas.

I checked the configuration. I verified queries redirected when using the CLI. I confirmed auto-commit was enabled. I read the docs cover to cover.

The problem? My test framework.

The test setup created nested transactions with writes, which pinned everything—including my auto-commit query—to the primary DB. ProxySQL was working fine. My testing setup was broken.

Here’s what I learned about debugging query redirection, and what I wish I’d done to save myself the confusion.

When This Matters

This applies if you’re:

  • Setting up query routing with ProxySQL
  • Using test frameworks that wrap DB connections in transactions
  • Debugging query behavior that differs between tests and production

My Debugging Journey

Days 1-2: I blamed the configuration

I checked query rules, verified auto-commit settings, and read through ProxySQL documentation. Everything looked correct.

Day 3: I built a minimal reproduction

I created a simple script outside the test framework. Query redirection worked immediately.

The culprit: My test framework’s setup nested all transactions in a parent transaction, which pinned all nested transactions—including my explicit auto-commit query—to the primary DB.

What I Learned About ProxySQL Query Routing

After 3 days of debugging, here’s what I now understand about how ProxySQL actually handles query redirection:

ProxySQL has two redirection options:

  1. Use a different ProxySQL port/user (all queries from that connection use a specific hostgroup)
  2. Use query rules to redirect specific queries matching certain conditions

I went with option 2—it seemed simpler since I wouldn’t need to change application code to manage two different database connections.

Here’s what the docs don’t emphasize enough:

ProxySQL doesn’t redirect queries that are part of a transaction—at least not transactions that weren’t already directed to a read replica from the start.

This left me with two options:

  1. Use auto-commit sessions (makes queries standalone in theory)
  2. Disable transaction_persistent for mysql users (but this breaks read-after-write consistency)

I chose option 1. And it worked… just not in my test framework.

The nested transaction trap:

My test framework created a parent transaction that did writes. My “auto-commit” query was actually a nested transaction within that parent. ProxySQL saw the parent transaction with writes and pinned everything to the primary DB—including my supposedly standalone query.

When I tested outside the framework, redirection worked perfectly. Even without explicit auto-commit, which contradicted what I understood from the docs.

Here’s why:

Explicit vs. Implicit Transactions

SQLAlchemy (which my API uses) creates implicit transactions—it doesn’t send explicit BEGIN TRANSACTION SQL statements. ProxySQL only detects a transaction if it sees explicit SQL statements like BEGIN, START TRANSACTION, or SET AUTOCOMMIT=0.

Since there’s no explicit transaction statement, ProxySQL doesn’t know a transaction exists. It sees standalone queries and happily redirects them—even though SQLAlchemy is tracking a transaction at the application level.

The test framework difference:

My test framework explicitly created transactions (with BEGIN statements) for setup. ProxySQL saw those and pinned everything. That’s why the same query behaved differently in tests vs. production.

Key Lessons

1. Test in the real environment first

I wasted 3 days debugging my test setup instead of verifying production behavior. If I’d tested outside the framework first, I would have discovered ProxySQL was working fine.

2. Understand nested transaction behavior

Writes in parent transactions pin everything to the primary DB. Even explicit auto-commit in child transactions can’t override this.

3. Start with minimal reproduction

When debugging complex systems, build the simplest version that works, then add complexity back. Don’t start by debugging the most complex setup.

4. Test frameworks can mislead

Just because I disabled transaction rollback didn’t mean I understood the full transaction setup. Test scaffolding can introduce behavior that doesn’t exist in production. In this case: my test framework used explicit transactions (which ProxySQL sees) while my production app used SQLAlchemy’s implicit transactions (which ProxySQL doesn’t see).

Bottom Line

If ProxySQL query routing isn’t working as expected, don’t assume it’s the configuration. Test with the simplest possible setup first—outside your test framework, outside your application stack.

If it works there, your problem isn’t ProxySQL. It’s your environment.

For nested transaction scenarios: remember that writes in parent transactions will pin child transactions to the primary, regardless of auto-commit settings.

Save yourself 3 days. Test simply first.

Check out this repo for a lab environment to test query redirection

Debugging Tips:

How was I testing this?

  • The query rule on ProxySql
mysql_query_rules=
(
  {
    rule_id = 2
    active = 1
    match_pattern = "^SELECT\s*\/\*\s*REDIRECTION\_TEST\s*\*\/"
    apply = 1
    destination_hostgroup = 3
    comment = "Send to read-replica"
  }
)
  • The query that’s sent:
# Login
mycli mysql://root@db.local:3306/classicmodels

# Query to primary
SELECT /* REDIRECTION_TEST */ * from customers;

# Query to replica
SELECT /* REDIRECTION_TEST */ * from customers;

How do I know if a query was redirected?

  • Turn on query logging
#ProxySQL DB Access
mycli -uradmin -pradmin -hproxysql.local -P6032

# Turn on logging
SET mysql-eventslog_filename='/queries.log';
SET mysql-eventslog_default_log=1;
# Log in json format 
SET mysql-eventslog_format=2;
# Load changes to runtime
LOAD MYSQL VARIABLES TO RUNTIME;
  • Sample Log line:
{"client":"172.18.0.128:64022","digest":"0x4F33AE77FCE89DDF","duration_us":11702,"endtime":"2024-04-13 20:15:01.510946","endtime_timestamp_us":1713039301510946,"event":"COM_QUERY","hostgroup_id":3,"query":"SELECT /* REDIRECTION_TEST */ * from customers","rows_sent":122,"schemaname":"classicmodels","server":"db-replica:3306","starttime":"2024-04-13 20:15:01.499244","starttime_timestamp_us":1713039301499244,"thread_id":4,"username":"root"}

  • Fields that matter:

    • “Query”: the query that’s being processed
    • “hostgroup_id”: What hostgroup the query is routed to
    • “server”: The server it was routed to
    • The “server” will tell you definitively if the query was redirected to the replica or not, the “hostgroup_id” will tell you if the query was routed to a particular host-group (based on the rule), but since the hostgroup does include the primary db at a low weight, we can’t rely on this value without also looking at server.

  • ProxySQL Stats:

    • Another method is looking at proxy sql stats to find out how many matches there were against a particular rule id, and also how many queries each server in a hostgroup handled so far. When the query is part of a transaction that contains a write, the query rule match count will increase, but noting will actually be sent to the replica.
# Query rule 
select * from stats_mysql_query_rules

# Connection stats 
select `hostgroup`,`Queries`, `srv_host` from stats_mysql_connection_pool
select * from stats_mysql_connection_pool

References