Jump to:

23355 Posts in 17838 Topics by 2862 members

General Questions

SilverStripe Forums » General Questions » MySQL Error [solved]

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

Page: 1
Go to End
Author Topic: 2341 Views
  • taligent
    Avatar
    Community Member
    18 Posts

    MySQL Error [solved] Link to this post

    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.

  • taligent
    Avatar
    Community Member
    18 Posts

    Re: MySQL Error [solved] Link to this post

    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?

  • Pigeon
    Avatar
    Community Member
    243 Posts

    Re: MySQL Error [solved] Link to this post

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

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

  • taligent
    Avatar
    Community Member
    18 Posts

    Re: MySQL Error [solved] Link to this post

    That fixed it. Thanks.

  • mkval
    Avatar
    Community Member
    3 Posts

    Re: MySQL Error [solved] Link to this post

    that helps! thanks. =]

    2341 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.