Upgraded from 5.2.5 to 5.3.2 - Sphinx Updated, Database errors when rebuilding

Collapse
X
 
  • Time
  • Show
Clear All
new posts

  • William Thomas Jr
    replied
    What kind of performance are you fellas seeing as beneficial by using Sphinx search?

    Leave a comment:


  • BarkyJ
    replied
    Wow @[email protected]
    You really did some digging.
    So the problem is all in core.php?
    Thank you so much for your efforts!

    vBulletin, was this not tested?
    Can we have a response from you please

    Leave a comment:


  • yi.zhou@xplusz.com
    replied
    BarkyJ I'm running into the same issue with you and after some debugging, turns out the issue is in /core/packages/vbsphinxsearch/core.php file.

    Line 85: $this->sphinxDB->connect('', $config['Misc']['sphinx_host'], $config['Misc']['sphinx_port'], '', '', false); By trace this, it's using the db_connect method in core/vb/database.php which missing db password and username. Change to this
    $this->sphinxDB->connect('', $config['Misc']['sphinx_host'], $config['Misc']['sphinx_port'], $config['MasterServer']['username'], $config['MasterServer']['password'], false);

    After this, there is another silly bug on the same file on line 1637:

    if ((strpos($output, 'ERROR') === 0) OR (strpos($output, 'WARNING') === 0) OR (strpos($output, 'FATAL') === 0))
    {
    throw new vB_Exception_Api('sphinx_error_x', $output);
    }

    It throw error when there is warning.......
    But seems sphinx not support this type anymore and latest vb didn't remove it from vbulletin-sphinx.php:
    WARNING: key 'charset_type' was permanently removed from Sphinx configuration. Refer to documentation for details.
    WARNING: key 'charset_type' was permanently removed from Sphinx configuration. Refer to documentation for details.
    WARNING: key 'max_matches' was permanently removed from Sphinx configuration. Refer to documentation for details.

    So I changed it to this if ((strpos($output, 'ERROR') === 0) OR (strpos($output, 'FATAL') === 0))

    Now everything works for me, hope this help you to solve yours. Also can some vb dev take a look at this bug!? Sphinx is important and huge performance improve, please make sure we can use those before release.

    Leave a comment:


  • BarkyJ
    replied
    Could not figure this out, so turned off Sphinx and put it back to DB Search.

    Rebuilt the index, again. About 22000 records in so far, and I get an email, similar to the ones above.
    End has this

    Database error in vBulletin 5.3.2:

    Invalid SQL:
    INSERT INTO searchtowords_c (`nodeid`,`wordid`,`is_title`,`score`,`position`) VALUES (22889,3413,0,10000,5) /**searchtowords_c**/;

    MySQL Error : Duplicate entry '3413-22889' for key 'wordid'
    Error Number : 1062
    Request Date : Monday, August 14th 2017 @ 02:42:23 AM
    Error Date : Monday, August 14th 2017 @ 02:42:30 AM
    Script : http://blah.mysite.au/admincp/misc.p...0&indextypes=0
    Referrer : http://blah.mysite.au/admincp/misc.p...0&indextypes=0
    IP Address : 121.xx.xx.xx
    Username : myusername
    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/multipleinsert.php on line 114
    #4 vB_dB_Query_MultipleInsert->doMultipleInserts() called in [path]/vb/db/query/multipleinsert.php on line 89
    #5 vB_dB_Query_MultipleInsert->execSQL() called in [path]/vb/db/assertor.php on line 302
    #6 vB_dB_Assertor->assertQuery() called in [path]/vb/db/assertor.php on line 515
    #7 vB_dB_Assertor->insertMultiple() called in [path]/packages/vbdbsearch/core.php on line 161
    #8 vBDBSearch_Core->indexText() called in [path]/vb/search/core.php on line 114
    #9 vB_Search_Core->index() called in [path]/vb/library/search.php on line 42
    #10 vB_Library_Search->index() called in [path]/vb/library/search.php on line 102
    #11 vB_Library_Search->indexRange() called in [path]/admincp/misc.php on line 229
    #12 require_once([path]/admincp/misc.php) called in /home/admin4d/public_html/forum/includes/api/interface/collapsed.php on line 127
    #13 Api_Interface_Collapsed->relay() called in /home/admin4d/public_html/forum/includes/vb5/frontend/controller/relay.php on line 39
    #14 vB5_Frontend_Controller_Relay->admincp() called in on line
    #15 call_user_func_array() called in /home/admin4d/public_html/forum/index.php on line 74
    what is this stuff?
    These errors are so ambiguous as to what the problem is

    Leave a comment:


  • BarkyJ
    replied
    While its rebuilding, I am once again getting constant emails

    A database error occured, please check the database settings in the config file or enable debug mode for additional information.
    etc

    Leave a comment:


  • BarkyJ
    replied
    After this, I went straight into cpanel and into Maintenance, and I rebuilt the search index, and then had the website state this!

    A System Error has occured.
    The software is experiencing a systems error.

    You should attempt to repeat your last action. If this error occurs again, please contact the site administrator.
    I then tested and Sphinx had been shut down. I assume that was from the 'php searchindex.php' being run?
    If thats the case, the instructions need tweaking.

    Why does the whole vbulletin website DIE when sphinx isnt running. Surely that shouldnt be the case. The forum reliant on a 3rd party search tool....

    I started it back up, and the forum worked again, but still nothing would work in Search.

    Have just stared another rebuild of the cache.

    I hope this is what solves it. But these instructions.... gees.

    Leave a comment:


  • BarkyJ
    replied
    After battling I think I am getting somewhere.

    what seems to have happened is a subtle change in the /core/include/config.php file.
    it used to point to a sphinx conf file, but its now php.

    This is NOT OBVIOUS. I had assumed it was for new installs, as those lines were already in my config.php file, but notice the subtle change.
    $config['Misc']['sphinx_host'] = '127.0.0.1';
    $config['Misc']['sphinx_port'] = '9306';
    $config['Misc']['sphinx_path'] = '/usr/local/sphinx'; //no trailing slash
    $config['Misc']['sphinx_config'] = $config['Misc']['sphinx_path'] . '/etc/vbulletin_sphinx.conf';
    vs

    $config['Misc']['sphinx_host'] = '127.0.0.1';
    $config['Misc']['sphinx_port'] = '9306';
    $config['Misc']['sphinx_path'] = '/usr/local/sphinx'; //no trailing slash
    $config['Misc']['sphinx_config'] = $config['Misc']['sphinx_path'] . '/etc/vbulletin-sphinx.php';
    I think this needs to be outlined a little more plainly.
    I had assumed I already had those 4 lines as they seemed the same.

    After changing this, the 'php searchindex.php' went a step further, but I still get some odd results. Is this normal?

    [email protected] [forum]# php searchindex.php
    Please enter the path to your vBulletin directory (default /home/admin4d/public_html/forum/core):
    Note:
    You should not normally need to empty the search index, however if items are showing in search results after they have been permanently deleted then emptying the index and rebuilding the search index will fix this. Search will not work correctly after emptying the index until the index is fully rebuilt.
    Click Here to empty the search index. You can rebuild the whole search index by clicking here if it is supported by your choice of search type.
    [0/1,Default=0]: 1

    Re-indexing all the content.
    .Sphinx 2.2.10-id64-release (2c212e0)
    Copyright (c) 2001-2015, Andrew Aksyonoff
    Copyright (c) 2008-2015, Sphinx Technologies Inc (http://sphinxsearch.com)

    using config file '/usr/local/sphinx/etc/vbulletin-sphinx.php'...
    WARNING: key 'charset_type' was permanently removed from Sphinx configuration. Refer to documentation for details.
    WARNING: key 'charset_type' was permanently removed from Sphinx configuration. Refer to documentation for details.
    WARNING: key 'max_matches' was permanently removed from Sphinx configuration. Refer to documentation for details.
    indexing index 'admin4d_forum_disk'...
    collected 43726 docs, 25.5 MB
    collected 257606 attr values
    sorted 0.3 Mvalues, 100.0% done
    sorted 4.3 Mhits, 100.0% done
    total 43726 docs, 25501430 bytes
    total 4.302 sec, 5926490 bytes/sec, 10161.85 docs/sec
    rotating indices: successfully sent SIGHUP to searchd (pid=25724).
    total 263098 reads, 0.118 sec, 0.1 kb/call avg, 0.0 msec/call avg
    total 83 writes, 0.054 sec, 730.8 kb/call avg, 0.6 msec/call avg
    rotating indices: successfully sent SIGHUP to searchd (pid=25724).
    [Mon Aug 14 15:57:32.892 2017] [26260] using config file '/usr/local/sphinx/etc/vbulletin-sphinx.php'...
    [Mon Aug 14 15:57:32.950 2017] [26260] stop: successfully sent SIGTERM to pid 25724
    Sphinx 2.2.10-id64-release (2c212e0)
    Copyright (c) 2001-2015, Andrew Aksyonoff
    Copyright (c) 2008-2015, Sphinx Technologies Inc (http://sphinxsearch.com)

    WARNING: key 'charset_type' was permanently removed from Sphinx configuration. Refer to documentation for details.
    WARNING: key 'charset_type' was permanently removed from Sphinx configuration. Refer to documentation for details.
    WARNING: key 'max_matches' was permanently removed from Sphinx configuration. Refer to documentation for details.
    Rebuilding the Search Index as a whole is not Implemented for the Selected Search Typeempty the index...2: mysqli_real_connect(): (HY000/2002): Connection refused

    Leave a comment:


  • Upgraded from 5.2.5 to 5.3.2 - Sphinx Updated, Database errors when rebuilding

    Hello,

    We just upgraded our production sites forum from 5.2.5 to 5.3.2, and noticed that Sphinx has changed, so followed the instructions and stopped it, updated the file in /usr/local/sphinx/etc/ and put the path back to where it should be. Then followed the instructions here https://www.vbulletin.com/forum/arti...e-sphinx-index as per the prompt in cpanel of the forum.
    But after running 'php searchindex.php' all it did was ask for the path to the forum/core, which is the default in our case. It then quits back to prompt. It didnt ask to clear cache or anything.
    Search however is not working. Comes up blank.
    So I go into cpanel and clear cache. No change
    Go into Maintenance and rebuild the search index, and while its doing it, I get constant errors coming into email:

    A database error occured, please check the database settings in the config file or enable debug mode for additional information.
    and

    Database error in vBulletin 5.3.2:

    Invalid SQL:
    INSERT INTO admin4d_forum(
    id, title, content, contenttypeid, lastcontentid, parentid, starter, starterparent, starteruser, closure, depth, userid, authorname, setfor, sentto, showpublished, approved, showapproved, viewperms, featured, inlist, protected, votes, lastcontent, created, publishdate, unpublishdate, prefixid, tagid, textcount, eventstartdate, eventenddate
    )
    VALUES (
    40216, '', ' that is why i asked you about your environment.\n\nyou write to the sound object for genie.\n\nfor designer, visi and serial you \'playwav\'.', 25, 40216, 40203, 40203, 28, 3872, (40216,40203,28,38011,2,1), '{"n40216":0,"n40203":1,"n28":2,"n38011":3,"n2":4,"n1":5}', 472, 'ESPsupport', 0, (), 1, 1, 1, 2, 0, 1, 0, 0, 1408225197, 1408225197, 1408225197, 0, 0, (), 0, 0, 0
    );

    MySQL Error : sphinxql: syntax error, unexpected $end, expecting FROM or ',' near 'version'
    Error Number : 1064
    Request Date : Sunday, August 13th 2017 @ 09:48:44 PM
    Error Date : Sunday, August 13th 2017 @ 09:48:44 PM
    Script : http://forum.oursitenamehere.com.au/...0&indextypes=0
    Referrer : http://forum.oursitenamehere.au/admi...0&indextypes=0
    IP Address : 121.xx.xx.xx
    Username : usernamehere
    Classname : vBSphinxSearch_Connection
    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]/packages/vbsphinxsearch/core.php on line 148
    #4 vBSphinxSearch_Core->indexText() called in [path]/vb/search/core.php on line 114
    #5 vB_Search_Core->index() called in [path]/vb/library/search.php on line 42
    #6 vB_Library_Search->index() called in [path]/vb/library/search.php on line 102
    #7 vB_Library_Search->indexRange() called in [path]/admincp/misc.php on line 229
    #8 require_once([path]/admincp/misc.php) called in /home/admin4d/public_html/forum/includes/api/interface/collapsed.php on line 127
    #9 Api_Interface_Collapsed->relay() called in /home/admin4d/public_html/forum/includes/vb5/frontend/controller/relay.php on line 39
    #10 vB5_Frontend_Controller_Relay->admincp() called in on line
    #11 call_user_func_array() called in /home/admin4d/public_html/forum/index.php on line 74
    every few seconds a new email like these come in.

    By the time the indexing finishes, the emails finish, but the search still doesnt work.
    I even tried clicking the hot URL links above the rebuilt index button in Maintenance, to clear the index, and rebuild the index. Clear didnt seem to do anything, and rebuilt resulted in an error.

    The forum is working, we can see content
    Sphinx is running, however its not returning anything at all.
    Indexing Sphinx results in constant error emails.

    Please please help

Related Topics

Collapse

Working...