How to get Doctrine to log queries in Symfony2

Question

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!

In dev this is automatically done by symfony. In production this is not reccommended as it's a slow process. — Stev
@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
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 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) dev.mysql.com/doc/refman/5.1/en/slow-query-log.html — Stev

Stev Stev · Accepted Answer · 2015-04-06T13:37:10

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

How to get Doctrine to log queries in Symfony2

2 Answers