374

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)

BinaryButterfly
  • 18,137
  • 13
  • 50
  • 91
Ghostrider
  • 7,545
  • 7
  • 30
  • 44

7 Answers7

603
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.

poolie
  • 9,289
  • 1
  • 47
  • 74
James McNellis
  • 348,265
  • 75
  • 913
  • 977
  • 3
    It seems phpmyadmin does not care about this, and still shows the truncated info. – giorgio79 Aug 28 '12 at 12:41
  • @giorgio79: If I recall correctly, phpMyAdmin truncates all string results. It's been four years since I did any web development, though, so I could very well be mistaken. – James McNellis Aug 28 '12 at 15:29
  • 2
    I'm seeing queries getting truncated after a certain length even when using `SHOW FULL PROCESSLIST`. Can I make it even fuller somehow? – wizonesolutions Aug 21 '14 at 21:16
  • the command `SHOW FULL PROCESSLIST` needs a semi-colon `;` at the end right? – Rakibul Haq Nov 22 '17 at 07:10
  • 1
    @R.Haq If it's the only query you're going to do, then the semicolon is not necessary. If you want to do more than one query, then you do need the semicolon after each of them. – Julio Garcia Dec 13 '17 at 17:20
  • @JulioGarcia all right, but i failed to execute this single line query in mysql cmd interface without semicolon, might do something wrong there i guess.Thanks. – Rakibul Haq Dec 14 '17 at 03:34
142

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' |
manatwork
  • 1,689
  • 1
  • 28
  • 31
Yogesh A Sakurikar
  • 1,731
  • 1
  • 11
  • 13
  • 19
    This is probably the most useful answer. – dr_ Nov 24 '16 at 09:40
  • My info column shows `COMMIT`. Do you know how I can view more details about the actual query? – m.spyratos Jan 14 '18 at 12:51
  • localhost:41060 , what does 41060 stands for ? any guess ? – Farveen Hassan May 21 '20 at 10:45
  • 1
    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 09 '20 at 23:35
24

See full query from SHOW PROCESSLIST : 

SHOW FULL PROCESSLIST;

Or

 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;
Adiii
  • 54,482
  • 7
  • 145
  • 148
Hasib Kamal Chowdhury
  • 2,476
  • 26
  • 28
15

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

hardcoder
  • 593
  • 5
  • 10
15

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
cristianoms
  • 3,456
  • 3
  • 26
  • 28
  • 1
    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 09 '22 at 17:46
  • you might try using `mytop(1)` – sjas Mar 16 '23 at 12:51
4
SHOW FULL PROCESSLIST

This shows the full processlist with more info.

ryadavalli
  • 101
  • 5
0

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
sjas
  • 18,644
  • 14
  • 87
  • 92