vBulletin Mods

Still waiting for some help here. Thanks.

Did you find a solution? I did a line by line comparison to my conf and did not find anything obvious.

You XXed out your db name (sql_db=xx) does that match sphinx.sphinx_counter. What I mean is, does your sql_db=sphinx ? (they should match or you can remove the sphinx. part and it should work as well)

Also, just as a suggestion, I would set up a mysql user other than root (i.e. sql_user = root).

-RayJ

No, the forum is in another database. sphinx is just a database for the counter.
I try the user change; but that shouldn´t make any trouble.

Bummer, I was hoping it was something simple. As far as the user name goes, that just seems to be the default security advice that I run into. So I thought I would pass it on.

I put my counter in the same DB as vBulletin (I am lazy!).

My question is: Does your script have a "mysql_select_db()" command to access the "sphinx" counter DB? I am asking partially due to my own ignorance as I have not taken the time to really learn how vBulletin and Sphinx work together and I am not sure if the counter DB is automatically "selected" for you. (again with the lazy!)

-RayJ

Why do you want to complicate things by keeping the counter in a separate db? What is the issue with keeping it in the same db?

Even if that does not solve the problem, at least we all will know the problem is elsewhere.

Here i go with sphinx counter in forum db:

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

According to Orban
So....

If searchd is already running
./indexer --config /usr/local/sphinx/etc/sphinx.conf --rotate --all

If searchd is NOT already running
./indexer --config /usr/local/sphinx/etc/sphinx.conf --all

I would delete all the indexes that were created under /var/sphinx/ first.

-RayJ

Thanks alot ! That was the problem. Thanks again.

Another question; what are recommended update settings ?

Just curious for people's indexer updates, how often do you do them and how to you manage the delta vs full?

Right now I have a cron job running every 5 minutes to do the delta files since they only take a few seconds. I do a full rebuild maybe once a week or so (after disabling the delta cronjob so it doesn't try to run at the same time).

I would like to automate the full rebuild, but changing the delta one would require a really ugly cron looking line. Alternatively I could have another script check the time and figure out which job to run and if a job is already running and all that.

Second, how do you guys rotate your searchd & query log files? The only way I have gotten it to do that is to kill searchd completely and restart it. It would be nice if there was a more graceful method.

Oh boy... found a little issue that might affect some people...

*If* you are running MySQL 4.0.x you can't do the sub-selects in the conf file. Not a *huge* deal as the code can be re-written (which I'm doing now). Yes I know I should be running a much newer version of mysql, but I have an app that uses the old timestamp format and I haven't had a chance to re-write it all completely, thus I'm still using the old mysql.

I'll post the modified conf up later once I fix it compeltely and edit out stuff specific to my forum. ;)

No problem! Having just gone through all this myself, I am happy to help by passing on what I have learned.

I also like to give credit where it is due:
so thank you Orban (
http://www.vbulletin.org/forum/showpost.php?p=1104866 )

If you are asking about indexing, keep on reading!

When to run the indexer seems to be a matter of preference, keeping in mind the usage/size of the database in question. I run two (almost) identical crons, one every 20 min (for the deltas) and one every day (for the full index). The LOCKFILE helps to keep them from stepping on each other.

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

You could also replace ">/dev/null 2>&1" with "| mail -s "Sphinx Report" YOUR_EMAIL_HERE" to get an email of the output.

I created a folder called: "/etc/cron.20minutes" and then added the line "*/20 * * * * root run-parts /etc/cron.20minutes" to "/etc/crontab"

then put the cron with the DELTAS_ONLY_LINE in that folder and put the cron with FULL_INDEX_LINE in my "/etc/cron.daily" (you could also put it in "/etc/cron.weekly")

As far as log rotation goes, I used the Power of Linux once more! I just created the file:

"/etc/logrotate.d/sphinx"

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

NOTE: the two paths/files need to match your sphinx.conf
log = /var/log/searchd.log
query_log = /var/log/query.log

Now they rotate all by themselves!

-RayJ

Thanks for all that info! I'm about to head home for the day but I will definitly re-read it more thoroughly later tonight. :)

Well, I made some headway after reading through this entire thread...

First I had the issue with the coventry error, since I don't use it I just commented out the code as a quick fix for now.

Then I started getting the assert error if I searched a different time frame from the default (ex: a week ago and newer). So I added the assert_options as another quick fix.

What was bugging me the most was my dates were not being sorted properly, and upon digging through the code I discovered why...

First, in your sphinx.conf file's "post_src" (whatever you name it - it's the first bracket code), in the sql_query you need to add lastpost to the end of the SELECT. Then below the existing sql_date_column line, add another saying sql_date_column = lastpost. You will also need to add the lastpost to the SELECT in the delta code (next bracket code bit).

Rebuild your indexes completely...

Then in the sphinx.php file you will notice the sort_date defaults to 'dateline', and in the 'titleonly' if/else statement it changes to 'lastpost'. The "else" part of that statement refers to all queries that searches the entire posts - which can return as either posts or threads. So basically within the 'else' code (like after the set weights) just add:

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

So now when results are returned as threads, it sorts them based on the last post date, not the date when the post with the matching word was found.

As promised, I'm attaching my sphinx.conf file that works with MySQL 4.0.x (because that version does not support sub-selects). I also have the above mentioned changes if you don't fully understand what I was trying to explain above. Included too is my stopwords file (it's just the MySQL default list).

Tomorrow I'm going to try and go through and ensure all the current functionality works with all the various search options, then I'm going to slowly try and work my way through to add the features that were removed from the vB template. :)

The problem with adding the "lastpost" to the attributes is that it will only be accurate immediately after reindexing. On a sufficiently busy forum, with frequently updated threads, the sorting by lastpost may be off by large amount just a few hours after full reindexing.
Though it's something you can live with if the forum isn't very active.

Ooo... you are right, I guess that's what I get for the late night coding. Boy, that is a pickle, since the delta only adds new posts & threads there is no way to go back and update the existing data (without a full reindex).

If only there was a way to query the mysql table to grab the latest post/thread attributes based on the sphinx results. I haven't fully examined the code so I don't know what all is and isn't possible yet.

Thanks for pointing out my oversight... ;)

I outlined the solution to this problem in the post #306:
http://www.vbulletin.org/forum/showp...&postcount=306

It's because of the bug in unpack() function in PHP 5.2.1:
http://bugs.php.net/bug.php?id=40749
First seen here, workaround included:
http://www.sphinxsearch.com/forum/vi...=340&from=1887

Yeah, shoban and I figured out what the problem was, he was contacting the PHP guys to yell at them who claim it "is not a bug". I hadn't seen the workaround though, thanks!

kmike - Yep, that worked perfectly.

The more I look at the sphinx.php code, the more I think overall functionality would be better just to merge the code in with the search.php file, then I think all stock vB functionality could be brought back. I'm going to tackle this on Sunday as I have some other things I need to take care of today and tomorrow I've got plans.

Sphinx 0.9.7 has been released:
http://www.sphinxsearch.com/doc.html#changelog
It fixes the crashes in the excerpts bulding routines and also the memory fragmentation problem. A host of new features are added, too.

Have you upgraded yet? Any issues? If I do upgrade from RC2, do I need to copy sphinxapi.php to all of my sites again or hasn't it changed?

Yes, I've upgraded and it works perfectly.
I don't know if there are any protocol changes in 0.9.7 release vs RC2, but I guess it's better to update sphinxapi.php just in case.

Yeah there's quite some changes to sphinxapi.php so I'd advise you to replace yours with the new one.

Well good to hear the new version works fine and there are no more changes. I really need to upgrade from the version we have been running since last year. I think we will probably wait and move with the next upgrade to Vb.

By the way has anyone had any problems compiling the latest release - I will be asking over on the sphinxsearch forums but thought I would ask here.

I did a quick configure and make on a new machine running FreeBSD 6.2 and it didn't like it at all.

Upgraded to sphinx 0.9.7 no issues yet.. Just curious has anyone found a way to fix the search ordering yet?

As we are plannning to move to vBulletin 3.6.5 (or whatever the latset version is) I was wondering which is the latest set of instructions for changing the vBulletin search.php instructions.

Having tried the first example I found - hoping that I cound just use the diff file I seem to get errors on my test server - the latest release of sphinx works fine and I can get results using test.php (though i did have fun installing on FreeBSD due to the 64bit error floating around - but fortunately the updated ports version worked).

Anyway whenever I try to use the modified version of search.php I don't get any results and get errors on the page.

Any ideas on what might be going wrong?

I have checked and as far as I can tell I am looking in the correct sphinxsearch database (on the local machine).

Having researched this frther I have found that although searchd and test.php work fine for my internal database (DS) when I try to use test.php against the hybrid index I get:

Query failed: failed to read searchd response (status=0, ver=263, len=-2147483487, read=0).

My guess this is possibly some kind of configuration issue with the sphinx.conf for the files and index though it seems to index the file perfectly.

Anyone have any ideas?

Are you running a 64bit operating system?

As Orban says, that sounds like the 64bit issue. I was hoping the latest sphinx update fixed it (the changelog seemed to indicate it had), but I still get that error with php 5.2.1. I had to revert back to 5.2.0.

The latest sphinx and php 5.2.0 works fine with 64bit.

Thanks - I had a different 64bit issue which I solved and didn't realise there was a second one.

can some one please provide a step by step how to install Sphinx Search and configuring
and then how to use it with Vbulletin

Hi,

Well I now have a system that appears to work but for the vBulletin database always gives no results but for my own database gives the expected results.

Between 0.9.6 and 0.9.7 have there been many changes to the sphinx.conf it is the only thing that I can think might be causing the issue unless anyone has any other ideas?

http://www.vbulletin.org/forum/showpost.php?p=1104866

This link is given right at the top of the first post of this thread. This is the most comprehensive guide so far. If this is not enough, then you have to read through the whole thread.

ALanJay, I am running sphinx without problems on FreeBSD amd64. If you have any specific questions about versions etc, maybe I can help.

Thanks amcd,

Well most of my FreeBSD issues have been solved - compilation and running all see fine.

I have changed sphinxapi.php to include the sugested:

function unpack31($f,$s)
{
$arr=unpack($f,$s);
foreach($arr as $k=>$v) {
$b = sprintf("%b", $v);
if(strlen($b) == 64){
$arr[$k]=bindec(substr($b, 33));
}
}
return $arr;
}


I have made the changes to sphix.conf for I think for the changes to the column settings ie

sql_group_column = forumid
sql_group_column = threadid
sql_group_column = userid
sql_group_column = postuserid
sql_date_column = dateline
sql_query_post =

I think the SQL stuff is unchanged from 0.9.6 to 0.9.7 and still have for the Post Index:

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

and the delta

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

index DSFullTextPostIndex
{
type = distributed
local = DSPostIndex
local = DSPostIndexDELTA
}


it all seems to work ok when I create the indexes from scratch but the test.php and vB search.php always give 0 results.

ie

php ./xx-test-search.php -i DSFullTextPostIndex "digital tv"
Query 'digital tv ' retrieved 0 of 0 matches in 0.000 sec.
Query stats:
'digital' found 0 times in 0 documents
'tv' found 0 times in 0 documents

yet when I use my internal database it all works fine:

php ./xx-test-search.php -i DSramsIndex "digital tv"
Query 'digital tv ' retrieved 1000 of 1729 matches in 0.005 sec.
Query stats:
'digital' found 20655 times in 4243 documents
'tv' found 24047 times in 5666 documents
Matches:
1. doc_id=4744, weight=208, date=2002-01-20 22:44:53
2. doc_id=4868, weight=208, date=2002-01-31 20:01:25

This would imply some sort of error with the creating of the vBulletin index in differences between the 3.0 stream I was previously using and the 3.6 stream of the new test site?

Any thoughts?

I am using sphinx 0.9.7 rc1. I have not made the edit for unpack31.

Why do you write spy_forum.sph_counter all the time? Is the counter in a different DB? I have the counter in the same DB.

One thing I noticed is that the command line search does not return any results from compound indexes.

search -c /usr/local/etc/sphinx.conf --index 'postmain' something
and
search -c /usr/local/etc/sphinx.conf --index 'postdelta' something
both work.

but
search -c /usr/local/etc/sphinx.conf --index 'post' something
returns zero results.

Maybe you are facing the same issue with the test script.

For what it is worth, I can send you my files if you want.

Confirmed...I don't know if that is a bug or a feature.

Well that explains something - having done some more testing I can get results from the test programme now from ThreadIndex but not PostIndex which is very peculiar.

And this follows through on the actual vb search.php in that if you "search titmes only" it seems to work but "search entire posts" :(

One of my colleages was kind enough to compare the actual mySQL and the results from the different 3.0 and 3.6 databases and we discovered that there was a difference. It appears that after the upgrade (to 3.6) the index doesn't seem to be being correctly read so an explicit inclusion of - USE INDEX (threadid) - we thoguht might work:

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

Except that doesn't work when creating the sphinx index but does when using mysql directly.

:(

Are further modifications to orban's code required to get this working?

It seems that my modified search.php is still using the VB search engine for some queries. (those without keywords)

Yes mine doesn't support that.

thanks orban for the script, I'm loving it!.

I used the search.php and applied orban's patch but I only get the result when I choose the option xx month ago or something but not "Any Date". The debug returns the following for "Any Date"

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

While the same keyword from "Yesterday" returns

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

I'm guessing something wrong with the php but not sure which script.

Hey All,

Mentioned way back was a possible solution for getting posts per userid into sphinx - has actually implemented this or does anyone have any other ideas - im pretty sure that some of the remaining slow queries in my board relate to this functionality and would be curious to see what people are doing?

btw the sphinx engine is running amazingly we are averaging now just under 10000 searches on it a day and its had no real issues!

Cheers

Neil

ALanJay, did you finally solve the problem? I upgraded php yesterday, and now my search doesn't work. All searches return zero results.

edit: It works now. The unpack31 trick did it.

I have this working but seem to be running into a strange issue..

When I search for the same word more then once, I get the following:

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

So, it appears that once I search for a particular keyword, it never "renews" the search and instead always shows the old results that were generated a long time ago?

that's because u set the search to share result.

Options->Message Searching Option->Search Result Sharing

Yup, your right. Thanks.

I do not see an error_reporting in the php files? How did you disable it?

its in php.ini

most vbulletin scripts also set it in the first few lines like this:

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

orban, I have a question. Did you patched and compiled MySQL 5.x as described in the readme file?
http://www.sphinxsearch.com/doc.html#sphinxse

I'm about to build an RPM for MySQL with SphinxSE. I think is good we test everything in this direction also, it should increase performance since everything is directly build into MySQL.
Let me know what you think. Thanks.

No, I'm not using SphinxSE. Afraid. :(

Has anyone figured out a fix for the "out of order" results issue yet?

The fix is to use sort_search_items() function where appropriate.

You wouldn't happen to have an easy way to implement that, would you? My PHP knowledge is somewhat lacking :)

Hehe, I was hoping for something a bit more cut and paste as well. I was hoping to not have to familiarize myself with the search code since I managed to avoid it the first time around ;)

It's been one of those things we get a user complaining about every so often that I've intended on fixing at some point but just haven't gotten to, and still don't really have the time to, but keeps getting brought up..

I changed the minimum search word length to 3 in sphinx.conf, but searches for 3 letter words still return no results (I rebuilt all the search indices)

Is there something else that will need to be changed to make this work?

You also need to change the limit in the admin panel.

I did that on my forum. I'll try to find the relevant code and post it here for ya. It was pretty easy IIRC.

Thanks Jason, that would be appreciated. I have had my hands full with other projects and haven't had time to go digging either.

I'd really appreciate that code as well.

On another note, does anyone know if I can use Sphinx to power the similar threads search in vBulletin or will I still need to use the default engine for that. Any pointers here would be much appreciated.

Orban, the template you have on your site:
http://forums.mtgsalvation.com/search.php

is this one?
http://dragy.de/public/sphinx/sphinx...s.template.txt

If you made any changes, please post them. Thanks.

Hello!

does anyone have a correct patch for search.php on a vbulletin 3.5?
what to add / replace etc.

See below. Easier.

doopz: You can probably apply to changes I've made down there easily to vB 3.5.

↓

Implementing Sphinx full-text search engine

Based on Sphinx 0.7.9 and vB 3.6.7 PL1. This means all file edits and config files are only tested with those two versions, it doesn't mean you cannot make Sphinx work with your vB 3.5 installation but it will require manual work on your side.

Known limitations

1. You cannot filter by number of replies
   • Possible Fix: Add another "sql_group_column" holding the number of thread replies, the search will using the numbers of the last thread reindexing though (depending on your setup, hours to days old results).
2. Sorting by title/number of replies/views/thread start date/username/forum isn't possible
   
   • Basically same issue as in (1.), Sphinx doesn't have the necessary data.
3. You can only use Sphinx to perform queries that have a full text component. So searches by userid/forumid WITHOUT a key word are not possible. These searches can run on indices though so they shouldn't be an issue.
   
   • Workaround by kmike. "You can emulate the search by user in sphinx by adding a fake unique keyword per each member in the mix (e.g. "_userid_12345"). Searching by this keyword will return all posts by the member with userid 12345."
4. Search Results out of order because the time stamps are too old
   
   • Sphinx doesn't query MySQL to get the latest time stamps. So if your thread had its last reply 3 days ago, was indexed by Sphinx 2 days ago and now today got a new reply, Sphinx will still assume its last reply was 3 days ago. In the search results, it will put waaay back instead being at the top. There is no easy fix for this, and certainly no fast one, because this is just what makes Sphinx so fast. We're sacrificing a bit of "up-to-date-ness" to gain speed. If are in desperate need of fixing this, kmike outlined a fix. Basically this will send a results to MySQL and sort it again, giving you up-to-date results by sacrificing speed. It's up to you to find out if it's worth it.

What Sphinx can do for you

1. Incredibly fast full text searches on huge amounts of posts
   
   • It's really fast, really really really fast. Even on intersections of multiple keywords on several hundred thousand results.
2. Replace forum search, search in this forum and search in this thread
   
   • Mimicking the default forum search for all but a few details
3. Nearly instant indexing of new posts
   
   • Thanks to a special config file setup called "Live Updates"

Setting up Sphinx

1. Grab Sphinx here: http://www.sphinxsearch.com/downloads.html and compile it
2. Read a bit of the documentation to get familiar with it, might wanna peek in the installation bit
3. Grab the sphinx.conf.txt at the end of this document (rename it to sphinx.conf). This is my configuration file. You have to, at least, fill in your database info and adjust the paths /.../
4. You have to create a counter table that holds information about the last indexed post/thread for the Live Updates:
   

   Block Disabled:      (Update License Status)
   Suspended or Unlicensed Members Cannot View Code.

   
   You can either place this in the same database as vB or in a different one, but don' forget to adjust sphinx.conf accordingly then (prefixing sph_counter with your database name: yourdb.sph_counter)

Running Sphinx

• You start Sphinx with "searchd --config /.../sphinx.conf" this will create a new process called "searchd".
• Indexing documents is handled by "indexer". You have to make sure you know whether it's running or not before you start an indexing process, this is crucial.
  • searchd is running: use "indexer --rotate", it will create temporary new files and rotate them in so searching won't be broken
  • searchd isn't running: use "indexer" without rotating it will just replace your current files
• For creating the full indices it is recommended to shut down Sphinx because it might take a while and your server will be quite busy (unless you run sphinx on a slave). Reindexing all posts and threads is done by "indexer --config /.../sphinx.conf --all" or "indexer --config /.../sphinx.conf --rotate --all" if searchd is running.
• Creating the delta indices for Live Updates is issued by "indexer --config /.../sphinx.conf --rotate postdelta threaddelta"
• You can test your indices with "search", the third executable installed by Sphinx. Call "search" and it tell you how to use it ;)

Live Updates

• You have to figure out a couple values: How often to re-index the whole thing, how often to re-index all threads, how often to do Live Updates for postdelta and threaddelta.
• "indexer -all": I do this about once per week on a very un-busy time, usually manually.
• I re-index all threads once per day, we just have 80k so this takes no time.
• I recreate the delta indices every five minutes for both posts and threads so you have to wait between 1 and 5 minutes before your new threads/posts start showing up in search results.
• I suggest adding cron jobs for those taks on *n*x, other OSes I don't know, can you even run Sphinx on Windows?

Plugging Sphinx into vBulletin

1. Sadly enough this requires file modification. I'm checking every version if they finally added a way to plug in a different search system like WordPress for examples does, but no luck so far. There is 5 edits required, I listed them in search.php.txt at the end of this document for easier references and so you can save it for future use. You will be editing "/.../forums/search.php". Don't forget that every vB upgrade the file will be overwritten and you will have to apply the changes again.
2. We also need sphinxapi.php, it's from "/.../src/sphinx-0.9.7/api" where your Sphinx source files are. Copy paste it to "/.../forums", where global.php lies.
3. And last item is sphinx.php which will handle the search. Grab sphinx.txt.php and rename it to sphinx.php and put it into "/.../forums/includes". Open it and adjust the values on top. You can obviously move those files to where you want just don't forget to adjust paths.
4. Because we cannot offer all search options vB default search can, I removed a couple lines from the "search_forums" template. They are listed in search_forums.txt at the end of this document.

Bugs and Fixes

• Problems with $Coventry: Try to kill " AND in_array($docinfo['attrs'][$sphinx_conventry_userid], $Coventry)" from sphinx.php if you are not using Coventry
• Problem with asserts in sphinxapi.php, values sent to sphinx not being integer: http://www.vbulletin.org/forum/showp...&postcount=284 Fix by amcd, thank you
• sphinxapi.php ignoring max matches limit set in sphinx.conf: http://www.vbulletin.org/forum/showp...&postcount=301 Thanks jason
• Problems with 2 or 3 letter words: Make sure you change the limit in vB AdminCP search options and in sphinx.conf, turn off searchd, indexer -all
• PHP unpack() bug on 64bit systems: http://www.sphinxsearch.com/forum/vi...=340&from=1887 4th post is helpful and fixed the issue for me http://www.vbulletin.org/forum/showp...&postcount=360 here's another post by AlanJay outlining the problem, and updating Sphinx
• MySQL 4.0 doesn't support Sub-Selects, eoc_Jason created a fix: http://www.vbulletin.org/forum/showp...&postcount=338 Thanks
• Issues with very old results showing up: Options->Message Searching Option->Search Result Sharing turn it off

Contributions

• Viewing Sphinx Log files in AdminCP: http://www.vbulletin.org/forum/showp...&postcount=312 Thanks UK_Jimbo.
• Bash script to help indexer fun more smoothly by creating lock files: http://www.vbulletin.org/forum/showp...&postcount=336 Thanks raywjohnson

Can somebody give this a look, I tried to list some limitations/bugs/contributions that we are currently experiencing. Did I miss anything important?

Orban,

Very nicely put together. I still have my older working Sphinx setup working (thanks to you and many others in here) so I have no need to change anything just yet, but thanks for the wonderful write-up!

Couple of small things:

1. you did

CREATE TABLE sph_counter

but used sphinx_counter in sphinx.conf.

2. Then it would be great to have PREFIX_ where you would place your personal Vb table prefix. I added them there but it is not an easy task for those who do not know mysql syntax.

3. A step by step how to implement sort_search_items() would be nice.

Thanks for EXCELLENT work!

EDIT: Couple of things I would still like to know: when you have Sphinx search in place, do you need to have FULLTEXT index(es) in Vbulletin at all?

Also, is Sphinx used in "new posts" seach, too?

orban, I don't see any reference in vBulletin or Sphinx to 'timesegments':

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

Is there something I miss? Thanks for explaining.
Also, if anyone got kmike's trick (for username is userid_12345) fixed into their configuration files, could you be kind and post here the actual code?

Thanks for taking the time to write this up.

Another question: is there a way to check if searchd is running and if not, put text "search is offline" to the search page?

Very easy.

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

I wrote the check as a function because you can use it in several areas, this way.

Now, back to my question. Can anyone help me with the username setup? I can't think how you can use a variable in Sphinx conf file... because you cannot. Obviously I`m wrong, kmike did it but unfortunatelly he is not available.

http://dev.mysql.com/doc/refman/5.0/...unction_concat

The query that grabs the posts, use two concats:

CONCAT( post, ' ', CONCAT( 'userid_', userid ) )

untested, but I hope you get the idea. Then you need to modify search.php to transform a given userid into the string...

Aha, thanks orban. What I want to do is this:
If an user wants to search for all threads/posts related to a specific user, he enters a username then leaves the search field empty. The results will show all threads started by that user, ordered the way you like it in Sphinx.

Anyone wants to work with me on this project? I PM'ed kmike, hoping he will join us... since he is the only one who managed to fix this, not to mention other little extras. :)

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

Then you call it anywhere you like:

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

Can you post results on your busy boards and let me know how it impacts the performance?
The function above has less processing code then the original sort_search_items() function.

The PHP BBCode at vb.org is screwed, it breaks the code lines. Switched back to Code, much better.

this will not work in a multi-server setup.

True, I'm not there yet with multiple servers. :)
Use this instead:

Block Disabled:      (Update License Status)
Suspended or Unlicensed Members Cannot View Code.

I run a failsafe on my server... if searchd is crashing, vbulletin search will take over automatically.

Edit: Let me dig into this more... I think that searchd will still spit an error, even if it's running, something like (no error).
I will post at sphinx site to ask Andrew how exacly the last error works.

You should use lockrun instead, is way more robust then a shell script.

Never mind, I sort it. :)

I still would like to know: when you have Sphinx search in place, do you need to have FULLTEXT index(es) in Vbulletin at all?

Maybe it is a stupid question and FAQ and RTFM etc, but please take a second to answer yes or no if you know the answer, thanks! :)

Nope.

not only do you not need the fulltext indexes, but also that having them will not give you the full benefit of an external search solution as mysql will continue to spend (waste) time keeping them up to date.

Hmmm amcd, you dropped the indexes? I never thought of that.
What exactly you guys did related to this issue? Thanks for your reply.

Dropping the indexes from the thread and post tables is one of the first things I did after installing sphinx.

Remember to close the forum while you drop the indexes but you should find that inserts to these tables are much faster.

Thanks for the info. :)
Can you post the queries?

If you're using FULLTEXT MySQL search then the word table won't be being used. I'd truncate the word table rather than dropping it just in case.

FULLTEXT search works (from memory) using indexes on the thread and post tables. You can drop those two after cutting over to sphinx.

edit: nice edit there TECK while I was posting :p

You are to fast for me. :)
I edited the previous reply. Could you be kind and post the queries?
I never played before with indexes. Thanks.

TRUNCATE TABLE `PREFIX_word`;

ALTER TABLE `PREFIX_post` DROP INDEX `title`;

KRon's improvements are worth doing, too:

ALTER TABLE `PREFIX_post` ADD INDEX `th_search` ( `threadid` , `visible` , `dateline` )

ALTER TABLE PREFIX_pmreceipt DROP KEY `userid`

ALTER TABLE `PREFIX_pmreceipt` ADD KEY `userid` (`userid`, `readtime`);

ALTER TABLE `PREFIX_post` DROP INDEX `userid`

ALTER TABLE `PREFIX_post` ADD INDEX (userid, dateline);

Thanks PSS for integrating Kron's MySQL optimizations. :)
What is the `th_search`? Is not a vBulletin field. Thanks.

that is just an index name. you can write anything there.

I'm leaving this place if somebody wants to take over this thread and keep the guide up to date feel free to do so it's on page 26 I think. Bye.

Sorry to hear you won't be posting here any more.

Why am I not surprised... Today, I've got a 30 points infraction warning for expressing myself freely.
Check my blog for more details.

Any ideas on this?

I am having problems with my Sphinx search.

I set it up in a basic setting and it is working fine, with a 4-character minimum search. I used the settings and hacks to search.php as described earlier in this thread.

Then I changed VBulletin to allow 3 character words and some exceptions for 2-letter words. I changed sphinx.conf to allow 2 letter words.

I reindexed sphinx, and still can search only for 4+ character words.

Some of the complicating factors: Web server is NOT the searchd server. I do my indexing and run searchd on one host, I run the web server on another host.

Any ideas? I can search using "search" from the CLI and find the 3-letter hits, so I think it has something to do with the way sphinx is shoe-horned in to search.php.

I'm also curious about this :(
I've read all his latest posts and there's no sign of anything that could be wrong. Pretty strange. All his mods were removed on his request.

Has any staff member more information about this as I also can't contact him (pm/email contact turned off) about it and the future of his old mods ...

Hey all,

I'm a bit confused as to whats going on - sphinx has saved our board massively and I want to continue to use it! However, I came to look for the install stuff as am about to upgrade versions and find that its gone - does anyone have access to that first post and also the diff files etc and I will attempt to see whats going on so that i can use it on my upgrade!

Obviously don't know any of the politics here, either way - thanks for all you have given this far orban this product has certainly saved my bacon and I truly hope that we can continue to use it!

Thanks

You mean this post: http://www.vbulletin.org/forum/showp...&postcount=387 ?

sorry my mistake - i always got the info through post 1 which now says ...

Thanks!

It's a shame that vb.org staff keep running away people that improve on vBulletin. This thread has helped so many big board admins run their boards more efficently.

Did he post anywhere that he is quitting vb.org because of staff problems?

No, there was no vB.org staff related discussion with orban on the forums. So I really don't understand it (any staff members to give us some more information about it?) :(

would be appriciated..if any one can give any clue........

Very interesting thread, following this closely.

Does this hack for Sphinx with VB allow for searches in titles only? We have it installed, I believe I installed it all correctly, and searches are working great. But if the search is done from the Advanced page, with "Search Titles Only" selected, it fails to return anything.

I can go in to the server itself and manually query the titles and get hits. It's purely the integration with VB that seems to be falling over.

Any ideas?

========== later ==============
Never mind, I found a typo in my sphinx.php file in <vbhome>/includes. Now that it's pointing at the right index, it works great.