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
[code language=”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";
[/code]

2. Set the variables $_debug, $_logAllQueries to true in MageHome/lib/varien/Db/Adapter/Pdo/Mysql.php
Set these variables to true
[code language=”php”]
protected $_debug = true;
protected $_logAllQueries = true;
[/code]
Find the variable $_debugFile, it will tell you the log file for the logs. The var is the var directory in Magento base directory.
[code language=”php”]
protected $_debugFile = ‘var/debug/pdo_mysql.log’;
[/code]

3. Activiate the Zend SQL Profiler.
First, add 1 in app/etc/local.xml
[code language=”xml”]
<resources>
<default_setup>
<connection>
<profiler>1</profiler>
[/code]

The you can print the queries executed anywhere by
[code language=”php”]
$profiler = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’)->getProfiler();
echo print_r($profiler->getQueryProfiles(),true);
[/code]

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.
[code language=”text”]
log = /path/to/your/logfile.log
[/code]

Run the query below to log to the table mysql.general_log,
[code language=”sql”]
SET GLOBAL log_output = ‘TABLE’;
SET GLOBAL general_log = ‘ON’;
[/code]

Run the query below to log to file.
[code language=”sql”]
SET GLOBAL log_output = "FILE";
SET GLOBAL general_log_file = /path/to/your/logfile.log
SET GLOBAL general_log = ‘ON’;
[/code]