XF 2.1 Server error - deadlock?

[RobParker]

So we were REALLY busy a few days ago and since then, even though our traffic is back to normal, I've been getting lots of these server errors in the logs. Any idea what I can do to fix this? It's not having any obvious effects.

• XF\Db\DeadlockException: MySQL query error [1213]: Deadlock found when trying to get lock; try restarting transaction
• src/XF/Db/AbstractStatement.php:212
• Generated by: Unknown account
• Aug 11, 2019 at 10:52 AM

Stack trace

UPDATE xf_job SET trigger_date = ?, last_run_date = ? WHERE job_id = ? AND trigger_date = ?
------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1213, '40001')
#1 src/XF/Db/Mysqli/Statement.php(78): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1213, '40001')
#2 src/XF/Db/AbstractAdapter.php(79): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(245): XF\Db\AbstractAdapter->query('UPDATE `xf_job...', Array)
#4 src/XF/Job/Manager.php(180): XF\Db\AbstractAdapter->update('xf_job', Array, 'job_id = ? AND ...', Array)
#5 src/XF/Job/Manager.php(76): XF\Job\Manager->runJobEntry(Array, 7.9992079734802)
#6 job.php(15): XF\Job\Manager->runQueue(false, 8)
#7 {main}

 

[Xon]

For a larger forum with access to the server's cron task system, I'ld recommend using;

CLI Job Runner for XF 2.1

This XenForo 2.x addon gives you the option to disable the browser triggered job runner and instead use a CLI triggered job runner for use with Unix cron. Requirements This addon requires PHP 7.0 or higher and has been tested on XenForo 2.1.10...

xenforo.com

 

[RobParker]

Thanks

We’ll look into that in the future. For now is there a way to clear the backlog?

 

[RobParker]

Our hosts got back to us and said there was a lot (!) of traffic hitting job.php. Our load is around 30 when it’s usually around 1.

Is that XF continually trying to rerun something that failed when we were too busy last week? Any easy way to fix it?

 

[Xon]

Thanks

We’ll look into that in the future. For now is there a way to clear the backlog?

Honestly that is the fastest way, as it disables front-end users being sent to hit job.php. Otherwise; blocking access to job.php via the webserver (ie htaccess rule or whatever) and then running the php job.php from the CLI may be your best bet.

 

[RobParker]

I can't just empty the job table in the database or restart mysql or anything?

 

[RobParker]

Searching the forum I think @Chris D has advised emptying the xf_job table when other people had problems?

 

[RobParker]

This is what my xf_job table looked like:

Edit Edit     Copy Copy     Delete Delete     2     63726f6e    XF\Job\Cron     [BLOB - 6 B]    0     1565631427     1565631367
   Edit Edit     Copy Copy     Delete Delete     15     7866436f6c6c6563745374617473    XF:CollectStats     [BLOB - 6 B]    1     1568383131     1565278329
   Edit Edit     Copy Copy     Delete Delete     47456     NULL    XF:Notifier     [BLOB - 8.1 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47457     NULL    XF:Notifier     [BLOB - 12 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47458     NULL    XF:Notifier     [BLOB - 4.9 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47460     NULL    XF:Notifier     [BLOB - 13.2 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47463     NULL    XF:Notifier     [BLOB - 4 KiB]    0     1565594787     1565631378
   Edit Edit     Copy Copy     Delete Delete     47466     NULL    XF:Notifier     [BLOB - 6.1 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47468     NULL    XF:Notifier     [BLOB - 3.5 KiB]    0     1565612684     1565631378
   Edit Edit     Copy Copy     Delete Delete     47470     NULL    XF:Notifier     [BLOB - 3.7 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47481     NULL    XF:Notifier     [BLOB - 4.4 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47484     NULL    XF:Notifier     [BLOB - 3.3 KiB]    0     1565594787     1565631378
   Edit Edit     Copy Copy     Delete Delete     47485     NULL    XF:Notifier     [BLOB - 3 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47492     NULL    XF:Notifier     [BLOB - 7.2 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47496     NULL    XF:Notifier     [BLOB - 4.5 KiB]    0     1565594774     1565631378
   Edit Edit     Copy Copy     Delete Delete     47497     NULL    XF:Notifier     [BLOB - 496 B]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47498     NULL    XF:Notifier     [BLOB - 3.5 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47556     NULL    XF:Notifier     [BLOB - 4.4 KiB]    0     1565594787     1565631378
   Edit Edit     Copy Copy     Delete Delete     47565     NULL    XF:Notifier     [BLOB - 958 B]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47583     NULL    XF:Notifier     [BLOB - 3.5 KiB]    0     1565612684     1565631378
   Edit Edit     Copy Copy     Delete Delete     47602     NULL    XF:Notifier     [BLOB - 1.6 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47612     NULL    XF:Notifier     [BLOB - 2.2 KiB]    0     1565612684     1565631378
   Edit Edit     Copy Copy     Delete Delete     47618     NULL    XF:Notifier     [BLOB - 1 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47619     NULL    XF:Notifier     [BLOB - 5.9 KiB]    0     1565612684     1565631378
   Edit Edit     Copy Copy     Delete Delete     47620     NULL    XF:Notifier     [BLOB - 1 KiB]    0     1565612684     1565631378
   Edit Edit     Copy Copy     Delete Delete     47633     NULL    XF:Notifier     [BLOB - 6.4 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47649     NULL    XF:Notifier     [BLOB - 2.2 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47669     NULL    XF:Notifier     [BLOB - 1.9 KiB]    0     1565612684     1565631378
   Edit Edit     Copy Copy     Delete Delete     47688     NULL    XF:Notifier     [BLOB - 1.9 KiB]    0     1565612684     1565631378
   Edit Edit     Copy Copy     Delete Delete     47703     NULL    XF:Notifier     [BLOB - 3.8 KiB]    0     1565632278     1565631378
   Edit Edit     Copy Copy     Delete Delete     47714     NULL    XF:Notifier     [BLOB - 1.4 KiB]    0     1565612684     1565631378
   Edit Edit     Copy Copy     Delete Delete     47725     NULL    XF:Notifier     [BLOB - 613 B]    0     1565632278     1565631378

By deleting all the entries as advised, the server load and CPU usage immediately dropped back to normal. No idea what caused it but that fixed it.

 

[Xon]

You might need to re-add the XF\Job\Cron task, I'm not sure if XF will regenerate that

 

[RobParker]

Ah Jonathan (the guy who answered the support ticket) said they were all fine to delete. I’ll double check, thanks!

 

[RobParker]

Yep, it seems you're right. We've had no stats collected for the last 2 days since I fixed this.
Any idea how I re-add those 2 jobs?

XF\Job\Cron
XF:CollectStats

 

[Xon]

@RobParker run this SQL;

insert into xf_job (unique_key,execute_class,execute_data,manual_execute,trigger_date) 
values
('cron','XF\Job\Cron', 'a:0:{}', 0, unix_timestamp()),
('xfCollectStats','XF:CollectStats', 'a:0:{}', 0, unix_timestamp());