Announcement

Collapse
No announcement yet.

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

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

  • 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

  • Wayne Luke
    replied
    https://www.vbulletin.com/forum/arti...e-sphinx-index

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

    Leave a comment:


  • pmquist
    replied
    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

    Leave a comment:


  • BarkyJ
    replied
    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 \\(like 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 \\-p1 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 \\(like 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        : http://forum.mysite.com.au/admincp/misc.php?do=doindextypes&startat=1000&pp=250&autoredirect=1&totalitems=0&indextypes=0
                                                    Referrer      : http://forum.mysite.com.au/admincp/misc.php?do=doindextypes&startat=750&pp=250&autoredirect=1&totalitems=0&indextypes=0
                                                    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?

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


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

    root@blah.mydomain.org [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?

    Leave a comment:


  • Glenn Vergara
    replied
    Originally posted by BarkyJ View Post
    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.

    Leave a comment:


  • yi.zhou@xplusz.com
    replied
    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.

    Leave a comment:


  • BarkyJ
    replied
    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:	9
Size:	36.3 KB
ID:	4375745

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

    Leave a comment:


  • Wayne Luke
    replied
    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.

    Leave a comment:


  • BarkyJ
    replied
    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

    Leave a comment:


  • Wayne Luke
    replied
    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.
    http://tracker.vbulletin.com/browse/VBV-16975
    http://tracker.vbulletin.com/browse/VBV-17135

    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.

    Leave a comment:


  • Mark.B
    replied
    Originally posted by BarkyJ View Post

    Hi Mark - Done
    VBV-17598

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

    Leave a comment:


  • BarkyJ
    replied
    Originally posted by Mark.B View Post
    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.

    Leave a comment:


  • Mark.B
    replied
    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.

    Leave a comment:

Related Topics

Collapse

Working...
X