Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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)

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.

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' |
                My info column shows COMMIT. Do you know how I can view more details about the actual query?
– m.spyratos
                Jan 14, 2018 at 12:51
                Quick query for showing actively running queries & the capacity to end a query on Amazon Aurora MySQL: select id pid, user, concat('CALL mysql.rds_kill(', id, ');'), time, state, info from information_schema.processlist where info is not null order by time desc;
– spen.smith
                Sep 9, 2020 at 23:35
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 :)
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
                This is the only answer that seems to have the key element needed to answer the original question, which is to get the info column without the query being truncated: terminating the query with \G instead of ;
– Araxia
                Dec 9, 2022 at 17:46
                I had a \G at the end of the command like this SHOW FULL PROCESSLIST \G But now it doesn't seem to work that way.. something changed?
– ryadavalli
                Apr 21 at 6:49
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