20

In magento 1.x I use the n98-magerun tool to get a log file for all DB Queries:

n98-magerun.phar dev:log:db [--on] [--off]

Is it possible to log database queries in Magento2?

5 Answers 5

19

you can add in one of your modules in the di.xml file this:

<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File"/>

The Magento\Framework\DB\Adapter\Pdo\Mysql class that is used to run the actual queries has a logger member Magento\Framework\DB\LoggerInterface.
By default, the preference for this dependency is set in app/etc/di.xml

<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\Quiet"/>

this Magento\Framework\DB\Logger\Quiet does nothing.

<?php
/**
 * Copyright © 2015 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Magento\Framework\DB\Logger;

class Quiet implements \Magento\Framework\DB\LoggerInterface
{
    /**
     * {@inheritdoc}
     */
    public function log($str)
    {
    }

    /**
     * {@inheritdoc}
     */
    public function logStats($type, $sql, $bind = [], $result = null)
    {
    }

    /**
     * {@inheritdoc}
     */
    public function critical(\Exception $e)
    {
    }

    /**
     * {@inheritdoc}
     */
    public function startTimer()
    {
    }
}

change the preference to Magento\Framework\DB\Logger\File and you should see the queries logged in var/debug/db.log.
Magento comes with these 2 loggers (Quiet and File) buy default, but you can create your own in case you need a different way of logging queries.

3
38

At least in newer versions (looking at a 2.2.1 here and now) you can do

bin/magento dev:query-log:enable

and have extensive logs in var/debug/db.log. Dont forget to switch logging off again with

bin/magento dev:query-log:disable

.

2
  • This command shows SQL Queries and trace of classes. We can include or exclude info adding options to the command. See --help for more information Jan 15, 2022 at 22:34
  • @Felix Thanks , this was helpful. Jan 8, 2023 at 20:12
4

To set logAllQueries=true you can add the following code to app/etc/di.xml to change __construct() parameters of Magento\Framework\DB\Logger\File:

<preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File"/>
<type name="Magento\Framework\DB\Logger\File">
    <arguments>
        <argument name="logAllQueries" xsi:type="boolean">true</argument>
    </arguments>
</type>

You can also change the other parameters $debugFile, $logQueryTime and $logCallStack in that way.

1

Here is my di.xml

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <preference for="Magento\Framework\DB\LoggerInterface" type="Magento\Framework\DB\Logger\File"/>

    <type name="Magento\Framework\DB\Logger\File">
        <arguments>
            <argument name="logAllQueries" xsi:type="boolean">true</argument>
            <argument name="debugFile" xsi:type="string">sql.log</argument>
        </arguments>
    </type>

</config>
1

Because I couldn't do a setting change and because I only needed it for one thing, I decided to temp modify the \Magento\Framework\DB\Logger\Quiet class on our admin server.

<?php
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
namespace Magento\Framework\DB\Logger;

class Quiet implements \Magento\Framework\DB\LoggerInterface
{

    protected $routeName = 'otc_orders_index';
    protected $userId = 9;

    protected $objectManager;
    protected $fileLogger;
    protected $request;

    /**
     * {@inheritdoc}
     */
    public function log($str)
    {
        if($proxy = $this->proxy()) {
            $proxy->log($str);
        }
    }

    /**
     * {@inheritdoc}
     */
    public function logStats($type, $sql, $bind = [], $result = null)
    {
        if($proxy = $this->proxy()) {
            $proxy->logStats($type, $sql, $bind, $result);
        }
    }

    /**
     * {@inheritdoc}
     */
    public function critical(\Exception $e)
    {
        if($proxy = $this->proxy()) {
            $proxy->critical($e);
        }
    }

    /**
     * {@inheritdoc}
     */
    public function startTimer()
    {
        if($proxy = $this->proxy()) {
            $proxy->startTimer();
        }
    }



    protected function proxy() {
        try {
            $request = $this->getObjectmanager()->get(\Magento\Framework\App\RequestInterface::class);
            if($request && $request->getFullActionName() === $this->routeName && $this->isUser()) {
                return $this->getFileLogger();
            }
        }
        catch (\Exception $exception) {

        }
        return false;

    }


    protected function isUser() {
        $authSession = $this->getObjectmanager()->get(\Magento\Backend\Model\Auth\Session::class);

        if($authSession && $authSession->getUser() && $authSession->getUser()->getId() == $this->userId) {
            return true;
        }
        return false;
    }


    /**
     * @return \Magento\Framework\App\ObjectManager
     */
    protected function getObjectmanager() {
        if(!$this->objectManager) {
            $this->objectManager = \Magento\Framework\App\ObjectManager::getInstance();
        }
        return $this->objectManager;
    }

    protected function getFileLogger() {
        if(!$this->fileLogger) {
            $fileFactory = $this->getObjectmanager()->get(\Magento\Framework\DB\Logger\FileFactory::class);
            $this->fileLogger = $fileFactory->create([
                'logAllQueries' => true,
                'debugFile' => 'log/sql.log'
            ]);
        }
        return $this->fileLogger;
    }
}

Logged to var/log/sql.log for my selected route/userId.

Obviously, this isn't great practice but it's quick. Make sure to restore the original, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.