How to See the Full Query from SHOW PROCESSLIST in MySQL
- Use the
FULL
keyword: The key is to add theFULL
keyword afterSHOW PROCESSLIST
. This instructs the server to display the complete query string in the output. The command becomes:
SHOW FULL PROCESSLIST;
- Tools might truncate by default: Even with
FULL
specified, some tools you might be using to interact with MySQL might truncate the results by default. For instance, in phpMyAdmin, you'll need to click the "Full texts" option (often displayed as a left-pointing arrow "← T →" in the top left corner of the results table) to see the full, untruncated output.
Points to Remember:
SHOW PROCESSLIST
only shows currently running queries. If the query has already finished executing, you won't see it here. You'd need to look at the general query log for that (refer to MySQL documentation for details on enabling the general query log).SHOW PROCESSLIST
is a useful tool for troubleshooting slow queries, identifying long-running operations, and managing connections. By seeing the full query, you can understand what the specific operation is doing.
mysql -u username -p password # Connect to MySQL server
SHOW FULL PROCESSLIST;
Replace username
with your MySQL username and password
with your MySQL password. This will connect you to the server and then execute the SHOW FULL PROCESSLIST
statement, displaying the complete queries for all running processes.
Using a PHP script:
<?php
$host = "localhost";
$user = "username";
$password = "password";
$dbname = "mydatabase";
$conn = mysqli_connect($host, $user, $password, $dbname);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
$sql = "SHOW FULL PROCESSLIST";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
echo "<table>";
echo "<tr>";
echo "<th>Id</th>";
echo "<th>User</th>";
echo "<th>Host</th>";
echo "<th>DB</th>";
echo "<th>Command</th>";
echo "<th>Time</th>";
echo "<th>State</th>";
echo "<th>Info</th>";
echo "</tr>";
while($row = mysqli_fetch_assoc($result)) {
echo "<tr>";
echo "<td>" . $row['Id'] . "</td>";
echo "<td>" . $row['User'] . "</td>";
echo "<td>" . $row['Host'] . "</td>";
echo "<td>" . $row['db'] . "</td>";
echo "<td>" . $row['Command'] . "</td>";
echo "<td>" . $row['Time'] . "</td>";
echo "<td>" . $row['State'] . "</td>";
echo "<td>" . $row['Info'] . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "No processes found";
}
mysqli_close($conn);
?>
This script connects to a MySQL database and executes the SHOW FULL PROCESSLIST
statement. It then iterates through the results and displays them in an HTML table format, including the full query string in the Info
column.
-
Information Schema
PROCESSLIST
table (MySQL 5.1.1 or higher):This method retrieves data from a built-in table called
PROCESSLIST
within theinformation_schema
database. This table stores information about running queries. You can use the following query to access it:SELECT * FROM information_schema.PROCESSLIST p;
While this might not show the full query by default depending on your MySQL version, it provides various details about the processes, including the initial part of the query in the
info
column. -
Performance Schema
threads
table (MySQL 8.0.22 or higher):Starting with MySQL 8.0.22, there's an alternative implementation for the processlist based on the Performance Schema. You can access information about threads (including queries) using the
threads
table:SELECT * FROM performance_schema.threads t;
Similar to the
information_schema
method, this might not show the full query by default, but offers a different perspective on running threads. -
General Query Log (Optional):
The general query log, if enabled, can be a good resource to see past queries executed on the server. It's a file containing all executed queries. However, enabling the general query log can add some overhead to the server. You can check the current logging configuration using:
SHOW VARIABLES LIKE "log_output%";
If
log_output
is set toFILE
, the general query log is enabled and written to a file (location can vary depending on your configuration). You'd need to access and analyze this file to see past queries.
mysql processlist