26

Want to get all Sql queries during the page load.

can any one help me on How to log all Magento 2 SQL queries ?

Krishna ijjada
  • 8,927
  • 6
  • 41
  • 70

3 Answers3

50
bin/magento dev:query-log:enable

. (copied my answer from Magento 2 log database queries)

Felix
  • 999
  • 8
  • 8
  • 5
    This is the correct answer for Magento 2.2 and beyond. As mentioned in the DevDocs page, the queries get logged to var/debug/db.log (not a file in var/log) – Erik Hansen Nov 07 '19 at 16:31
36

In your app/etc/di.xml, replace the line:

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

with

<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">log/sql.log</argument>
  </arguments>
</type>

Flush Magento cache (in backend, or by executing bin/magento cache:flush) after this.

Log file is located here: var/log/sql.log

Mykhailo Shatilov
  • 550
  • 6
  • 23
Siju Joseph
  • 815
  • 8
  • 6
  • 6
    I am not sure, but this might not work anymore in 2.2 ; at least it doesnt in my case. – Felix Nov 14 '17 at 16:45
  • 3
    In latest version of magento (i'm checking on 2.3.1) You can use this: php bin/magento dev:query-log:enable --include-all-queries=1 --query-time-threshold=1 --include-call-stack=false (as mentioned by @Felix below) – Siju Joseph Jun 12 '19 at 07:44
8

Alternative (to the answer of Siju Joseph),

if you don't want to change your magento config, you can also configure mysql itself: activate slow.log and set it to 0 seconds.

Therefore edit /etc/mysql/my.cnf and set slow_query_log to 'on' and long_query_time to '0'.

This will result in all mysql queries getting logged.

Felix
  • 999
  • 8
  • 8
Jonas Hünig
  • 491
  • 3
  • 7