MySQL Error : Lock wait timeout exceeded; try restarting transaction

[aelalfey]

Hi, I am having this error. Keeps getting emails with this error "A lot of emails"

Invalid SQL:
UPDATE session SET `lastactivity`=1535978615,`styleid`=43,`languageid`=1
WHERE (`sessionhash` = '')
/**session**/;
MySQL Error : Lock wait timeout exceeded; try restarting transaction
Error Number : 1205
Request Date : Monday, September 3rd 2018 @ 06:43:35 AM
Error Date : Monday, September 3rd 2018 @ 06:44:32 AM
Script : https://on.mac-eg.com/chat/loadheaderdata
Referrer : https://on.mac-eg.com/
IP Address :
Username :
Classname : vB_Database_MySQLi
MySQL Version :
Stack Trace:
#0 vB_Database->getErrorData() called in [path]/vb/database.php on line 1188
#1 vB_Database->halt() called in [path]/vb/database/mysqli.php on line 201
#2 vB_Database_MySQLi->execute_query() called in [path]/vb/database.php on line 542
#3 vB_Database->query_write() called in [path]/vb/db/query/update.php on line 116
#4 vB_dB_Query_Update->doUpdates() called in [path]/vb/db/query/update.php on line 101
#5 vB_dB_Query_Update->execSQL() called in [path]/vb/db/assertor.php on line 304
#6 vB_dB_Assertor->assertQuery() called in [path]/vb/db/assertor.php on line 541
#7 vB_dB_Assertor->update() called in [path]/vb/session.php on line 612
#8 vB_Session->save() called in [path]/vb/shutdown.php on line 86
#9 vB_Shutdown->shutdown() called in [path]/vb/vb.php on line 486
#10 vB::shutdown() called in on line



my.cnf

[mysqld]
performance-schema=0

default-storage-engine=MyISAM
interactive_timeout=300
key_cache_block_size=4096
max_heap_table_size=32M
max_join_size=1000000000
max_allowed_packet=268435456
open_files_limit=10000
query_cache_size=32M
thread_cache_size=100
tmp_table_size=32M
wait_timeout=7800
max_user_connections=50
myisam_recover_options=FORCE
innodb_file_per_table=1
innodb_flush_log_at_trx_commit=0
innodb_purge_threads=1
innodb_support_xa=0
innodb_thread_concurrency=8
pid-file=/var/lib/mysql/mysqld.pid

Please help.

[Mark.B]

This is your error:

MySQL Error : Lock wait timeout exceeded;

You'll need to increase the wait_timeout value in the first instance. MySQL will need to be restarted once the change is made.

[Wayne Luke]

You actually want to increase the innodb_lock_wait_timeout value for MySQL.

If it continues, you'll want to -

1. Turn off your forums
2. Restart MySQL.
3. Delete the contents of the cache, cacheevent, and searchlog tables.
4. Turn on your forums.

[Craig]

You actually want to increase the innodb_lock_wait_timeout value for MySQL.

If it continues, you'll want to -

1. Delete the contents of the cache, cacheevent, and searchlog tables.

What is the best way to do this for someone not familiar with this process?

[Wayne Luke]

For the first two, click "Clear System Cache" in the AdminCP under Maintenance. The searchlog was fixed shortly after this topic was created so shouldn't be much of a problem these days.

Otherwise, you can run these queries:

truncate table cache;
truncate table cacheevent;
truncate table searchlog;

Whether this will solve your problem or not, I don't know. If you're running 5.4.X, then probably. If you're running 5.5.X, then it might not. Opening a new topic if it continues can provide up to date troubleshooting.

[Craig]

One of my sites is having this similar issue and my host recommend I try these instructions. They have already raised the timeout innodb_lock_wait_timeout from 120 to 200.

If this issue continues I will open a separate post.


Thank you!