How to see full query from SHOW PROCESSLIST

Question

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

User · Answer

See full query from SHOW PROCESSLIST     SHOW FULL PROCESSLIST    Or   SELECT   FROM INFORMATION SCHEMA PROCESSLIST

User · Answer

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

User · Answer

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  lt  gt   Sleep  AND HOST NOT LIKE  10 164 25 133    G

User · Answer

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

User · Answer

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

[mysql] How to see full query from SHOW PROCESSLIST

Examples related to mysql