How to see full query from SHOW PROCESSLIST
MysqlMysql Problem Overview
When I issue SHOW PROCESSLIST query, only 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)
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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' |
Solution 3 - Mysql
See full query from SHOW PROCESSLIST :
SHOW FULL PROCESSLIST;
Or
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
Solution 4 - Mysql
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 :)
Solution 5 - Mysql
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
Solution 6 - Mysql
SHOW FULL PROCESSLIST\G
This shows the full processlist with more info.