16

I'm pretty new to Symfony2, and I'm looking for a way to log SQL queries (including timings) to the same log file as the rest of my application.

From what I can determine from the documentation this should all work out of the box, but after a few hours of trying I can't figure out what I'm doing wrong.

config_dev.yml

monolog:
    handlers:
        doctrine:
            action_level: debug
            type: stream
            path: %kernel.logs_dir%/%kernel.environment%_doctrine.log
            channels: doctrine

config.yml

# Doctrine Configuration
doctrine:
    dbal:
        driver:   "%database_driver%"
        host:     "%database_host%"
        port:     "%database_port%"
        dbname:   "%database_name%"
        user:     "%database_user%"
        password: "%database_password%"
        charset:  UTF8
        logging:  true
        profiling:  true

    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        auto_mapping: true

I get no log file generated at all. My other logging handler works fine (not listed here).

I'm wondering where I've gone wrong here, but also whether this is really the right approach or if I should implement a new class which implements the SQL Logger, as mentioned here: http://vvv.tobiassjosten.net/symfony/logging-doctrine-queries-in-symfony2/

But I've no idea how to plug that in via the configuration/services in order to make it apply project-wide (I don't want to have to call it in every Controller, for example).

Many thanks for any help!

Andy Raines
  • 297
  • 1
  • 3
  • 9
  • In dev this is automatically done by symfony. In production this is not reccommended as it's a slow process. – Stev Apr 06 '15 at 13:29
  • @Stev - when you say "slow" do you have any kind of metrics to back that up with? I consider logging database queries to be absolutely necessary for analysing slow performing sites and heavy DB load, and I hear this said a lot about logging being slow. My assumption is that we are talking about microseconds here, rather than milliseconds or tens of milliseconds? – Andy Raines Apr 06 '15 at 15:24
  • I don't personally have metrics, but file writing it's considered a slow operation. And as the file size increases then it will get slower, and even if you build up with a rotation system (like the OS logging) you'll still affect your app performance. From my experience I chose to use tools like http://newrelic.com/ to monitor slow queries. There's also a config for MySQL (but I'm sure there's one for any DB engine) to log slow queries (and you can choose what slow means to you, like >50ms is slow) https://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html – Stev Apr 06 '15 at 19:22

2 Answers2

21

If you are really sure that you need to log doctrine 2 queries in production then you can set this up in the configs for doctrine. 

connections:
        # A collection of different named connections (e.g. default, conn2, etc)
        default:
                # when true, queries are logged to a "doctrine" monolog channel
            logging: true

http://symfony.com/doc/current/reference/configuration/doctrine.html

And config monolog to log doctrine like explained in the docs: http://symfony.com/doc/current/cookbook/logging/channels_handlers.html

A similar issue can be found at symfony 2.4 can't get the doctrine channel in prod environment

Community
  • 1
  • 1
Stev
  • 1,062
  • 11
  • 23
  • Awesome - thanks. I completely missed why this wasn't working (basically had an override in another config file), but this is definitely the correct answer. – Andy Raines Apr 06 '15 at 15:23
  • 1
    This question is from 2015 and for Symfony 2. Of course the answer doesn't work for 2020 on Symfony 5. But the solution is as simple. See the "logging" key under doctrine -> dbal, which you can set to true to always enable logging. https://symfony.com/doc/current/reference/configuration/doctrine.html – Stev Jun 08 '20 at 12:59
3

Full example of config:

config/packages/dev/doctrine.yaml:

doctrine:
    dbal:
        connections:
            default:
                logging: true

If you still don't have monolog, install it: composer require symfony/monolog-bundle and you should start getting all SQL queries in the log file: var/log/dev.log

Ilya Levin
  • 351
  • 3
  • 9