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

    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
    James
  • BarkyJ
    Senior Member
    • Apr 2014
    • 221
    • 5.0.X

    #2
    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
    James

    Comment

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

      #3
      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.
      James

      Comment

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

        #4
        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
        James

        Comment

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

          #5
          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
          James

          Comment

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

            #6
            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.

            Comment

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

              #7
              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
              James

              Comment

              • William Thomas Jr
                Senior Member
                • Nov 2014
                • 526
                • 5.1.x

                #8
                What kind of performance are you fellas seeing as beneficial by using Sphinx search?

                Comment

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

                  #9
                  We installed Sphinx soon after migrating to vBulletin from another forum software, and found the standard DB search at the time to be extremely bad. So many searches we would do, would come back saying they were too common etc, and have no results. Or blatantly not find something which was valid. Maybe things with DB Search have improved since then, I am not sure. Sphinx just seems to be more powerful with what it returns, but if something goes wrong with Sphinx, like if its not running for whatever reason, it takes down the whole forum with an ambiguous error which means absolutely nothing and isn't helpful.

                  Wish there were better options.
                  James

                  Comment

                  • Mark.B
                    vBulletin Support
                    • Feb 2004
                    • 24286
                    • 6.0.X

                    #10
                    Best thing given the above information would be to get a bug report raised in the tracker, so the developers can take a look at it.
                    MARK.B
                    vBulletin Support
                    ------------
                    My Unofficial vBulletin 6.0.0 Demo: https://www.talknewsuk.com
                    My Unofficial vBulletin Cloud Demo: https://www.adminammo.com

                    Comment

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

                      #11
                      Originally posted by Mark.B
                      Best thing given the above information would be to get a bug report raised in the tracker, so the developers can take a look at it.
                      Hi Mark - Done
                      VBV-17598

                      Please check / edit as required. Hopefully what I posted is suitable.
                      James

                      Comment

                      • Mark.B
                        vBulletin Support
                        • Feb 2004
                        • 24286
                        • 6.0.X

                        #12
                        Originally posted by BarkyJ

                        Hi Mark - Done
                        VBV-17598

                        Please check / edit as required. Hopefully what I posted is suitable.
                        Thank you.
                        MARK.B
                        vBulletin Support
                        ------------
                        My Unofficial vBulletin 6.0.0 Demo: https://www.talknewsuk.com
                        My Unofficial vBulletin Cloud Demo: https://www.adminammo.com

                        Comment

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

                          #13
                          1) The config.php variable change from vbulletin_sphinx.conf to vbulletin-sphinx.php isn't necessary in many cases. Either approach will work but if you use the conf file you need to generate it with vbulletin-sphinx.php before hand. On unix, you can use the vbulletin-sphinx.php file direct (which is what we recommend), but that doesn't work on Windows. If you want to use the conf file instead of the PHP file, follow the Windows instructions to generate it.

                          We have not made any recent changes config.php.new file recently or anything else recently that might affect this. See the installation notes within your do_not_upload folder. They vary based on OS. We provide a number of notable defaults in the config.php file. Most people will never change any of the beyond the database connection information. Not all defaults work with all server configurations. This is why they are listed in the configuration file and not hidden in some PHP class deep within the file structure. When switching to advanced functionality like Sphinx search, it is often assumed that the user has enough knowledge to make these changes to fit their server configuration. However, they are often set in regards to a minimum level of technical knowledge.

                          2) The WARNING check doesn't hurt anything but we plan to improve this in the future.

                          3) The char_set and max_results warnings are due to changes in Sphinx, not vBulletin. However, we have fixes that will be applied in 5.3.3.



                          The quickest workaround to this issue is to write the conf file out instead of using the php file directly (see the windows Sphinx install instructions) and to delete the charset and maxresults lines. They aren't really needed if your version of Sphinx is displaying a warning.

                          4) The duplicate entry error shouldn't occur. If this continues to occur after clearing the searchindexes please let us know. They may be caused by prematurely refreshing the page if you're rebuilding in the AdminCP. The error says that a specific word in a specific post has already been indexed and it is trying to index it again for some reason. Normally, we recommend that you don't empty the searchindex before rebuilding but it can resolve issues like this. The database error and stack trace are actually very specific, as opposed to being ambiguous, but they are written for developers, not laymen.

                          I hope this helps.

                          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

                            #14
                            Hi Wayne,

                            I am on Unix.
                            The config.php file originally had .conf set in it, since the start.
                            I followed the instructions, and it simply did not work. Running 'php searchindex.php' with the conf file set, did not run the script. It had to be set to php for that to work, but resulted in what I posted above. conf or php set, neither had a positive result at all, errors after errors, and search inoperable at the end of it.

                            When rebuilding the index, which is what it prompts to do, Emails with these SQL errors and DB errors come in every few seconds. By the time it finished, I had hundreds and hundreds of emails. And at the end of it, search didn't work.

                            Followed the Unix install to a T, its running, but it simply doesnt work, and it use to work perfectly fine before the upgrade.

                            Sphinx use to work fine on 5.2.5 and we started using it many years before that.
                            But 5.3.2 has broken it, and we don't have the know how in house to know how to fix it. If its not designed for everyday people to be able to carry out these upgrades and understand the errors when they do come up, then surely the instructions and errors should be written to suit the audience. vBulletin isnt just used by developers and administrators.

                            The warnings themselves I am not concerned about, its the fact the errors flood the inbox and nothing works afterwards, despite following the instructions.

                            I cleared index multiple times, it did nothing to resolve the issues posted above.
                            Nothing was refreshed during the rebuild process, it was left in cpanel on the rebuilding screen, counting 250 entries at a time, for 30 minutes or so while it rebuilt, at the same time flooding the inbox with errors.

                            It is not clear me me about the two tracker tickets you have quoted, if these even fix the problems I am experiencing.
                            Why is it that the solution on post #6 above, solves it for that person, yet the reply in the tracker seems to suggest it is not relevant.

                            I am still none the wiser as to how to solve this problem
                            James

                            Comment

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

                              #15
                              There were no changes to Sphinx in 5.3.2. However, there were changes in previous versions between 5.2.5 and 5.3.2. You have to delete your 5.2.5 indexes and tables, restart Sphinx, then rebuild. Changes to Sphinx configurations are noted in the release notes and these often require deleting the existing indexes and Sphinx tables before indexing.

                              When 5.3.3 is released, the indexes in Sphinx will need to be redone as well.
                              Translations provided by Google.

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

                              Comment

                              Related Topics

                              Collapse

                              Working...