How do I print all the queries in Magento?

问题:

Is it possible to display all the query strings in Magento? I really like to see what queries are executed.

Thanks

回答1:

I'm not 100% sure this will catch every query, but most run through the query method Zend_Db_Adapter_Abstract query method in

lib/Zend/Db/Adapter/Abstract.php

With that in mind, you could temporarily add some debugging statements (to a copy you make in app/code/local/Mage to be safe)

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";
    var_dump($bind);

If you need to catch them all, you'd be better off doing this at the MySQL level (which isn't always possible depending on your host/IT situation)

回答2:

In Varien_Db_Adapter_Pdo_Mysql

Magento 1.4 : lib/varien/Db/Adapter/Pdo/Mysql.php

set

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

and (if nor already there) create the folder defined in

protected $_debugFile           = 'var/debug/sql.txt';

Give read / write permission

回答3:

Activate the Zend SQL Profiler with the following node in your local.xml

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

Then you can access the profiler somewhere in your code and retrieve a lot of informations about all executed queries:

$profiler = Mage::getSingleton('core/resource')->getConnection('core_write')->getProfiler();

To simply output all queries:

print_r($profiler->getQueryProfiles());

You can add these two lines at the end of index.php to see all queries at the bottom of each page. Be aware that this will break AJAX requests that return a JSON response, so you might consider logging the queries instead of printing them, with this code (again, add it at the end of index.php):

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

Then you will find all queries in var/log/queries.log

Don't forget to remove the lines again after you finished debugging!

回答4:

The queries will vary significantly depending on your activities. If you have some control over your MySQL server, try turning on query logging:

set global general_log = on

Then you can get the SQL log to see queries. As a word or warning, Magento tends to execute dozens of queries on every page load, and hundreds to save an object.

Thanks, Joe

回答5:

$collection->printLogQuery(true);

回答6:

Turn on the MySQL logging will sure log all queries transactions. Here is how you can turn on the logging on.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

Then if you want to log to the table mysql.general_log, run these queries:

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

Run these if you want to log to the file:

SET GLOBAL log_output = "FILE"; 
SET GLOBAL general_log = 'ON';