How to disable query logging in console while load Doctrine fixtures?

Question

I have a fixtures that loads a huge amount of data and all the time I run into this error:

Fatal error: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 16777224 bytes) in /var/www/html/platform-cm/vendor/doctrine/dbal/lib/Doctrine/DBAL/Logging/DebugStack.php on line 65

[Symfony\Component\Debug\Exception\OutOfMemoryException] Error: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 16777224 bytes)

After research a bit I found this post where I read that logging could be the cause of the issue because AppKernel is instantiated with debug set to true by default and then the SQL commands get stored in memory for each iteration.

The first attempt without disable the debug at AppKernel was run the command as:

doctrine:fixtures:load --no-debug

But I didn't get luck since the same error still.

The second attempt was disable the debug at config_dev.yml but this is not recommended since I am getting ride of every logs but didn't work neither.

monolog:
    handlers:
        main:
            type:   stream
            path:   "%kernel.logs_dir%/%kernel.environment%.log"
            level:  debug
#        console:
#            type:   console
#            bubble: false
#            verbosity_levels:
#                VERBOSITY_VERBOSE: INFO
#                VERBOSITY_VERY_VERBOSE: DEBUG
#            channels: ["!doctrine"]
#        console_very_verbose:
#            type:   console
#            bubble: false
#            verbosity_levels:
#                VERBOSITY_VERBOSE: NOTICE
#                VERBOSITY_VERY_VERBOSE: NOTICE
#                VERBOSITY_DEBUG: DEBUG
#            channels: ["doctrine"]

So, this is how my fixture looks like:

class LoadMsisdn extends AbstractFixture implements OrderedFixtureInterface
{
    public function getOrder()
    {
        return 13;
    }

    public function load(ObjectManager $manager)
    {
        $content = file_get_contents('number.txt');
        $numbers = explode(',', $content);
        shuffle($numbers);

        foreach ($numbers as $key => $number) {
            $msisdn = new Msisdn();
            $msisdn->setMsisdn($number);
            $msisdn->setBlocked((rand(1, 1000) % 10) < 7);
            $msisdn->setOperator($this->getReference('operator-' . rand(45, 47)));

            $this->addReference('msisdn-' . $key, $msisdn);
            $manager->persist($msisdn);
        }

        $manager->flush();
    }
}

How do I disable the logger if I need to do it from EntityManager as shown in a answer on the same post?

$em->getConnection()->getConfiguration()->setSQLLogger(null);

Solution 1

The object manager that is being passed into the load method is an instance of the the entity manager (Doctrine\Common\Persistence\ObjectManager is just an interface that the entity/document/etc managers implement).

This mean that you can use the same command as in your question to nullify the SQL logger like..

$manager->getConnection()->getConfiguration()->setSQLLogger(null);

One thing to note is that the default logging setting for a DBAL connection is %kernel.debug% meaning that, unless you have overridden it in your config, the logging should only happen in the dev environment. I can see you have tried using the --no-debug option but I can only assume that, as the logger is set during the container compilation, that it doesn't unset it to the container not being rebuilt.

---

Symfony 3/4/5 approach in test case

final class SomeTestCase extends KernelTestCase
{
    protected function setUp(): void
    {
        $this->bootKernel('...');

        // @see https://stackoverflow.com/a/35222045/1348344
        // disable Doctrine logs in tests output
        $entityManager = self::$container->get(EntityManagerInterface::class);
        $entityManager->getConfiguration();
        $connection = $entityManager->getConnection();

        /** @var Configuration $configuration */
        $configuration = $connection->getConfiguration();
        $configuration->setSQLLogger(null);
    }
}

Solution 2

I am not sure if it will help with the memory limit issue, but you can also try to change logging (more comfortably) via YAML configuration:

---

Disable Doctrine log channel

(as in your commented code)

monolog:
    handlers:
        main:
            type:   stream
            path:   "%kernel.logs_dir%/%kernel.environment%.log"
            level:  debug
            channels: ["!doctrine"]    # "!event", "!php"

---

or Setting a custom dummy logger

In services.yaml set the class for the default Doctrine logger:

doctrine.dbal.logger:
        class: App\ORM\Doctrine\DBAL\Logging\DummySQLLogger

And enabling it in doctrine.yaml:

doctrine:
    dbal:
        driver: 'pdo_mysql'
        logging: true

The custom logger must implement the interface Doctrine\DBAL\Logging\SQLLogger. The concrete methods startQuery and stopQuery can be left empty, to skip logging; or just use error_log instead (like in my case).

---

I am not sure though if this will prevent the memory exceeded problem. Eventually increase the PHP_MEMORY_LIMIT (via env variables if supported by your environment, or via ini_set()).