System/Status Variables, Dynamic Settings, Persisted Configuration Patterns, and Validation

Learning outcomes

A ServiceHub operator runs SET GLOBAL sql_mode=..., verifies the value in the same session, and writes “configuration changed permanently” in the incident log. After a later restart, the old setting returns. In a different case, a global change succeeds but the application’s existing connections continue using their previous session value. These outcomes are not bugs; they follow MariaDB’s scope and persistence rules.

MariaDB exposes system variables that configure server/session behavior and status variables that report counters or state. Some system variables are global only; some have both GLOBAL and SESSION copies; some are dynamic; others are startup-only. Runtime SET and persistent option-file configuration are separate mechanisms. This lesson teaches you to reason about all four dimensions: meaning, scope, mutability, and persistence.

1. System variables configure; status variables observe

A system variable changes or describes server behavior: connection limits, SQL mode, character sets, timeouts, optimizer switches, storage-engine settings, and many others. A status variable is normally read-only operational evidence: number of connections, questions executed, temporary tables created, bytes sent, buffer-pool activity, or thread state. Confusing the two leads to attempts to “set” counters or to use a configuration value as proof of workload behavior.

SHOW GLOBAL VARIABLES LIKE 'max_connections';SHOW GLOBAL VARIABLES LIKE 'sql_mode';SHOW SESSION VARIABLES LIKE 'sql_mode';SHOW GLOBAL STATUS LIKE 'Connections';SHOW GLOBAL STATUS LIKE 'Threads_connected';SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';SELECT VARIABLE_NAME, VARIABLE_VALUEFROM information_schema.GLOBAL_STATUSWHERE VARIABLE_NAME IN ('CONNECTIONS','THREADS_CONNECTED');

Status values require context. Many are cumulative since server start or reset, while others represent gauges. A counter increasing is not automatically a problem; you need elapsed time, workload volume, reset time, and a baseline. Observability Chapter 15 will turn these primitives into rates and incident signals.

2. GLOBAL and SESSION are different objects

For a variable that supports both scopes, @@GLOBAL.name and @@SESSION.name can differ. A global value usually acts as the template for future sessions. Existing sessions keep their own copy until changed or reconnected. This is why changing a global SQL mode does not retroactively rewrite the semantics of every already-open application connection.

SELECT @@GLOBAL.sql_mode AS global_sql_mode,       @@SESSION.sql_mode AS session_sql_mode,       @@GLOBAL.time_zone AS global_time_zone,       @@SESSION.time_zone AS session_time_zone,       @@GLOBAL.wait_timeout AS global_wait_timeout,       @@SESSION.wait_timeout AS session_wait_timeout;

Some variables are global-only or session-only. Do not assume both forms exist. The official variable reference labels scope and whether a setting is dynamic. In scripts, fail loudly when a required variable is absent rather than silently substituting a similarly named option from MySQL or another MariaDB series.

3. A reversible SESSION experiment: SQL mode

Session scope is ideal for learning because the blast radius is one connection. Capture the current value, add a strict behavior only in the lab session, demonstrate the effect, then restore the original value or reconnect.

SET @old_sql_mode := @@SESSION.sql_mode;SELECT @old_sql_mode;SET SESSION sql_mode = CONCAT_WS(',', @@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY');SELECT @@SESSION.sql_mode;-- Restore exactly what this session had before.SET SESSION sql_mode = @old_sql_mode;SELECT @@SESSION.sql_mode;

Do not construct production SQL modes with ad-hoc string concatenation because duplicates, deprecated modes, and application assumptions deserve deliberate review. The exercise only proves session scope and rollback. Chapter 04 teaches SQL mode as a data-quality/compatibility boundary.

4. A reversible GLOBAL experiment—and why a second session matters

A dynamic global variable can be changed while the server runs, provided the account has sufficient administrative privilege. Use a low-risk disposable setting, capture the old global value, change it, open a fresh session, then restore it. sql_mode is useful because it visibly demonstrates inheritance.

SET @old_global_sql_mode := @@GLOBAL.sql_mode;SELECT @old_global_sql_mode;SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';SELECT @@GLOBAL.sql_mode AS new_global,       @@SESSION.sql_mode AS admin_session_still_has_its_own_copy;

SELECT @@GLOBAL.sql_mode AS global_value,       @@SESSION.sql_mode AS inherited_session_value;

SET GLOBAL sql_mode = @old_global_sql_mode;SELECT @@GLOBAL.sql_mode;

If your client connection is recreated between capture and rollback, a user variable such as @old_global_sql_mode disappears because it is session state. In a real change ticket, record the original value externally before modification. Also remember that a GLOBAL runtime change may still vanish on restart if the option file was not updated.

5. Dynamic versus startup-only settings

The MariaDB documentation marks variables as Dynamic when they can be changed at runtime. Startup-only variables require a controlled restart. Examples include settings that define fundamental process/storage initialization or features that allocate instrumentation at startup. performance_schema, for example, is documented as startup-controlled rather than something you can simply enable with SET GLOBAL after the server is already running.

The wrong workflow is “try SET GLOBAL and see.” The safer workflow is to consult the exact target-version variable reference, inspect current state, determine scope and dynamic status, stage a change in configuration management, validate on a disposable/staging server, then restart only when required. Runtime rejection is useful evidence, but production should not be your variable-discovery tool.

6. Persistence means configuration survives process replacement

A runtime change lives in server memory. Persistence means a future server process receives the intended setting again. For MariaDB’s ordinary server configuration, that normally means placing the option in an appropriate server option group in an option file or supplying it through a managed startup command/environment according to your deployment platform.

# 90-servicehub-baseline.cnf[mariadb]sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION# Before restart, inspect what the program parses.mariadbd --print-defaultsmy_print_defaults --mariadbd

The filename and directory are examples only. Use the option-file discovery from Lesson 1 to choose the real managed location. For containers, persistence may live in a mounted configuration file or declared command arguments; for Windows, it may be a my.ini location and service startup definition. The operational contract is identical: source-controlled desired state plus post-restart verification.

7. Do not confuse sys.sys_config with server variable persistence

MariaDB’s sys schema includes a persistent sys_config table for configuration used by sys-schema helpers. That table is not a generic replacement for server option-file persistence. Writing a row to sys.sys_config does not magically persist max_connections, sql_mode, or innodb_buffer_pool_size as server variables.

SELECT SCHEMA_NAMEFROM information_schema.SCHEMATAWHERE SCHEMA_NAME='sys';SELECT TABLE_NAMEFROM information_schema.TABLESWHERE TABLE_SCHEMA='sys'  AND TABLE_NAME='sys_config';

8. Validation requires a new session and sometimes a restart

A configuration change is not verified merely because a file diff exists. For a session-inherited variable, check the global value and then connect again to verify the new session receives it. For a startup-only variable, restart a disposable/staging instance and query the effective value afterward. For path/listener settings, verify both SQL variables and actual connectivity/logging.

9. Deliberately wrong approach: assume a successful SET is durable

Consider a maintenance window where an administrator runs SET GLOBAL max_connections=250; and leaves. The current server reports 250. Weeks later the host reboots and the value returns to the option-file/default value. The failure was not that SET GLOBAL ignored the command; the failure was treating runtime state as desired persistent state.

Repair the process: capture the original value; make the runtime change only if immediate relief is justified; update the managed option source separately; validate parsed options; schedule a controlled restart when appropriate; query the post-restart value; and record both the runtime and persisted source in change management. If the emergency change is temporary, explicitly do not persist it and add an expiry/review task.

10. Hands-on scope and persistence lab

1. Record global and session values for sql_mode, time_zone, and wait_timeout.
2. Perform the reversible SESSION SQL-mode experiment and restore it.
3. With a disposable administrative account/server, capture global SQL mode, set a known low-risk global value, and prove the current session is not automatically rewritten.
4. Open a new session and observe inheritance.
5. Restore the original global value.
6. On the disposable alternate instance from Lesson 1, add the intended SQL mode to its explicit option file.
7. Stop/start that alternate instance and verify @@GLOBAL.sql_mode after restart.
8. Inspect whether performance_schema is enabled and identify it as startup-controlled.

Verification checklist

• No global value was changed without first recording its original value.
• Session and global SQL modes were observed separately.
• A new connection was used to prove global-to-session inheritance.
• The runtime change was restored.
• The persisted setting survived a restart of only the disposable instance.
• You did not use MySQL SET PERSIST or edit undocumented data-directory configuration files.

11. Summary and bridge

You now have a configuration decision model: identify the variable → distinguish configuration from status → check scope → check Dynamic status → make a reversible change → verify in the right connection → persist through the managed startup source only when intended → restart and verify when required. That discipline prevents both invisible session mismatches and “it reverted after reboot” incidents.

The next lesson expands the observability surface. You will use SHOW, INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, the mysql system database, and sys to answer concrete questions about tables, indexes, sessions, privileges, and server activity—while learning why metadata columns and internal tables are not automatically stable application APIs.