Cleaning up database errors, one left : concurrent OPTIMIZE (or ALTER etc) is running; UPDATE timed out; please retry

[James Crivellone]

After migration from 3.X to 5.X I've been cleaning up a decent amount of database errors and optimizing the forum as much as possible. I still see Cache Event issues occasionally which require a MySQL Bump to resolve, however, the main one now is after we have migrated over to Sphinx Search. Getting a ton of these throughout the day, although search is working fine from what I can tell.

Database error in vBulletin 5.6.0:

Invalid SQL:
UPDATE r3vdb SET starterlastcontent = 1586883498, startertextcount = 285 WHERE starter = 383320;

MySQL Error : index 'r3vdb': concurrent OPTIMIZE (or ALTER etc) is running; UPDATE timed out; please retry
Error Number : 1064
Request Date : Tuesday, April 14th 2020 @ 09:58:18 AM
Error Date : Tuesday, April 14th 2020 @ 09:58:26 AM
Script : https://www.r3vlimited.com/board/create-content/text/
Referrer : https://www.r3vlimited.com/board/for...toration/page8
IP Address : 8.36.116.204
Username : 2mAn
Classname : vBSphinxSearch_Connection
MySQL Version :


Stack Trace:
#0 vB_Database->getErrorData() called in [path]\vb\database.php on line 1207
#1 vB_Database->halt() called in [path]\vb\database\mysqli.php on line 249
#2 vB_Database_MySQLi->execute_query() called in [path]\vb\database.php on line 561
#3 vB_Database->query_write() called in [path]\packages\vbsphinxsearch\core.php on line 164
#4 vBSphinxSearch_Core->propagateStarterInheritedFields() called in [path]\packages\vbsphinxsearch\core.php on line 367
#5 vBSphinxSearch_Core->attributeChanged() called in [path]\vb\library\search.php on line 57
#6 vB_Library_Search->attributeChanged() called in [path]\vb\library\node.php on line 5455
#7 vB_Library_Node->updateParentCountsList() called in [path]\vb\library\node.php on line 5399
#8 vB_Library_Node->updateParentCounts() called in [path]\vb\library\content.php on line 1003
#9 vB_Library_Content->afterAdd() called in [path]\vb\library\content\text.php on line 778
#10 vB_Library_Content_Text->add() called in [path]\vb\api\content\text.php on line 172
#11 vB_Api_Content_Text->add() called in [path]\vb\api\wrapper.php on line 201
#12 vB_Api_Wrapper->__call() called in C:\inetpub\wwwroot\R3VLimited.com\board\includes\api\interface\collapsed.php on line 101
#13 Api_Interface_Collapsed->callApi() called in C:\inetpub\wwwroot\R3VLimited.com\board\includes\vb5\frontend\controller\createcontent.php on line 446
#14 vB5_Frontend_Controller_CreateContent->createNewNode() called in C:\inetpub\wwwroot\R3VLimited.com\board\includes\vb5\frontend\controller\createcontent.php on line 296
#15 vB5_Frontend_Controller_CreateContent->actionTextNodeInternal() called in C:\inetpub\wwwroot\R3VLimited.com\board\includes\vb5\frontend\controller\createcontent.php on line 186
#16 vB5_Frontend_Controller_CreateContent->index() called in C:\inetpub\wwwroot\R3VLimited.com\board\index.php on line 74

[Wayne Luke]

It appears that you're running optimize on the server for some reason. You'll need to wait until the indexes are rebuilt before you can do other work on the server.

[James Crivellone]

Thank you, Wayne,

Not running anything to my knowledge (its been happening since we kicked up sphinx) Anything I could verify to see why it's trying to optimize?

[Wayne Luke]

Either the MYSQL logs or the Sphinx Log.

What version of Sphinx are you running?

[James Crivellone]

Either the MYSQL logs or the Sphinx Log.

What version of Sphinx are you running?

Running Sphinx 3.2.1

Nothing in the Sphinx logs that are particular about optimization.

[Sun Apr 12 01:12:58.060 2020] [6560] rt: index r3vdb: ramchunk saved ok (mode=periodic, prev_tid=6805926, tid=6808482, ram=65.8 Mb, dt=36001 sec, took=0.1 sec)
[Sun Apr 12 11:12:59.955 2020] [6560] rt: index r3vdb: ramchunk saved ok (mode=periodic, prev_tid=6808482, tid=6810653, ram=65.8 Mb, dt=36002 sec, took=0.1 sec)
[Sun Apr 12 21:13:03.546 2020] [6560] rt: index r3vdb: ramchunk saved ok (mode=periodic, prev_tid=6810653, tid=6813416, ram=66.0 Mb, dt=36004 sec, took=0.1 sec)
[Mon Apr 13 07:13:08.445 2020] [6560] rt: index r3vdb: ramchunk saved ok (mode=periodic, prev_tid=6813416, tid=6814237, ram=66.0 Mb, dt=36004 sec, took=0.2 sec)
[Mon Apr 13 18:32:57.088 2020] [6560] rt: index r3vdb: ramchunk saved ok (mode=periodic, prev_tid=6814237, tid=6814238, ram=66.0 Mb, dt=40789 sec, took=0.2 sec)
[Tue Apr 14 04:32:59.141 2020] [6560] rt: index r3vdb: ramchunk saved ok (mode=periodic, prev_tid=6814238, tid=6816111, ram=66.0 Mb, dt=36002 sec, took=0.1 sec)
[Tue Apr 14 14:33:01.716 2020] [6560] rt: index r3vdb: ramchunk saved ok (mode=periodic, prev_tid=6816111, tid=6818442, ram=66.1 Mb, dt=36002 sec, took=0.1 sec)

I looked through the MySQL logs but don't see anything running from what I can tell.

[James Crivellone]

Following up on this. I shut down Sphinx and went back to DB search, all DB errors went away, so its something with Sphinx

[James Crivellone]

Any thoughts? I doublechecked the configuration with the Sphinx implementation guide and its identical.

[Wayne Luke]

I don't know much about Sphinx. Not finding a lot of information on the error on Google as it is returning information on optimizing Sphinx.

Did you dump the index and rebuild it after upgrading?