How to See the Full Query from SHOW PROCESSLIST in MySQL

1. Use the FULL keyword: The key is to add the FULL keyword after SHOW PROCESSLIST. This instructs the server to display the complete query string in the output. The command becomes:

SHOW FULL PROCESSLIST;

1. 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.

1. Information Schema PROCESSLIST table (MySQL 5.1.1 or higher):

   This method retrieves data from a built-in table called PROCESSLIST within the information_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.

2. 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.

3. 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 to FILE, 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.