Fulltext search sql-query has incompatible with InnoDB rules syntax

[Tikhonov Ivan]

We've converted all tables of forum's DB into InnoDB format as suggested by AdminCenter.

Just then the system log began collecting the errors in search query "IPS\Db\Exception: syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*' (1064)" 

The query itself is in the following format:
SELECT *, (( MATCH(index_title) AGAINST ('*some_word* *' IN BOOLEAN MODE)<... and so on...>

We believe the forum core generates it while this is coming from data input when the user is asking something like to "see everything", but for InnoDB AGAINST('*' ...) is not valid. Fulltext expects to see some_word(s), not a lone wildcard '*' (nor separately).

Would it be fixed (or tell us, please, how can we do it) or we are better to fall back to MyISAM table?

Invision Community v4.7.5, PHP 8.0.26, Percona Server for MySQL 5.7.40-43

[Marc]

This should be working with INNODB (and in fact this is what we work with). Could I confirm you havent changed anything else, such as switched it to strict? If not, please try rebuilding the search index from your admin CP. 

[Tikhonov Ivan]

I'am not sure I got the point. InnoDB strict mode is enabled by default and we guess that's correct. Rebuilding the search index is not in connection with innodb syntax rules.

System core generates AGAINST ('*someword* *') or AGAINST ('*') which leads to error statement, just try to execute the following two commands.

• IPS applications/core/modules/front/search/search.php query:
  SELECT *, ( MATCH(index_title) AGAINST ('*someword* *' IN BOOLEAN MODE)) as result FROM `core_search_index` LIMIT 5;
• no error query: 
  SELECT *, ( MATCH(index_title) AGAINST ('*someword*' IN BOOLEAN MODE)) as result FROM `core_search_index` LIMIT 5;

[Marc]

I have created a ticket on this, so we can get this looked into further.