Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

General Questions /

General questions about getting started with SilverStripe that don't fit in any of the categories above.

Moderators: martimiz, Sean, biapar, Willr, Ingo, swaiba, simon_w

Site Search code returning DB error when passed anything


Reply


3 Posts   1593 Views

Avatar
te_chris

Community Member, 24 Posts

8 February 2011 at 3:07pm

Hey guys,

So i'm building a pretty simple site at the moment and need to add search. I'm using 2.4.5 and have basically copied verbatim what the search tutorial says.

Whenever I pass nothing through the form, it works fine, says it can't faind anything as it is supposed to. Whenever I pass any form of input into the form it goes crazy and throws a DB error like this one

User Error] Couldn't run query: SELECT ClassName, "SiteTree_Live".ID, ParentID, Title, MenuTitle, URLSegment, Content, LastEdited, Created, _utf8'' AS Filename, _utf8'' AS Name, MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) AND ShowInSearch 0) UNION SELECT ClassName, "File".ID, _utf8'' AS ParentID, Title, _utf8'' AS MenuTitle, _utf8'' AS URLSegment, Content, LastEdited, Created, Filename, Name, MATCH (Filename, Title, Content) AGAINST ('a') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('a*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY "Relevance" DESC LIMIT 0, 10 Can't find FULLTEXT index matching the column list
GET /paymentsnz/home/SearchForm?Search=a&action_results=Go

ource

516    }
517    
518    function databaseError($msg, $errorLevel = E_USER_ERROR) {
519       // try to extract and format query
520       if(preg_match('/Couldn\'t run query: ([^\|]*)\|\s*(.*)/', $msg, $matches)) {
521          $formatter = new SQLFormatter();
522          $msg = "Couldn't run query: \n" . $formatter->formatPlain($matches[1]) . "\n\n" . $matches[2];
523       }
524       
525       user_error($msg, $errorLevel);
526    }
527    
528    /**
529     * Return a boolean type-formatted string
530     *
531     * @param array $values Contains a tokenised list of info about this data type
Trace

Couldn't run query: SELECT ClassName, "SiteTree_Live".ID, ParentID, Title, MenuTitle, URLSegment, Content, LastEdited, Created, _utf8'' AS Filename, _utf8'' AS Name, MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) AND ShowInSearch <> 0) UNION SELECT ClassName, "File".ID, _utf8'' AS ParentID, Title, _utf8'' AS MenuTitle, _utf8'' AS URLSegment, Content, LastEdited, Created, Filename, Name, MATCH (Filename, Title, Content) AGAINST ('a') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('a*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY "Relevance" DESC LIMIT 0, 10 Can't find FULLTEXT index matching the column list
Line 525 of MySQLDatabase.php
MySQLDatabase->databaseError(Couldn't run query: SELECT ClassName, "SiteTree_Live".ID, ParentID, Title, MenuTitle, URLSegment, Content, LastEdited, Created, _utf8'' AS Filename, _utf8'' AS Name, MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) AND ShowInSearch <> 0) UNION SELECT ClassName, "File".ID, _utf8'' AS ParentID, Title, _utf8'' AS MenuTitle, _utf8'' AS URLSegment, Content, LastEdited, Created, Filename, Name, MATCH (Filename, Title, Content) AGAINST ('a') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('a*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY "Relevance" DESC LIMIT 0, 10 | Can't find FULLTEXT index matching the column list,256)
Line 123 of MySQLDatabase.php
MySQLDatabase->query(SELECT ClassName, "SiteTree_Live".ID, ParentID, Title, MenuTitle, URLSegment, Content, LastEdited, Created, _utf8'' AS Filename, _utf8'' AS Name, MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) AND ShowInSearch <> 0) UNION SELECT ClassName, "File".ID, _utf8'' AS ParentID, Title, _utf8'' AS MenuTitle, _utf8'' AS URLSegment, Content, LastEdited, Created, Filename, Name, MATCH (Filename, Title, Content) AGAINST ('a') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('a*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY "Relevance" DESC LIMIT 0, 10,256)
Line 129 of DB.php
DB::query(SELECT ClassName, "SiteTree_Live".ID, ParentID, Title, MenuTitle, URLSegment, Content, LastEdited, Created, _utf8'' AS Filename, _utf8'' AS Name, MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('a*' IN BOOLEAN MODE) AND ShowInSearch <> 0) UNION SELECT ClassName, "File".ID, _utf8'' AS ParentID, Title, _utf8'' AS MenuTitle, _utf8'' AS URLSegment, Content, LastEdited, Created, Filename, Name, MATCH (Filename, Title, Content) AGAINST ('a') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('a*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY "Relevance" DESC LIMIT 0, 10)
Line 822 of MySQLDatabase.php
MySQLDatabase->searchEngine(Array,a*,0,10,"Relevance" DESC,,1)
Line 129 of SearchForm.php
SearchForm->getResults()
Line 52 of Page.php
Page_Controller->results(Array,SearchForm,SS_HTTPRequest)
Line 329 of Form.php
Form->httpSubmission(SS_HTTPRequest)
Line 143 of RequestHandler.php
RequestHandler->handleRequest(SS_HTTPRequest)
Line 161 of RequestHandler.php
RequestHandler->handleRequest(SS_HTTPRequest)
Line 147 of Controller.php
Controller->handleRequest(SS_HTTPRequest)
Line 199 of ContentController.php
ContentController->handleRequest(SS_HTTPRequest)
Line 67 of ModelAsController.php
ModelAsController->handleRequest(SS_HTTPRequest)
Line 282 of Director.php
Director::handleRequest(SS_HTTPRequest,Session)
Line 125 of Director.php
Director::direct(/home/SearchForm)
Line 127 of main.php

Anyone had this before?

Any ideas?

Thanks :D

Avatar
Willr

Forum Moderator, 5513 Posts

8 February 2011 at 8:34pm

The error message "Can't find FULLTEXT index matching the column list" would suggest that an index is missing. Tried running a dev/build?flush=all.? If that doesn't add the missing index then you will need to dig into why the indexes aren't being added (See FulltextSearchable)

Avatar
te_chris

Community Member, 24 Posts

8 February 2011 at 8:51pm

Fixed it! Figured it must've been a DB level error of sorts. Thanks!