MySQL - show query by query id in mysql < 5.1.7

Question

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

Answer 1

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