What kind of performance are you fellas seeing as beneficial by using Sphinx search?
Upgraded from 5.2.5 to 5.3.2 - Sphinx Updated, Database errors when rebuilding
Collapse
X
-
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 pleaseLeave a comment:
-
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:
-
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
These errors are so ambiguous as to what the problem isLeave a comment:
-
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.Leave a comment:
-
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.
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:
-
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';
$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 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.
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
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 helpTags: None
Related Topics
Collapse
-
by activemindsI recently performed an upgrade to vb 5.6.3 and it caused some problems that seem related to the sphinx search. I switched to database search and the problems disappeared. The database search doesn't...
-
Channel: Support Issues & Questions
Fri 4 Sep '20, 1:06am -
-
by CraigIs there a way to determine, by log I suppose, whether a search index rebuild was successful?
I did the 5.6.3 upgrade with no issue.
Ran the search index rebuild when prompted...-
Channel: Support Issues & Questions
Fri 28 Aug '20, 4:21am -
-
by artlserverI had the error, "invalid SQL," on building the search index. It occurs with both the command line and web method.
With the web interface, the query seems to have run successfully...-
Channel: Support Issues & Questions
Mon 23 Sep '19, 1:39pm -
-
by fishingforumsearch index via command line - need some very simple a to z instructions on how to do this asim nice but a bit thick ... thankyou
-
Channel: Support Issues & Questions
Thu 4 Jul '13, 2:12am -
-
by CraigHow does the rebuild index process work?
Is there a way to rebuild a search index without maxing out db query limits (for me its 300,000).
The problem im running into is i get...-
Channel: Support Issues & Questions
Wed 11 Oct '17, 12:54pm -
Leave a comment: