How to see full query from SHOW PROCESSLIST?

Question

When I issue SHOW PROCESSLIST query, only the first 100 characters of the running SQL query are returned in the info column.

Is it possible to change MySQL config or issue a different kind of request to see complete query (the queries I'm looking at are longer than 100 characters)

Answer 1

SHOW FULL PROCESSLIST

If you don't use FULL, "only the first 100 characters of each statement are shown in the Info field".

When using phpMyAdmin, you should also click on the "Full texts" option ("← T →" on top left corner of a results table) to see untruncated results.

Answer 2

SHOW FULL PROCESSLIST

This shows the full processlist with more info.

Answer 3

Show Processlist fetches the information from another table. Here is how you can pull the data and look at 'INFO' column which contains the whole query :

select * from INFORMATION_SCHEMA.PROCESSLIST where db = 'somedb';

You can add any condition or ignore based on your requirement.

The output of the query is resulted as :

+-------+------+-----------------+--------+---------+------+-----------+----------------------------------------------------------+
| ID    | USER | HOST            | DB     | COMMAND | TIME | STATE     | INFO                                                     |
+-------+------+-----------------+--------+---------+------+-----------+----------------------------------------------------------+
|     5 | ssss | localhost:41060 | somedb | Sleep   |    3 |           | NULL                                                     |
| 58169 | root | localhost       | somedb | Query   |    0 | executing | select * from sometable where tblColumnName = 'someName' |

Answer 4

I just read in the MySQL documentation that SHOW FULL PROCESSLIST by default only lists the threads from your current user connection.

Quote from the MySQL SHOW FULL PROCESSLIST documentation:

If you have the PROCESS privilege, you can see all threads.

So you can enable the Process_priv column in your mysql.user table. Remember to execute FLUSH PRIVILEGES afterwards :)

Answer 5

If one want to keep getting updated processes (on the example, 2 seconds) on a shell session without having to manually interact with it use:

watch -n 2 'mysql -h 127.0.0.1 -P 3306 -u some_user -psome_pass some_database -e "show full processlist;"'

The only bad thing about the show [full] processlist is that you can't filter the output result. On the other hand, issuing the SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST open possibilities to remove from the output anything you don't want to see:

SELECT * from INFORMATION_SCHEMA.PROCESSLIST
WHERE DB = 'somedatabase'
AND COMMAND <> 'Sleep'
AND HOST NOT LIKE '10.164.25.133%' \G

Answer 6

See full query from SHOW PROCESSLIST :

SHOW FULL PROCESSLIST;

Or

 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

Answer 7

bash

## identify the query id
mysql -e 'SHOW processlist'

## show only the related sql query for this process omitting the other columns
## change: YOUR_QUERY_ID_YOU_LOOKED_UP
mysql -sre 'SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=YOUR_QUERY_ID_YOU_LOOKED_UP'|tr '\n' ' ';echo

## bonus: get mysql EXPLAIN for this query (when its too long to copy properly)
## change: YOUR_DATABASE,YOUR_QUERY_ID_YOU_LOOKED_UP
mysql -e "USE YOUR_DATABASE;EXPLAIN $(mysql -sre 'SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=YOUR_QUERY_ID_YOU_LOOKED_UP'|tr '\n' ' ';echo)"

mysql

-- identify the query id
SHOW processlist;

/*
show only the related sql query for this process omitting the other columns
change: YOUR_QUERY_ID_YOU_LOOKED_UP
*/
SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id=YOUR_QUERY_ID_YOU_LOOKED_UP \G