I faced the error while search any string Magento 2

Question

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

Answer 1

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);

Answer 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!