DataBase error Input

Hostboard

Participant
Joined
Aug 18, 2019
Messages
68
Problem 1 I am not a coder. Problem 2 I know enough to get myself in trouble...

So yesterday I received a database error:
Database error in vBulletin 4.2.5: Invalid SQL: DELETE FROM session WHERE userid = 243710; MySQL Error : Deadlock found when trying to get lock; try restarting transaction Error Number : 1213 Request Date : Saturday, December 4th 2021 @ 11:23:52 AM Error Date : Saturday, December 4th 2021 @ 11:23:52 AM Script : http://*********/forums/login.php?do=logout&logouthash=1638616993-f4e4385b0855cd8dc7782419fa700d4d5ebe1924 Referrer : https://*********/forums/f320/125246-shark-repelling-wetsuit.html?highlight=wetsuits IP Address : 31.43.59.173 Username : *****51 Classname : vB_Database_MySQLi MySQL Version : Server : 131.153.7.194 User Agent : Stack Trace: #0 vB_Database->halt() called in [path]/includes/class_core.php on line 1391 #1 vB_Database_MySQLi->execute_query() called in [path]/includes/class_core.php on line 442 #2 vB_Database->query_write() called in [path]/includes/functions_login.php on line 516 #3 process_logout() called in [path]/login.php on line 77

The database has had the following tables converted to InnoDB and has been running like this for many many months
These were the tables I altered:
alter table attachment engine=InnoDB;
alter table datastore engine=InnoDB;
alter table deletionlog engine=InnoDB;
alter table forum engine=InnoDB;
alter table pm engine=InnoDB;
alter table pmreceipt engine=InnoDB;
alter table pmtext engine=InnoDB;
alter table post engine=InnoDB;
alter table postparsed engine=InnoDB;
alter table searchcore engine=InnoDB;
alter table searchgroup engine=InnoDB;
alter table searchlog engine=InnoDB;
alter table sigparsed engine=InnoDB;
alter table subscribethread engine=InnoDB;
alter table thread engine=InnoDB;
alter table threadviews engine=InnoDB;
alter table user engine=InnoDB;
alter table usertextfield engine=InnoDB;

It was also altered to support: UTF8mb4 by a previous IB vBulletin developer and has also been running like this for many many months.

In researching I came across this:

I can only surmise that this is primarily due to the size of the tables? So, I guess my question has anyone ever tried to put a try/catch around the query execution logic and look for a deadlock when errors occur? Or am I entirely missing something? Any hints or suggestions are welcome :)

TIA
 

Deathstarr

Forum Owner
Joined
Mar 15, 2011
Messages
390
It looks to me as I’m not a coder either that your search is causing MySQL to lock
Up being so large. Maybe I’m wrong.
 

Alpha1

Administrator
Joined
May 28, 2007
Messages
4,115
IIRC vb Datastore table should not be InnoDB. It should be MyISAM.
Following over a decade old instructions doesnt seem like a good idea to me when it comes to database optimization.

Paul M will be able to answer this one better than me.
 

Hostboard

Participant
Joined
Aug 18, 2019
Messages
68
Then can I just use this command to change that one table back?

alter table Datastore engine=MyISAM;

Anything else in terms of cleanup that needs to be done?
 

Alpha1

Administrator
Joined
May 28, 2007
Messages
4,115
Basically, yeah.
Code:
ALTER TABLE database_name.Datastore ENGINE=MyISAM;
Don't forget any table prefix if you have that.
 
Top