XF 1.1 Lock Wait Timeout on InnoDB

[Ghan_04]

So I've taken a default Xenforo install and imported two phpBB 3.0 boards into it. Database size is now about 750 MB.
Merging the two boards has resulted in about 450 usergroups that we want to clean out. However, when we use the ACP to delete a group, the request hangs and I eventually get:

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

I've got the timeout set to 50 seconds. It should not be taking this long on a database of this size. I've checked the InnoDB status and it appears that the transaction running this query hangs waiting for a lock to be released from somewhere. Looks like a deadlock. I'm not really sure where to go. This might be a bug that needs reporting.

To add some info, I have another Xenforo install running with InnoDB on the same server (though it is smaller) and do not have this issue - I can create a delete usergroups just fine.

Also, I've tried copying this site over to a completely different server and I still encounter the same issue.

Any ideas?

 

[Paul B]

That many user groups is going to cause problems with rebuilds when deleting.

If at all possible, you should try optimising the user groups before importing.

 

[Jake Bunce]

I agree with Brogan. Definitely try to clean that out before importing.

Long rebuilds are a known design issue when you have lots of nodes / groups.

If you have root access to the server then you can edit the my.cnf file (usually at /etc/my.cnf) to increase the value of innodb_lock_wait_timeout. Increasing that limit should avoid this error. If you don't have root access then you need to ask your host or server person to do this.

 

[Ghan_04]

This is a VPS that I run myself so I can make any changes I need to. I was more interested in finding out why exactly it was taking so long to do this. At least this seems to be expected behavior, so I at least know that it is not something simple I overlooked. Thanks for the help, guys.

 

[yavuz]

I'm getting this error too while deleting an addon:

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

I haven't innodb_lock_wait_timeout this in my.conf what should I set this to?

 

[Jake Bunce]

I haven't innodb_lock_wait_timeout this in my.conf what should I set this to?

The default is 50 seconds. You can increase it as much as is needed for your forum.

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout