Find query from query_id in mysql

Question

Can I find the exact query from mysql query id?

This is the part of "SHOW ENGINE INNODB STATUS" in MySQL:

MySQL thread id 1106, query id 1360 localhost 127.0.0.1 test2 ---TRANSACTION 0 19491, not started, OS thread id 2960035840 

Is there any way I can find the query with id 1360?

Answer 1

Some say that turn on the "general log" and you will find your request by id. http://forums.mysql.com/read.php?22,419784,419896#msg-419896

Answer 2

Just add this line to my.cnf

 log=/tmp/mysql_query.log 

Then restarted the mysql service ( /etc/init.d/mysql stop /etc/init.d/mysql start )

Then crossed out the log file. It seems to have a request id in it!

 110825 15:07:49 36 Connect ***@localhost on *** ... 36 Query SELECT * FROM genre g LIMIT 0,1000 36 Quit 

See also http://www.jeff-barr.com/?p=112 and http://dev.mysql.com/doc/refman/5.1/en/query-log.html

Answer 3

You can use the following command: SHOW PROCESSLIST;

It will provide you with the entire current running process with their request identifier and the request that is being executed.