Framework: 5.4 Queue:work fails if Mysql server restarts or reloads (using supervisord)

[stevevg]

• Laravel Version: 5.4.23
• PHP Version: 7.0.19
• Database Driver & Version: MySQL 5.7.17

Description:

Note: might be related to #19072, but is not a duplicate, since this is not fixed by #19080.

Using supervisor, when mysql connection is lost, the queue worker stops working and the following is logged to laravel.log:

[2017-05-17 15:44:09] dev.ERROR: Symfony\Component\Debug\Exception\FatalThrowableError: Call to a member function beginTransaction() on null in /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php:108
Stack trace:
#0 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Database/Concerns/ManagesTransactions.php(92): Illuminate\Database\Connection->createTransaction()
#1 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Queue/DatabaseQueue.php(192): Illuminate\Database\Connection->beginTransaction()
#2 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(239): Illuminate\Queue\DatabaseQueue->pop('default')
#3 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(104): Illuminate\Queue\Worker->getNextJob(Object(Illuminate\Queue\DatabaseQueue), 'default')
#4 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(101): Illuminate\Queue\Worker->daemon('database', 'default', Object(Illuminate\Queue\WorkerOptions))
#5 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(85): Illuminate\Queue\Console\WorkCommand->runWorker('database', 'default')
#6 [internal function]: Illuminate\Queue\Console\WorkCommand->fire()
#7 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(29): call_user_func_array(Array, Array)
#8 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(87): Illuminate\Container\BoundMethod::Illuminate\Container\{closure}()
#9 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(31): Illuminate\Container\BoundMethod::callBoundMethod(Object(Illuminate\Foundation\Application), Array, Object(Closure))
#10 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Container/Container.php(531): Illuminate\Container\BoundMethod::call(Object(Illuminate\Foundation\Application), Array, Array, NULL)
#11 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Console/Command.php(182): Illuminate\Container\Container->call(Array)
#12 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/symfony/console/Command/Command.php(264): Illuminate\Console\Command->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
#13 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Console/Command.php(167): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Illuminate\Console\OutputStyle))
#14 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/symfony/console/Application.php(835): Illuminate\Console\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#15 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/symfony/console/Application.php(200): Symfony\Component\Console\Application->doRunCommand(Object(Illuminate\Queue\Console\WorkCommand), Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#16 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/symfony/console/Application.php(124): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#17 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(122): Symfony\Component\Console\Application->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#18 /var/www/vhosts/my-domain.com/subdomains/dev/my-domain/artisan(35): Illuminate\Foundation\Console\Kernel->handle(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))
#19 {main}

Steps To Reproduce:

Add laravel-worker command to supervisor, as per documentation, for example mine is:
command=/opt/plesk/php/7.0/bin/php /var/www/vhosts/my-domain.com/subdomains/dev/area/artisan queue:work --tries=3

Then restart or reload mysql:
service mysqld reload
or
service mysqld restart

Now, despite supervisor is still running laravel-worker...

# supervisorctl status
laravel-worker RUNNING    pid 62316, uptime 0:17:16

...running strace -p 62316 I get:
write(6, "[2017-05-17 15:52:36] dev.ERROR:"..., 4253) = 4253
(File descriptor 6 is laravel.log, and this writes the errors I pasted above).

I was running Laravel 5.4.16, which had this issue, updated to 5.4.23 after reading about fix #19080, but the issue is still here.

[themsaid]

Don't use the database queue driver in production, this was discussed multiple times you can look into previous issues. I recommend that you use redis instead.

---

Update

Try to avoid using the database queue driver when you're having a large queue, it's recommended that you use something like Redis which doesn't have the deadlock problems a database-based driver has.

[laurencei]

@stevevg - can you try manually doing the 3 changes in this PR - and see if it fixes your problem?

https://github.com/laurencei/framework/commit/a5c73220c8ef94eadb0b44769786fa52f35edf97

Please let me know. If it fixes it - I'll submit the PR.

[sisve]

@themsaid ... whaaaaat? Aren't the database queue driver production worthy? Can you please provide links to the issues where this was discussed/mentioned? The documentation totally fails to mention that the database driver is in any way less stable than any of the other choices (except, of course, the sync and null driver).

[stevevg]

@laurencei Your patch seems to do the job.

If I restart mysql service, the laravel-worker process in supervisor gets killed, automatically restarted, and is then able to reconnect to mysql, once it comes back online.

[laurencei]

@stevevg - PR has been merged. Once the version is tagged you can use that.

[themsaid]

Thanks @laurencei

@sisve I suggest that you read the conversation in this issue, starting with https://github.com/laravel/framework/issues/7046#issuecomment-220727045

[sisve]

@themsaid There's a big leap going from (para-phrasing) "the database-based queue driver will run into deadlock issues when you increase the number of workers" or "it scales badly" to "don't use the database queue driver in production". This issue does not mention a heavy workload. In fact, Taylor says in the thread you linked that "The database driver is primarily useful in local development environments or in small production environments" (Ref: https://github.com/laravel/framework/issues/7046#issuecomment-277491802) Small production environments are still a production environments.

Heck, Laravel doesn't support concurrent requests to the same session, and still we don't go around saying "don't use sessions in production"... right? (Ref: https://github.com/laravel/framework/issues/14385)

So, would you consider editing your previous statement/comment and perhaps rephrase it to something like "try avoiding the database worker for heavy workloads and look into redis instead"?

[themsaid]

@sisve done :) Thanks for helping me phrase it better.