Query in job

[rafi16d]

Hello

I'm using queuesadilla to send SMS and Email from a web interface. When the user send a message in the web interface, the recipients are saved in a database with a "_disturbanceid" and a job was created with the same "_disturbanceid".

The job call the method processSendEmail() with the "_disturbanceid" as agument. I'm using this id to find all the recipient.

[DisturbancesController.php:367] 
$recipients = $tblRecipients->find()->where(['disturbance_id' => $arg['id']]);

It's working, but I have an error when the worker was running for a long time.

After few hour, the worker continue to display Debug: No job! and when a new job was added he display this error :

Warning Error: Error while sending QUERY packet. PID=9404 in [D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Database\Statement\MysqlStatementphp,line 39]

2017-02-13 14:10:38 Warning: Warning (2): Error while sending QUERY packet. PID=9404 in [D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Database\Statement\MysqlStatementphp, line 39]
Trace:
Cake\Error\BaseErrorHandler::handleError() - CORE\src\Error\BaseErrorHandler.php, line 146PDOStatement::execute() - [internal], line ??
Cake\Database\Statement\MysqlStatement::execute() - CORE\src\Database\Statement\MysqlStatement.php, line 39
Cake\Database\Connection::run() - CORE\src\Database\Connection.php, line 276
Cake\Database\Query::execute() - CORE\src\Database\Query.php, line 190
Cake\ORM\Query::_execute() - CORE\src\ORM\Query.php, line 957
Cake\ORM\Query::_all() - CORE\src\Datasource\QueryTrait.php, line 276
Cake\ORM\Query::all() - CORE\src\ORM\Query.php, line 905
Cake\ORM\Query::getIterator() - CORE\src\Datasource\QueryTrait.php, line 132
Exploitation\Controller\DisturbancesController::processSendEmail() - ROOT\plugins\Exploitation\src\Controller\DisturbancesController.php, line 367
josegonzalez\Queuesadilla\Worker\SequentialWorker::perform() -ROOT\vendor\josegonzalez\queuesadilla\src\josegonzalez\Queuesadilla\Worker\SequentialWorker.php,line 99

2017-02-13 14:10:38 Alert: Exception: "SQLSTATE[HY000]: General error: 2006 MySQL server has gone away"
Exception: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away in [D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Database\Statement\MysqlStatement.php, line 39]
2017-02-13 14:10:38 Error: [PDOException] SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
Stack Trace:
#0 D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Database\Statement\MysqlStatement.php(39): PDOStatement->execute(NULL)
#1 D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Database\Connection.php(276): Cake\Database\Statement\MysqlStatement->execute()
#2 D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Database\Query.php(190): Cake\Database\Connection->run(Object(Cake\ORM\Query))
#3 D:\wwwroot\test_extranet_mob_ch\src\Event\QueueListener.php(75): Cake\Database\Query->execute()
#4 D:\wwwroot\test_extranet_mob_ch\vendor\josegonzalez\queuesadilla\src\josegonzalez\Queuesadilla\Event\MultiEventListener.php(27): App\Event\QueueListener->jobException(Object(josegonzalez\Queuesadilla\Event\Event))
#5 [internal function]: josegonzalez\Queuesadilla\Event\MultiEventListener->handle(Object(josegonzalez\Queuesadilla\Event\Event))
#6 D:\wwwroot\test_extranet_mob_ch\vendor\league\event\src\Emitter.php(217): call_user_func_array(Array, Array)
#7 D:\wwwroot\test_extranet_mob_ch\vendor\league\event\src\Emitter.php(169): League\Event\Emitter->invokeListeners('Worker.job.exce...', Object(josegonzalez\Queuesadilla\Event\Event), Array)
#8 D:\wwwroot\test_extranet_mob_ch\vendor\josegonzalez\queuesadilla\src\josegonzalez\Queuesadilla\Event\EventManagerTrait.php(68): League\Event\Emitter->emit(Object(josegonzalez\Queuesadilla\Event\Event))
#9 D:\wwwroot\test_extranet_mob_ch\vendor\josegonzalez\queuesadilla\src\josegonzalez\Queuesadilla\Worker\SequentialWorker.php(62): josegonzalez\Queuesadilla\Worker\Base->dispatchEvent('Worker.job.exce...', Array)
#10 D:\wwwroot\test_extranet_mob_ch\vendor\josegonzalez\cakephp-queuesadilla\src\Shell\QueuesadillaShell.php(22): josegonzalez\Queuesadilla\Worker\SequentialWorker->work()
#11 [internal function]: Josegonzalez\CakeQueuesadilla\Shell\QueuesadillaShell->main()
#12 D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Console\Shell.php(472): call_user_func_array(Array, Array)
#13 D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Console\ShellDispatcher.php(227): Cake\Console\Shell->runCommand(Array, true, Array)
#14 D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Console\ShellDispatcher.php(182): Cake\Console\ShellDispatcher->_dispatch(Array)
#15 D:\wwwroot\test_extranet_mob_ch\vendor\cakephp\cakephp\src\Console\ShellDispatcher.php(128): Cake\Console\ShellDispatcher->dispatch(Array)
#16 D:\wwwroot\test_extranet_mob_ch\bin\cake.php(34): Cake\Console\ShellDispatcher::run(Array)
#17 {main}

2017-02-13 14:10:38 Info: Shutting down
2017-02-13 14:10:38 Info: Worker shutting down after running 10825 iterations in 10993s

I'm using MysqlEngine and the connection is set to 'persistent' => false.

It seems happend when I'm doing a query in the job method that is called by queuesadilla :

[DisturbancesController.php:367] 
$recipients = $tblRecipients->find()->where(['disturbance_id' => $arg['id']]);

Maybe I was going in the wrong way, what would be the best way to do this ?

Sorry for my english, I'm from Switzerland :)

[cleptric]

You have to manually disconnect the CakePHP MySQL connection after a successful job. The problem is, that you're running into the wait_timeout of MySQL after 8h by default. CakePHP doesn't know that the connection died. Use sth like

        $worker->attachListener('Worker.job.success', function ($event) {
            ConnectionManager::get('default')->disconnect();
        });

in a custom QueuesadillaShell class

[josegonzalez]

@cleptric docs pull request? You can also attach using the global event manager in a config/events.php that is only attached in the cli context?