Not looking for external contributions on this one. We're already working in this area and intend to fix it ourselves. Please don't open a PR against this issue — it's filed to track the problem and record the analysis, not as an invitation. Comments and additional repro cases are very welcome.
Overview of the Issue
When a SET of a system variable runs while the session holds reserved shard connections (after a DDL, a lock, or with SET_VAR unavailable), vtgate stores the new value in the session before it sends the set statement to those connections: SysVarReservedConn.Execute stores through checkAndUpdateSysVar and then updates the shard sessions, and SysVarSQLMode.Execute stores the canonical value and then fans it out. If the fan-out fails on some shards, the client gets an error, but the session already reports and parses under the new value while the surviving connections keep the old one, or a mix.
A client is not required to retry a failed SET. For sql_mode, #20880 makes a retry converge the shard sessions (the statement is re-sent even when the value is unchanged), but nothing converges them without one. For the other reserved-connection variables, checkAndUpdateSysVar probes a single shard for whether the value changed, so a retry that lands on a shard that succeeded is treated as a no-op and the stale shard is never updated.
The exposure is limited to truly pinned connections (ReservedId != 0): settings-pool connections re-derive their pre-queries from the session on every query and self-heal.
MySQL applies none of a failing SET; #20893 covers the multi-assignment side of that. This issue is the ordering between the session and the shard sessions for a single assignment.
Raised in review of #20880 (#20880 (comment) and #20880 (comment)).
Reproduction Steps
- Open a session, run a DDL or
select get_lock('l', 1) so the session holds reserved connections on every shard.
- Make one shard fail the next statement (stop its primary, or use the sandbox in a unit test).
set sql_mode = 'STRICT_TRANS_TABLES' (or any reserved-connection variable): the statement errors.select @@sql_mode reports the new value; the reserved connections on the shards that answered step 3 run under it, the failed shard's connection still runs under the previous value once it is back.
Fix direction
Either apply the shard updates first and store the session value only when all of them succeed, or on partial failure mark the failed shard sessions so their next use re-applies the session's settings (or drop their reservation). Add tests for single-shard, all-shard and partial failure followed by a retry, for sql_mode and for a SysVarReservedConn variable.
Binary Version
main (after #20880)
Operating System and Environment details
n/a
Log Fragments
n/a
Overview of the Issue
When a
SETof a system variable runs while the session holds reserved shard connections (after a DDL, a lock, or withSET_VARunavailable), vtgate stores the new value in the session before it sends thesetstatement to those connections:SysVarReservedConn.Executestores throughcheckAndUpdateSysVarand then updates the shard sessions, andSysVarSQLMode.Executestores the canonical value and then fans it out. If the fan-out fails on some shards, the client gets an error, but the session already reports and parses under the new value while the surviving connections keep the old one, or a mix.A client is not required to retry a failed
SET. Forsql_mode, #20880 makes a retry converge the shard sessions (the statement is re-sent even when the value is unchanged), but nothing converges them without one. For the other reserved-connection variables,checkAndUpdateSysVarprobes a single shard for whether the value changed, so a retry that lands on a shard that succeeded is treated as a no-op and the stale shard is never updated.The exposure is limited to truly pinned connections (
ReservedId != 0): settings-pool connections re-derive their pre-queries from the session on every query and self-heal.MySQL applies none of a failing
SET; #20893 covers the multi-assignment side of that. This issue is the ordering between the session and the shard sessions for a single assignment.Raised in review of #20880 (#20880 (comment) and #20880 (comment)).
Reproduction Steps
select get_lock('l', 1)so the session holds reserved connections on every shard.set sql_mode = 'STRICT_TRANS_TABLES'(or any reserved-connection variable): the statement errors.select @@sql_modereports the new value; the reserved connections on the shards that answered step 3 run under it, the failed shard's connection still runs under the previous value once it is back.Fix direction
Either apply the shard updates first and store the session value only when all of them succeed, or on partial failure mark the failed shard sessions so their next use re-applies the session's settings (or drop their reservation). Add tests for single-shard, all-shard and partial failure followed by a retry, for
sql_modeand for aSysVarReservedConnvariable.Binary Version
main (after #20880)
Operating System and Environment details
n/a
Log Fragments
n/a