Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

search - error 1191 - Can't find FULLTEXT

edited March 2012 in Vanilla 2.0 - 2.8

in my forum doesn't work the "search" function

it replay

Can't find FULLTEXT index matching the column
list|Gdn_Database|Query|select *
from (
select match(d.Name, d.Body) against(:Search0) as Relavence,
d.DiscussionID as PrimaryID, d.Name as Title, d.Body as Summary,
d.Format as Format, d.CategoryID as CategoryID,
concat('/discussion/', d.DiscussionID) as Url, d.DateInserted as
DateInserted, d.InsertUserID as UserID
from GDN_Discussion d
where match(d.Name, d.Body) against (:Search1)
union all
select match(c.Body) against(:Search2) as Relavence, c.CommentID as
PrimaryID, d.Name as Title, c.Body as Summary, c.Format as
Format, d.CategoryID as CategoryID, concat('/discussion/comment/',
c.CommentID, '/#Comment
', c.CommentID) as Url, c.DateInserted as
DateInserted, c.InsertUserID as UserID
from GDN_Comment c
join GDN_Discussion d on d.DiscussionID = c.DiscussionID
where match(c.Body) against (:Search3)
) s
order by s.DateInserted desc
limit 20_

to every request

i've tried to isolate the problema and in the mysql also a simple
select * from GDN_Comment c where match(c.Body) against ('prova')

give the same error

I have found
From http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html :

For natural-language full-text searches, it is a requirement that the
columns named in the MATCH() function be the same columns included in
some FULLTEXT index in your table.

....
In what way can I fix the problem?

thanks

Best Answer

  • peregrineperegrine MVP
    edited March 2012 Answer ✓

    I didn't try your first statement, but the second statement worked for me. Maybe this will help.

    columns named in the MATCH() function be the same columns included in some FULLTEXT index in your table

    show index from GDN_Comment;
    (index_type full text shows up for column name body)

    I ran your last simple test.

    I have the word test in the body of several messages and got results with this query.

    SELECT *
    FROM GDN_Comment c
    WHERE MATCH (
    c.Body
    )
    AGAINST (
    'test'
    )
    LIMIT 0 , 30;
    

    if you are trying to do full text on other columns that are currently not full text
    ALTER TABLE tablename ADD FULLTEXT name_of_index(example,sample);

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

Answers

  • peregrineperegrine MVP
    edited March 2012 Answer ✓

    I didn't try your first statement, but the second statement worked for me. Maybe this will help.

    columns named in the MATCH() function be the same columns included in some FULLTEXT index in your table

    show index from GDN_Comment;
    (index_type full text shows up for column name body)

    I ran your last simple test.

    I have the word test in the body of several messages and got results with this query.

    SELECT *
    FROM GDN_Comment c
    WHERE MATCH (
    c.Body
    )
    AGAINST (
    'test'
    )
    LIMIT 0 , 30;
    

    if you are trying to do full text on other columns that are currently not full text
    ALTER TABLE tablename ADD FULLTEXT name_of_index(example,sample);

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • Thank you!
    I have created two FULLTEXT index
    and it works

    .. but why Vanilla doesn't create the indexes by itself?

  • peregrineperegrine MVP
    edited March 2012

    Who knows? A guess. Perhaps you downloaded an interim version, when the full text index was not implemented properly. Within the past week or so, this forum didn't search properly for a day or so (maybe that was the same problem). I've not run into the problem myself on my install.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

Sign In or Register to comment.