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

Mr Shap

Member
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

Member
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

Well-known member
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...

 

mattrogowski

Well-known member
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:

Code:
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.
 
Last edited:

mattrogowski

Well-known member
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.
 
Top