MariaDB Tutorial / Inspect Server Settings, SQL Modes, Time Zones, and Logs

Inspect MariaDB configuration at three levels: option files, global runtime values, and the current connection’s session values. SQL modes and time zones can differ between connections, while error logs and slow-query logs serve different purposes and have different recording conditions.

Separate file, global, and session scope

Changing a GLOBAL default does not necessarily update existing sessions. A session-only change also does not establish what will survive a restart. Begin with the read-only queries below. The executable examples were checked on MariaDB 12.3.3; the file’s slow-log option names target MariaDB 10.11 and later. Use Store Dates and Times with DATE, DATETIME, and TIMESTAMP for time-zone-sensitive types, Schedule Database Tasks and Verify Event Results for schedule interpretation, and Read EXPLAIN and Compare Plans Before and After an Index to distinguish plan estimates from execution diagnosis.

SELECT VERSION(), @@GLOBAL.sql_mode, @@SESSION.sql_mode,
       @@GLOBAL.time_zone, @@SESSION.time_zone, @@system_time_zone;
SHOW GLOBAL VARIABLES WHERE Variable_name IN
  ('datadir', 'log_error', 'log_output', 'log_slow_query',
   'log_slow_query_file', 'log_slow_query_time');

Privileges or managed-service policies may limit the information available. Inspect the actual server version and variable names rather than importing assumptions from another installation. The system variable reference describes scope and whether each setting can change dynamically.

Find the files the service actually reads

Unix-like systems commonly use my.cnf, while Windows can use my.ini or my.cnf. Exact locations depend on installation and service arguments. This command identifies the binary’s default paths and option groups; use its full executable path if it is not on PATH.

mariadbd --help --verbose

Read the default-option-file and group sections, then check whether the service supplies a separate --defaults-file. Later files can override an earlier value. Output can contain sensitive option values, so do not share it wholesale. Consult the official option-file rules.

On a dedicated lab server, the following options can demonstrate persistent configuration inside the actual file’s [mariadbd] group. Back up that file and record the lines changed first. Option-file names do not use the command-line -- prefix.

[mariadbd]
sql-mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
default-time-zone=+00:00
log-output=FILE
log-slow-query=ON
log-slow-query-time=1
log-slow-query-file=appops-slow.log

The slow-log filename is relative; query the actual data directory and generated path afterward. Use the installed Windows service manager or the normal Linux service procedure to restart this isolated server. If startup fails, inspect the error log and restore the backed-up file. A shared production restart is not part of this exercise.

Change one SQL mode in the current session

Create this disposable table in appops_lab. If it already exists, inspect its definition and prior exercise state before repeating creation.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE mode_items (
  item_id INT PRIMARY KEY, category_name VARCHAR(20), price INT
) ENGINE=InnoDB;
INSERT INTO mode_items VALUES (1, 'Books', 10), (2, 'Books', 20), (3, 'Tools', 30);

SET @saved_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = CONCAT_WS(',', NULLIF(@saved_mode, ''), 'ONLY_FULL_GROUP_BY');
SELECT category_name, SUM(price) AS total FROM mode_items GROUP BY category_name;

The valid aggregate returns Books 30 and Tools 30. Execute this next statement separately: ONLY_FULL_GROUP_BY rejects the unaggregated price that is not determined by the grouping columns. After that expected error, explicitly run the restoration statements.

SELECT category_name, price FROM mode_items GROUP BY category_name;

SET SESSION sql_mode = @saved_mode;
SELECT @@SESSION.sql_mode;

Instead of emptying the entire mode list to permit an arbitrary price per group, decide what result is intended and fix the aggregate or grouping. Stored programs also retain relevant creation-time SQL modes, so an option-file value alone does not explain all routine behavior. See the SQL_MODE reference.

Compare TIMESTAMP and DATETIME across time zones

SET @saved_time_zone = @@SESSION.time_zone;
SET SESSION time_zone = '+09:00';
CREATE TABLE zone_example (id INT PRIMARY KEY, ts TIMESTAMP, dt DATETIME);
INSERT INTO zone_example VALUES (1, '2026-01-01 09:00:00', '2026-01-01 09:00:00');
SELECT ts, dt FROM zone_example;
SET SESSION time_zone = '+00:00';
SELECT ts, dt FROM zone_example;
SET SESSION time_zone = @saved_time_zone;

The first SELECT shows 2026-01-01 09:00:00 for both columns. After switching to +00:00, TIMESTAMP displays 2026-01-01 00:00:00 while DATETIME stays at 09:00:00. This separates TIMESTAMP’s session-zone conversion from a DATETIME value’s date-and-time fields. The final statement restores the original session zone.

Named zones such as Asia/Seoul depend on the server’s time-zone tables. If a named zone is unknown, inspect their installation and update state. A fixed numeric offset and a named geographical zone need not have equivalent daylight-saving behavior. Establish a consistent application policy for storage and display as well. The time_zone and system_time_zone descriptions explain their respective scopes.

Read each log for the question it answers

An enabled slow-query log does not record every query a user considers slow. Check its threshold and filters, and distinguish a new global default from the current session. Identify FILE versus TABLE output. SQL may contain personal data, so manage log access, rotation, and retention. The slow-query log overview covers recording conditions and renamed variables.

In a newly connected isolated lab session with a one-second threshold and permissive filters, SELECT SLEEP(1.2); can help verify the logging destination. This deliberately waits instead of slowing a business workload. If no record appears, inspect enable state, destination, and filters before repeatedly increasing the wait.

Restore settings and confirm the outcome

1. Record both session and global values and identify the connections or jobs a change can affect.
2. Change one session condition and observe its effect.
3. Only when persistence is needed, back up the option file and edit the relevant option.
4. Follow the administrator’s restart process, then query effective values through a new connection.
5. If necessary, restore the recorded values or backed-up file and verify the error log and expected behavior.

To remove only the disposable tables, use DROP TABLE appops_lab.mode_items, appops_lab.zone_example;. Restoring a file, restoring session settings, and deleting lab tables are separate actions; dropping a table does not reset server configuration.