>How to enable SQL log on Magento 1

How to enable SQL log on Magento 1

SQL stands for Structured Query language which can be effectively used to store, manipulate, and retrieve data in database. In Magento, it provides a great deal of insight into the behind-the-scenes functionality to debug slow-running queries and other Model-based performance problems. Today, let’s go insight into how to enable logging SQL on Magento 1.x.
To begin with, please open file folder_code/lib/Varien/Db/Adapter/Pdo/Mysql.php in file editor. At line 106:

    /**
     * Write SQL debug data to file
     *
     * @var bool
     */
    protected $_debug               = false;

    /**
     * Minimum query duration time to be logged
     *
     * @var float
     */
    protected $_logQueryTime        = 0.05;

    /**
     * Log all queries (ignored minimum query duration time)
     *
     * @var bool
     */
    protected $_logAllQueries       = false;

    /**
     * Add to log call stack data (backtrace)
     *
     * @var bool
     */
    protected $_logCallStack        = false;

    /**
     * Path to SQL debug data log
     *
     * @var string
     */
    protected $_debugFile           = 'var/debug/pdo_mysql.log';

protected $_debug               = false;

To enable logging SQL change value to “true”.

protected $_logQueryTime        = 0.05;

This set minimum query duration time to be logged. To find slow query change this value to 1.0 seconds.

protected $_logAllQueries       = false;

Change this value to “true” to log everything.

protected $_logCallStack        = false;

This logs a backtrace of methods executed.

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

Logs file will be created at folder var/debug/pdo_mysql.log
The logs executed will be like this:

## 2018-05-20 15:39:03
## 6752 ## QUERY
SQL: SELECT `core_url_rewrite`.* FROM `core_url_rewrite` WHERE (request_path IN (:path0, :path1)) AND (store_id IN(0, 1))
BIND: array (
  ':path0' => 'customer/account/index/',
  ':path1' => 'customer/account/index',
)
AFF: 0
TIME: 0.0004
TRACE: #1 Magento_Db_Adapter_Pdo_Mysql[Varien_Db_Adapter_Pdo_Mysql]#000000006b4f0dd900000000447f845e#->_debugStat(2, 'SELECT `core_url...', array(':path0' => 'customer/account...', ':path1' => 'customer/account...'), &Varien_Db_Statement_Pdo_Mysql#000000006b4f0de600000000447f845e#) called at [lib\Varien\Db\Adapter\Pdo\Mysql.php:509]
#2 Magento_Db_Adapter_Pdo_Mysql[Varien_Db_Adapter_Pdo_Mysql]#000000006b4f0dd900000000447f845e#->query('SELECT `core_url...', array(':path0' => 'customer/account...', ':path1' => 'customer/account...')) called at [lib\Zend\Db\Adapter\Abstract.php:737]
#3 Magento_Db_Adapter_Pdo_Mysql[Zend_Db_Adapter_Abstract]#000000006b4f0dd900000000447f845e#->fetchAll(&Varien_Db_Select#000000006b4f0de700000000447f845e#, array('path0' => 'customer/account...', 'path1' => 'customer/account...')) called at [app\code\core\Mage\Core\Model\Resource\Url\Rewrite.php:151]
#4 Mage_Core_Model_Resource_Url_Rewrite#000000006b4f0dfb00000000447f845e#->loadByRequestPath(&Mage_Core_Model_Url_Rewrite#000000006b4f0dd700000000447f845e#, array('customer/account...', 'customer/account...')) called at [app\code\core\Mage\Core\Model\Url\Rewrite.php:100]
#5 Mage_Core_Model_Url_Rewrite#000000006b4f0dd700000000447f845e#->loadByRequestPath(array('customer/account...', 'customer/account...')) called at [app\code\core\Mage\Core\Model\Url\Rewrite\Request.php:139]
#6 Mage_Core_Model_Url_Rewrite_Request#000000006b4f0dd100000000447f845e#->_rewriteDb() called at [app\code\core\Mage\Core\Model\Url\Rewrite\Request.php:116]
#7 Mage_Core_Model_Url_Rewrite_Request#000000006b4f0dd100000000447f845e#->rewrite() called at [app\code\core\Mage\Core\Controller\Varien\Front.php:165]
#8 Mage_Core_Controller_Varien_Front#000000006b4f0dd300000000447f845e#->dispatch() called at [app\code\core\Mage\Core\Model\App.php:365]
#9 Mage_Core_Model_App#000000006b4f0dcb00000000447f845e#->run(array('scope_code' => '', 'scope_type' => 'store', 'options' => array())) called at [app\Mage.php:683]
#10 Mage::run('', 'store') called at [index.php:83]

We hope that the article is helpful for you to enable SQL log in minutes. Feel free to take a tour around and explore dozens of well-coded Magento extensions here.

 

< Previous Post
Next Post >