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

[Wayne Luke]

https://www.vbulletin.com/forum/articles/vbulletin-5-connect-aa/vbulletin-5-tutorials-aa/4363879-rebuilding-the-sphinx-index

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

[pmquist]

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

[BarkyJ]

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.

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?

[Wayne Luke]

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.

[BarkyJ]

@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?

[glennrocksvb]

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.

[[email protected]]

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.

[BarkyJ]

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.



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

[Wayne Luke]

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.

[BarkyJ]

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

[Wayne Luke]

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.

[Mark.B]

Hi Mark - Done
VBV-17598

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

Thank you.

[BarkyJ]

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.

[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.

[BarkyJ]

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.