no reconnection attempted when runnning a db queue and db goes away during commit

[cosmok]

• Laravel Version: 5.3.9
• PHP Version: 7.0.11
• Database Driver & Version: mysqlnd 5.0.12, mysql 5.7.12

Description:

When using the DB Queue Driver I am noticing that when server connection goes away while committing, transactions count remains the same. (function below):

framework/src/Illuminate/Database/Concerns/ManagesTransactions.php

Line 153 in 7212b1e

public function commit()

During the next iteration (of the queue/loop) there are more server connection errors (General error: 2006 MySQL server has gone away) (as the db connection is not reconnected and because connection obj is being reused in daemon mode) (function below):

framework/src/Illuminate/Database/Concerns/ManagesTransactions.php

Line 112 in 7212b1e

} elseif ($this->transactions >= 1 && $this->queryGrammar->supportsSavepoints()) {

The behavior has been verified in 5.3. 5.4 appears to be no different in handling this scenario.

How should I handle this?

[barryvdh]

I'm also getting a similar error on Laravel 5.3.30

I'm running 10 queue workers, and sometimes they seem to crash and take down the mysql server (needs to restart to function again). Not sure if the queue works are the cause or just showing up in the logs as result..

Stacktrace:

exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away'

[2017-02-14 12:38:19] production.ERROR: exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in /home/app/vendor/laravel/framework/src/Illuminate/Database/Connection.php:624 Stack trace: #0 /home/app/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): PDO->exec('SAVEPOINT trans...') #1 /home/app/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(175): Illuminate\Database\Connection->beginTransaction() #2 /home/app/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(175): Illuminate\Queue\DatabaseQueue->pop('default') #3 /home/app/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(146): Illuminate\Queue\Worker->getNextJob(Object(Illuminate\Queue\DatabaseQueue), 'default') #4 /home/app/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(75): Illuminate\Queue\Worker->runNextJob('database', 'default', Object(Illuminate\Queue\WorkerOptions)) #5 /home/app/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(101): Illuminate\Queue\Worker->daemon('database', 'default', Object(Illuminate\Queue\WorkerOptions)) #6 /home/app/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(85): Illuminate\Queue\Console\WorkCommand->runWorker('database', 'default') #7 [internal function]: Illuminate\Queue\Console\WorkCommand->fire()

[barryvdh]

As observation, I had a lot of errors in my logs, every 3 seconds (the sleep duration) about not being able to connect, while the mysql connection was available for other processes. So it seems that no reconnection was attempted but the script kept running (but failing).
A restart of the supervisor processes made it work again.

So it should either kill the queue worker process or restart, not keep failing..

[barryvdh]

So basically; not sure where it started, but now it does the following according to stack trace:

• beginTransaction() calls createTransaction();

  framework/src/Illuminate/Database/Concerns/ManagesTransactions.php

  Line 92 in 7212b1e

  $this->createTransaction();

• Because transaction > 0, it creates a savepoint;

  framework/src/Illuminate/Database/Concerns/ManagesTransactions.php

  Line 113 in 7212b1e

  $this->createSavepoint();

• This fails because it can't connect; throws exception which is not caught.
• $transactions-- is never reached;

  framework/src/Illuminate/Database/Concerns/ManagesTransactions.php

  Line 94 in 7212b1e

  ++$this->transactions;

But probably an error occurred first during the commit;

framework/src/Illuminate/Database/Concerns/ManagesTransactions.php

Line 156 in 7212b1e

$this->getPdo()->commit();

Which didn't reach the transactions change;

framework/src/Illuminate/Database/Concerns/ManagesTransactions.php

Line 159 in 7212b1e

$this->transactions = max(0, $this->transactions - 1);

So should we wrap all $this->getPdo()->... with a try/catch and check causedByLostConnection($e) ?

[cosmok]

This is what I ended up doing in app/Exceptions/Handler.php

use Illuminate\Database\DetectsLostConnections;

inside report function:

if ('cli' === php_sapi_name() && $e instanceof PDOException && $this->causedByLostConnection($e)) {
     sleep(1);
     try {
         DB::reconnect('connection_name');
      } catch (Exception $exceptionOnReConnect) {
      }
}   

[barryvdh]

So is that working reliably? Seems a bit hacky..

[cosmok]

Appears to be working as expected during tests.

[rap2hpoutre]

Not sure but maybe similar: #18384

[barryvdh]

I think this might be fixed with #19080 ?
(I switched to Redis, which also solved it..)