Slow Query Log, Error Log, General Log, and Structured Diagnostic Workflows

Learning outcomes

Performance Schema answers detailed questions while the server is running, but incidents often need a timeline that crosses sessions and survives longer than a small event ring. MySQL maintains several logs with different purposes. The slow query log records statements meeting slow-log criteria; the general query log records connections/disconnections and statements as the server receives them; the error log records server diagnostics such as startup/runtime warnings and errors. Treating these as interchangeable creates both blind spots and unnecessary overhead.

Three evidence streams, three questions

The binary log from Chapters 13–14 is different again: it records data-changing events for replication/recovery. Do not use it as a substitute for a query activity or error log.

Inventory current destinations and preserve them

SELECT @@global.log_output,       @@global.slow_query_log,       @@global.slow_query_log_file,       @@global.long_query_time,       @@global.general_log,       @@global.general_log_file,       @@global.log_error,       @@global.log_error_services,       @@global.log_error_verbosity,       @@global.log_timestamps;-- Also record connection identity/time context for correlation.SELECT CONNECTION_ID() AS connection_id,       NOW(6) AS session_time,       @@session.time_zone,       @@global.log_timestamps;

For a cross-platform free lab, we temporarily send the slow/general logs to MySQL's log tables using log_output='TABLE'. This avoids hard-coding Unix or Windows filesystem paths. Production systems often prefer FILE output plus operating-system rotation/collection, because table logging has its own operational costs.

Slow-log drill: create exactly one obvious candidate

Save the original global values outside the server (notes or variables in your terminal). Global changes affect other sessions, so this exercise belongs only on the disposable lab instance.

SET @old_log_output = @@global.log_output;SET @old_slow_log  = @@global.slow_query_log;SET @old_long_time = @@global.long_query_time;SET GLOBAL log_output='TABLE';SET GLOBAL slow_query_log=ON;SET GLOBAL long_query_time=0.10;-- long_query_time is also session-scoped; make the current session explicit.SET SESSION long_query_time=0.10;SELECT NOW(6) AS before_slow, CONNECTION_ID() AS connection_id;SELECT SLEEP(0.20) AS intentionally_slow;SELECT NOW(6) AS after_slow;

The exact measured duration depends on scheduling and machine load, but SLEEP(0.20) should exceed the local 0.10-second threshold under normal conditions. Query the table rather than assuming logging succeeded.

SELECT start_time, query_time, lock_time, rows_sent, rows_examined,       db, sql_text, thread_idFROM mysql.slow_logORDER BY start_time DESCLIMIT 10;

General-log drill: useful, noisy, temporary

SET @old_general = @@global.general_log;SET GLOBAL general_log=ON;USE servicehub_observe_lab;SELECT 'GENERAL_LOG_MARKER' AS marker, CONNECTION_ID() AS connection_id;SELECT COUNT(*) FROM work_orders WHERE region='north';SET GLOBAL general_log=OFF;SELECT event_time, user_host, thread_id, server_id, command_type, argumentFROM mysql.general_logWHERE argument LIKE '%GENERAL_LOG_MARKER%'   OR argument LIKE '%work_orders%region%'ORDER BY event_time DESCLIMIT 20;

General-log order reflects statements received by the server and can differ from completion order. It is invaluable for questions like “what did this client send?” but is rarely appropriate as an always-on high-volume production trace. MySQL rewrites passwords in certain logged statements by default; disabling that protection with raw logging is a security risk.

Harmless SQL error: correlate it correctly

A common misconception is that every SQL error returned to a client must appear in the server error log. The error log is primarily about server diagnostics, not a complete per-statement application error stream. Create an ordinary unknown-column error and observe it in the client and statement instrumentation. Then inspect the error log. If no matching server event exists, that is expected evidence—not a failure of your query.

USE servicehub_observe_lab;SELECT definitely_missing_columnFROM work_ordersLIMIT 1;-- Expected client error: ER_BAD_FIELD_ERROR / unknown column (typically errno 1054).

SELECT THREAD_ID, EVENT_ID, CURRENT_SCHEMA,       MYSQL_ERRNO, RETURNED_SQLSTATE,       LEFT(MESSAGE_TEXT,160) AS message_text,       LEFT(SQL_TEXT,160) AS sql_textFROM performance_schema.events_statements_history_longWHERE MYSQL_ERRNO <> 0ORDER BY EVENT_ID DESCLIMIT 10;

If the history-long consumer is disabled, use the bounded enablement pattern from Lesson 1 before generating the error. Then inspect the SQL-accessible error log ring buffer if your configured log sink supports it.

SELECT LOGGED, THREAD_ID, PRIO, ERROR_CODE, SUBSYSTEM, DATAFROM performance_schema.error_logORDER BY LOGGED DESCLIMIT 20;SHOW GLOBAL STATUS LIKE 'Error_log_buffered%';SHOW GLOBAL STATUS LIKE 'Error_log_latest_write';

The performance_schema.error_log table is a fixed-size in-memory ring populated only by compatible error-log sink configurations. It is convenient for SQL access but does not replace durable log retention.

Wrong approach: leave diagnostic logging enabled and forget the experiment

-- Replace these assignments with the exact values you recorded.-- Examples only:SET GLOBAL general_log=OFF;SET GLOBAL slow_query_log=OFF;SET GLOBAL long_query_time=10;SET GLOBAL log_output='FILE';SELECT @@global.log_output,       @@global.slow_query_log,       @@global.long_query_time,       @@global.general_log;

Rotation and structured incident workflow

FILE logs need rotation and retention appropriate to the operating system and collection stack. MySQL can close/reopen logs with FLUSH LOGS, but file ownership and external rotation still matter. TABLE logging is convenient for a lab, not a reason to store unlimited diagnostics inside the database you are diagnosing.

Production judgment

Logs are powerful because they add temporal context and can be shipped to durable storage, but they are not free and they do not all capture the same event. Default to Performance Schema/sys for routine workload diagnosis, use the slow log for appropriately scoped query-latency evidence, use the general log for short targeted “what was sent?” investigations, and treat the error log as core server-operational evidence. Design privacy, retention, access control, and rotation alongside observability—not after an incident exposes sensitive query text.

Next we connect SQL/server evidence to resource economics. A single counter value is rarely useful; rates, saturation, and correlated host behavior are what make metrics diagnostic.

Authoritative references

• MySQL Server Logs
• General Query Log
• Slow Query Log
• Error Log
• Performance Schema error_log table
• Server Log Maintenance