XF 1.5 Lock wait timeout exceeded

[BassMan]

When I try to change permissions for users or even if I try to create a new node I get this error:

Server Error

Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction

    Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
    Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
    Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 632
    Zend_Db_Adapter_Abstract->update() in XenForo/Model/Permission.php at line 1583
    XenForo_Model_Permission->rebuildPermissionCombination() in XenForo/Model/Permission.php at line 1504
    XenForo_Model_Permission->rebuildPermissionCache() in XenForo/Deferred/Permission.php at line 19
    XenForo_Deferred_Permission->execute() in XenForo/Model/Deferred.php at line 295
    XenForo_Model_Deferred->runDeferred() in XenForo/Model/Deferred.php at line 429
    XenForo_Model_Deferred->_runInternal() in XenForo/Model/Deferred.php at line 374
    XenForo_Model_Deferred->run() in XenForo/ControllerAdmin/Tools.php at line 159
    XenForo_ControllerAdmin_Tools->actionRunDeferred() in XenForo/FrontController.php at line 347
    XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 134
    XenForo_FrontController->run() in /home/mladiuc/public_html/forum/admin.php at line 13

What can I do about that?

 

[Paul B]

How many user groups do you have?

 

[Robru]

This started happening to me when my database size grew and i was doing a lot of transactions on it.
Truth is there is prob some way to optimize either your queries or your DB but try these 2 queries for a work around fix.

Run this:

SET GLOBAL innodb_lock_wait_timeout =5000;

And then this:

SET innodb_lock_wait_timeout =5000;

 

[BassMan]

How many user groups do you have?

21? I use auto-promoting to some user groups when users select it on registering.

This started happening to me when my database size grew and i was doing a lot of transactions on it.
Truth is there is prob some way to optimize either your queries or your DB but try these 2 queries for a work around fix.

Run this:

SET GLOBAL innodb_lock_wait_timeout =5000;

And then this:

SET innodb_lock_wait_timeout =5000;

Where should I put that in phpmyadmin?

 

[BassMan]

@Brogan
You think I have too many groups? Actually I don't need them, but would like to have an option to show users some kind of banner to which "group" they belong (example: teacher, senoir teacher, student etc.).

 

[BassMan]

Nevermind, I've found some other solution to display those groups in message user info.

I'll delete all those groups I don't need and I'll hope it will work ok. But I think the major problem is I have too many users in primary registered group.

Well, I'll see, also my host can increase the number for execution time a bit.

 

[BassMan]

This started happening to me when my database size grew and i was doing a lot of transactions on it.
Truth is there is prob some way to optimize either your queries or your DB but try these 2 queries for a work around fix.

Run this:

SET GLOBAL innodb_lock_wait_timeout =5000;

And then this:

SET innodb_lock_wait_timeout =5000;

Get this: #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation

I've contacted my host already. Thank you.

 

[Mike]

While this could be related to the amount of work that needs to be done in this request, it's really indicative of another request being slow. That's likely also permission related, but the rebuilds generally shouldn't take that long. That said, there is a tweak coming in 1.5.1 that might help a specific case.

That said, if it is permission related, it generally shouldn't happen unless there are two changes happening simultaneously.

 

[Robru]

You can also set it to higher value in /etc/my.cnf permanently with this line

[mysqld]

innodb_lock_wait_timeout=120

and restart mysql

 

[JackieChun]

@Brogan @Mike I am getting this error while trying to use the spam cleaner. Each spammer has 25,000 to 35,000 posts, so MySQL just errors out. Are there recommended settings for running this action?

 

[JackieChun]

Still looking for an answer to this

 

[jb9826]

This started happening to me when my database size grew and i was doing a lot of transactions on it.
Truth is there is prob some way to optimize either your queries or your DB but try these 2 queries for a work around fix.

Run this:

SET GLOBAL innodb_lock_wait_timeout =5000;

And then this:

SET innodb_lock_wait_timeout =5000;

Why is the default timeout for this if you want to change it back