6

When I search for any string in Magento2 then there is result page showing blank. I got the exception.log file.

"[2018-06-01 08:57:23] main.CRITICAL: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list, query was: SELECT search_synonyms.* FROM search_synonyms WHERE (MATCH (synonyms) AGAINST ('9898' IN BOOLEAN MODE)) {"exception":"[object] (Zend_Db_Statement_Exception(code: 0): SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list, query was: SELECT search_synonyms.* FROM search_synonyms WHERE (MATCH (synonyms) AGAINST ('9898' IN BOOLEAN MODE)) at /opt/lampp/htdocs/magento223/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list at /opt/lampp/htdocs/magento223/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228)"} [] "

6
  • Apply index matching the column list ----- ALTER TABLE COLUMNS ADD FULLTEXT(column1,column2); Commented Jun 1, 2018 at 9:37
  • COLUMNS == YOUR COLUMNS WHICH GENERATING ISSUE Commented Jun 1, 2018 at 9:37
  • 1
    does any one found solution of it? Commented Oct 24, 2018 at 11:53
  • @ChiragRajput Can you please give me your URL? Can you please check in the luma theme once? Commented Oct 25, 2018 at 6:00
  • @SunnyRahevar , Magento2 is installed in my local system (MAMP). I think the issue was due to database migration from magento1 to magento2. The answer of user payalpatel help me to solve this issue. Commented Oct 25, 2018 at 6:37

2 Answers 2

6
+50

Please check this query :

SELECT search_synonyms.* FROM search_synonyms WHERE (MATCH (synonyms) AGAINST ('9898' IN BOOLEAN MODE))

And give the query in database table search_synonyms

ALTER TABLE search_synonyms ADD FULLTEXT(synonyms);
1
  • Working great !!!! Only ran the Alter command ALTER TABLE search_synonyms ADD FULLTEXT(synonyms); Commented Aug 12, 2019 at 11:23
2

FULLTEXT indexes are created on text-based columns (CHAR, VARCHAR, or TEXT columns) to help speed up queries and DML operations on data contained within those columns, omitting any words that are defined as stopwords.

A FULLTEXT index is defined as part of a CREATE TABLE statement or added to an existing table using ALTER TABLE or CREATE INDEX.

Cant find FULLTEXT index matching the column list

By your query, it seems that you are attempting to search the desired data using FULLTEXT INDEX functionality on NON-INDEXED columns of your table namely (title, detail, answer) that is why you are getting #1191 - Can't find FULLTEXT index matching the column error.

  1. Add the FULLTEXT INDEX on the concerned column. You can do this simply by the following query: ALTER TABLE books ADD FULLTEXT name_of_index(title,detail,answer);
  2. Or change your query by adding "IN BOOLEAN MODE" because MySQL allows to search on FULLTEXT non-indexed columns in this mode. So you will have to do the following:

SELECT id, title, category_id FROM books WHERE MATCH (title, detail, answer) AGAINST ('What is mysql', IN BOOLEAN MODE) LIMIT 0 , 30;

Note : Above code is giving a knowledge about your error you need to check how to do in magento.

I hope it helps!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.