vtgate to vttablet: expired MySQL backend credentials and sandbox-mode error propagation across the connection pool

Direct Answer

Vitess does not transparently recover from expired MySQL backend credentials. vttablet maintains a pool of authenticated connections that remain valid until they are closed, recycled, or fail a health check. The sandbox-mode error (MySQL 1820, ER_MUST_CHANGE_PASSWORD) only surfaces when vttablet attempts to open a new connection due to pool exhaustion, explicit reconnect, or failed validation. At that point, vttablet logs the error and returns a generic connection failure to vtgate, which propagates to the client.

Confirmed Behavior

• Connection pools mask expiration: Authenticated connections stay valid after ALTER USER ... PASSWORD EXPIRE because MySQL only enforces sandbox mode during the initial handshake.
• Failure is delayed, not prevented: Pool sizing (pool_size, max_idle_connections) controls the latency between credential expiration and visible failure. Larger pools delay detection.
• Error propagation is generic: vttablet wraps the driver error (go-sql-driver/mysql) and typically returns 'access denied' or 'connection lost' to vtgate.
• No automatic recovery: Operators must rotate credentials out-of-band, and then restart vttablet or trigger a pool refresh (for example, a rolling restart).

Authentication Plugin Differences

Plugin	Sandbox Behavior	Typical Error Code
mysql_native_password	Allows only SET PASSWORD in sandbox mode	1820 (ER_MUST_CHANGE_PASSWORD)
caching_sha2_password (MySQL 8.0+ default)	Stricter; may block even SET PASSWORD until password change	3948 (ER_PASSWORD_EXPIRED) or 1820 depending on server version

Note: With caching_sha2_password using RSA key exchange, handshake can fail earlier if the public key is unavailable.

Version-Specific Handling

• Vitess 15+ (2023+): Improved connection pool checking and error classification, but no release adds automatic renewal.
• Before v12: Less robust pool validation; expired credentials could cause silent failures until manual intervention.
• Per-keyspace users: Each keyspace's pool fails independently when its credential expires, causing partial keyspace unavailability rather than cluster-wide failure.

Verification

1. Check vttablet logs for ER_MUST_CHANGE_PASSWORD or ERROR 1820 during connection acquisition.
2. Run vtctl GetTablet <tablet_alias> and inspect pool stats; compare connection creation timestamps against rotation time.
3. Simulate expiration: ALTER USER 'app_user'@'%' PASSWORD EXPIRE; then send traffic. Observe whether existing connections succeed and new ones fail.
4. Verify Vitess version via vtgate --version and vttablet --version; consult release notes for connection pool changes.

Diagnostic Detail Needed

Which authentication plugin is configured for the backend accounts (mysql_native_password versus caching_sha2_password)? This changes the exact error code surfaced and whether RSA key exchange adds an earlier failure mode.