Mysqli Prepared Statements – Debugging/Echoing SQL Queries

Delving into prepared statements for Mysqli, I discovered the first shortcoming of using the ‘improved’ method… debugging your SQL statements. Whilst you can call up the error message that might occur, it’s not possible to echo the sql query with PHP to see what the query is with the bound parameters.

Solution: Turn on mysql query logging (not slow logs).

Here’s how: Locate your my.cnf file (I used the command ‘locate my.cnf’) and open it with your favourite editor. Scroll down to the area in your my.cnf file under the heading [mysqld], section *Logging and replication* (see snippet below).¬† Uncomment the line that says general_log_file. For added clarity, I changed the location of my log file to /var/log/mysql/query-debug.log rather than the combined /var/log/mysql/mysql.log that was set as my default. You’ll need to create¬† (touch) the query-debug.log file and change it’s ownership and permissions to match the mysql.log (you’ll need root access to do this).

# * Logging and Replication
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /var/log/mysql/query-debug.log
general_log             = 0

Restart mysql to apply this change in your my.cnf.
As I don’t want the logging to be on all the time, I left the general_log as 0 (zero). We can turn this off and on via your PHP script.

Now in your PHP script where you want to debug the SQL query, add the line

$mysqli->query("SET GLOBAL general_log = 'ON'");

where $mysqli is your mysql connection object.

Now you can less or tail that log file to see your queries.

I’m using Ubuntu 12.04, PHP v5.3.10 and MySQL v5.5.24 so I’m not sure if this applies directly to other MySQL versions or Linux distributions but it should give you a good idea.