MySQL gone away error with a proposed solution

[monteiro]

@rosstuck first of all, thanks a lot for the amazing work with tactician.

Problem

Before making a PR, wanted to ask you if this makes sense.

When we use workers, if the worker doesn't do a query to the MySQL database (for example) for more than 8 hours, the connection is expired and the next time you query the database MySQL will return you "MySQL gone away" with a DBAL exception.

The connection to the database is controlled by the WAIT_TIMEOUT parameter. In order to test the issue is to for example on your MySQL client:

2 seconds timeout: SET GLOBAL wait_timeout=2;

If you do a sleep before executing the command bus, you will see that doctrine-dbal will fail querying the database with the MySQL gone away error.

Proposed solution

Create a new DBAL middleware, inspired by the symfony messenger DoctrinePingConnectionMiddleware and on my specific usecase I will use a specific middleware for console commands and another one without this middleware for http requests.

final class DoctrinePingConnectionMiddleware implements Middleware
{
    /**
     * @var Connection
     */
    private $connection;
    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    public function execute($command, callable $next)
    {
        if (!$this->connection->ping()) {
            $this->connection->close();
            $this->connection->connect();
        }

        return $next($command);
    }
}

The biggest advantage using this approach is that we don't let the worker to fail because we verify the connection every time.

What do you think? Is it worth doing a PR for this?

[rosstuck]

I dig it, PR away!