[DOCS/ENHANCEMENT] Clarify runtime_mysql_users desynchronization recovery & atomic purge flow during client handshake errors

Summary

In high-concurrency production environments utilizing ProxySQL for connection pooling, client connections can fail during Spring Boot / HikariCP pool initialization with ProxySQL Error: Access denied for user 'app-user'@'10.x.x.x' (using password: YES) even when the user exists and authenticates cleanly on backend MySQL writer nodes.

This occurs when runtime_mysql_users becomes desynchronized or retains duplicate/stale hashes compared to mysql_users. Because ProxySQL authenticates incoming client handshakes at the proxy layer prior to multiplexing traffic to backend hostgroups, any discrepancy in ProxySQL's in-memory user cache results in immediate connection rejections.

We would like to propose a documentation enhancement (and potential admin script helper guidance) clarifying the exact, production-safe atomic purge and reload flow for runtime_mysql_users.

Production Incident Context & Failure Mode

1. Failure Symptom: Application containers enter a boot-loop with HikariCP failing fast on initial handshake:
   复制

   Caused by: java.sql.SQLException: ProxySQL Error: Access denied for user 'app-svc'@'10.x.x.x' (using password: YES)

Root Cause: Direct updates or partial reloads leave stale entries in runtime_mysql_users. Pushing LOAD MYSQL USERS TO RUNTIME without purging stale entries can preserve conflicting memory references in ProxySQL's internal user hash table.

Proposed Operational Recovery Flow To safely recover without disrupting other active database users managed by ProxySQL, the following atomic purge and backup sequence was validated in production:

1. In-Memory & Physical Disk Backup Before modifying ProxySQL's memory layer, operators should back up both the in-memory SQLite table and physical database file:

SQL

-- Admin Interface (Port 6032)

CREATE TABLE mysql_users_backup_YYYYMMDD AS SELECT * FROM mysql_users;

BASH

sudo cp /var/lib/proxysql/proxysql.db /var/lib/proxysql/proxysql.db.bak

1. Atomic Purge and Runtime Reload To ensure stale hashes are completely removed before re-inserting clean mapping to Hostgroup 0:

SQL

-- Purge conflicting user entries from memory table
DELETE FROM mysql_users WHERE username = 'target_service_user';
LOAD MYSQL USERS TO RUNTIME;

-- Insert single clean mapping to default hostgroup
INSERT INTO mysql_users (username, password, default_hostgroup, active, transaction_persistent) 
VALUES ('target_service_user', 'EXACT_MATCHING_PASSWORD', 0, 1, 1);

-- Atomically push to runtime memory and persist to disk
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Proposed Contribution / PR Scope

We would like to submit a Pull Request to update the official ProxySQL documentation (specifically under the Global Setup / Admin Management / User Management section) to:

Document the distinction between backend MySQL mysql.user and ProxySQL runtime_mysql_users handshake evaluation order.

Provide the recommended atomic purge & recovery commands for resolving stale runtime_mysql_users cache entries.

Include standard SQLite physical backup instructions prior to executing admin memory mutations.

Please let us know if the maintainers are open to a PR updating the documentation repository or adding a troubleshooting guide section for this flow!