Mysql error: Mysql server has gone away

Question

I'm developing a Symfony 3.1 application which basically puts jobs messages in RabbitMQ queue and with supervisors, I spawn some processes that consume RabbitMQ messages and do the job which is managed in Symfony. The consumer's job is to decode the message and create a new Symfony\Bundle\FrameworkBundle\Console\Application injecting the same kernel and executing a Symfony command which has database operations both read/write.

Here begins the problem. Usually, at the same time, I usually leave spawned 4 worker processes to consume the messages. At the same time, all the consumers do the same database operations just with different data so basically all the processes execute the same Symfony RabbitMQ consumer. Sometimes it works all fine no problem whatsoever, but sometimes MySQL returns the error "MySQL server has gone away", and usually to fix this I have to restart supervisor service so the worker processes are killed and spawned again. Usually after restarting it works fine for a few hours but its really unpredictable pattern.

It's not a MySQL connection timeout since I'm sure they are small operations and I've increased the connect_timeout of MySQL. I've also increased the wait_timeout to 2 days so basically, this doesn't solve the problem.

Answer 1

We were facing a similar problem with an application processing via command line tried tweaking the mysql all variables were in the proper range but the 'Mysql gone away error' kept popping up.

The only fix that worked for us was pinging the connection before any query in the application and closing the connection which was hanging around and inactive and then create a new connection.

if ($this->em->getConnection()->ping() === FALSE) {
    $this->em->getConnection()->close(); // Close any previous connection as they are not active
    $this->em->getConnection()->connect(); // Get a fresh connection
}
$reseller = $this->em->getRepository(User::class)->findOneBy(['id' => $id]);

Also mentioned in the doctrine library: https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Connection.php#L1649

Answer 2

It can be some of this problems.

1. max packet size you can increase it from configuration max_allowed_packet max_allowed_packet

2. Or it can be timeout problem, because you open php process and it never ended, you can implement some try catch block, and when you have a mysql error create a new mysql connection.