vBulletin Mods

The Official vBulletin Modifications Site
https://www.vbulletin.org/forum/showthread.php?t=127868

Mb81 14 Mar 2007 15:06

Still waiting for some help here. Thanks.

Quote:

Originally Posted by Mb81 (Post 1201809)
Here it is. I donīt see any mistake.
It would be really nice if someone could confirm it. Thanks alot.


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


raywjohnson 18 Mar 2007 04:55

Quote:

Originally Posted by Mb81 (Post 1203301)
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

Mb81 18 Mar 2007 16:50

Quote:

Originally Posted by raywjohnson (Post 1206042)
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.

raywjohnson 18 Mar 2007 23:05

Quote:

Originally Posted by Mb81 (Post 1206455)
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

amcd 18 Mar 2007 23:42

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.

Mb81 19 Mar 2007 00:24

Here i go with sphinx counter in forum db:


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


raywjohnson 19 Mar 2007 02:12

Quote:

Originally Posted by Mb81 (Post 1206888)
Here i go with sphinx counter in forum db:
debian:/usr/local/sphinx/bin# ./indexer --config /usr/local/sphinx/etc/sphinx.conf --rotate --all
....
debian:/usr/local/sphinx/bin# ./searchd --config /usr/local/sphinx/etc/sphinx.conf
....

According to Orban
Quote:

Originally Posted by orban (Post 1104866)
3. Indexing
Important: When searchd is running, add --rotate, if it's not running, don't add :)

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

Mb81 19 Mar 2007 03:39

Quote:

Originally Posted by raywjohnson (Post 1206941)
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 ?

eoc_Jason 19 Mar 2007 20:54

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. ;)

raywjohnson 19 Mar 2007 23:08

Quote:

Originally Posted by Mb81 (Post 1207006)
Thanks alot ! That was the problem. Thanks again.

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 )

Quote:

Another question; what are recommended update settings ?
If you are asking about indexing, keep on reading!

Quote:

Originally Posted by eoc_Jason (Post 1207528)
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.

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

eoc_Jason 19 Mar 2007 23:26

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. :)

eoc_Jason 20 Mar 2007 04:05

2 Attachment(s)
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. :)

kmike 20 Mar 2007 09:27

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.

eoc_Jason 20 Mar 2007 15:51

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... ;)

kmike 20 Mar 2007 23:34

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


All times are GMT. The time now is 19:00.

Powered by vBulletin® Version 3.8.14
Copyright © 2022, MH Sub I, LLC dba vBulletin. All Rights Reserved. vBulletin® is a registered trademark of MH Sub I, LLC
Copyright ©2001 - , vbulletin.org. All rights reserved.