Resolving MySQL Process ID to page that ran it?

Question

I asked this question on stackoverflow and was directed to ask this question here.

I have a couple of scripts in PHP that run MySQL queries. On occasion they cause the server to seize up or become clogged with MySQL processes that take a while, for example badly formed LEFT JOINs etc.

When I run the query to find information about all current MySQL queries that have been run is it be possible to resolve a query ID to the file that ran it?

There is a query that has been sleeping for 3973 there is no info about this query and so I am trying to find as much information as possible?

Answer 1

There are three(3) ways to find out info on queries and its process ID

Way #1 : Activate the General Log as a Text File

If you add the following lines to my.cnf and restart mysql

[mysqld]
general-log
general-log-file=/var/log/mysql_general.log

You can parse /var/log/mysql_general.log text file. The header of each query should have the process ID (Thread ID) that the query ran under

Way #2 : Activate the General Log as a MyISAM table

There is a table called mysql.general_log. It is a CSV file by default. Run the following commands:

ALTER TABLE mysql.general_log ENGINE=MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

then add the following to my.cnf

[mysqld]
general-log
log-output=FILE

Once you have the general log entries stored as a table rows, you can run SELECTS against the MyISAM at will, with queries like:

SELECT * FROM mysql.general_log
WHERE event_time >= (DATE(NOW())+INTERVAL 0 SECOND)
AND event_time <= NOW();
AND argument like '...';

Way #3 : Check the Process List

If you want to catch the queries in the act of running long, do the following (if you have MySQL 5.1) :

SELECT id,info FROM information_schema.processlist
WHERE command = 'Query' AND user <> 'system user';

This shows you every query currently running. You can parse the info column for whatever query you are tracking down