XF 2.2 Site down for two hours because of XF\Db\Exception: MySQL query error [1205]: Lock wait timeout exceeded

[Mr Shap]

Hi All.

A few hours ago I tried to spam clean a big user. This made the site unusable. Ever since then (2+ hours now) the site is up but anything we try to do results in the same Mysql lock wait error.

Here is the first logged error

• XF\Db\Exception: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction
• src/XF/Db/AbstractStatement.php:228
• Generated by: utropia90
• Mar 15, 2022 at 6:56 PM

Stack trace​

UPDATE xf_forum SET message_count = ?, last_post_date = ?, last_post_id = ?, last_post_user_id = ?, last_post_username = ?, last_thread_id = ?, last_thread_title = ? WHERE node_id = 17
------------

#0 src/XF/Db/Mysqli/Statement.php(196): XF\Db\AbstractStatement->getException('MySQL query err...', 1205, 'HY000')
#1 src/XF/Db/Mysqli/Statement.php(77): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1205, 'HY000')
#2 src/XF/Db/AbstractAdapter.php(94): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(326): XF\Db\AbstractAdapter->query('UPDATE `xf_for...', Array)
#4 src/XF/Mvc/Entity/Entity.php(1515): XF\Db\AbstractAdapter->update('xf_forum', Array, 'node_id = 17')
#5 src/XF/Mvc/Entity/Entity.php(1242): XF\Mvc\Entity\Entity->_saveToSource()
#6 src/XF/Entity/Thread.php(1373): XF\Mvc\Entity\Entity->save()
#7 src/XF/Entity/Thread.php(1177): XF\Entity\Thread->updateForumRecord()
#8 src/addons/AddonsLab/GuestPosting/XF/Entity/Thread.php(128): XF\Entity\Thread->_postSave()
#9 src/addons/ThemeHouse/AttachmentGallery/XF/Entity/Thread.php(9): AddonsLab\GuestPosting\XF\Entity\Thread->_postSave()
#10 src/addons/XFES/XF/Entity/Thread.php(11): ThemeHouse\AttachmentGallery\XF\Entity\Thread->_postSave()
#11 src/XF/Mvc/Entity/Entity.php(1266): XFES\XF\Entity\Thread->_postSave()
#12 src/XF/Entity/Post.php(624): XF\Mvc\Entity\Entity->save()
#13 src/XF/Entity/Post.php(582): XF\Entity\Post->updateThreadRecord()
#14 src/addons/AddonsLab/GuestPosting/XF/Entity/Post.php(88): XF\Entity\Post->_postSave()
#15 src/addons/XFES/XF/Entity/Post.php(9): AddonsLab\GuestPosting\XF\Entity\Post->_postSave()
#16 src/XF/Mvc/Entity/Entity.php(1266): XFES\XF\Entity\Post->_postSave()
#17 src/XF/Service/Thread/Replier.php(209): XF\Mvc\Entity\Entity->save(true, false)
#18 src/XF/Service/ValidateAndSavableTrait.php(40): XF\Service\Thread\Replier->_save()
#19 src/XF/Pub/Controller/Thread.php(600): XF\Service\Thread\Replier->save()
#20 src/addons/AddonsLab/GuestPosting/XF/Pub/Controller/Thread.php(69): XF\Pub\Controller\Thread->actionAddReply(Object(XF\Mvc\ParameterBag))
#21 src/XF/Mvc/Dispatcher.php(350): AddonsLab\GuestPosting\XF\Pub\Controller\Thread->actionAddReply(Object(XF\Mvc\ParameterBag))
#22 src/XF/Mvc/Dispatcher.php(261): XF\Mvc\Dispatcher->dispatchClass('XF:Thread', 'AddReply', Object(XF\Mvc\RouteMatch), Object(SV\ReportImprovements\XF\Pub\Controller\Thread), NULL)
#23 src/XF/Mvc/Dispatcher.php(113): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(SV\ReportImprovements\XF\Pub\Controller\Thread), NULL)
#24 src/XF/Mvc/Dispatcher.php(55): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#25 src/XF/App.php(2344): XF\Mvc\Dispatcher->run()
#26 src/XF.php(512): XF\App->run()
#27 index.php(20): XF::runApp('XF\\Pub\\App')
#28 {main}



Any advice would be appreciated. My host has no idea what to do. They feel restarting will mess with the integrity of the data.

 

[Mr Shap]

Anything I try to do on the site I get

XF\Db\Exception: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction in src/XF/Db/AbstractStatement.php at line 228

1. XF\Db\AbstractStatement->getException() in src/XF/Db/Mysqli/Statement.php at line 196
2. XF\Db\Mysqli\Statement->getException() in src/XF/Db/Mysqli/Statement.php at line 77
3. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
4. XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 145
5. XF\Db\AbstractAdapter->fetchRow() in src/XF/Entity/Post.php at line 858
6. XF\Entity\Post->softDelete() in src/XF/Service/Post/Deleter.php at line 81
7. XF\Service\Post\Deleter->delete() in src/addons/Snog/Groups/XF/Service/Post/Deleter.php at line 10
8. Snog\Groups\XF\Service\Post\Deleter->delete() in src/XF/Pub/Controller/Post.php at line 270
9. XF\Pub\Controller\Post->actionDelete() in src/XF/Mvc/Dispatcher.php at line 350
10. XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 261
11. XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 113
12. XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 55
13. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2344
14. XF\App->run() in src/XF.php at line 512
15. XF::runApp() in index.php at line 20

 

[Masetrix]

Either your server is too weak for actions in large themes or there is a problem with the Themehouse/Snog add-on (GuestPosting/Snog Groups).

First of all I would restart the SQL server.
If this does not solve the problem, the Themehouse and/or Snog Groups add-on should be deactivated.
Then you can also change the value for the timeout...

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction · SingleStore Documentation

SingleStore is a modern relational database for cloud and on-premises that delivers immediate insights for modern applications and analytical systems. Book a demo or trial today!

docs.singlestore.com

 

[mattrogowski]

Snog Groups and Guest Posting (and actually all the addons) are only in the stack trace due to class extensions, they're not directly involved with the query being run. Commonly extended entities like User and Thread may be extended by several addons at a time but not directly related to any issues as it's already passed through them. The issue here was that a completely separate query had created a table lock, these queries simply hit that lock, so are not the cause of the issue at all.

The cause in this case was actually from an addon that was using XenForo's toggle for loading unread watched threads:

public function findThreadsForWatchedList($unreadOnly = false)

This is a core function (XF\Repository\Thread) but the $unreadOnly argument is never set to true in core XF, but if it is used, it generates the query that was causing the issue. We're looking into whether this is something XF needs to (or even can) fix in core, or whether it's an isolated issue.

 

[mattrogowski]

Conclusion is that a spam cleaner job was running on an account with a lot of posts as the post limit setting for the feature was set to 0 - this caused high database activity that locked up the unread watched threads query specifically, which then in turn locked several other queries grinding the forum to a halt. Spam cleaner post limit has now been set which should alleviate the issue.