Printing all the queries that mySql generates

Question

I work with Workbench and phpMyAdmin with mySql, I use Procedures and Function etc... Sometimes one procedure triggers another Function and so on... eventually something goes wrong somewhere.

Is there any tool known to anybody that can show all the queries mySql runs? Like a command prompt that shows every command MySql runs

it would be a very helpful debugging tool

Answer 1

Run SET GLOBAL general_log = 'ON'; , all queries will be logged to a log-file. See here

Answer 2

I use on a very big site this function I made:

define(test, 3);

function my_query($SQL){//this is a layer over mysql_query to get interesting data while debugging

     if(test == 3){ // paranoia

             $query_start = get_microtime_ms();
             $exp = mysql_query("EXPLAIN ".$SQL);
             $query_end =   get_microtime_ms();
             $query_time = $query_end - $query_start;
             $explain .="\n<table border=\"1\">";
             $explain .="<tr><td>id</td><td>select_type</td><td>table</td><td>type</td><td>possible_keys</td><td>key</td><td>key_len</td><td>ref</td><td>rows</td><td>Extra</td></tr>";

             while( $explain_r = mysql_fetch_array($exp)){
                 $explain .="\n\t<tr>";
                     for($i=0;$i<10;$i++){
                         $explain .= "\n\t\t<td>".$explain_r[$i]." </td>";
                     }
                 $explain .="\n\t</tr>";
             }
             $explain .="\n</table>";
             $r = mysql_query($SQL)or die(mysql_error(),$SQL);
             $returned = mysql_num_rows($r);
             print("<pre>".trim($SQL)."\n".$explain."Time spent: ".$query_time."\nReturned rows: ".$returned."\n--------------\n</pre>");
             return $r;

     }

     if(test == 2){ // hard debugging
         print("<pre>\n\n".trim($SQL)."\n--------------\n</pre>");
         $r = mysql_query($SQL)or die((mysql_error(),$SQL));
         return $r;
     }
     elseif(test == 1){ // testing
         $r = mysql_query($SQL)or die((mysql_error(),$SQL));
         return $r;
     }
     elseif(test == 0){ //production

        if($_SESSION['uid'] == 59){
            $only_me = $SQL;
        }



     $r = mysql_query($SQL)or die(
         mail("[email protected]",
              "[Error http://".$_SERVER["HTTP_HOST"].$_SERVER['REQUEST_URI']."] Error on ".$_SERVER['SCRIPT_NAME'],
              "From: ".$_SERVER['REMOTE_ADDR'].
              "\nReferer: ".$_SERVER['HTTP_REFERER']."\nDate: ".date()."\nError:\n".mysql_error()."\n".$SQL.
              "\nUsername: ".$_SESSION['username']."\nhttp://".$_SERVER["HTTP_HOST"].$_SERVER['REQUEST_URI'].
              "\nRef: ".$_SERVER['HTTP_REFERER']).
              " a query went wrong <a href=\"http://example.com\"&gt;Please click here to go back to home page</a><br><br><pre>$only_me".mysql_error()."</pre>");
         return $r;
     }

 }

Answer 3

In your database abstraction class, the best way to do it is to add a debug option that logs queries and debug information to a file, or if there is enough overhead in your application, keep it all available in a variable you can inspect at runtime. Once you've got all this information there, you can output it to a hidden div with a control, or inspect it directly with something like xdebug and netbeans.

On a general note - if you have a large application which you wish to debug, it's really worth spending a bit of time investigating breakpoints, and interactive debugging - it's not so complex to setup and extremely useful. I asked about this recently, and it's really helped me with this kind of stuff!

If your database calls aren't abstracted (shame!) then you can override core PHP functions to include a debug call as demonstrated in this useful example on the PHP site, using the APD extension. Very useful! For example, you can override mysql_query() with your own code to log the query.

Answer 4

Edit your MySQL configuration file (my.cnf or my.ini) and add a log="/path/to/yourlog.log" line in the [mysqld] section. Make sure you restart MySQL to have it pick up the changes in the config file.

This should log all queries that are executed (though I'm not sure it will log the queries executed inside stored procedures).