[mysql] How to see full query from SHOW PROCESSLIST

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)

See full query from SHOW PROCESSLIST :

SHOW FULL PROCESSLIST;

Or

 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

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 :)

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' |

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