System Variables, Status Variables, Dynamic Configuration, and SET PERSIST

Learning outcomes

ServiceHub's team wants to change a timeout “temporarily,” then make the same value survive restart. Someone checks a status counter, mistakes it for a setting, and tries SET GLOBAL Threads_connected = 20. Another person uses SET GLOBAL successfully and assumes the value is now permanent. Both errors come from mixing two different categories: configuration variables and measurements.

System variables configure server or session behavior. Status variables report observed server/session activity. This lesson makes that distinction concrete and then introduces the supported persistence path for eligible global variables.

Configuration versus measurement

A system variable is a configurable value the server uses to control behavior. Examples include max_connections, wait_timeout, and sql_mode. A status variable is a measurement the server reports, such as Threads_connected or Questions. Status values can change because workload changes; they are not normally assigned with SET.

SHOW GLOBAL VARIABLES LIKE 'max_connections';SHOW GLOBAL STATUS LIKE 'Threads_connected';SELECT @@GLOBAL.max_connections AS configured_limit;SELECT VARIABLE_VALUE AS current_connectionsFROM performance_schema.global_statusWHERE VARIABLE_NAME = 'Threads_connected';

GLOBAL and SESSION are different scopes

Some system variables exist only globally, some only per session, and many have both scopes. A global value usually controls the server-wide policy and becomes the default from which new sessions initialize their session value. An existing session can continue with an older session value after the global default changes.

SELECT @@GLOBAL.max_execution_time AS global_before,       @@SESSION.max_execution_time AS session_before;SET SESSION max_execution_time = 2500;SELECT @@GLOBAL.max_execution_time AS global_after,       @@SESSION.max_execution_time AS session_after;-- This session changed; other existing sessions did not.

max_execution_time is useful for a lab because it limits eligible read-only SELECT execution time in milliseconds without changing storage. The exact applicability of the variable is documented and should be rechecked if the course baseline changes.

When a session-only variable is sufficient, prefer that narrow scope. It minimizes blast radius. Global changes affect new sessions and potentially server behavior beyond one application.

Dynamic versus restart-required settings

A dynamic system variable can be changed while the server is running. A non-dynamic or read-only-at-runtime variable requires startup configuration. Scope and dynamism are separate properties: a variable can be global and dynamic, global and startup-only, or global/session and dynamic.

Do not guess. The reference manual lists scope and dynamic status for each variable, and Performance Schema exposes metadata. The following query is useful for inspection:

SELECT VARIABLE_NAME,       VARIABLE_SOURCE,       VARIABLE_PATH,       MIN_VALUE,       MAX_VALUE,       SET_TIME,       SET_USER,       SET_HOSTFROM performance_schema.variables_infoWHERE VARIABLE_NAME IN ('max_execution_time','max_connections','port');

Attempting to set a read-only variable produces an error rather than silently changing it. That failure is useful evidence: it tells you this setting belongs to startup configuration or another documented mechanism.

SET GLOBAL version = 'not-a-version';-- Expected class of result:-- ERROR: Variable 'version' is a read only variable

SET GLOBAL changes runtime, not durable policy

SET GLOBAL changes the global runtime value for a dynamic global variable. Unless another mechanism stores that choice, a restart reconstructs configuration from startup sources and the runtime-only change can disappear.

SELECT @@GLOBAL.max_execution_time AS before_value;SET GLOBAL max_execution_time = 5000;SELECT @@GLOBAL.max_execution_time AS runtime_value;-- Open a new connection and inspect @@SESSION.max_execution_time.-- Record the original value so you can restore it.

SET PERSIST and SET PERSIST_ONLY

SET PERSIST combines two actions for eligible global variables: it changes the global runtime value and writes a durable setting into the server-managed mysqld-auto.cnf. SET PERSIST_ONLY writes the durable value without changing the current runtime value, which is useful for variables intended to take effect at a future startup.

-- 1) Record the current values and source.SELECT @@GLOBAL.max_execution_time;SELECT VARIABLE_NAME, VARIABLE_SOURCE, VARIABLE_PATHFROM performance_schema.variables_infoWHERE VARIABLE_NAME='max_execution_time';-- 2) Persist a lab value.SET PERSIST max_execution_time = 5000;-- 3) Verify runtime and durable state.SELECT @@GLOBAL.max_execution_time;SELECT * FROM performance_schema.persisted_variablesWHERE VARIABLE_NAME='max_execution_time';-- 4) Remove the persisted entry when the lab is complete.RESET PERSIST max_execution_time;-- 5) Restore the original runtime value explicitly if needed.

Persisting a variable requires privilege and the variable must be persistible. Session-only variables cannot be persisted. Some sensitive or special variables are nonpersistible or persist-restricted. Treat an error as a signal to read that variable's documentation rather than trying to force the file.

What happens to existing sessions?

Suppose max_execution_time has both global and session values. If you change the global value, already-open sessions do not necessarily adopt it. New sessions normally initialize from the new global value. This is why configuration rollouts that affect session defaults can appear “partially applied” until pools recycle connections.

T0: global max_execution_time = 0T1: Session A connects -> session value becomes 0T2: administrator SET PERSIST max_execution_time = 5000T3: Session A may still have session value 0T4: Session B connects -> session value initializes from new global 5000T5: pool gradually recycles -> more sessions inherit new default

Application operators need to coordinate server changes with connection pools. A documented server change may require pool recycle or explicit session initialization to make behavior deterministic.

Failure lab: wrong scope and wrong persistence assumption

Run these mistakes intentionally on the disposable lab and read the error or resulting state.

-- Mistake 1: use SESSION for a global-only variable.SET SESSION max_connections = 200;-- Expected: error explaining max_connections is GLOBAL.-- Mistake 2: assume runtime means persistent.SET GLOBAL max_execution_time = 3000;SELECT @@GLOBAL.max_execution_time;-- The value changed now, but no persisted_variables row was created by SET GLOBAL.-- Mistake 3: try to persist a nonpersistible variable.SET PERSIST port = 3307;-- Expected: error; port is not an arbitrary runtime/persistable knob.

The correct repair depends on intent. Session behavior belongs in session initialization where possible. Runtime-only emergency changes should be documented and later reconciled with managed configuration. Durable changes should use a supported persistent mechanism and be reviewed like code.

Hands-on lab: change, verify, revert, and prove the revert

1. Record @@GLOBAL.max_execution_time and the current variables_info row.
2. Run SET PERSIST max_execution_time = 5000.
3. Verify the global value, the persisted row, and the variable source.
4. Open a fresh client session and compare its session value with a client that was already connected.
5. Run RESET PERSIST max_execution_time to remove the durable entry.
6. Restore the runtime global value you recorded in step 1.
7. If you control the disposable server and can restart safely, restart once and prove that the removed persisted setting no longer reappears. Otherwise document why restart verification was skipped.

Production judgment and references

Every production setting change should answer four questions: what scope changes, when does it take effect, does it survive restart, and how do we roll it back? Record the before value and provenance, change one thing at a time, verify from the server, observe workload effects, and reconcile runtime state with configuration management.

Avoid large batches of unrelated SET PERSIST changes. Persisted settings are powerful because they bypass the need to edit option files, but that also means they can become invisible drift if your deployment system does not inventory them.

Authoritative references

• MySQL 8.4 Reference Manual — SET Syntax for Variable Assignment
• MySQL 8.4 Reference Manual — Persisted System Variables
• MySQL 8.4 Reference Manual — Nonpersistible and Persist-Restricted Variables
• MySQL 8.4 Reference Manual — Performance Schema System Variable Tables
• MySQL 8.4 Reference Manual — Server Status Variables

Next: configuration is not only numbers. Character sets, collations, time zones, and locale defaults can silently change how text and temporal values are interpreted.