Using SHOW FULL PROCESSLIST to Debug PHP Query

Problem Statement:

We often use SHOW FULL PROCESSLIST (or even the nifty mytop program) to view running queries on a system to see what query is executing. Granted, you can write a log file from your application and use the tail utility to watch the log or even send your query log to syslog. But SHOW FULL PROCESSLIST is a easy way to watch whats going on but only if we could tag the queries to identify which application or module is running them.

Here we will discuss how you can do a simple trick to do this for your PHP-based Web applications.

Solution

If you are old-fashioned PHP developer who actually write the entire query as a statement in your code, you can simply change code such as following:

     $stmt    = "SELECT * FROM my_table WHERE id = $sanitizedUID";
     $results = mysql_query($stmt);

to the following code:

    $queryID  = basename(__FILE__) . ' near line: ' . __LINE__ ;
    $stmt     = "/* $queryID */ SELECT * FROM my_table WHERE id > $sanitizedUID";
    $results  = mysql_query($stmt);

This will inject a filename and line number as a comment in your query statement and SHOW FULL PROCESSLIST will show this information for you. This allows you to identify your script/line for the query and helps you debug things easily.

If you are more advanced PHP developer who use a framework of some sort, you will have to hack your framework to include the query comments.  Our own framework — phpsteel — will support query comments and query profiling in the next version.

Category: Debugging, PHP, Programming | Tags: