Find the Query from query_id in MySQL (InnoDB)

A MySQL deadlock report shows a query ID, but you can't find the SQL statement that caused the problem. You have the thread ID and query ID from SHOW ENGINE INNODB STATUS, yet neither tells you directly what was executed.

Direct answer: To find the query from query_id in mysql, first check whether the SQL statement is still visible in the InnoDB status report or process list. If it has finished, use previously collected Performance Schema history or general query logs. MySQL doesn't provide a standard lookup command that retrieves historical SQL using an InnoDB query ID alone.

Prerequisites before investigating the MySQL query ID

You'll need:

• MySQL 8.0 or 8.4 for the examples below. MariaDB has some differences.
• Access to the MySQL command-line client, MySQL Workbench, or another SQL client.
• The PROCESS privilege to inspect server-wide activity and InnoDB status.
• Appropriate SELECT privileges for Performance Schema or log tables.
• Administrative privileges if you need to change server logging settings.

The investigation starts with a real kind of InnoDB status entry:

MySQL thread id 1106, query id 1360 localhost 127.0.0.1 test2
---TRANSACTION 0 19491, not started, OS thread id 2960035840

We want to identify the SQL statement associated with query ID 1360.

There's an important distinction:

These identifiers aren't interchangeable.

1. Find the query from query_id in MySQL InnoDB status

Start by connecting to MySQL:

mysql -u root -p

Check the server version:

SELECT VERSION(), @@version_comment;

Then capture the current InnoDB status:

SHOW ENGINE INNODB STATUS\G

Look for these sections:

LATEST DETECTED DEADLOCK
TRANSACTIONS
LIST OF TRANSACTIONS FOR EACH SESSION

InnoDB can include the actual SQL statement immediately after the connection and query information.

For example, you might see:

MySQL thread id 1106, query id 1360 localhost root updating
UPDATE orders SET status = 'paid' WHERE id = 25

In that case, you've found the statement without enabling additional logging.

But sometimes the output only contains:

---TRANSACTION 0 19491, not started

That means the transaction shown isn't currently active. It doesn't provide enough information to reconstruct its previous SQL statements.

For a saved report on Linux, run:

mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G" > /tmp/innodb-status.txt

Search the captured text:

grep -n -A 15 "query id 1360" /tmp/innodb-status.txt

The -A 15 option displays 15 lines after the match, where SQL text may appear.

One gotcha: SHOW ENGINE INNODB STATUS is a snapshot, not a complete historical query archive. A newer deadlock can replace the previous deadlock details.

2. Find the SQL query using the MySQL thread ID

The thread ID is usually more useful than the InnoDB query ID.

In our example:

MySQL thread id 1106

Run:

SHOW FULL PROCESSLIST;

This displays connected sessions and their current statements.

You can narrow the search:

SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    INFO
FROM information_schema.PROCESSLIST
WHERE ID = 1106\G

The INFO column contains the SQL statement currently associated with that connection, when one is available.

Use SHOW FULL PROCESSLIST instead of SHOW PROCESSLIST if you need the complete statement. The shorter command can truncate the displayed SQL.

If connection 1106 is sleeping, INFO may be NULL. If the connection has closed, its row won't appear at all.

Also, don't confuse the connection ID with the query ID. Searching the process list for 1360 instead of 1106 could identify an entirely different connection.

If you're checking which database the application connected to, our guide to the host parameter in mysqli_connect() explains how PHP chooses its MySQL server.

3. Recover recent statements with Performance Schema

Performance Schema is the preferred built-in option for investigating recent statement execution in modern MySQL.

Check whether it's enabled:

SHOW VARIABLES LIKE 'performance_schema';

If the value is ON, inspect the statement consumers:

SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME LIKE 'events_statements%';

To inspect the current statement for connection 1106:

SELECT
    t.PROCESSLIST_ID,
    e.EVENT_ID,
    e.EVENT_NAME,
    e.SQL_TEXT
FROM performance_schema.threads AS t
JOIN performance_schema.events_statements_current AS e
    ON e.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID = 1106\G

For recently completed statements, use:

SELECT
    t.PROCESSLIST_ID,
    e.EVENT_ID,
    e.SQL_TEXT
FROM performance_schema.threads AS t
JOIN performance_schema.events_statements_history AS e
    ON e.THREAD_ID = t.THREAD_ID
WHERE t.PROCESSLIST_ID = 1106
ORDER BY e.EVENT_ID DESC
LIMIT 10;

This gives you recent SQL associated with the connection, provided the events were recorded and haven't been discarded.

For a larger, server-wide history, enable its consumer before reproducing the problem:

UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'statement/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME IN (
    'events_statements_current',
    'events_statements_history_long'
);

Then examine recent captured statements:

SELECT
    THREAD_ID,
    EVENT_ID,
    SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT IS NOT NULL
ORDER BY THREAD_ID DESC, EVENT_ID DESC
LIMIT 20;

For a known live connection, filter these events by joining to performance_schema.threads, as shown earlier.

There are limitations. Performance Schema history has finite capacity, SQL text may be truncated, and a connection that has already closed might no longer have a thread mapping.

Its EVENT_ID is also not the InnoDB query ID 1360.

See the MySQL Performance Schema documentation for the supported statement-history tables.

4. Enable the MySQL general query log to capture future queries

If the query has already disappeared, the general query log is often the most practical way to investigate a reproducible problem.

The log records statements received by MySQL together with their connection identifiers.

First, save the existing settings:

SHOW GLOBAL VARIABLES
WHERE Variable_name IN ('general_log', 'log_output');

For temporary troubleshooting, enable table logging:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

If log_output was previously FILE and you need to preserve file logging, use FILE,TABLE instead.

Now reproduce the application problem.

Search for statements belonging to connection 1106:

SELECT
    event_time,
    thread_id,
    command_type,
    CONVERT(argument USING utf8mb4) AS sql_text
FROM mysql.general_log
WHERE thread_id = 1106
  AND command_type = 'Query'
ORDER BY event_time DESC
LIMIT 30;

The output shows the statements received on that connection after logging was enabled.

This doesn't mean the general log stores query ID 1360. It stores the connection ID, timestamp, command type, and statement text. You'll correlate the relevant statement using the thread ID and time of the problem.

General logging can generate substantial data and expose sensitive application values. I'd enable it only for a short diagnostic window, preferably outside peak traffic.

If the investigation exposes an incorrect SQL expression, our guide to selecting multiple columns based on SQL conditions covers a related part of checking query logic.

5. Keep MySQL statement history enabled after restart

Runtime changes to Performance Schema consumers don't necessarily survive a server restart.

For a Linux MySQL installation using the Debian or Ubuntu package layout, a common configuration path is:

/etc/mysql/mysql.conf.d/mysqld.cnf

Other installations may use /etc/my.cnf or another configuration file.

To keep the extended statement-history consumer enabled, add these settings under [mysqld]:

[mysqld]
performance_schema=ON
performance-schema-consumer-events-statements-history-long=ON

If the server requires these changes, restart MySQL during an approved maintenance window:

sudo systemctl restart mysql

Then verify the configuration:

SELECT NAME, ENABLED
FROM performance_schema.setup_consumers
WHERE NAME = 'events_statements_history_long';

The expected value is:

events_statements_history_long | YES

This captures future completed statements. It cannot restore query history that was never recorded.

Verify that query capture works

After enabling monitoring, execute a harmless statement from an application connection:

SELECT CONNECTION_ID();
SELECT 12345 AS query_capture_test;

Keep the connection open.

From another administrative connection, inspect the history:

SELECT
    t.PROCESSLIST_ID,
    e.EVENT_ID,
    e.SQL_TEXT
FROM performance_schema.threads AS t
JOIN performance_schema.events_statements_history_long AS e
    ON e.THREAD_ID = t.THREAD_ID
WHERE e.SQL_TEXT LIKE '%query_capture_test%'
ORDER BY e.EVENT_ID DESC;

You should find the test statement if the consumer is collecting events and the row hasn't been discarded.

For general logging, search mysql.general_log using the connection ID returned by CONNECTION_ID().

Common errors during setup

Access denied when reading Performance Schema

Your MySQL account may lack the necessary permissions.

Check your grants:

SHOW GRANTS FOR CURRENT_USER();

Ask the database administrator for the required read access. Avoid granting broad administrative permissions to application accounts.

Unknown column 'QUERY_ID' in Performance Schema

MySQL's statement event tables use THREAD_ID and EVENT_ID.

They don't provide an InnoDB QUERY_ID column.

Don't try:

SELECT *
FROM performance_schema.events_statements_history_long
WHERE QUERY_ID = 1360;

Instead, use the thread mapping and statement history demonstrated above.

No rows returned from statement history

Check that the required consumers are enabled and that the statements were executed after monitoring started.

History can also be overwritten on a busy server. events_statements_history_long isn't permanent storage.

MariaDB query IDs behave differently

MariaDB exposes a QUERY_ID column in information_schema.PROCESSLIST.

For a still-running query, you can try:

SELECT
    ID,
    QUERY_ID,
    INFO
FROM information_schema.PROCESSLIST
WHERE QUERY_ID = 1360;

This is MariaDB-specific and only helps while the relevant process-list entry remains available.

Don't assume MySQL and MariaDB expose identical diagnostic columns, even if both are accessed using the mysql command-line client.

Rollback: disable temporary query logging

Once you've captured enough evidence, turn off general query logging if you enabled it solely for troubleshooting:

SET GLOBAL general_log = OFF;

Restore the original log_output value you recorded before making changes.

For example, only if the previous value was FILE:

SET GLOBAL log_output = 'FILE';

If general logging was already enabled before your investigation, restore that original state instead of leaving it disabled.

Also revert temporary Performance Schema instrumentation and consumer changes to their recorded previous values.

For persistent changes, remove only the settings you added to the MySQL configuration and restart during maintenance.

Don't truncate or delete diagnostic logs until you've saved the evidence you need.

FAQ

Can I get the exact SQL statement using only a MySQL query ID?

Not reliably. An InnoDB query ID isn't a persistent lookup key for SQL history. You'll need the original status output, an active connection, or previously captured statement logs to identify the query.

What is the difference between thread ID and query ID in MySQL?

The thread ID identifies a MySQL connection. The query ID identifies a query execution in the server's diagnostic context. One connection can execute many statements, so the values represent different things.

Does SHOW PROFILES display the same query ID as InnoDB?

No. SHOW PROFILES uses session profiling IDs, which aren't the same as the query IDs printed by InnoDB. The profiling statements are also deprecated in MySQL, so Performance Schema is the better choice for new monitoring setups.

Can I find a query after the MySQL connection has closed?

Only if the statement was captured somewhere that still retains it, such as the general query log or suitable diagnostic history. A closed connection disappears from the process list, and Performance Schema history isn't guaranteed to survive long enough for later investigation.

Can MySQL automatically save SQL statements involved in deadlocks?

MySQL can record InnoDB deadlock information, including SQL statements available at the time. For ongoing deadlock investigation, innodb_print_all_deadlocks can send details of all detected InnoDB deadlocks to the MySQL error log rather than relying only on the most recent deadlock shown by SHOW ENGINE INNODB STATUS.

If you need hands-on help configuring query diagnostics, you can contact HeadsNTail.

The practical next step when you need to find the query from query_id in mysql is to capture the InnoDB report, record the connection ID, and enable short-term SQL logging before reproducing the issue.

Originally published at headsntail.com. Free tools for PHP, Laravel and cPanel work: headsntail.com/tools.