Pdo error log

PDO::errorInfo

(PHP 5 >= 5.1.0, PHP 7, PECL pdo >= 0.1.0)

PDO::errorInfo —
Получает расширенную информацию об ошибке, произошедшей в ходе
последнего обращения к базе данных

Описание

public array PDO::errorInfo
( void
)

Возвращаемые значения

PDO::errorInfo() возвращает массив с информацией об ошибке
произошедшей в ходе выполнения последней операции с базой данных. Массив
содержит следующие поля:

Замечание:

Если не задан SQLSTATE код или драйвер не сообщил об ошибке, то элементы
следующие за нулевым будут иметь значение NULL.

PDO::errorInfo() выдает информацию об ошибке только для операций
совершаемых с базой данных напрямую из PDO. Если создать объект PDOStatement
методами PDO::prepare() или
PDO::query(), и вызвать ошибку его методами,
PDO::errorInfo() эту ошибку не отобразит. В таких случаях
нужно пользоваться собственными методами получения информации об ошибках объекта
PDOStatement PDOStatement::errorInfo().

Примеры

PDO::errorInfo():
Array
(
    [0] => HY000
    [1] => 1
    [2] => near "bogus": syntax error
)

Смотрите также

• PDO::errorCode() — Возвращает код SQLSTATE результата последней операции с базой данных
• PDOStatement::errorCode() — Определяет SQLSTATE код соответствующий последней операции объекта PDOStatement
• PDOStatement::errorInfo() — Получение расширенной информации об ошибке, произошедшей в результате работы
  объекта PDOStatement

echo $pdo->errorCode();
print_r($pdo->errorInfo());

• 
  Вопрос задан

  более трёх лет назад
  

• 1679 просмотров

try {
  $stmt->execute($sq);
} catch (PDOException $e) {
  print_r($e->getMessage());
  $stmt->debugDumpParams();
}

Пригласить эксперта

• 
  Показать ещё
  Загружается…
  

1. Home

2. php — How to debug PDO database queries?

670 votes

18 answers

Get the solution ↓↓↓

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 acatch 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?

2022-05-24

Write your answer

---

492

votes

Answer

Solution:

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 usevar_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.

---

713

votes

Answer

Solution:

Looking in the database log

Although Pascal MARTIN is correct that PDO doesn’t send the complete query to the database all at once, ryeguy‘s suggestion to use the DB’s logging function actually allowed me to see the complete query as assembled and executed by the database.

Here’s how:
(These instructions are for MySQL on a Windows machine — your mileage may vary)

• Inmy.ini, under the[mysqld] section, add alog command, likelog="C:Program FilesMySQLMySQL Server 5.1datamysql.log"
• Restart MySQL.
• It will start logging every query in that file.

That file will grow quickly, so be sure to delete it and turn off logging when you’re done testing.

---

526

votes

Answer

Solution:

Sure you can debug using this mode{{ PDO::ATTR_ERRMODE }}
Just add new line before your query then you will show the debug lines.

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$db->query('SELECT *******');  

---

571

votes

Answer

Solution:

Probably what you want to do is use debugDumpParams() on the statement handle. You can run that any time after binding values to the prepared query (no need toexecute() the statement).

It doesn’t build the prepared statement for you, but it will show your parameters.

---

356

votes

Answer

Solution:

An old post but perhaps someone will find this useful;

function pdo_sql_debug($sql,$placeholders){
    foreach($placeholders as $k => $v){
        $sql = preg_replace('/:'.$k.'/',"'".$v."'",$sql);
    }
    return $sql;
}

---

799

votes

Answer

Solution:

Here’s a function to see what the effective SQL will be, adpated from a comment by «Mark» at php.net:

function sql_debug($sql_string, array $params = null) {
    if (!empty($params)) {
        $indexed = $params == array_values($params);
        foreach($params as $k=>$v) {
            if (is_object($v)) {
                if ($v instanceof DateTime) $v = $v->format('Y-m-d H:i:s');
                else continue;
            }
            elseif (is_string($v)) $v="'$v'";
            elseif ($v === null) $v='NULL';
            elseif (is_array($v)) $v = implode(',', $v);

            if ($indexed) {
                $sql_string = preg_replace('/?/', $v, $sql_string, 1);
            }
            else {
                if ($k[0] != ':') $k = ':'.$k; //add leading colon if it was left out
                $sql_string = str_replace($k,$v,$sql_string);
            }
        }
    }
    return $sql_string;
}

---

577

votes

Answer

Solution:

No. PDO queries are not prepared on the client side. PDO simply sends the SQL query and the parameters to the database server. The database is what does the substitution (of the?‘s). You have two options:

• Use your DB’s logging function (but even then it’s normally shown as two separate statements (ie, «not final») at least with Postgres)
• Output the SQL query and the
  paramaters and piece it together
  yourself

---

504

votes

Answer

Solution:

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

---

297

votes

Answer

Solution:

for example you have this pdo statement :

$query="insert into tblTest (field1, field2, field3)
values (:val1, :val2, :val3)";
$res=$db->prepare($query);
$res->execute(array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
));

now you can get the executed query by defining an array like this :

$assoc=array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
);
$exQuery=str_replace(array_keys($assoc), array_values($assoc), $query);
echo $exQuery;

---

412

votes

Answer

Solution:

Searching internet I found this as an acceptable solution. A different class is used instead of PDO and PDO functions are called through magic function calls. I am not sure this creates serious performance problems. But it can be used until a sensible logging feature is added to PDO.

So as per this thread, you can write a wrapper for your PDO connection which can log and throws an exception when you get a error.

Here is simple example:

class LoggedPDOSTatement extends PDOStatement    {

function execute ($array)    {
    parent::execute ($array);
    $errors = parent::errorInfo();
    if ($errors[0] != '00000'):
        throw new Exception ($errors[2]);
    endif;
  }

}

so you can use that class instead of PDOStatement:

$this->db->setAttribute (PDO::ATTR_STATEMENT_CLASS, array ('LoggedPDOStatement', array()));

Here a mentioned PDO decorator implementation:

class LoggedPDOStatement    {

function __construct ($stmt)    {
    $this->stmt = $stmt;
}

function execute ($params = null)    {
    $result = $this->stmt->execute ($params); 
    if ($this->stmt->errorCode() != PDO::ERR_NONE):
        $errors = $this->stmt->errorInfo();
        $this->paint ($errors[2]);
    endif;
    return $result;
}

function bindValue ($key, $value)    {
    $this->values[$key] = $value;    
    return $this->stmt->bindValue ($key, $value);
}

function paint ($message = false)    {
    echo '<pre>';
    echo '<table cellpadding="5px">';
    echo '<tr><td colspan="2">Message: ' . $message . '</td></tr>';
    echo '<tr><td colspan="2">Query: ' . $this->stmt->queryString . '</td></tr>';
    if (count ($this->values) > 0):
    foreach ($this->values as $key => $value):
    echo '<tr><th align="left" style="background-color: #ccc;">' . $key . '</th><td>' . $value . '</td></tr>';
    endforeach;
    endif;
    echo '</table>';
    echo '</pre>';
}

function __call ($method, $params)    {
    return call_user_func_array (array ($this->stmt, $method), $params); 
}

}

---

782

votes

Answer

Solution:

To log MySQL in WAMP, you will need to edit the my.ini (e.g. under wampbinmysqlmysql5.6.17my.ini)

and add to[mysqld]:

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

---

731

votes

Answer

Solution:

Here is a function I made to return a SQL query with «resolved» parameters.

function paramToString($query, $parameters) {
    if(!empty($parameters)) {
        foreach($parameters as $key => $value) {
            preg_match('/(?(?!=))/i', $query, $match, PREG_OFFSET_CAPTURE);
            $query = substr_replace($query, $value, $match[0][1], 1);
        }
    }
    return $query;
    $query = "SELECT email FROM table WHERE id = ? AND username = ?";
    $values = [1, 'Super'];

    echo paramToString($query, $values);

Assuming you execute like this

$values = array(1, 'SomeUsername');
$smth->execute($values);

This function DOES NOT add quotes to queries but does the job for me.

---

538

votes

Answer

Solution:

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!

---

683

votes

Answer

Solution:

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 commandlsb_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 directoriescd /etc/mysql. You should see a file calledmy.cnf. That’s the file we’re gonna change.
3. Verify you’re in the right place by typingcat my.cnf | grep general_log. This filters themy.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 cmdtail -f /var/log/mysql/mysql.log.

Your output will look something like this:

73 Connect  [email protected] on your_db
73 Query    SET NAMES utf8mb4
74 Connect  [email protected] on your_db
75 Connect  [email protected] on your_db
74 Quit 
75 Prepare  SELECT email FROM customer WHERE email=? LIMIT ?
75 Execute  SELECT email FROM customer WHERE email='[email protected]b.co' 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, hitcmd/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!

---

772

votes

Answer

Solution:

The problem I had with the solution to catch PDO exemptions for debuging purposes is that it only caught PDO exemptions (duh), but didn’t catch syntax errors which were registered as php errors (I’m not sure why this is, but «why» is irrelevant to the solution). All my PDO calls come from a single table model class that I extended for all my interactions with all tables… this complicated things when I was trying to debug code, because the error would register the line of php code where my execute call was called, but didn’t tell me where the call was, actually, being made from. I used the following code to solve this problem:

/**
 * Executes a line of sql with PDO.
 * 
 * @param string $sql
 * @param array $params
 */
class TableModel{
    var $_db; //PDO connection
    var $_query; //PDO query

    function execute($sql, $params) { 
        //we're saving this as a global, so it's available to the error handler
        global $_tm;
        //setting these so they're available to the error handler as well
        $this->_sql = $sql;
        $this->_paramArray = $params;            

        $this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->_query = $this->_db->prepare($sql);

        try {
            //set a custom error handler for pdo to catch any php errors
            set_error_handler('pdoErrorHandler');

            //save the table model object to make it available to the pdoErrorHandler
            $_tm = $this;
            $this->_query->execute($params);

            //now we restore the normal error handler
            restore_error_handler();
        } catch (Exception $ex) {
            pdoErrorHandler();
            return false;
        }            
    }
}

So, the above code catches BOTH PDO exceptions AND php syntax errors and treats them the same way. My error handler looks something like this:

function pdoErrorHandler() {
    //get all the stuff that we set in the table model
    global $_tm;
    $sql = $_tm->_sql;
    $params = $_tm->_params;
    $query = $tm->_query;

    $message = 'PDO error: ' . $sql . ' (' . implode(', ', $params) . ") n";

    //get trace info, so we can know where the sql call originated from
    ob_start();
    debug_backtrace(); //I have a custom method here that parses debug backtrace, but this will work as well
    $trace = ob_get_clean();

    //log the error in a civilized manner
    error_log($message);

    if(admin(){
        //print error to screen based on your environment, logged in credentials, etc.
        print_r($message);
    }
}

If anyone has any better ideas on how to get relevant info to my error handler than setting the table model as a global variable, I would be happy to hear it and edit my code.

---

355

votes

Answer

Solution:

this code works great for me :

echo str_replace(array_keys($data), array_values($data), $query->queryString);

Don’t forget to replace $data and $query by your names

---

489

votes

Answer

Solution:

i use this class to debug PDO (with Log4PHP)

<?php

/**
 * Extends PDO and logs all queries that are executed and how long
 * they take, including queries issued via prepared statements
 */
class LoggedPDO extends PDO
{

    public static $log = array();

    public function __construct($dsn, $username = null, $password = null, $options = null)
    {
        parent::__construct($dsn, $username, $password, $options);
    }

    public function query($query)
    {
        $result = parent::query($query);
        return $result;
    }

    /**
     * @return LoggedPDOStatement
     */
    public function prepare($statement, $options = NULL)
    {
        if (!$options) {
            $options = array();
        }
        return new LoggedPDOStatement(parent::prepare($statement, $options));
    }
}

/**
 * PDOStatement decorator that logs when a PDOStatement is
 * executed, and the time it took to run
 * @see LoggedPDO
 */
class LoggedPDOStatement
{

    /**
     * The PDOStatement we decorate
     */
    private $statement;
    protected $_debugValues = null;

    public function __construct(PDOStatement $statement)
    {
        $this->statement = $statement;
    }

    public function getLogger()
    {
        return Logger::getLogger('PDO sql');
    }

    /**
     * When execute is called record the time it takes and
     * then log the query
     * @return PDO result set
     */
    public function execute(array $params = array())
    {
        $start = microtime(true);
        if (empty($params)) {
            $result = $this->statement->execute();
        } else {
            foreach ($params as $key => $value) {
                $this->_debugValues[$key] = $value;
            }
            $result = $this->statement->execute($params);
        }

        $this->getLogger()->debug($this->_debugQuery());

        $time = microtime(true) - $start;
        $ar = (int) $this->statement->rowCount();
        $this->getLogger()->debug('Affected rows: ' . $ar . ' Query took: ' . round($time * 1000, 3) . ' ms');
        return $result;
    }

    public function bindValue($parameter, $value, $data_type = false)
    {
        $this->_debugValues[$parameter] = $value;
        return $this->statement->bindValue($parameter, $value, $data_type);
    }

    public function _debugQuery($replaced = true)
    {
        $q = $this->statement->queryString;

        if (!$replaced) {
            return $q;
        }

        return preg_replace_callback('/:([0-9a-z_]+)/i', array($this, '_debugReplace'), $q);
    }

    protected function _debugReplace($m)
    {
        $v = $this->_debugValues[$m[0]];

        if ($v === null) {
            return "NULL";
        }
        if (!is_numeric($v)) {
            $v = str_replace("'", "''", $v);
        }

        return "'" . $v . "'";
    }

    /**
     * Other than execute pass all other calls to the PDOStatement object
     * @param string $function_name
     * @param array $parameters arguments
     */
    public function __call($function_name, $parameters)
    {
        return call_user_func_array(array($this->statement, $function_name), $parameters);
    }
}

---

10

votes

Answer

Solution:

In Debian NGINX environment i did the following.

Goto/etc/mysql/mysql.conf.d editmysqld.cnf if you findlog-error = /var/log/mysql/error.log add the following 2 lines bellow it.

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

To see the logs goto/var/log/mysql andtail -f mysql.log

Remember to comment these lines out once you are done with debugging if you are in production environment deletemysql.log as this log file will grow quickly and can be huge.

---

Share solution ↓

Additional Information:

Date the issue was resolved:

2022-05-24

Link To Source

Link To Answer
People are also looking for solutions of the problem: a non-numeric value encountered in

Didn’t find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

---

Similar questions

Find the answer in similar questions on our website.

Welcome to programmierfrage.com

programmierfrage.com is a question and answer site for professional web developers, programming enthusiasts and website builders. Site created and operated by the community. Together with you, we create a free library of detailed answers to any question on programming, web development, website creation and website administration.

Get answers to specific questions

Ask about the real problem you are facing. Describe in detail what you are doing and what you want to achieve.

Help Others Solve Their Issues

Our goal is to create a strong community in which everyone will support each other. If you find a question and know the answer to it, help others with your knowledge.

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?

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.

Looking in the database log

Although Pascal MARTIN is correct that PDO doesn’t send the complete query to the database all at once, ryeguy‘s suggestion to use the DB’s logging function actually allowed me to see the complete query as assembled and executed by the database.

Here’s how:
(These instructions are for MySQL on a Windows machine — your mileage may vary)

• In my.ini, under the [mysqld] section, add a log command, like log="C:Program FilesMySQLMySQL Server 5.1datamysql.log"
• Restart MySQL.
• It will start logging every query in that file.

That file will grow quickly, so be sure to delete it and turn off logging when you’re done testing.

Sure you can debug using this mode {{ PDO::ATTR_ERRMODE }}
Just add new line before your query then you will show the debug lines.

$db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$db->query('SELECT *******');  

Probably what you want to do is use debugDumpParams() on the statement handle. You can run that any time after binding values to the prepared query (no need to execute() the statement).

It doesn’t build the prepared statement for you, but it will show your parameters.

An old post but perhaps someone will find this useful;

function pdo_sql_debug($sql,$placeholders){
    foreach($placeholders as $k => $v){
        $sql = preg_replace('/:'.$k.'/',"'".$v."'",$sql);
    }
    return $sql;
}

Here’s a function to see what the effective SQL will be, adpated from a comment by «Mark» at php.net:

function sql_debug($sql_string, array $params = null) {
    if (!empty($params)) {
        $indexed = $params == array_values($params);
        foreach($params as $k=>$v) {
            if (is_object($v)) {
                if ($v instanceof DateTime) $v = $v->format('Y-m-d H:i:s');
                else continue;
            }
            elseif (is_string($v)) $v="'$v'";
            elseif ($v === null) $v='NULL';
            elseif (is_array($v)) $v = implode(',', $v);

            if ($indexed) {
                $sql_string = preg_replace('/?/', $v, $sql_string, 1);
            }
            else {
                if ($k[0] != ':') $k = ':'.$k; //add leading colon if it was left out
                $sql_string = str_replace($k,$v,$sql_string);
            }
        }
    }
    return $sql_string;
}

No. PDO queries are not prepared on the client side. PDO simply sends the SQL query and the parameters to the database server. The database is what does the substitution (of the ?‘s). You have two options:

• Use your DB’s logging function (but even then it’s normally shown as two separate statements (ie, «not final») at least with Postgres)
• Output the SQL query and the
  paramaters and piece it together
  yourself

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

for example you have this pdo statement :

$query="insert into tblTest (field1, field2, field3)
values (:val1, :val2, :val3)";
$res=$db->prepare($query);
$res->execute(array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
));

now you can get the executed query by defining an array like this :

$assoc=array(
  ':val1'=>$val1,
  ':val2'=>$val2,
  ':val3'=>$val3,
);
$exQuery=str_replace(array_keys($assoc), array_values($assoc), $query);
echo $exQuery;

Searching internet I found this as an acceptable solution. A different class is used instead of PDO and PDO functions are called through magic function calls. I am not sure this creates serious performance problems. But it can be used until a sensible logging feature is added to PDO.

So as per this thread, you can write a wrapper for your PDO connection which can log and throws an exception when you get a error.

Here is simple example:

class LoggedPDOSTatement extends PDOStatement    {

function execute ($array)    {
    parent::execute ($array);
    $errors = parent::errorInfo();
    if ($errors[0] != '00000'):
        throw new Exception ($errors[2]);
    endif;
  }

}

so you can use that class instead of PDOStatement:

$this->db->setAttribute (PDO::ATTR_STATEMENT_CLASS, array ('LoggedPDOStatement', array()));

Here a mentioned PDO decorator implementation:

class LoggedPDOStatement    {

function __construct ($stmt)    {
    $this->stmt = $stmt;
}

function execute ($params = null)    {
    $result = $this->stmt->execute ($params); 
    if ($this->stmt->errorCode() != PDO::ERR_NONE):
        $errors = $this->stmt->errorInfo();
        $this->paint ($errors[2]);
    endif;
    return $result;
}

function bindValue ($key, $value)    {
    $this->values[$key] = $value;    
    return $this->stmt->bindValue ($key, $value);
}

function paint ($message = false)    {
    echo '<pre>';
    echo '<table cellpadding="5px">';
    echo '<tr><td colspan="2">Message: ' . $message . '</td></tr>';
    echo '<tr><td colspan="2">Query: ' . $this->stmt->queryString . '</td></tr>';
    if (count ($this->values) > 0):
    foreach ($this->values as $key => $value):
    echo '<tr><th align="left" style="background-color: #ccc;">' . $key . '</th><td>' . $value . '</td></tr>';
    endforeach;
    endif;
    echo '</table>';
    echo '</pre>';
}

function __call ($method, $params)    {
    return call_user_func_array (array ($this->stmt, $method), $params); 
}

}

To log MySQL in WAMP, you will need to edit the my.ini (e.g. under wampbinmysqlmysql5.6.17my.ini)

and add to [mysqld]:

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

Here is a function I made to return a SQL query with «resolved» parameters.

function paramToString($query, $parameters) {
    if(!empty($parameters)) {
        foreach($parameters as $key => $value) {
            preg_match('/(?(?!=))/i', $query, $match, PREG_OFFSET_CAPTURE);
            $query = substr_replace($query, $value, $match[0][1], 1);
        }
    }
    return $query;
    $query = "SELECT email FROM table WHERE id = ? AND username = ?";
    $values = [1, 'Super'];

    echo paramToString($query, $values);

Assuming you execute like this

$values = array(1, 'SomeUsername');
$smth->execute($values);

This function DOES NOT add quotes to queries but does the job for me.

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  [email protected] on your_db
73 Query    SET NAMES utf8mb4
74 Connect  [email protected] on your_db
75 Connect  [email protected] on your_db
74 Quit 
75 Prepare  SELECT email FROM customer WHERE email=? LIMIT ?
75 Execute  SELECT email FROM customer WHERE email='[email protected]' 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!

The problem I had with the solution to catch PDO exemptions for debuging purposes is that it only caught PDO exemptions (duh), but didn’t catch syntax errors which were registered as php errors (I’m not sure why this is, but «why» is irrelevant to the solution). All my PDO calls come from a single table model class that I extended for all my interactions with all tables… this complicated things when I was trying to debug code, because the error would register the line of php code where my execute call was called, but didn’t tell me where the call was, actually, being made from. I used the following code to solve this problem:

/**
 * Executes a line of sql with PDO.
 * 
 * @param string $sql
 * @param array $params
 */
class TableModel{
    var $_db; //PDO connection
    var $_query; //PDO query

    function execute($sql, $params) { 
        //we're saving this as a global, so it's available to the error handler
        global $_tm;
        //setting these so they're available to the error handler as well
        $this->_sql = $sql;
        $this->_paramArray = $params;            

        $this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->_query = $this->_db->prepare($sql);

        try {
            //set a custom error handler for pdo to catch any php errors
            set_error_handler('pdoErrorHandler');

            //save the table model object to make it available to the pdoErrorHandler
            $_tm = $this;
            $this->_query->execute($params);

            //now we restore the normal error handler
            restore_error_handler();
        } catch (Exception $ex) {
            pdoErrorHandler();
            return false;
        }            
    }
}

So, the above code catches BOTH PDO exceptions AND php syntax errors and treats them the same way. My error handler looks something like this:

function pdoErrorHandler() {
    //get all the stuff that we set in the table model
    global $_tm;
    $sql = $_tm->_sql;
    $params = $_tm->_params;
    $query = $tm->_query;

    $message = 'PDO error: ' . $sql . ' (' . implode(', ', $params) . ") n";

    //get trace info, so we can know where the sql call originated from
    ob_start();
    debug_backtrace(); //I have a custom method here that parses debug backtrace, but this will work as well
    $trace = ob_get_clean();

    //log the error in a civilized manner
    error_log($message);

    if(admin(){
        //print error to screen based on your environment, logged in credentials, etc.
        print_r($message);
    }
}

If anyone has any better ideas on how to get relevant info to my error handler than setting the table model as a global variable, I would be happy to hear it and edit my code.

this code works great for me :

echo str_replace(array_keys($data), array_values($data), $query->queryString);

Don’t forget to replace $data and $query by your names

i use this class to debug PDO (with Log4PHP)

<?php

/**
 * Extends PDO and logs all queries that are executed and how long
 * they take, including queries issued via prepared statements
 */
class LoggedPDO extends PDO
{

    public static $log = array();

    public function __construct($dsn, $username = null, $password = null, $options = null)
    {
        parent::__construct($dsn, $username, $password, $options);
    }

    public function query($query)
    {
        $result = parent::query($query);
        return $result;
    }

    /**
     * @return LoggedPDOStatement
     */
    public function prepare($statement, $options = NULL)
    {
        if (!$options) {
            $options = array();
        }
        return new LoggedPDOStatement(parent::prepare($statement, $options));
    }
}

/**
 * PDOStatement decorator that logs when a PDOStatement is
 * executed, and the time it took to run
 * @see LoggedPDO
 */
class LoggedPDOStatement
{

    /**
     * The PDOStatement we decorate
     */
    private $statement;
    protected $_debugValues = null;

    public function __construct(PDOStatement $statement)
    {
        $this->statement = $statement;
    }

    public function getLogger()
    {
        return Logger::getLogger('PDO sql');
    }

    /**
     * When execute is called record the time it takes and
     * then log the query
     * @return PDO result set
     */
    public function execute(array $params = array())
    {
        $start = microtime(true);
        if (empty($params)) {
            $result = $this->statement->execute();
        } else {
            foreach ($params as $key => $value) {
                $this->_debugValues[$key] = $value;
            }
            $result = $this->statement->execute($params);
        }

        $this->getLogger()->debug($this->_debugQuery());

        $time = microtime(true) - $start;
        $ar = (int) $this->statement->rowCount();
        $this->getLogger()->debug('Affected rows: ' . $ar . ' Query took: ' . round($time * 1000, 3) . ' ms');
        return $result;
    }

    public function bindValue($parameter, $value, $data_type = false)
    {
        $this->_debugValues[$parameter] = $value;
        return $this->statement->bindValue($parameter, $value, $data_type);
    }

    public function _debugQuery($replaced = true)
    {
        $q = $this->statement->queryString;

        if (!$replaced) {
            return $q;
        }

        return preg_replace_callback('/:([0-9a-z_]+)/i', array($this, '_debugReplace'), $q);
    }

    protected function _debugReplace($m)
    {
        $v = $this->_debugValues[$m[0]];

        if ($v === null) {
            return "NULL";
        }
        if (!is_numeric($v)) {
            $v = str_replace("'", "''", $v);
        }

        return "'" . $v . "'";
    }

    /**
     * Other than execute pass all other calls to the PDOStatement object
     * @param string $function_name
     * @param array $parameters arguments
     */
    public function __call($function_name, $parameters)
    {
        return call_user_func_array(array($this->statement, $function_name), $parameters);
    }
}

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!

In Debian NGINX environment i did the following.

Goto /etc/mysql/mysql.conf.d edit mysqld.cnf if you find log-error = /var/log/mysql/error.log add the following 2 lines bellow it.

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

To see the logs goto /var/log/mysql and tail -f mysql.log

Remember to comment these lines out once you are done with debugging if you are in production environment delete mysql.log as this log file will grow quickly and can be huge.