XenForo doesn't work because of XF\Db\Exception: MySQL query error [1205]: Lock wait timeout exceeded

[Argema]

Hi everyone,
for no reason, last week, our Xen forum got this error and users can no longer write messages or do anything else.

Our setup at the time was: xenforo 2.1, php 7.3.33, mariadb 10.3;
And now we have: xenforo 2.2.8, php 8.0.28, mariadb 10.5.18;
We tried switching the server (dedicated server; cpu AMD EPYC 7313; ram 64gb; disks nvme).
We also tried to disabling crons and addons.
With or without using xf-rebuild.

{Inno db setting}
innodb-flush-method = O_DIRECT
innodb-log-file-size = 4G
innodb_flush_log_at_trx_commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 32G
default-storage-engine =innodb
innodb_autoinc_lock_mode=2

{Log server error (one of them)}
XF\Db\Exception: MySQL query error [1205]: Lock wait timeout exceeded; try restarting transaction src/XF/Db/AbstractStatement.php:230

UPDATE xf_forum SET message_count = ?, last_post_date = ?, last_post_id = ?, last_post_user_id = ?, last_post_username = ? WHERE node_id = 36
------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL query err...', 1205, 'HY000')
#1 src/XF/Db/Mysqli/Statement.php(79): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1205, 'HY000')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(324): XF\Db\AbstractAdapter->query('UPDATE `xf_for...', Array)
#4 src/XF/Mvc/Entity/Entity.php(1526): XF\Db\AbstractAdapter->update('xf_forum', Array, 'node_id = 36')
#5 src/XF/Mvc/Entity/Entity.php(1253): XF\Mvc\Entity\Entity->_saveToSource()
#6 src/XF/Entity/Thread.php(1404): XF\Mvc\Entity\Entity->save()
#7 src/XF/Entity/Thread.php(1201): XF\Entity\Thread->updateForumRecord()
#8 src/XF/Mvc/Entity/Entity.php(1277): XF\Entity\Thread->_postSave()
#9 src/XF/Entity/Post.php(633): XF\Mvc\Entity\Entity->save()
#10 src/XF/Entity/Post.php(582): XF\Entity\Post->updateThreadRecord()
#11 src/XF/Mvc/Entity/Entity.php(1277): XF\Entity\Post->_postSave()
#12 src/XF/Service/Thread/Replier.php(214): XF\Mvc\Entity\Entity->save(true, false)
#13 src/XF/Service/ValidateAndSavableTrait.php(42): XF\Service\Thread\Replier->_save()
#14 src/XF/Pub/Controller/Thread.php(609): XF\Service\Thread\Replier->save()
#15 src/XF/Mvc/Dispatcher.php(352): XF\Pub\Controller\Thread->actionAddReply(Object(XF\Mvc\ParameterBag))
#16 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:Thread', 'AddReply', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Thread), NULL)
#17 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\Thread), NULL)
#18 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#19 src/XF/App.php(2483): XF\Mvc\Dispatcher->run()
#20 src/XF.php(524): XF\App->run()
#21 index.php(20): XF::runApp('XF\\Pub\\App')
#22 {main}

 

[JoshA]

It seems that your XenForo forum is experiencing a lock wait timeout error during a MySQL update operation. This issue can be caused by various factors such as server configuration, database setup, or application code.

Here are a few suggestions to troubleshoot and potentially resolve the issue:

1.Increase the lock wait timeout value: Edit your MySQL configuration file (usually my.cnf or my.ini) and add or update the following line:

innodb_lock_wait_timeout = 120

This will increase the lock wait timeout to 120 seconds. You can adjust the value as needed. After making the change, restart your MySQL server for the changes to take effect.

2. Optimize your database tables: Run the following command in your MySQL shell or use a tool like phpMyAdmin to execute it:

OPTIMIZE TABLE xf_forum, xf_thread, xf_post;

This will help defragment and optimize the specified tables.

3. Identify and resolve long-running queries: Check for any long-running queries that could be causing lock contention. You can use the following command in your MySQL shell to identify such queries:

SHOW PROCESSLIST;

If you identify any long-running queries, investigate the cause and optimize them if possible.

4. Review custom add-ons and modifications: Although you mentioned disabling add-ons, it's still a good idea to review any customizations made to your XenForo installation, including custom themes and modifications to the source code. These could be causing issues with the database or application logic.
5. Monitor server resources: Ensure that your server has enough resources (CPU, memory, disk space) to handle the current load. Monitor server performance and resource usage to identify any bottlenecks or issues that could be affecting the forum's performance.

Just a few ideas