CODEWITHSUNDEEP
phpmysqlmagentodebugging
Moon
Moon

Reputation: 22565

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

Upvotes: 18

Views: 32684

Answers (6)

Alana Storm
Alana Storm

Reputation: 166076

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)

Upvotes: 26

Fabian Schmengler
Fabian Schmengler

Reputation: 24551

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!

Upvotes: 22

s-hunter
s-hunter

Reputation: 25816

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';

Upvotes: 1

Mukesh Chapagain
Mukesh Chapagain

Reputation: 25966

$collection->printLogQuery(true);

Upvotes: 5

Claudia Schasiepen
Claudia Schasiepen

Reputation: 531

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

Upvotes: 53

Joe Mastey
Joe Mastey

Reputation: 27119

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

Upvotes: 6

Related Questions