No announcement yet.

Database error when using Help Rebuild Topic Information

  • Filter
  • Time
  • Show
Clear All
new posts

  • Database error when using Help Rebuild Topic Information


    I recently updated to VB5. I saw that search was returning only a few among hundreds of expected results. I rebuilt the search index from command line. That did not help.

    I went to update the thread topic info. I did this with 2K threads, 40K threads and in the end I get the same message. Can someone please help?

    Error Invalid SQL: UPDATE node AS node INNER JOIN ( SELECT node.starter, SUM(CASE WHEN node.parentid = node.starter AND (node.showpublished > 0 AND node.showapproved > 0) THEN 1 ELSE 0 END) AS textcount, SUM(CASE WHEN node.parentid = node.starter AND (node.showpublished = 0 OR node.showapproved = 0) THEN 1 ELSE 0 END) AS textunpubcount, SUM(CASE WHEN node.nodeid != node.starter AND (node.showpublished > 0 AND node.showapproved > 0) THEN 1 ELSE 0 END) AS totalcount, SUM(CASE WHEN node.nodeid != node.starter AND (node.showpublished = 0 OR node.showapproved = 0) THEN 1 ELSE 0 END) AS totalunpubcount, MAX(node.publishdate) AS lastcontent FROM node AS node WHERE node.starter between 15267583 and 15287582 AND node.contenttypeid NOT IN (30,34,35) GROUP BY node.starter ) AS counts ON node.nodeid = counts.starter SET node.textcount = counts.textcount, node.textunpubcount = counts.textunpubcount, node.totalcount = counts.totalcount, node.totalunpubcount = counts.totalunpubcount, node.lastcontent = counts.lastcontent /**updateThreadCounts**/;
    Exception trace:
    ## /data/core/vb/database.php(1189) Exception Thrown 
    #0 /data/core/vb/database/mysqli.php(201): vB_Database->halt()
    #1 /data/core/vb/database.php(542): vB_Database_MySQLi->execute_query(true, Object(mysqli))
    #2 /data/core/vb/db/query/stored.php(111): vB_Database->query_write('Invalid SQL:\r\n\n...')
    #3 /data/core/vb/db/assertor.php(304): vB_dB_Query_Stored->execSQL()
    #4 /data/core/admincp/misc.php(773): vB_dB_Assertor->assertQuery('vBAdmincp:updat...', Array)
    #5 /data/includes/api/interface/collapsed.php(142): require_once('/data/c...')
    #6 /data/includes/vb5/frontend/controller/relay.php(33): Api_Interface_Collapsed->relay('admincp/')
    #7 /data/index.php(74): vB5_Frontend_Controller_Relay->admincp('misc.php')
    #8 {main}

  • #2
    Here is another interesting happening. I went to update the forum information and got below message. Now I am sure I dont have 1.6M forums. Not sure whats going on here. Gona kill this screen.

    Updating Forums
    If the forum counts are significantly off this will run multiple times. It is not unusual for this to start at zero and scan the forums as many as six or eight times. Please do not interrupt, but allow this to complete.
    101 to 110 of 16626485


    • Wayne Luke
      Wayne Luke commented
      Editing a comment
      The 16626485 number has nothing to do with the number of forums you have on the site. That is total nodes in the system. A channel can be any node. If you create 10 channels, then 1 million posts, the next channel will have a node id above 1 million. Forum nodes aren't stored separately from any others.

  • #3
    By default, vBulletin will return the top 500 results that fit the search parameters. This is a cutoff specified in the Search Engine Options under Settings -> Options. This is done for performance reasons. If you want to increase this value, you should offload the search engine to SphinxSearch.

    I don't see the error in your SQL query. What is the content of the email sent about this error? It will be sent to the technical email listed in /core/includes/config.php. It is probably a timeout. You might need to lower the amount of topics per iteration.
    Translations provided by Google.

    Wayne Luke
    The Rabid Badger - a vBulletin Cloud customization and demonstration site.
    vBulletin 5 Documentation - Updated every Friday. Report issues here.
    vBulletin 5 API - Full / Mobile
    I am not currently available for vB Messenger Chats.


    Related Topics