You just placed an order. Why is your order history empty?

The checkout page says the order succeeded. You open your order history, and there is nothing there. Refresh a moment later, and the order appears.

The database may have done everything it was asked to do. The application inserted the order on the primary, then read the history from a replica. The replica answered before it had applied that insert.

For the person buying something, that distinction is invisible. They have just been told two different things about the same purchase.

Sending the history query to the primary is one way to keep those answers consistent. But perhaps you already use replicas to serve reads, and you want to keep using them when they have caught up. ProxySQL can make that routing decision using the transaction position of the write.

Stable ProxySQL 3.0.12 extends this capability to MariaDB. It gives an important read a condition: use a replica only when its reported progress covers the transaction this application connection just committed.

Follow one order through two queries

Suppose the checkout code holds one connection to ProxySQL, with autocommit enabled. On that connection, it sends an insert to an existing demonstration table:

INSERT INTO article_orders (order_id, customer_id, status)
VALUES (1001, 42, 'received');

After the successful response, it asks for the customer’s history:

SELECT order_id, status FROM article_orders WHERE customer_id = 42 ORDER BY order_id;

These application queries go through ProxySQL’s database listener, normally port 6033. For our example, article_orders already exists and order ID 1001 is unused.

Without a freshness condition, either configured replica might answer the second query. With causal routing configured, ProxySQL uses the tracked write position to narrow the choice. A replica that has reached the required position can serve the history. One that is still behind cannot qualify merely because it is online and accepting connections.

The application can therefore keep the familiar insert-then-read sequence while ProxySQL chooses a suitable backend.

Give ProxySQL both sides of the comparison

A transaction identifier gives the write a position in the replication history. MariaDB writes it as domain-server-sequence: an illustrative value is 0-17-402. ProxySQL compares the sequence watermark in the relevant domain. This depends on a correctly configured replication topology; an identifier does not make unrelated databases interchangeable.

ProxySQL needs to learn the position of this connection’s write and the progress of the servers that might answer its read.

For the first part, request transaction tracking through ProxySQL’s Admin interface, normally port 6032:

SET mysql-default_session_track_gtids='OWN_GTID';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Although that setting uses the familiar GTID name, MariaDB supplies the information differently from MySQL. ProxySQL asks MariaDB to track its last_gtid system variable, preserving other variables already being tracked. It collects the transaction identifier from the successful server response. It does not issue an additional blocking query after the write to discover that position.

For the second part, use binlog reader 2.5.0 with the MariaDB servers. The reader follows each server’s binary log and reports transaction progress to ProxySQL. Each relevant mysql_servers row needs the corresponding reader’s listening port in gtid_port; ProxySQL must be able to reach that reader at the row’s hostname.

For the reader to report applied writes, MariaDB needs binary logging, row-based events and replica updates included in its binary log (log-slave-updates). Set up the reader credentials and network access too. The release includes a reference MariaDB arrangement and ProxySQL configuration showing how the reader ports connect to the database servers. Use your deployment’s accounts and addresses.

Before relying on this route, confirm that writes populate the session’s tracked position and that reader progress appears in stats_mysql_gtid_executed on the Admin interface. An absent tracked position cannot constrain a later read.

If you followed our older GTID consistent reads article, this MariaDB path is the new addition; that article’s setup describes MySQL.

Make the history read depend on the write

ProxySQL collects backend servers into numbered destination groups called hostgroups. For this example, the writer is already in group 9100, replicas are in 9101, and the checkout account sends its writes to 9100.

This deliberately narrow Admin rule selects the history query above:

INSERT INTO mysql_query_rules
    (rule_id, active, username, match_pattern,
     destination_hostgroup, gtid_from_hostgroup, apply)
VALUES
    (910010, 1, 'checkout',
     '^SELECT order_id, status FROM article_orders WHERE customer_id = [0-9]+ ORDER BY order_id[[:space:]]*;?[[:space:]]*$',
     9101, 9100, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Use an unused rule ID and place it before any broader rule that would finish processing this query. Adapt the match to the SQL your application actually sends. Here, destination_hostgroup chooses the readers; gtid_from_hostgroup says to use this session’s tracked position from the writer group as the requirement. Keep every rule on this query’s processing path free of a positive cache_ttl, including earlier matching rules: a previously cached history could otherwise defeat its purpose.

A reader-only destination can wait when no suitable replica has reached the position, and the request can time out. Choosing a primary fallback is a separate routing/topology decision. This rule does not add the primary to the reader group.

Keep the application’s connection in view

The tracked position belongs to the application connection’s ProxySQL session. A browser redirect that obtains a different pooled connection does not automatically inherit it. If checkout and history run on separate connections, the application needs an explicit way to carry the required position, or a routing choice that keeps that confirmation read on the writer.

Start by following that connection in your checkout code. If it stays with the insert and the history read, configured tracking gives ProxySQL the information to choose a replica that can show the order the customer just placed.