Bug Report: stored-procedure body session state (SET SESSION, temp tables) persists on pooled connections after CALL

Overview of the Issue

Session state that a stored-procedure body leaves behind — SET SESSION variables and CREATE TEMPORARY TABLE — persists on a pooled backend connection after the CALL returns, and is then served to whatever query next borrows that connection.

CALL is planned as PlanCallProc and runs on a connection from the shared OLTP read pool, with no reserved connection. execCallProc recycles that connection after checking only two things: whether more result sets remain, and whether the procedure left a transaction open. It does not check, or reset, session state. The connection pool itself resets only a tracked Setting (the reset SQL Vitess generates for a client's top-level SET); it has no generic session reset. A SET SESSION or a temporary table created inside a procedure body is never a tracked Setting, so nothing ever cleans it up.

The mechanism, on main:

• NeedsReservedConn is set only for statement shapes the parser can see: a lock function in a SELECT's expression list, a top-level SET, CREATE TEMPORARY DDL (stmt.IsTemporary()), FLUSH ... WITH READ LOCK, and the streaming lock-function SELECT. CALL is not among them, so PlanCallProc runs with NeedsReservedConn == false (go/vt/vttablet/tabletserver/planbuilder/builder.go, plan.go).
• execCallProc (go/vt/vttablet/tabletserver/query_executor.go) gets a connection from qe.conns (the OLTP read pool), runs the procedure text, and defer conn.Recycle(). Its only pre-recycle checks are IsMoreResultsExists() and IsInTransaction().
• The pool (go/pools/smartconnpool) resets a connection only when it carries a Setting, by running that setting's reset query. There is no COM_RESET_CONNECTION of the backend connection (that opcode is implemented only server-side, for Vitess's own clients).

The asymmetry is the crux: a top-level CREATE TEMPORARY TABLE or SET is classified and moved onto a reserved connection, where it is isolated and cleaned up. The identical statements inside a procedure body are invisible to the parser and escape that classification entirely.

Impact

• Correctness (affects even a single-application deployment). A procedure that runs, say, SET SESSION transaction_isolation = 'READ-UNCOMMITTED' leaves that isolation level on the pooled connection. Later, unrelated queries routed to that same connection inherit it and can perform dirty reads — reading a row another session wrote but has not committed (and may roll back). The same applies to time_zone, sql_mode, and other session variables, and to leftover temporary tables. Nothing reports the change.
• Cross-connection residue. In a deployment that fronts one shared backend account with several authenticated vtgate identities, the residual settings and the temporary-table contents cross between those identities. This is not an authorization bypass: all frontend identities map to the single vt_app backend account, MySQL applies the same authorization to each, and vtgate is not a documented trust boundary between them. That is why this is filed as a normal bug rather than a security advisory.

Reproduction Steps

1. Bring up an unsharded keyspace (vtgate refuses CALL on sharded keyspaces), with a stock OLTP read pool.
2. Create a stored procedure whose body dirties the session, e.g.:
   sql复制

   CREATE PROCEDURE dirty_session()
   BEGIN
     CREATE TEMPORARY TABLE leaked (id int);
     SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
   END;

3. CALL dirty_session(); and let the connection return to the pool.
4. Issue an ordinary read that lands on that pooled connection. Observe that the session is at READ-UNCOMMITTED (so it can read another in-flight session's uncommitted row) and that the leaked temporary table is still visible — neither was reset when the connection was recycled.

Contrast with running the same two statements at top level, which are routed to a reserved connection and do not leak onto the pool.

Scope

• Confirmed on main; the same code path is present on the supported release branches (release-23.0, release-24.0).
• This is the buffered Execute CALL path. The streaming CALL path was hardened for the multi-result-set and transaction-leak checks in #20372, but that change did not address session residue, so the streaming path very likely shares this gap.
• Not covered here: sharded keyspaces (vtgate refuses CALL), stored functions, and triggers.

Possible directions (for discussion, not a decision)

• Taint the pooled connection after a CALL so it is closed rather than reused. Simplest and fully safe, at the cost of one connection churned per CALL.
• Detect the change precisely: Vitess negotiates CLIENT_SESSION_TRACK but the OK-packet parser keeps only GTID entries; surfacing the session-state-changed signal would let vttablet taint only when a body actually dirtied the session. Needs verification that MySQL reports these changes under the negotiated trackers.
• Explicitly reset the backend connection (COM_RESET_CONNECTION / mysql_reset_connection, plus dropping session temp tables) before recycle.

Binary Version

Reproduced against main; code path present on release-23.0 and release-24.0. Originally reported against v24.0.3.

Operating System and Environment details

Not environment-specific (query-serving / connection-pool logic).

Credit

Reported privately by @xclow3n through a GitHub security advisory, with a self-contained reproducer. After review it crosses no security boundary Vitess documents — all frontend identities map to one backend account — so it is being handled in the open as a normal bug. Thanks to the reporter for the careful, well-scoped write-up.

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 — (this issue) 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 — still open: the pooled-connection baseline differs between fresh and settings-reset connections on servers using init_connect