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.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

General Questions /

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

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

Site Search code returning DB error when passed anything


Go to End


3 Posts   3526 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, 5523 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!