Catch and log mysql queries executed by Magento

Below are 4 different ways to catch and log mysql queries executed by Magento.

1. Put a print statement in the function query in MageHome/lib/Zend/Db/Adapter/Abstract.php

    /**
     * Prepares and executes an SQL statement with bound data.
     *
     * @param  mixed  $sql  The SQL statement with placeholders.
     *                      May be a string or Zend_Db_Select.
     * @param  mixed  $bind An array of data to bind to the placeholders.
     * @return Zend_Db_Statement_Interface
     */
    public function query($sql, $bind = array())
    {
        // connect to the database if needed
        $this->_connect();

        // is the $sql a Zend_Db_Select object?
        if ($sql instanceof Zend_Db_Select) {
            if (empty($bind)) {
                $bind = $sql->getBind();
            }

            $sql = $sql->assemble();
        }
		echo $sql . "\n<br />\n";

2. Set the variables $_debug, $_logAllQueries to true in MageHome/lib/varien/Db/Adapter/Pdo/Mysql.php
Set these variables to true

protected $_debug               = true;
protected $_logAllQueries       = true;

Find the variable $_debugFile, it will tell you the log file for the logs. The var is the var directory in Magento base directory.

protected $_debugFile           = 'var/debug/pdo_mysql.log';

3. Activiate the Zend SQL Profiler.
First, add 1 in app/etc/local.xml

<resources>
 <default_setup>
  <connection>
   <profiler>1</profiler>

The you can print the queries executed anywhere by

$profiler = Mage::getSingleton('core/resource')->getConnection('core_write')->getProfiler();
echo print_r($profiler->getQueryProfiles(),true);

4. Turn on MySQL logging to log every query request
To turn on the logging to log to a file. Place the following in the my.cnf file or my.ini file if on windows, and restart MySQL.

log = /path/to/your/logfile.log

Run the query below to log to the table mysql.general_log,

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

Run the query below to log to file.

SET GLOBAL log_output = "FILE"; 
SET GLOBAL general_log_file = /path/to/your/logfile.log
SET GLOBAL general_log = 'ON';

Search within Codexpedia

Custom Search

Search the entire web

Custom Search