PHP/MySQL Debug Trick

Update: As some people have pointed out, if this is on all the time in a production environment, it could be bad. We only enable it when debugging. But, in the interest of keeping the PHP world safe, I have edited the code.

Preface: I would not use this in a production environment on a full time basis.  When we do use it, we turn it on, gather some logs and turn it off.  Also, it has been pointed out to me that this will cause MySQL to not use the query cache in versions previous to 5.0.  That is another reason to not use this in a production environment full time.

So, on dealnews.com we often end up with a lot of queries that look kind of the same. It usually because we have a complex query that simply selects article ids from one or two tables and then a second query to select the data from the article table using those ids. If you have used MySQL much, you know that is often the better solution than doing a join where one table is huge and you need to order by the smaller table.

However, this becomes a problem when you start having a problem with a query or two. You can’t tell from a mysqladmin proc where the query is coming from. So, I came up with a solution.

We use a class that I wrote for all of our access to mysql. Its basically a wrapper for mysql_connect, mysql_select_db, mysql_query, mysql_num_rows(), etc. One method to handle all of that. Lots of you probably have one of these objects or libraries. Anyhow, what I did was add a comment into the sql just before I run the query:

if(!empty($_SERVER["REQUEST_URI"])){
    $uri = str_replace("*/", "%2A/", $_SERVER["REQUEST_URI"]);
    $SQL = "/* $uri */\n$SQL";
} else {
    $uri = str_replace("*/", "%2A/", $_SERVER["SCRIPT_FILENAME"]);
    $SQL = "/* $uri */\n$SQL";
}

Now, mysqladmin proc has the request URI for every query in it. The MySQL slow query log does as well. I know this is a simple little thing, but man, is it useful.

About these ads

13 Responses to PHP/MySQL Debug Trick

  1. Ed says:

    You should be careful using this — on some older (and not too much older) versions of MySQL putting a comment before SELECT caused it to bypass the query cache.

    e.g.

    SELECT cols from table — can use the query cache

    /* http://mysite.com/ */ SELECT cols from table — cannot use the query cache (on some versions)

  2. Ed says:

    Slight clarification via the manual:

    Before MySQL 5.0, a query that began with a leading comment could be cached, but could not be fetched from the cache. This problem is fixed in MySQL 5.0.

  3. Stefan Esser says:

    I wonder if you have ever heard about SQL Injection…

  4. sor says:

    SQL injection made easy – just go to page.php?comment ends here*/evil query;– or page.php/endcomment*/query;– (you get the idea). Fortunately enough spaces become %20 so it kinda prevents you from doing anything useful, but I guess you could work around it. I hope you filter REQUEST_URI somehow.

  5. Stefan Esser says:

    Bypassing Space %20 problem is quite easy:

    Queries like:

    select/**/*/**/from/**/user

    do not need a space….

  6. FlorentG says:

    I think this trick is not intended to be put in production. Just do it on your test server, no ?

  7. till says:

    Funny “trick”, did you mean “joke”? :-)

    By the way, I like MySQL very much but the query cache concept is so overrated. Once you issue a statement on a table to change data (e.g. INSERT, UPDATE, DELETE), MySQL will flush the entire query cache that belonged to the table.

  8. Xaprb says:

    I did something similar at my current employee and published about it at my blog: How to track what owns a MySQL connection. What puzzled me a bit was how many people seemed to misunderstand the purpose of doing such a thing. No, you cannot find out from SHOW PROCESSLIST what program is responsible for a query :-) You have to do something like you’ve explained above (though I will jump in line like others and say you have to be careful about security. It’s really not hard to escape or parametrize the REQUEST_URI, though — you cannot take it as a given that this is open to injection).

  9. Brian Moon’s Blog: PHP/MySQL Debug Trick

  10. Please remove that code or at least do it properly as a paramaterized query. You’re on Planet PHP and a lot of people will be looking at this code. People will copy and paste code snippets like that.

    I know its not your fault if people are bad coders, but please don’t post bad code.

  11. doughboy says:

    First, I have updated the code. Your concerns are not totally invalid. We do only run this when testing/debugging. At worst, this could break a site. PHP’s mysql extension will not run multiple queries in one mysql_query() call. I could find no way to actually inject SQL that would run. Still, breaking a site is not good either as way too many ISPs run with display_errors on. So, the code above should stop any attempts should someone put this in use permenantly. I would still not recommend you do it.

    Ed, I use MySQL 5 and have for a while. I was unaware of that issue. I will note that in the post.

    Michael, I am not sure what you mean by “paramaterized query” unless you are referring to a prepared statement. I am not sure how that would help you here.

  12. andrew says:

    I’ve done something similar in the past, but with debug_backtrace(). For instance:

    $backtrace = debug_backtrace();
    $query = “/* “.__CLASS__.”::”.__FUNCTION__.” called from {$backtrace[0][file]}::{$backtrace[0][line]} */”.$query;

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: