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

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 2

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 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->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.