MySQL – Server and Session Functions

A handful of functions answer the question "where am I and what am I connected to". They are worth knowing because the first step in debugging almost anything is confirming you are talking to the server you think you are.

Where am I

SELECT VERSION()        AS server_version,
       DATABASE()       AS current_database,
       USER()           AS connected_as,
       CURRENT_USER()   AS authenticated_as,
       CONNECTION_ID()  AS thread_id;

Output varies by server and session, which is the point of asking. Two of those are not the same thing, and the difference matters:

• USER() is who you said you were and where from — root@172.17.0.1.
• CURRENT_USER() is the account row MySQL actually matched — possibly root@%, or even ''@'%' if you landed on an anonymous account.

When permissions behave unexpectedly, compare the two. "I granted privileges to app@localhost and it still cannot read the table" is usually the connection having matched app@% instead. See users and privileges.

DATABASE() returns NULL when no database is selected, which explains "No database selected". CONNECTION_ID() gives your thread id — the number you pass to KILL when a query has to be stopped.

Server variables

SELECT @@version_comment, @@hostname, @@port, @@datadir;
SELECT @@session.sql_mode, @@global.max_connections;

@@name reads a system variable. Most exist at two levels: @@global is the server-wide value, @@session is yours. A bare @@name gives the session value where one exists.

That distinction is the source of a common confusion. Setting a variable for your session changes nothing for anyone else:

SET SESSION sql_safe_updates = 1;        -- just me, until I disconnect
SET GLOBAL  max_connections   = 300;     -- everyone, until the server restarts

Neither survives a restart. A permanent change goes in the configuration file, or — in MySQL 8 — through SET PERSIST, which writes it to mysqld-auto.cnf as well as applying it:

SET PERSIST max_connections = 300;

Note also that SET GLOBAL usually affects only new connections — existing sessions keep the value they started with, which is why a setting change sometimes appears to do nothing until you reconnect.

SHOW VARIABLES and SHOW STATUS

SHOW VARIABLES LIKE 'innodb_buffer_pool%';   -- configuration: what it is set to
SHOW STATUS    LIKE 'Threads_%';             -- counters: what has happened
SHOW GLOBAL STATUS LIKE 'Questions';

The pair is worth keeping straight. Variables are settings; status is measurement. Status counters are mostly cumulative since the server started, so a single reading tells you little — take two, subtract, and divide by the elapsed time. A rate is meaningful; a total since an unknown start time is not.

A few worth knowing by name: Threads_connected and Threads_running (the second is the one that indicates trouble), Slow_queries, Innodb_row_lock_waits, and Aborted_connects.

What is happening right now

SHOW PROCESSLIST;          -- your queries, or everyone's with PROCESS privilege
SHOW FULL PROCESSLIST;     -- without truncating the query text at 100 characters

This is the first thing to run when the database is slow. Sort by Time and look at the top: a query that has been running for 400 seconds is usually your answer. KILL 1234 stops one by its Id — and note KILL QUERY 1234 stops the statement while leaving the connection alive, which is gentler.

SHOW ENGINE INNODB STATUS is the deeper version, and is where the details of the most recent deadlock live. See deadlocks and running queries in production.

Two small ones for testing

SELECT SLEEP(3);              -- block this session for 3 seconds
SELECT BENCHMARK(1000000, MD5('x'));

SLEEP is genuinely useful: it is how you hold a lock open long enough to watch another session wait for it, which is how the deadlock lesson reproduces one on purpose.

BENCHMARK re-runs a scalar expression many times and reports the elapsed time. It measures expression evaluation only — it does not fetch rows, so it cannot tell you anything about a query's real cost. For that, use EXPLAIN ANALYZE.

Sizing things on disk

SELECT TABLE_NAME,
       ROUND(DATA_LENGTH  / 1024 / 1024, 1) AS data_mb,
       ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS index_mb
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'pizza'
ORDER  BY DATA_LENGTH + INDEX_LENGTH DESC
LIMIT  5;

Useful for finding what is actually big, and for noticing when indexes outweigh the data. These figures are estimates that move as InnoDB resamples — treat them as approximate, and see INFORMATION_SCHEMA for the rest of the catalogue.

What to remember

• USER() is what you claimed; CURRENT_USER() is what MySQL matched. Compare them when permissions surprise you.
• @@global and @@session are different values; SET GLOBAL affects new connections.
• Nothing survives a restart except the config file or SET PERSIST.
• Variables are settings, status is measurement — take two readings and subtract.
• SHOW FULL PROCESSLIST first when the database is slow.