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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • BarkyJ
    Senior Member
    • Apr 2014
    • 221
    • 5.0.X

    #16
    As mentioned, I have already done this. Deleting indexes and rebuilding does nothing other than create a swag of constant errors via email, and after the process, search responds with 0 results 100% of the time. Sphinx is running as otherwise the whole forum does not even run.

    The process to delete index's is simply by clicking this, right? the 2nd link, does not work by the way, it comes up with the forum inside the sub window of cpanel menus still on the left, and it says its encountered an error or something to that effect.

    Click image for larger version

Name:	2017-08-16_10-37-02.png
Views:	260
Size:	36.3 KB
ID:	4375745

    Is there anything else that needs to be done, as simply doing this and rebuilding, does not work.
    James

    Comment

    • yi.zhou@xplusz.com
      Member
      • Feb 2017
      • 31
      • 5.2.x

      #17
      BarkyJ , when you run php searchindex.php, after you choose the core path, choose for 1 to clear index, that's where I got the connection error, but once I pass the db username and password in the core.php, it fixed for me.

      Admincp not work for me either.

      Comment

      • glennrocksvb
        Former vBulletin Developer
        • Mar 2011
        • 4011
        • 5.7.X

        #18
        Originally posted by BarkyJ
        the 2nd link, does not work by the way, it comes up with the forum inside the sub window of cpanel menus still on the left, and it says its encountered an error or something to that effect.
        There was a known issue where some AdminCP links are missing the /admincp path. They fixed it but there are still some broken links and I think that's one of them. Try opening the link in a new tab or window then add /admincp to the URL.


        Flag Icon Postbit Insert GIPHY Impersonate User BETTER INITIALS AVATAR Better Name Card Quote Selected Text Bookmark Posts Post Footer Translate Stop Links in Posts +MORE!

        Comment

        • BarkyJ
          Senior Member
          • Apr 2014
          • 221
          • 5.0.X

          #19
          @yi.zhou Yes as per bottom of Post #2, I did select option 1

          [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

          Is this what you mean?
          So to me, your core.php fix is still required to make that work, yes?
          Or is the proposed solution vBulletin said in the Tracker, the solution?
          James

          Comment

          • Wayne Luke
            vBulletin Technical Support Lead
            • Aug 2000
            • 73976

            #20
            Your Sphinx is only writing 83 records for some reason.

            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

            You shouldn't have to edit any files in the /core directory.
            Translations provided by Google.

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

            Comment

            • BarkyJ
              Senior Member
              • Apr 2014
              • 221
              • 5.0.X

              #21
              Hi

              Just updated to 5.3.3 and tried Sphinx again, and getting the same thing as before.
              The Dev site is working on Sphinx, so I changed the vbulletin-sphinx.php to point to our production site, and now when rebuilding I get emails constantly with database errors, the same as before.

              Code:
              Database error in vBulletin 5.3.3:
              
                                                              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, trendingweight, eventstartdate, eventenddate, titlesort
                                                              )
                                                              VALUES (
                                                                              6382, 'sound generation for picaso [URL="file://(like"]\\(like[/URL] p1t\\)', '\r\n\r\nas i\'ve mentioned in the previous post the site is in the process of being updated and the list of new 4dgl features is not yet visible. the picaso on the new [URL="file://-p1"]\\-p1[/URL] modules will actually play wave files stored under fat16 and not rtttl tunes which is only limited to goldelox based modules.', 25, 6382, 598, 598, 60, 324, (6382,598,60,38013,2,1), '{"n6382":0,"n598":1,"n60":2,"n38013":3,"n2":4,"n1":5}', 2, 'Atilla', 0, (), 1, 1, 1, 2, 0, 1, 0, 0, 1220631420, 1220631420, 1220631420, 0, 0, (), 0, 0, 0, 0, 'Sound generation for PICASO [URL="file://(like"]\\(like[/URL] P1T\\)'
                                                              );
              
                                                              MySQL Error   : sphinxql: syntax error, unexpected $end, expecting FROM or ',' near 'version'
                                                              Error Number  : 1064
                                                              Request Date  : Thursday, September 21st 2017 @ 11:02:37 PM
                                                              Error Date    : Thursday, September 21st 2017 @ 11:02:37 PM
                                                              Script        : [URL="http://forum.4dsystems.com.au/admincp/misc.php?do=doindextypes&startat=1000&pp=250&autoredirect=1&totalitems=0&indextypes=0"]http://forum.mysite.com.au/admincp/misc.php?do=doindextypes&startat=1000&pp=250&autoredirect=1&totalitems=0&indextypes=0[/URL]
                                                              Referrer      : [URL="http://forum.4dsystems.com.au/admincp/misc.php?do=doindextypes&startat=750&pp=250&autoredirect=1&totalitems=0&indextypes=0"]http://forum.mysite.com.au/admincp/misc.php?do=doindextypes&startat=750&pp=250&autoredirect=1&totalitems=0&indextypes=0[/URL]
                                                              IP Address    : 121.xx.xx.xx
                                                              Username      : myusername
                                                              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 157
              #4 vBSphinxSearch_Core->indexText() called in [path]/vb/search/core.php on line 116
              #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 106
              #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

              Any ideas?
              James

              Comment

              • pmquist
                Senior Member
                • Jan 2006
                • 232
                • 5.7.X

                #22
                BarkyJ , did you get this problem solved?
                I have had the same problem, trying to figure it out, and have finally solved it with some help from th vB-staff. In my opinion the guides on how to update sphinx here on vBulletin would need an upgrade as well since there is lots of information missing. Wayne Luke i have written down what I did below, but it would need some quality control from you since this is not mine expertise and I might have done some unneccesary steps. Please comment. I would love to have a complete guide here on the forum or in the do-not upload directory. It seems like all guides are missing some important parts.

                In short the solution was:
                - Removing the old sphinx index files manually by stopping searchd, deleting the files in the sphinx/data/ and then starting searchd again (this was the key i think)
                Stop searchd by sudo /etc/init.d/searchd stop
                In the current vbulletin-sphinx.php remove the parts about index
                Rotate searchd with sudo /usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/vbulletin-sphinx.php --all –rotate
                Remove all the files in /sphinx/data/
                Now replace the vbulletin-sphinx.php with the correct vbulletin-sphinx.php (where the index part is there)
                - Now start searchd again with /usr/local/sphinx/bin/searchd --config /usr/local/sphinx/etc/vbulletin-sphinx.php
                - Run searchindex.php in the www-root (This did not work for me. Did not create the new datafiles)
                - Made a rotate /usr/local/sphinx/bin/indexer --config /usr/local/sphinx/etc/vbulletin-sphinx.php --all –rotate
                - Reindexed the database in admincp

                Comment

                • Wayne Luke
                  vBulletin Technical Support Lead
                  • Aug 2000
                  • 73976

                  #23
                  From time to time, we will need to update the indexing schema for the Sphinx server. In order for this fix to take effect, you will need to update the sphinx schema for the index. Follow these steps to rebuild


                  This is the current steps. If you're on Linux, then you need to have the appropriate permissions to rebuild the index.
                  Translations provided by Google.

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

                  Comment

                  Related Topics

                  Collapse

                  Working...