Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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

  • #2
    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.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud demonstration site.
    vBulletin 5 API

    Comment


    • #3
      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?

      Comment


      • #4
        Either the MYSQL logs or the Sphinx Log.

        What version of Sphinx are you running?
        Translations provided by Google.

        Wayne Luke
        The Rabid Badger - a vBulletin Cloud demonstration site.
        vBulletin 5 API

        Comment


        • #5
          Originally posted by Wayne Luke View Post
          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.

          Comment


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

            Comment


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

              Comment


              • #8
                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?

                Translations provided by Google.

                Wayne Luke
                The Rabid Badger - a vBulletin Cloud demonstration site.
                vBulletin 5 API

                Comment

                Related Topics

                Collapse

                Working...
                X