How to debug PDO database queries?

Question

Before moving to PDO, I created SQL queries in PHP by concatenating strings. If I got database syntax error, I could just echo the final SQL query string, try it myself on the database, and tweak it until I fixed the error, then put that back into the code.

Prepared PDO statements are faster and better and safer, but one thing bothers me: I never see the final query as it's sent to the database. When I get errors about the syntax in my Apache log or my custom log file (I log errors inside a catch block), I can't see the query that caused them.

Is there a way capture the complete SQL query sent by PDO to the database and log it to a file?

Answer 1

almost nothing was said about error displaying except check error logs, but there's a rather helpful functionality:

<?php
/* Provoke an error -- bogus SQL syntax */
$stmt = $dbh->prepare('bogus sql');
if (!$stmt) {
    echo "\PDO::errorInfo():\n";
    print_r($dbh->errorInfo());
}
?>

(source link)

it is clear that this code can be modified to be used as exception message or any other kind of error handling

Answer 2

How to debug PDO mysql database queries in Ubuntu

TL;DR Log all your queries and tail the mysql log.

These directions are for my install of Ubuntu 14.04. Issue command lsb_release -a to get your version. Your install might be different.

Turn on logging in mysql

1. Go to your dev server cmd line
2. Change directories cd /etc/mysql. You should see a file called my.cnf. That’s the file we’re gonna change.
3. Verify you’re in the right place by typing cat my.cnf | grep general_log. This filters the my.cnf file for you. You should see two entries: #general_log_file = /var/log/mysql/mysql.log && #general_log = 1.
4. Uncomment those two lines and save via your editor of choice.
5. Restart mysql: sudo service mysql restart.
6. You might need to restart your webserver too. (I can’t recall the sequence I used). For my install, that’s nginx: sudo service nginx restart.

Nice work! You’re all set. Now all you have to do is tail the log file so you can see the PDO queries your app makes in real time.

Tail the log to see your queries

Enter this cmd tail -f /var/log/mysql/mysql.log.

Your output will look something like this:

73 Connect  xyz@localhost on your_db
73 Query    SET NAMES utf8mb4
74 Connect  xyz@localhost on your_db
75 Connect  xyz@localhost on your_db
74 Quit 
75 Prepare  SELECT email FROM customer WHERE email=? LIMIT ?
75 Execute  SELECT email FROM customer WHERE email='[e-mail]' LIMIT 5
75 Close stmt   
75 Quit 
73 Quit 

Any new queries your app makes will automatically pop into view, as long as you continue tailing the log. To exit the tail, hit cmd/ctrl c.

Notes

1. Careful: this log file can get huge. I’m only running this on my dev server.
2. Log file getting too big? Truncate it. That means the file stays, but the contents are deleted. truncate --size 0 mysql.log.
3. Cool that the log file lists the mysql connections. I know one of those is from my legacy mysqli code from which I'm transitioning. The third is from my new PDO connection. However, not sure where the second is coming from. If you know a quick way to find it, let me know.

Credit & thanks

Huge shout out to Nathan Long’s answer above for the inspo to figure this out on Ubuntu. Also to dikirill for his comment on Nathan’s post which lead me to this solution.

Love you stackoverflow!

Answer 3

You say this :

I never see the final query as it's sent to the database

Well, actually, when using prepared statements, there is no such thing as a "final query" :

• First, a statement is sent to the DB, and prepared there
  • The database parses the query, and builds an internal representation of it
• And, when you bind variables and execute the statement, only the variables are sent to the database
  • And the database "injects" the values into its internal representation of the statement

So, to answer your question :

Is there a way capture the complete SQL query sent by PDO to the database and log it to a file?

No : as there is no "complete SQL query" anywhere, there is no way to capture it.

The best thing you can do, for debugging purposes, is "re-construct" an "real" SQL query, by injecting the values into the SQL string of the statement.

What I usually do, in this kind of situations, is :

• echo the SQL code that corresponds to the statement, with placeholders
• and use var_dump (or an equivalent) just after, to display the values of the parameters
• This is generally enough to see a possible error, even if you don't have any "real" query that you can execute.

This is not great, when it comes to debugging -- but that's the price of prepared statements and the advantages they bring.

Answer 4

Probably what you want to do is use debugDumParams() It doesn't build the prepared statement for you, but it will show your parameters.

Answer 5

To log MySQL in WAMP, you will need to edit the my.ini (e.g. under wamp\bin\mysql\mysql5.6.17\my.ini)

and add to [mysqld]:

general_log = 1
general_log_file="c:\\tmp\\mysql.log"

Answer 6

I've created a modern Composer-loaded project / repository for exactly this here:

pdo-debug

Find the project's GitHub home here, see a blog post explaining it here. One line to add in your composer.json, and then you can use it like this:

echo debugPDO($sql, $parameters);

$sql is the raw SQL statement, $parameters is an array of your parameters: The key is the placeholder name (":user_id") or the number of the unnamed parameter ("?"), the value is .. well, the value.

The logic behind: This script will simply grad the parameters and replace them into the SQL string provided. Super-simple, but super-effective for 99% of your use-cases. Note: This is just a basic emulation, not a real PDO debugging (as this is not possible as PHP sends raw SQL and parameters to the MySQL server seperated).

A big thanks to bigwebguy and Mike from the StackOverflow thread Getting raw SQL query string from PDO for writing basically the entire main function behind this script. Big up!