Admin: support the SET statements mysqldump issues, so mysqldump can back up the Admin interface

[renecannao]

ProxySQL's own docs and most runbooks suggest backing up the Admin tables with mysqldump. With MySQL 8.0's mysqldump this no longer works: it aborts on connect, before dumping anything.

Because the usual recipe pipes through a filter —

mysqldump -uadmin -padmin -h127.0.0.1 -P6032 --no-tablespaces --skip-triggers --replace main \
  | grep '^REPLACE' > backup.sql

— the failure is silent: you get a 0-byte file and exit 0, and only discover it at restore time, which is typically mid-upgrade.

mariadb-dump works today, so there is a workaround, but mysqldump is what most people reach for.

Environment

• ProxySQL 3.0.10-426-gf5f1e14, Ubuntu 22.04, admin on 127.0.0.1:6032
• mysqldump 8.0.46 (Ubuntu mysql-client-core-8.0)
• mariadb-dump 10.6.23 — works, produces a complete dump that restores correctly

What mysqldump 8.0 sends

Captured with general_log on a real MySQL, then replayed statement-by-statement against Admin:

Statement	Admin
/*!40100 SET @@SQL_MODE='' */	OK
/*!40103 SET TIME_ZONE='+00:00' */	OK
/*!80000 SET SESSION information_schema_stats_expiry=0 */	OK (versioned comment ignored)
SET SESSION NET_READ_TIMEOUT=86400, SESSION NET_WRITE_TIMEOUT=86400	FAIL — aborts the dump
SHOW VARIABLES LIKE 'gtid_mode'	OK
SELECT @@GLOBAL.GTID_EXECUTED	FAIL
show tables / show table status like ...	OK
LOCK TABLES ... READ / UNLOCK TABLES	OK
SET SQL_QUOTE_SHOW_CREATE=1	OK
SET SESSION character_set_results = 'binary'	OK
show create table / show fields from	OK
SELECT /*!40001 SQL_NO_CACHE */ * FROM ...	OK
SELECT ... FROM information_schema.COLUMN_STATISTICS	FAIL

Most of the protocol surface is already there — the dump dies on the first SET SESSION.

The three failures

1. SET SESSION <var> — the blocker. The scope keyword is not parsed; SESSION is absorbed into the variable name:

mysql> SET SESSION NET_READ_TIMEOUT=86400;
ERROR 1045 (28000): ProxySQL Admin Error: ERROR: Unknown global variable: 'SESSION NET_READ_TIMEOUT'.

mysql> SET SESSION autocommit=1;
ERROR 1045 (28000): ProxySQL Admin Error: ERROR: Unknown global variable: 'SESSION autocommit'.

Note the second one: autocommit is a known variable, and it still fails — so this is about the SESSION keyword, not about the variable being unknown. No mysqldump flag avoids this statement.

2. SELECT @@GLOBAL.<var> — @@scope.var is not parsed:

mysql> SELECT @@GLOBAL.gtid_executed;
ERROR 1045 (28000): ProxySQL Admin Error: near ".": syntax error
mysql> SELECT @@global.version;
ERROR 1045 (28000): ProxySQL Admin Error: near ".": syntax error

Avoidable with --set-gtid-purged=OFF.

3. information_schema.COLUMN_STATISTICS does not exist — avoidable with --column-statistics=0.

Inconsistency noticed while testing

Single-variable SET is stricter than the multi-variable form:

mysql> SET sql_mode="";                   -- FAIL: Unknown global variable: 'sql_mode'
mysql> SET autocommit=1, sql_mode="";     -- OK

Same unknown variable, two different outcomes depending on whether it is alone in the statement. Probably unintended regardless of this issue.

Suggested scope

Minimum to unblock mysqldump (with --set-gtid-purged=OFF --column-statistics=0):

• parse the SESSION / GLOBAL / LOCAL scope prefix in SET
• accept-and-ignore session variables Admin has no concept of (NET_READ_TIMEOUT, NET_WRITE_TIMEOUT, …) rather than erroring — these are client-side hints and harmless as no-ops
• reconcile single vs multi-variable SET strictness

To make plain mysqldump work with no flags:

• support SELECT @@GLOBAL.<var> / @@session.<var>
• expose an empty information_schema.COLUMN_STATISTICS

Reproduce

mysqldump -uadmin -padmin -h127.0.0.1 -P6032 --no-tablespaces --skip-triggers --replace main > /tmp/b.sql
echo "exit=$? size=$(stat -c%s /tmp/b.sql)"    # exit=2 size=0

Found while verifying the backup procedure in ProxySQL training material against a live instance.

🤖 Generated with Claude Code

[coderabbitai]

🔗 Related PRs

#5166 - MySQL: Track session-specific system variables changes [merged]
#5857 - feat(admin): surface Records/Updated/Rejected/Unknown in OK info for LOAD * VARIABLES TO RUNTIME (issue #1288) [closed]
#5872 - test: vendor replication/shunning suite from jenkins-build-scripts into test/repl/ [merged]
#5876 - test(repl): re-vendor suite from correct proxysql_repl_tests branch (fixes missing proxysql.cnf) [merged]
#5956 - Add dedicated TLS for admin interfaces [open]

---

📝 Issue Planner

_{Check the box below or use the @coderabbitai plan command to generate an implementation plan and prompts that you can use with your favorite coding assistant.}

• Create Plan

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!