Mysqli statement execute error : Lock wait timeout exceeded

[CivilWarTalk]

My site is experiencing a new problem recently. Users are complaining that actions such as posting and liking posts are "slow", and I've experienced the problem too. Sometimes the slowness leads to double posts, although I've never had this issue happen to me, I've deleted a bunch of them. Browsing is still fast and server loads are low (0.5 most of the time), so I'm not sure what the issue is.

I thought there was a problem with XenMedio, I uninstalled the mod and was getting errors from that (Mysqli prepare error: Table 'cwt1861_xenForo.EWRmedio_keywords' doesn't exist and such), I reinstalled the mod, but left it disabled. It might be my problem, or maybe not, I'm not sure...

This is the error I occasionally see in the Server logs:

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction - library/Zend/Db/Statement/Mysqli.php:214
 
#0 /home/XXXX/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/XXXX/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/XXXX/public_html/library/XenForo/DataWriter/DiscussionMessage.php(828): Zend_Db_Adapter_Abstract->query('?????UPDATE xf_...', 2666)
#3 /home/XXXX/public_html/library/XenForo/DataWriter/DiscussionMessage/Post.php(133): XenForo_DataWriter_DiscussionMessage->_updateUserMessageCount(false)
#4 /home/XXXX/public_html/library/XenForo/DataWriter/DiscussionMessage.php(504): XenForo_DataWriter_DiscussionMessage_Post->_updateUserMessageCount()
#5 /home/XXXX/public_html/library/XenForo/DataWriter.php(1385): XenForo_DataWriter_DiscussionMessage->_postSave()
#6 /home/XXXX/public_html/library/XenForo/ControllerPublic/Thread.php(505): XenForo_DataWriter->save()
#7 /home/XXXX/public_html/library/XenForo/FrontController.php(310): XenForo_ControllerPublic_Thread->actionAddReply()
#8 /home/XXXX/public_html/library/XenForo/FrontController.php(132): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#9 /home/XXXX/public_html/index.php(13): XenForo_FrontController->run()
#10 {main}
 
array(3) {
  ["url"] => string(84) "http://civilwartalk.com/threads/civil-war-movies-which-are-the-worst.71315/add-reply"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(8) {
    ["message_html"] => string(199) "<p>[quote="ole, post: 437833, member: 186"]You mean missed noticing that Stuart looked like he was still chewing on a muskrat?[/quote]</p>
<p>&nbsp;</p>
<p>Actually, it looks like two muskrats.</p>"
    ["_xfRelativeResolver"] => string(81) "http://civilwartalk.com/threads/civil-war-movies-which-are-the-worst.71315/page-3"
    ["attachment_hash"] => string(32) "304d17d9e7f37dc339acb1884121e9ab"
    ["last_date"] => string(10) "1333745735"
    ["_xfToken"] => string(56) "2666,1333746184,9551af705b4f5e41eaa2f51e056bc11535c7b426"
    ["_xfRequestUri"] => string(58) "/threads/civil-war-movies-which-are-the-worst.71315/page-3"
    ["_xfNoRedirect"] => string(1) "1"
    ["_xfResponseType"] => string(4) "json"
  }
}

I'm wondering if I should do a repair or optimize tables in phpmyadmin?

 

[Jake Bunce]

That's a server limit.

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.

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

The slow posting and double posting can be the result of a spotty connection to the server. It can also be the result of connection problems to the SMTP server (when sending notification emails of a new post), but that would show up in your error log.

 

[CivilWarTalk]

My server admin is moving my site to another box, a suspected load issue that I'm not seeing. Thanks for the idea Jake, I'll update my tech with your idea. I'll return with an updated report soon.

 

[CivilWarTalk]

Is it safe to use PHPMyAdmin to Repair and Optimize my tables just to be sure?

 

[Jake Bunce]

Is it safe to use PHPMyAdmin to Repair and Optimize my tables just to be sure?

It doesn't hurt. But most tables in XenForo are InnoDB which doesn't support repair.

 

[mrGTB]

I done it using WampServer without any issues, but obliviously that locahost.

 

[CivilWarTalk]

It doesn't hurt. But most tables in XenForo are InnoDB which doesn't support repair.

Okay, got it.

 

[CivilWarTalk]

Just an update:

After my host moved my site to a new box all slowness issues have gone away. I did get one report of a duplicate post from a member, but it may have just been something leftover from before the fix.

I still can't uninstall xenMedio without getting errors, I guess I'll leave it installed and disabled. Stupid add-ons....

 

[Clovis]

That's a server limit.

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.

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

The slow posting and double posting can be the result of a spotty connection to the server. It can also be the result of connection problems to the SMTP server (when sending notification emails of a new post), but that would show up in your error log.

Sorry for thread necro.

I can't seem to locate innodb_lock_wait_timeout in my my.cnf file located under /etc/mysql/my.cnf

My my.cnf config looks like this:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

I tried adding a line
innodb_lock_wait_timeout=120 to it but it just causes MySQL to crash on restart.

I also tried running mysqld --innodb_lock_wait_timeout=120 with no luck.