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)"} [] "

  • Apply index matching the column list ----- ALTER TABLE COLUMNS ADD FULLTEXT(column1,column2); – Aditya Shah Jun 1 '18 at 9:37
  • COLUMNS == YOUR COLUMNS WHICH GENERATING ISSUE – Aditya Shah Jun 1 '18 at 9:37
  • 1
    does any one found solution of it? – Chirag Rajput Oct 24 '18 at 11:53
  • @ChiragRajput Can you please give me your URL? Can you please check in the luma theme once? – Sunny Rahevar Oct 25 '18 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. – Chirag Rajput Oct 25 '18 at 6:37
5
+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);
  • Working great !!!! Only ran the Alter command ALTER TABLE search_synonyms ADD FULLTEXT(synonyms); – Ejilarasan J Aug 12 at 11:23
1

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, privacy policy and cookie policy

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