Magento 2 log database queries

Question

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?

Answer 1

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

.

Answer 2

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.

Answer 3

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.

Answer 4

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>

Answer 5

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-&gt;proxy()) {
        $proxy-&gt;log($str);
    }
}

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

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

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



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

    }
    return false;

}


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

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


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

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