Skip to content

CONCEPT Cited by 1 source

Session-level system setting

Definition

A session-level system setting is a MySQL configuration variable whose value is scoped to a single connection — set once, it persists for every subsequent query on that connection, and does not affect other connections. Canonical examples: unique_checks, time_zone, character_set_client, collation_connection, sql_mode, autocommit, transaction_isolation.

MySQL exposes a three-level variable hierarchy: GLOBAL (server-wide, applies to new connections), SESSION (current connection), and QUERY (per-query via the SET_VAR optimizer hint, MySQL 8.0+). Session-level settings are set with SET @@session.var = value, SET SESSION var = value, or just SET var = value (session is the default scope for most variables).

Why it matters to connection pooling

Session settings are per-connection state. A pooled connection that has been modified by one caller cannot be safely lent to another caller without the settings going with it — this is the tainted-connection problem. The pool must either restore the setting on return, close the connection, or reserve the connection for the original caller's session.

ORMs (Rails ActiveRecord, Django ORM, Hibernate, Sequelize) typically issue a batch of SET statements when opening a connection, establishing the settings profile they expect. Different ORMs choose different settings: Rails commonly sets sql_mode = STRICT_ALL_TABLES, Hibernate may touch autocommit and transaction_isolation, Django sets sql_mode = 'STRICT_TRANS_TABLES', etc.

"when multiple applications with different ORMs are running on a single Vitess cluster. Each ORM may set a different value for the same setting, making the MySQL default settings ineffective, resulting in a high number of reserved connections being used." — Harshit Gangal, sources/2026-04-21-planetscale-connection-pooling-in-vitess

The structural problem is that aligning MySQL's GLOBAL defaults to match one ORM's expectations doesn't help when different ORMs want conflicting values on the same cluster. Per-ORM alignment requires per-session overrides, which taint connections, which — without a settings-aware pool — forces reservation.

Common settings ORMs touch

Variable Effect SET_VAR-eligible?
sql_mode Strictness of data validation (strict vs relaxed inserts) Yes
autocommit Whether every statement auto-commits No
transaction_isolation READ COMMITTED / REPEATABLE READ / SERIALIZABLE Yes (partial)
time_zone Timestamp interpretation Yes
character_set_client / character_set_results / collation_connection Charset + collation for the connection No
unique_checks Skip unique-index checks (bulk-load optimisation) Yes
foreign_key_checks Skip FK checks (bulk-load optimisation) Yes
sql_safe_updates Require WHERE on UPDATE/DELETE No
max_execution_time Query timeout (ms) Yes

MySQL publishes the full SET_VAR-eligible list in Server System Variables; eligibility is a per-variable property marked in the reference.

Relationship to other connection-state taint sources

SET is the most common but not the only taint source. Others include:

  • Open transactions (BEGIN without matching COMMIT/ROLLBACK).
  • Temporary tables (CREATE TEMPORARY TABLE ... persists for the session).
  • Prepared statements (PREPARE stmt FROM ...).
  • User-defined variables (SET @x = 1 — note @ prefix, different from @@).
  • Session-scoped locks (GET_LOCK, advisory locks).

A connection pool that handles SET-driven taint but ignores temp tables or prepared statements has an incomplete safety story. Vitess's vttablet reserved-connection documentation enumerates the full taint surface it tracks.

Relationship to GLOBAL vs SESSION

Changing GLOBAL sql_mode affects only new connections opened after the change, not existing pooled connections. This is why restarting applications is required after some MySQL config changes — existing connections in the pool still carry the old session settings, inherited at connection-open time from the previous global value. Pool recycling disciplines (max connection age, periodic bounce) exist partly to propagate GLOBAL changes through the pool without requiring full server restart.

Seen in

  • sources/2026-04-21-planetscale-connection-pooling-in-vitess — canonical first wiki framing of session-level system settings as the dominant source of connection taint in production Vitess workloads and as the specific problem the settings pool solves. The post's worked example: "let's consider an application sending a query such as set unique_checks = 0. Vitess will then send a query select 0 from dual where @@unique_checks != 0 to MySQL. If the query returns a row, it means that the connection setting is being modified, the session will be marked to use a reserved connection, and the new setting will be applied to the connection. Otherwise, a reserved connection is not required and the SET statement can be ignored."

  • sources/2026-04-21-planetscale-character-sets-and-collations-in-mysql — related wiki instance of session-level setting mechanics at the charset + collation altitude; explicitly covers the four-level inheritance hierarchy (column > table > database > server) and shows how the same COLLATE cast operator flips case-sensitivity at query-time without touching the connection's session state.

Last updated · 347 distilled / 1,201 read