VTTablet: pooled-connection baseline is inconsistent between fresh and settings-reset connections on servers using init_connect

Overview of the Issue

Spun out of the review of #21062 (see this thread), raised by @arthurschreiber.

A pooled vttablet connection can be in one of two "clean" states, and they differ on a server that uses init_connect:

• Fresh: after the handshake, MySQL runs init_connect for non-admin users (vt_app without SUPER/CONNECTION_ADMIN), so the session carries whatever it set — e.g. time_zone, transaction_isolation, runtime sql_mode members. Vitess then runs sqlmode.NeutralizeSessionQuery, which deliberately sources from the session value to preserve those adjustments (go/vt/dbconfigs/dbconfigs.go).
• Returned from the settings pool: a connection that carried a client Setting is reset with the query BuildSettingQuery generates (go/vt/vttablet/tabletserver/planbuilder/plan.go), which is SET <name> = DEFAULT for every variable except sql_mode. DEFAULT resolves to the global value, not the init_connect value, so the init_connect adjustment for that variable is gone. (sql_mode was special-cased to sqlmode.NeutralizedGlobalExpr, which also sources from the global.)

So today, whether a pooled connection reflects init_connect depends on its history. #21062 did not change this — closing a connection after CALL yields a fresh connection, one of the two existing states — but the review surfaced it.

Impact

Operators who rely on init_connect to set per-session defaults for the app user get them on some pooled connections and not others, silently. Anyone not using init_connect (or whose app user is exempt from it) is unaffected.

Options discussed

1. Define the baseline as the post-reset (globals) state for every connection, e.g. run COM_RESET_CONNECTION (or equivalent) as part of connection setup, so fresh and reset connections match. Consistent, but it means deliberately discarding init_connect for all pooled connections — a behavior change for operators relying on it, and it would need a documented migration path (move those settings to globals or --init-file). Note a reset immediately after connect also does not touch the handshake-negotiated charset, which is the correct outcome here.
2. Define the baseline as the fresh state, and make the settings-pool reset restore it — e.g. capture each reset variable's post-init_connect session value at connect time and reset to that instead of DEFAULT. Preserves init_connect, at the cost of per-connection bookkeeping per settable variable.
3. Document the drift as a known limitation of combining init_connect with vtgate session settings.

Whichever way, it should be decided deliberately rather than as a side effect of a bug fix.

Reproduction Steps

1. Start mysqld with init_connect = "SET SESSION time_zone='+05:30'" and a vt_app user without SUPER/CONNECTION_ADMIN.
2. Through vtgate, on connection A: select @@session.time_zone → +05:30 (fresh pooled connection).
3. On connection B: set @@session.time_zone = '+01:00' (puts the connection in the settings pool), run a query, then close B so the connection is reset and returned.
4. Repeat step 2 until the read lands on the reset connection → SYSTEM (the global), not +05:30.

Binary Version

main; the code path is present on the supported release branches.

Related

This is one of a set of changes that came out of one investigation into what a stored procedure, or any statement whose tables the planner never sees, can do on a vttablet. They reference each other so the whole picture is reachable from any one of them.

Table ACL — GHSA-w6mx-2f8x-pqf4

• #21053 — fail closed under strict table ACL for statements whose tables the parser discards (DO, CALL, REPAIR, OPTIMIZE, LOAD DATA)
• #21139 — stacked on #21053: check the reads embedded in statements the planner does parse (CREATE TABLE ... AS SELECT, EXPLAIN ANALYZE, SHOW ... WHERE, SET), reject connection settings that carry a subquery, and have a targeted vtgate session store a SET's value rather than its expression
• #21134 — still open: a stored function invoked inside an expression runs its body outside the table ACL
• #21138 — still open: parse the CREATE TABLE source forms the grammar only partially accepts, which #21139 has to deny outright today
• #21140 — still open: ALTER/REVERT VITESS_MIGRATION derive no permissions, so any authenticated caller can control Online DDL migrations; split out of #21053's review

Stored-procedure session residue

• #21046 — procedure-body session state (SET SESSION, temp tables) persists on pooled connections after CALL
• #21062 — fixes #21046 by discarding the pooled connection after CALL
• #21063 — still open: the same residue on a transaction connection recycled at commit
• #21066 — (this issue) still open: the pooled-connection baseline differs between fresh and settings-reset connections on servers using init_connect