Menu
I have a long running transaction running on a database
---TRANSACTION 2 4166167596, ACTIVE 14868 sec, process no 7906, OS thread id 1169635648
MySQL thread id 34318076, query id 1997556522 10.186.131.136 admin
The above is an output from SHOW INNODB ENGINE STATUS command
I want to see what query is running by with the query id 1997556522. SHOW FULL PROCESSLIST is not an option, since it requires me to manually find the query from a list of hundreds.
This SO Thread says you can do that by querying the INFORMATION_SCHEMA.PROCESSLIST table on MySQL 5.1.7 and greater. Is it possible to do so on an older version?
I am running on MySQL 5.0.45
667
SHOW [FULL] PROCESSLIST. The MySQL process list indicates the operations currently being performed by the set of threads executing within the server. The SHOW PROCESSLIST statement is one source of process information. For a comparison of this statement with other sources, see Sources of Process Information.
Login to DB; Run a command show full processlist; to get the process id with status and query itself which causes the database hanging; Select the process id and run a command KILL <pid>; to kill that process.
You should use the thread id, not the query id.
If you have shell access you can execute show full processlist and then grep for the relevant thread id.
For example:
mysql -e "show full processlist;" | grep 34318076
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
