How to keep-alive a Doctrine DBAL connection on a long-running process that is idle for hours awaiting input?

Question

I have a long-running process, a symfony command that uses ReactPhp behind and lasts running for hours.

If the DBAL $connection is not used, it breaks and the next time a request enters and is processed, it throws an exception.

I am wondering if that is not recoverable or doctrine provides a mechanism to "refresh" the connection (some sort of keepalive).

I've run both

php bin/console debug:config doctrine
php bin/console config:dump-reference doctrine

and I had no luck.

Question

Can I add some sort of "keep-alive" directive to send some sort of dummy SELECT 1 every 5 mins to avoid the connection breaking?

Answer 1

you just need to test and reopen your connection.

use Doctrine\Persistence\ManagerRegistry;

class MyService
{

    public function __construct(protected ManagerRegistry $managerRegistry)
    {}

    public function getWhatever(): Collection|array
    {
         $qb = $this->getEntityManagerFor(MyEntity::class)->createQueryBuilder('myentity');
         $qb->andWhere('myentity.whatever = :whatever')
            ->setParameter('whatever', 'what?');

         return $qb->getQuery()->getResult();
    }

    protected function getEntityManagerFor(string $entityClass): EntityManager
    {
        if (!$this->managerRegistry->getManagerForClass($entityClass)->isOpen()) {
            $this->managerRegistry->resetManager();
        }

        return $this->managerRegistry->getManagerForClass($entityClass);
    }
}

this is also usefull if you have an SQL error which closes the manager.