Queue just stop executing job and therefore (MySQL server has gone away)

[YanAlex]

Just a regular case of using queue.

Normal behavior, with no worker hanging.

I get MySQL server has gone away.

yii queue/listen --verbose --color --isolate=0 if I use isolated mode It can work about 6 days and dies, if I use isolated=1 It dies every day. Having default TTR 30

IMPORTANT there no errors before I push ENTER button on console, only after intercation with console this error shown, otherway I just see there nothing happend and can detect process is down only by checking MYSQL process exists. . I assume worker didnt get signal that process gone and wait for it infinitely until wait_timeout reached

SHOW VARIABLES WHERE variable_name LIKE '%timeout%'

yii queue/listen --verbose --color --isolate=0

CreateReport.php

	public function execute($queue)
    {
		\Yii::info([
			'status' => 'started',
			'id_gtin' => $this->id_gtin,
		], 'create');
		
		$model = new \app\models\Report(); 
		$model->id_gtin = $this->id_gtin;
		$model->id_type = $this->id_type;
		$model->date_start = $this->date_start;
		$model->date_end = $this->date_end;
		
		$model->setBatches($this->id_batches);
		
		// When creating model we do HTTP request to outer API, and waiting for response, only after we save model.
		if ($model->save()) { ... }

As you can see there is a Yii log should be written right after executing started, but I didnt see this log, so the job didnt executed at all, but has a flag with 1 attemp.

MYSQL LOG

php.ini

t.

console.php

Server

[YanAlex]

In some rare cases fresh started queue/listener not detecting jobs, until I press ENTER on a console. This happend on a VirtualBox Win 10. Weird behaviors.

[rob006]

You have interactive_timeout and wait_timeout set as 28800 (default value), but your job takes over 34k seconds, so DB connection is automatically terminated on main worker process (because it is idle for over 28800 seconds, since it wait till job process finish). You need to increase these values if you have such long running jobs (or implement some kind of reconnection mechanism).

[YanAlex]

I think there should be a hard limit on job execution, Ive checked max_execution_time=120. Ok, ive got there are no time limit on console app.

Isnt job should be terminated by any timeout? Im not assume job still not executed, because regular job as you can see in log executed about less that one minute, I think job is just stuck somehow.

How to terminate such stuck jobs ?

[rob006]

max_execution_time should not affect console. Queue should kill the job after it exceeds TTR, I'm not sure why this is not the case here.

Also, if it happens every day, and you run this job every day, that would suggest that job always stucks, this is not some random event.

[YanAlex]

max_execution_time should not affect console. Queue should kill the job after it exceeds TTR, I'm not sure why this is not the case here.

Also, if it happens every day, and you run this job every day, that would suggest that job always stucks, this is not some random event.

TTR is Time To Reserve, this is a time the job shouldnt run again until this time passed, it does nothing than wait until resolve job to be repeated. Some of my jobs executed correct more than 30 seconds despite TTR = 30

[rob006]

TTR is used as timeout for job process:

yii2-queue/src/cli/Command.php

Line 185 in 7d12073

$process = new Process($cmd, null, null, $message, $ttr);

Process should be terminated after timeout. In other case unreserved job could be picked by another worker, and having 2 concurrent processes working on the same job is definitely a bad thing. I'm not sure if it works with non-isolate mode.

[YanAlex]

max_execution_time should not affect console. Queue should kill the job after it exceeds TTR, I'm not sure why this is not the case here.

Also, if it happens every day, and you run this job every day, that would suggest that job always stucks, this is not some random event.

Ive added SLEEP(300), Is this a bug?

[rob006]

Do you have pcntl PHP extension enabled?

It could be also some Windows quirk.

[YanAlex]

Do you have pcntl PHP extension enabled?

It could be also some Windows quirk.

Nope, found info about

Note: This extension is not available on Windows platforms.

By the way if I used Isolated=1 mode I had this error exceeeded timeout

But after some hours spend

> Symfony\Component\Process\Exception\ProcessTimedOutException: The process "C:\xampp\php\php.exe C:\WWWRoot\MDLP\yii "queue/exec" 217 30 1 4060 --verbose=1 --color=1" exceeded the timeout of 30 seconds.
2021-10-07 10:38:40 [pid: 4060] - Worker is stopped (20:01:25)
Exception 'yii\db\Exception' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away
The SQL being executed was: SELECT GET_LOCK('14c2c3805cf9fe67e7c8cb8bd42d65a0479e6079', 3)'

in C:\WWWRoot\MDLP\vendor\yiisoft\yii2\db\Schema.php:678

Error Info:
Array
(
    [0] => HY000
    [1] => 2006
    [2] => MySQL server has gone away
)

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