Insights / Technology + Platforms

PUBLISHED: Oct 18, 2009 2 min read

Logging all SQL in Magento

I’ve been looking for a way to log all the sql that Magento is running for debugging purposes. There are a number of logging mechanisms built in to Magento, but none that would allow you to log all the sql that’s being run. Finally, I’ve found a simple change that can be made to a core file (I know, not ideal because it will get overwritten when you upgrade Magento – but it’s only a few lines in one file).

The Change

1. Open the file <magentoroot>/lib/Varien/Db/Adapter/Pdo/Mysql.php.

2. Add the following lines:

$code = 'SQL: ' . $sql . "rn";
if ($bind) {
     $code .= 'BIND: ' . print_r($bind, true) . "rn";
$this->_debugWriteToFile("[".date('Y-m-d H:i:s')."] ".$code);

Add it to the “query” function as shown below:

public function query($sql, $bind = array())

     try {
          $sql = (string)$sql;
          if (strpos($sql, ':') !== false || strpos($sql, '?') !== false) {
               $this->_bindParams = $bind;
               $sql = preg_replace_callback('#((['"])((2)|((.*?[^\])2)))#', array($this, 'proccessBindCallback'), $sql);
               $bind = $this->_bindParams;

          $code = 'SQL: ' . $sql . "rn";
          if ($bind) {
               $code .= 'BIND: ' . print_r($bind, true) . "rn";
          $this->_debugWriteToFile("[".date('Y-m-d H:i:s')."] ".$code);

          $result = parent::query($sql, $bind);
     catch (Exception $e) {
          $this->_debugStat(self::DEBUG_QUERY, $sql, $bind);
     $this->_debugStat(self::DEBUG_QUERY, $sql, $bind, $result);
     return $result;

The Log

By default your sql will be logged to:



1. As mentioned above, the file will likely be overwritten during a Magento upgrade.

2. There’s nothing that will turn logging off (except a Magento upgrade).  So you need to be careful that this is a temporary setting for debugging only.

“e-commerce done right