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

MySQL Error [solved]


Reply


5 Posts   2476 Views

Avatar
taligent

Community Member, 18 Posts

19 May 2010 at 1:11am

Edited: 19/05/2010 8:29pm

I had started our website in 2.3.7 and had everything working fine. Then I noticed the 2.4.0 was coming out so I held off a few days to install the site when 2.4.0 came out. Now, when I run a search on the site I am getting an SQL error that I can't seem to get past. The actual error is listed below:

[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 ('home') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) AND ShowInSearch 0) AND ("SiteTree_Live"."Locale" = 'en_US') 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 ('home') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('home*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY Relevance DESC LIMIT 0, 3 Can't find FULLTEXT index matching the column list
GET /home/SearchForm?Search=home&locale=en_US&action_results=Search

Line 536 in /srv/http/nginx/html/sapphire/core/model/MySQLDatabase.php
Source

527    }
528    
529    function databaseError($msg, $errorLevel = E_USER_ERROR) {
530       // try to extract and format query
531       if(preg_match('/Couldn\'t run query: ([^\|]*)\|\s*(.*)/', $msg, $matches)) {
532          $formatter = new SQLFormatter();
533          $msg = "Couldn't run query: \n" . $formatter->formatPlain($matches[1]) . "\n\n" . $matches[2];
534       }
535       
536       user_error($msg, $errorLevel);
537    }
538    
539    /**
540     * Return a boolean type-formatted string
541     *
542     * @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 ('home') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) AND ShowInSearch <> 0) AND ("SiteTree_Live"."Locale" = 'en_US') 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 ('home') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('home*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY Relevance DESC LIMIT 0, 3 Can't find FULLTEXT index matching the column list
Line 536 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 ('home') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) AND ShowInSearch <> 0) AND ("SiteTree_Live"."Locale" = 'en_US') 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 ('home') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('home*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY Relevance DESC LIMIT 0, 3 | Can't find FULLTEXT index matching the column list,256)
Line 134 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 ('home') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) AND ShowInSearch <> 0) AND ("SiteTree_Live"."Locale" = 'en_US') 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 ('home') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('home*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY Relevance DESC LIMIT 0, 3,256)
Line 126 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 ('home') + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home') AS Relevance, CanViewType FROM "SiteTree_Live" WHERE ( MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) + MATCH (Title, MenuTitle, Content, MetaTitle, MetaDescription, MetaKeywords) AGAINST ('home*' IN BOOLEAN MODE) AND ShowInSearch <> 0) AND ("SiteTree_Live"."Locale" = 'en_US') 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 ('home') AS Relevance, NULL AS CanViewType FROM "File" WHERE (MATCH (Filename, Title, Content) AGAINST ('home*' IN BOOLEAN MODE) AND ClassName = 'File') ORDER BY Relevance DESC LIMIT 0, 3)
Line 833 of MySQLDatabase.php
* MySQLDatabase->searchEngine(Array,home*,0,3,Relevance DESC,,1)
Line 120 of SearchForm.php
* SearchForm->getResults(,Array)
Line 49 of Page.php
* Page_Controller->results(Array,SearchForm,SS_HTTPRequest)
Line 296 of Form.php
* Form->httpSubmission(SS_HTTPRequest)
Line 134 of RequestHandler.php
* RequestHandler->handleRequest(SS_HTTPRequest)
Line 152 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 283 of Director.php
* Director::handleRequest(SS_HTTPRequest,Session)
Line 127 of Director.php
* Director::direct(home/SearchForm)
Line 127 of main.php

I am running on a site with multiple locales defined (Croatian, German, and English). From my guess, it appears that a field or index is missing. I have run /dev/build on the site as well as refreshing the browser cache and doing a flush. Our search form is a barely modified version from the SilverStripe book, section 11.4. Any help would be appreciated.

Avatar
taligent

Community Member, 18 Posts

19 May 2010 at 7:35pm

I had some more time to do some digging and found that I am missing the FULLTEXT indexes on my data files. I compared my development (2.3) tables to the tables on my host server. So, since the installer failed to add the FULLTEXT indexes on my tables, how to I get them added on to the tables? Did something change between 2.3 and 2.4 that the FULLTEXT indexes are no longer created, or did my install fail to create them and also fail to tell me it couldn't?

Avatar
Pigeon

Community Member, 243 Posts

19 May 2010 at 8:11pm

Edited: 06/06/2010 11:10am

2.4 has full text indexing disabled as default. Probably for performance gains.

source: http://doc.silverstripe.org/tutorial:4-site-search#and_newer

Avatar
taligent

Community Member, 18 Posts

19 May 2010 at 8:28pm

That fixed it. Thanks.

Avatar
mkval

Community Member, 3 Posts

22 August 2010 at 6:12am

that helps! thanks. =]