8000 5.4 Queue:work fails. MySQL server has gone away · Issue #19072 · laravel/framework · GitHub
[go: up one dir, main page]
Skip to content

5.4 Queue:work fails. MySQL server has gone away #19072

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
Broutard opened this issue May 5, 2017 · 10 comments
Closed

5.4 Queue:work fails. MySQL server has gone away #19072

Broutard opened this issue May 5, 2017 · 10 comments

Comments

@Broutard
Copy link
Contributor
Broutard commented May 5, 2017
  • Laravel Version: 5.4.19
  • PHP Version: 5.6.30
  • Database Driver & Version: Mysql 5.6

Description:

Closed issue #19005 is not fixed.

exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Connection.php:319
Stack trace:
#0 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Connection.php(319): PDO->prepare('select * from `...')
#1 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Connection.php(640): Illuminate\Database\Connection->Illuminate\Database\{closure}('select * from `...', Array)
#2 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Connection.php(607): Illuminate\Database\Connection->runQueryCallback('select * from `...', Array, Object(Closure))
#3 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Connection.php(326): Illuminate\Database\Connection->run('select * from `...', Array, Object(Closure))
#4 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(1710): Illuminate\Database\Connection->select('select * from `...', Array, false)
#5 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(1694): Illuminate\Database\Query\Builder->runSelect()
#6 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(452): Illuminate\Database\Query\Builder->get(Array)
#7 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(436): Illuminate\Database\Eloquent\Builder->getModels(Array)
#8 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Concerns/BuildsQueries.php(71): Illuminate\Database\Eloquent\Builder->get(Array)
#9 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(262): Illuminate\Database\Eloquent\Builder->first(Array)
#10 /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(292): Illuminate\Database\Eloquent\Builder->find(1507, Array)
#11 /var/www/project1/vendor/laravel/framework/src/Illuminate/Queue/SerializesAndRestoresModelIdentifiers.php(46): Illuminate\Database\Eloquent\Builder->findOrFail(1507)
#12 /var/www/project1/vendor/laravel/framework/src/Illuminate/Queue/SerializesModels.php(42): App\Jobs\UpdatePage->getRestoredPropertyValue(Object(Illuminate\Contracts\Database\ModelIdentifier))
#13 [internal function]: App\Jobs\UpdatePage->__wakeup()
#14 /var/www/project1/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(38): unserialize('O:19:"App\\Jobs\\...')
#15 /var/www/project1/vendor/laravel/framework/src/Illuminate/Queue/Jobs/Job.php(69): Illuminate\Queue\CallQueuedHandler->call(Object(Illuminate\Queue\Jobs\RedisJob), Array)
#16 /var/www/project1/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(291): Illuminate\Queue\Jobs\Job->fire()
#17 /var/www/project1/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(258): Illuminate\Queue\Worker->process('redis', Object(Illuminate\Queue\Jobs\RedisJob), Object(Illuminate\Queue\WorkerOptions))
#18 /var/www/project1/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(110): Illuminate\Queue\Worker->runJob(Object(Illuminate\Queue\Jobs\RedisJob), 'redis', Object(Illuminate\Queue\WorkerOptions))
#19 /var/www/project1/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(102): Illuminate\Queue\Worker->daemon('redis', 'updatePage', Object(Illuminate\Queue\WorkerOptions))

In Illuminate/Database/Connection.php line 608, run() seems to don't catch all exceptions.
In this example, the PDOException is not catched by the catch (QueryException $e) so handleQueryException() is not executed.

Steps To Reproduce:

  1. Start a Queue Worker using command php artisan queue:work
  2. Force or wait the connection be dropped (restart mysql server is a way to do it)
  3. You will see errors being thrown every second, and the queue worker will not work again.
@Broutard Broutard mentioned this issue May 5, 2017
Closed
@iget-master
Copy link

Thanks @Broutard. To laravel staff: I can work on a PR to fix this issue if you confirm that's a bug.

@Broutard
Copy link
Contributor Author
Broutard commented May 5, 2017 •
edited
Loading

Sorry I have just seen that there are two Exceptions thrown :

exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away' in /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Connection.php:319

[...]

Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away (SQL: select * from `pages` where `pages`.`id` = 1507 limit 1)' in /var/www/project1/vendor/laravel/framework/src/Illuminate/Database/Connection.php:647

So a QueryException is well thrown.

But, this issue is still valid, because if you run artisan queue:work (a deamon), and if you use transactions, handleQueryException() doesn't reconnect the database when a lost connection happens :

protected function handleQueryException($e, $query, $bindings, Closure $callback)
    {
        if ($this->transactions >= 1) {
            throw $e;
        }

        return $this->tryAgainIfCausedByLostConnection(
            $e, $query, $bindings, $callback
        );
    }

The jobs workers are still running but are "down"...

Maybe you should add this in the documentation!

@iget-master
Copy link

Maybe a fast solution is just kill the Queue Worker when this exception occurs, and the supervisor take cares of restarting it as already suggested on Laravel Docs

@iget-master
Copy link

At Illuminate\Queue\Worker the method runJob:

 protected function runJob($job, $connectionName, WorkerOptions $options)
 {
     try {
         return $this->process($connectionName, $job, $options);
     } catch (Exception $e) {
         $this->exceptions->report($e);
     } catch (Throwable $e) {
         $this->exceptions->report(new FatalThrowableError($e));
     }
 }

Note that when an exception occours, the exeption is reported but is not throwed again.
My suggestion (if the problem could not be solved on Illuminate\Database\Connection end, is to catch any PDOException that might hit this try-catch block and force the worker to restart after n occurences.

     protected function runJob($job, $connectionName, WorkerOptions $options)
     {
         try {
             return $this->process($connectionName, $job, $options);
         } catch (PDOException $e) {
             $this->databaseExceptionsCount++;
         } catch (Exception $e) {
             $this->exceptions->report($e);
         } catch (Throwable $e) {
             $this->exceptions->report(new FatalThrowableError($e));
         }
     }

and update stopIfNecessary method:

    /**
     * Stop the process if necessary.
     *
     * @param  WorkerOptions  $options
     * @param  int  $lastRestart
     */
    protected function stopIfNecessary(WorkerOptions $options, $lastRestart)
    {
        if ($this->shouldQuit) {
            $this->kill();
        }

        if ($this->memoryExceeded($options->memory)) {
            $this->stop(12);
        } elseif ($this->queueShouldRestart($lastRestart)) {
            $this->stop();
        } elseif ($this->databaseExceptionsCount > 3) {
            $this->stop();
        }
    }

Note that I'm not sure if is necessary to wait for databaseExceptionsCount hit n times. Maybe just restarting everytime a PDOException leak would be fine.

@Broutard
Copy link
Contributor Author
Broutard commented May 5, 2017

Not tested yet, but I think we can do this in our jobs :

  • add \Illuminate\Database\DetectsLostConnections trait
  • try...catch all the handle() method like :
try{
   // my job code
} catch(\Illuminate\Database\QueryException $e) {
   if($this->causedByLostConnection($e) {
        exit(1);
   }
}

iget-esoares added a commit to iget-master/framework that referenced this issue May 5, 2017
@iget-esoares
Fixes laravel#19072
ddf6d4a
iget-esoares added a commit to iget-master/framework that referenced this issue May 5, 2017
@iget-esoares
Fixes laravel#19072
46423e6
@iget-master iget-master mentioned this issue May 5, 2017
Closed
@iget-master
Copy link

I've suggested a fix on PR #19078 . @Broutard could you please test it on you environment? I've tested on a fictitious application and worked fine. The Queue Worker dies (with error code 1) and supervisord restart it

@Broutard
Copy link
Contributor Author
Broutard commented May 5, 2017 •
edited
Loading

@iget-master, it doesn't work.
You have forget the runJob() method (getNextJob only retreive the next job to handle)

PR #19080 fix this issue

@Broutard Broutard mentioned this issue May 5, 2017
Merged
@Broutard
Copy link
Contributor Author
Broutard commented May 9, 2017 •
edited
Loading

Thanks to @taylorotwell
This issue can be closed, it is tested and it works

@iget-master
Copy link

Thanks @Broutard for the fix!

@themsaid themsaid closed this as completed May 9, 2017
@stevevg stevevg mentioned this issue May 17, 2017
Closed
@xuantianyingjie
Copy link
xuantianyingjie commented Aug 16, 2017 •
edited
Loading

最近遇到了同样的问题，我尝试了很多次，今天我测试了，我发现这个函数'tryAgainIfCausedByLostConnection'，但是我不知道为什么当数据库连接超时时是没用的。 然后我检查错误的日志，发现'Laravel Queue'更新数据时使用事务，在处理事务时，它不会检测数据库链接是否有效，并直接引发异常。 在队列更新操作之前执行数据库查询时，'tryAgainIfCausedByLostConnection'方法工作并重新连接数据库。
Recently I encountered the same problem, I tried many times, today I tested it, I found this function 'tryAgainIfCausedByLostConnection', but I do not know why it is useless when the database connection times out. Then I check the wrong log and find that 'Laravel Queue' uses transaction to update the data, and when processing the transaction, it does not detect whether the database link is valid and throws an exception directly. When I executed a database query before the queue update operation, the 'tryAgainIfCausedByLostConnection' method worked and reconnected the database. Forgive me that I can't speek English very well.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@Broutard @themsaid @iget-master @xuantianyingjie
0