ProxySQL integration for Flashback

SELECT * FROM _flashback.orders AS OF '2026-04-27 09:00:00' WHERE id = 42;
SELECT * FROM _diff.orders BETWEEN '2026-04-27 09:00' AND '2026-04-27 10:00' WHERE id = 42;
SELECT * FROM _snapshot.orders AT GTID '3E11FA47-71CA-11E1-9E33-C80AA9429562:42' WHERE id = 1;

Plain MySQL protocol: mysql, DBeaver, Metabase, an ORM. No plugin.

Three ways to connect

• ProxySQL. shim.yaml, hostgroups, query rules, systemd units, MySQL 8 auth-plugin notes: setup guide.

• Web interface port. Settings → MCP Server → Connect a SQL client turns it on and shows the line to copy. Off until you press Turn on there: Query Parquet with MySQL.

  mysql -h 127.0.0.1 -P 3309 -u <server-name> -p

• Terminal. bintrail shim: dedicated terminal.

• PostgreSQL sources. Same AS OF from psql: querying PostgreSQL sources.

SQL dialect

SELECT * FROM _flashback.t AS OF '<ts>' WHERE id = 42;
SELECT * FROM _flashback.t FOR SYSTEM_TIME AS OF '<ts>' WHERE id = 42;  -- ANSI alias
SELECT * FROM _flashback.t AS OF '5 minutes ago' WHERE id = 42;         -- or NOW() - INTERVAL 5 MINUTE
SELECT * FROM _diff.t BETWEEN '<ts1>' AND '<ts2>' WHERE id = 42;
SELECT * FROM _snapshot.t AT GTID '<gtid>' WHERE id = 42;
SELECT /*+ DBTRAIL_AT='<ts>' */ * FROM t WHERE id = 42;                 -- rewritten to _flashback
SELECT * FROM t WHERE id = 42 AS OF '<ts>';                             -- AS OF must end the statement
SELECT * FROM _snapshot.t AS OF '<ts>';                                 -- whole table, streamed, no row cap

• WHERE must be one equality on a single-column primary key, any name. A non-key WHERE is an error, never a wrong row. IN and BETWEEN on the key parse but are not served yet.
• _flashback and _snapshot need AS OF; current rows live in the real table.
• _diff columns: _event_id, _event_timestamp, _event_type, _gtid, then before_<col> and after_<col> for every column.
• No WHERE: whole table at that instant. _flashback returns only rows with changes in the retained window; _snapshot returns every row when a backup is configured.

Ordinary SQL on the copy

The web interface port also answers ordinary read-only SQL, on the same connection: Query Parquet with MySQL. The standalone bintrail shim answers time travel only.

Deleted vs never existed

Errors you might see

The connection stays open after any of them. Setup failures: repository guide.

ERROR 1045 (28000): access denied

Two logins: ProxySQL checks mysql_users, then the shim checks shim.yaml tenants[]. The user must be in both, and the app must send the cleartext mysql_password from shim.yaml. After editing shim.yaml, regenerate and apply, then restart the shim:

rm -f proxysql-setup.sql
bintrail proxysql-config --out proxysql-setup.sql
mysql -u admin -p -h 127.0.0.1 -P 6032 < proxysql-setup.sql

Query reaches MySQL (_flashback.t doesn't exist)

The routing rule did not match:

mysql -u admin -p -h 127.0.0.1 -P 6032 \
  -e "SELECT rule_id, match_pattern, destination_hostgroup FROM runtime_mysql_query_rules WHERE rule_id BETWEEN 990001 AND 990006;"

Expect six rows to hostgroup 991. Missing: re-apply proxysql-setup.sql. Present: a rule with a smaller rule_id is catching _flashback.* first.

connection refused on the shim port

The shim is down or on another address (default 127.0.0.1:3308):

systemctl status bintrail-shim
ss -tlnp | grep 3308
journalctl -u bintrail-shim -n 100

Usual causes: unreadable shim.yaml, no --index-dsn (required on the CLI, never read from yaml), or an unquoted mysql_password.

ERROR 1064 (42000): unsupported shape

The message names the problem: no AS OF (_flashback, _snapshot) or BETWEEN (_diff), a bad timestamp, no schema selected (USE <database>;), a WHERE that is not the single-column primary key, or a join with a real table (join client-side). No WHERE at all is allowed: whole-table rebuild.

ERROR 1235: this server only handles _flashback / _snapshot / _diff virtual-schema queries

A normal query reached a shim with no copy to run it on: the standalone bintrail shim (usually a client connected to the shim port instead of ProxySQL; point it at ProxySQL), or the web interface port for a server whose copy is not on local disk yet or whose archive access is off. The message says which. On the web interface port, ordinary SQL on a local copy runs instead (see above).

ERROR 1526 (HY000): outside retention

The time falls where the index has no data and no archive. Narrow the range, or check archive coverage. The shim is strict by default; --allow-gaps warns and continues instead.

ERROR 1104 (42000): over 100,000 rows

A whole-table _flashback, or a whole-table query with LIMIT, is capped. Narrow the time, add WHERE <pk> = <value> (point lookups are uncapped), or query _snapshot without LIMIT: with a backup configured it streams the whole table with no cap.

On ordinary SQL the cap is 10,000 rows: add a LIMIT, or narrow the statement. Query Parquet with MySQL.

ERROR 1105 (HY000): internal error

A real server fault: index timeout, archive fetch outage, or a bug. Read journalctl -u bintrail-shim, retry, and if it persists open an issue with the message and the SQL.

ERROR 1317 or ERROR 1203: timeout or busy

• 1317: the query passed its deadline, or the client disconnected. Standalone shim: --query-timeout (default 5 minutes). The web interface port also bounds each query at 5 minutes by default.
• 1203: too many whole-table queries at once (retry, filter by primary key, or raise --max-fulltable-queries, default 4), or, for ordinary SQL on the web interface port, every worker stayed busy for more than 30 seconds: retry.
• Killed with no shim error: ProxySQL gave up first. Raise mysql-default_query_timeout or mysql-connect_timeout_server.

Empty result

1. No change at or before that time for the row.
2. The last change before that time was a DELETE.
3. A gap, only under --allow-gaps. Strict mode returns 1526 or 1105 instead.

Tell 1 from 2 with _diff on that key: a deleted row has events, a row that never existed has none.

Limitations during beta

• A deleted row returns empty, not a tombstone: use _diff.
• No composite primary keys. Workaround: _diff, filter client-side.
• A table dropped from the source loses its key metadata: queries fail with "table has no primary key"; _diff still works.
• One equality predicate only.
• A whole-table _flashback, or a whole-table query with LIMIT, is buffered and capped at 100,000 rows. A whole-table _snapshot without LIMIT streams with no cap when a backup is configured. Point lookups are uncapped.
• Prepared statements work since 0.98.0. Cursors are refused.
• No joins between virtual and real tables.
• Read-only. One statement per round trip.
• _diff has no row cap: narrow the window on hot rows.
• A column added after the target time reads NULL.

Blocked? Open an issue.