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   1553 Views

Avatar
te_chris

8 February 2011 at 3:07pm Community Member, 24 Posts

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

8 February 2011 at 8:34pm Forum Moderator, 5511 Posts

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

8 February 2011 at 8:51pm Community Member, 24 Posts

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