Gearman, ZF2, Doctrine2, MySQL, SQLSTATE[HY000]: G

Question

I use ZF2, Doctrine2, MySQL, Gearman. When working Gearman periodically has an error:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away.

I tried these steps to fix the problem:

1) I investigated MySQL queries. Queries haven't any problem. It's simple (without subqueries) and fast. For example, this is EXPLAIN of one of queries when MySQL server has gone away.

+-------------+-------+-------+-----------------------+-----------------------+---------+-------+------+
| select_type | table | type  |     possible_keys     |          key          | key_len |  ref  | rows |
+-------------+-------+-------+-----------------------+-----------------------+---------+-------+------+
| SIMPLE      | t0    | const | UNIQ_8D93D649E7927C74 | UNIQ_8D93D649E7927C74 |     767 | const |    1 |
| SIMPLE      | t13   | const | UNIQ_BA388B79395C3F3  | UNIQ_BA388B79395C3F3  |       5 | const |    1 |
+-------------+-------+-------+-----------------------+-----------------------+---------+-------+------+

2) I tried the version from this post (for reconnect to DB) But it's not help me. And some of my queries was lost.

3) I disabled all Gearman jobs and workers except 3 for testing. I cleaned Gearman queues and restart Gearman server and Gearman workers. But it's not help me.

4) I enabled these settings for MySQL: max_allowed_packet = 500M

   max_connections      = 2000
   max_user_connections = 300
   wait_timeout         = 3600
   net_read_timeout     = 3600

Can someone help? Thanks!

Answer 1

Here is my solution described in my blog post (in Russian, you can translate with Google).

http://seyferseed.ru/php/reshenie-problemy-doctrine-2-i-mysql-server-has-gone-away.html

I'm using this code for solving problem with MySQL server has gone away

public function disconnect()
{
    $this->getEm()->getConnection()->close();
}

public function connect()
{
    $this->getEm()->getConnection()->connect();
}

/**
 * MySQL Server has gone away
 */
public function reconnect()
{
    $connection = $this->getEm()->getConnection();
    if (!$connection->ping()) {

        Debug::vars("MySQL ping failed");

        $this->disconnect();
        $this->connect();

        $this->checkEMConnection($connection);
    }
}

/**
 * method checks connection and reconnect if needed
 * MySQL Server has gone away
 *
 * @param $connection
 * @throws \Doctrine\ORM\ORMException
 */
protected function checkEMConnection($connection)
{
    if (!$this->getEm()->isOpen()) {
        $config = $this->getEm()->getConfiguration();

        $this->em = $this->getEm()->create(
            $connection, $config
        );
    }
}

Answer 2

Each worker script should connect to the database at the start and disconnect at the end. Don't try and hold open the connection as if MySQL times out, then your script won't notice which is why you then get the error.