vb 3.8.6 pl1 / vbseo 3.5.1 - Unknown column 'relevance' in 'order clause'

i received an email regarding this error, vbulletin confirmed that the column 'relevance' is not part of their product so i am wondering if the error is vbseo related:

Code:

Invalid SQL:

				SELECT d.discussionid, gm.state
					
				FROM discussion AS d
				INNER JOIN groupmessage AS gm ON (gm.gmid = d.firstpostid)
				
				
				WHERE
					d.groupid = 8
				
				 AND (MATCH(gm.title, gm.pagetext) AGAINST ('+(blue) +ray' IN BOOLEAN MODE) )
				
				ORDER BY relevance DESC;

MySQL Error   : Unknown column 'relevance' in 'order clause'
Error Number  : 1054
Request Date  : Sunday, July 25th 2010 @ 03:36:33 PM
Error Date    : Sunday, July 25th 2010 @ 03:36:33 PM
Script        : http://laserpointerforums.com/groups/
Referrer      : http://laserpointerforums.com/groups/diy-group/
IP Address    : removed..
Username      : removed...
Classname     : vB_Database
MySQL Version :

here is my discussions table

Code:

mysql> describe discussion
    -> ;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| discussionid | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| groupid      | int(10) unsigned | NO   | MUL | NULL    |                |
| firstpostid  | int(10) unsigned | NO   |     | NULL    |                |
| lastpostid   | int(10) unsigned | NO   |     | NULL    |                |
| lastpost     | int(10) unsigned | NO   |     | NULL    |                |
| lastposter   | varchar(255)     | NO   |     | NULL    |                |
| lastposterid | int(10) unsigned | NO   |     | NULL    |                |
| visible      | int(10) unsigned | NO   |     | 0       |                |
| deleted      | int(10) unsigned | NO   |     | 0       |                |
| moderation   | int(10) unsigned | NO   |     | 0       |                |
| subscribers  | enum('0','1')    | YES  |     | 0       |                |
+--------------+------------------+------+-----+---------+----------------+
11 rows in set (0.02 sec)

UPDATE: i found what causes the error but i don't know why the error is being caused... the error is caused when a user goes to Social Group Discussions and performs a custom search in the Search Discussions box

UPDATE: it appears 3 users are reporting the problem here (including myself):

http://tracker.vbulletin.com/browse/VBIII-12797

anyone know what might be wrong?
thanks!

-avery

i got an update from vbulletin:

"There is no 'relevance' field in either the 3.8.6 discussion or groupmessage tables. That error is either from a bad file or an add-on."

the only other add-ons i have are vbseo / sitemap generator, and a very simple mod to show user reputation comments

i am guessing this must be a vbseo bug then?

please advise
thx
-ave

vBSEO and the sitemap should not add fields to existing tables. Perhaps it was from an older addon that you removed?

It's probably a bug in 3.8.6 code base. vbseo doesn't change anything with internal search queries.

thanks for the replies Michael and Brian

i have tried grepping through PHP files for pieces of the SQL query, for example "ORDER BY relevance DESC", but can't find where it is coming from.

any other idea how to narrow down exactly where the SQL is coming from? I also tried grepping for "relevance" in all php files in home dir but didn't find anything.

thx again for the replies, u guys rock
peace
-ave

Unfortunate, Since it's not from vBSEO, Then you should continue with vBulletin support.

You should make sure it is not related to your plugins. try disabling hooks within config.php and check to see if the error is there or not. If the error is not visible after disabling hooks , you will need to check one by one which mod is causing the issue.

@michael, i guess that could be the case


according to vbulletin the issue does not involve their product and the relevance field isn't even a default vbulletin table.

@mert, i disabled hooks with Enable Plugin/Hook System in vbulletin options. however, the invalid SQL error still occurs

also, after disabling these plugins, the error still occurs:

Cyb - Advanced Forum Statistics 6.6.2 Cyb - Advanced Forum Statistics

Cyb - PayPal Donate 4.8.2 Cyb - PayPal Donate

Member Profile Reputation Display

i don't see how or why the non-vbseo plugins would do anything to modify social group searches...but i will try disabling them and see what happens and let you all know the results

we can definitely confirm though its impossible this is related to vbseo, right?

question:

i figure there must be code somewhere stating "ORDER BY relevance DESC". It has to be somewhere. However, I grep'ed through all php files and don't see it. is there any other place i need to look to see "where this is even coming from to begin with"?

grep -r "ORDER BY relevance" /home/blah/public_html/*.php was used


NEW FINDING:

ok so in this SQL, maybe relevance shouldn't be a table, but part of the query itself... for example

AGAINST('+A* *Sales' IN BOOLEAN MODE)AS relevance

Code:

				SELECT d.discussionid, gm.state
					
				FROM discussion AS d
				INNER JOIN groupmessage AS gm ON (gm.gmid = d.firstpostid)
				
				
				WHERE
					d.groupid = 17
				
				 AND (MATCH(gm.title, gm.pagetext) AGAINST ('d' IN BOOLEAN MODE) )
				
				ORDER BY relevance DESC;

Code:

[blah@lazarus public_html]# grep -r "IN BOOLEAN MODE" /home/blah/public_html/*.php
/home/laserpoi/public_html/search.php:                                                  $thread_query_logic[] = "MATCH(thread.title) AGAINST ('$wordlist' IN BOOLEAN MODE)";
/home/laserpoi/public_html/search.php:                                                  $post_query_logic[] = "MATCH(post.title, post.pagetext) AGAINST ('$wordlist' IN BOOLEAN MODE)";

maybe As relevance is missing?

does anyone else with vbulletin 3.8.6 have this issue?

UPDATE

i just tried registering on another random vbulletin site... i get a database error when doing a social group search after joining one... can anyone else confirm this problem.. i have reported again to vbulletin

TEAM update

i posted this issue on vbulletin forum if anyone else is affected and wants to report it there:

http://www.vbulletin.com/forum/showt...84#post2016784

i know this is not vbseo related at this point, but just wanted to help u all track this issue down if u are affected too

thx team
peace
-ave

Yes we can confirm this has nothing to do with vBSEO as vBSEO doesn't modify any search results at all and we don't have a query that check relevance within other fields

Sounds like a new bug within 3.8.6

I just freshly downloaded and installed 3.8.6 pl1, and made a social group called test, went into groups and in search box entered nothing, got no error. then non existing word, no error, then test, and i found test.
I went to advanced search for groups and did the same, can't reproduce this.

[edit]
The bug report on vbcom says : "if this is in fact a bug, it might be a security issue" <--- that's complete nonsense.

yeah vbseo team rocks thx everyone
i recently upgrade my licenese and im a proud owner

and i hope that this thread helps other people track down the vbulletin bug / etc

[#VBIII-12797] Database error when performing a groups discussion search - vBulletin JIRA

peace
-ave