How to see full query from SHOW PROCESSLIST?

Question

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)

Repetitive

Answer 1

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.

Vesperal

It seems phpmyadmin does not care about this, and still shows the truncated info. – Unlimber

@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. – Vesperal

I'm seeing queries getting truncated after a certain length even when using SHOW FULL PROCESSLIST. Can I make it even fuller somehow? – Endearment

the command SHOW FULL PROCESSLIST needs a semi-colon ; at the end right? – Patentor

@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. – Wifely

@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. – Patentor

Answer 2

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

Farming

This is probably the most useful answer. – Expedite

My info column shows COMMIT. Do you know how I can view more details about the actual query? – Kickstand

localhost:41060 , what does 41060 stands for ? any guess ? – Askari

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; – Cramoisy

Answer 3

See full query from SHOW PROCESSLIST :

SHOW FULL PROCESSLIST;

Or

 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

Darkish

Answer 4

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

Note

This is not true if you are root. – Grillage

Answer 5

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

Bernal

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 ; – Idealist

you might try using mytop(1) – Matsumoto

Answer 6

SHOW FULL PROCESSLIST

This shows the full processlist with more info.

Angrist

I'm sorry but how is this different from the accepted answer? – Benefice

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? – Angrist

Answer 7

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

Matsumoto