[PHPBB3-15224] Advanced search in "message text only" crashes with SQL error when using Mysql fulltext search index Created: 11/May/17 Updated: 22/Dec/17 Resolved: 22/Dec/17
With Mysql 5.7 engine InnoDB and phpbb using mysql fulltext search index, any advanced search with a setting "Search within: Message text only" results in SQL general error:
[code] SQL ERROR [ mysqli ]
Can't find FULLTEXT index matching the column list 
SELECT SQL_CALC_FOUND_ROWS p.post_id FROM phpbb_posts p WHERE MATCH (p.post_text) AGAINST ('+video ' IN BOOLEAN MODE) AND (p.post_visibility = 1 OR p.forum_id IN (1, 2, [long list of all forum IDs], 144)) ORDER BY p.post_time DESC LIMIT 250
FILE: (not given by php)
|Comment by AmigoJack [ 15/May/17 ]|
See also https://www.phpbb.com/community/viewtopic.php?f=556&t=2423351 which seems to imply 3.2 being affected.
|Comment by A_Jelly_Doughnut [ 04/Jul/17 ]|
I investigated this briefly. The culprit is the line at , which creates an index on (post_subject, post_text). There is no fulltext index for only the post text, causing the error message described.
I would WONTFIX this bug due to the storage necessary to double-index the post text column, and silently use the combined index. Or hide the "text only" option in the UI.
|Comment by AmigoJack [ 04/Jul/17 ]|
I disagree strongly: 3.0 had no problems here, which means someone decided to shift indexes for a reason without being aware of breaking this feature. If there was no reason in different indexes then the culprit of this bug is in that very past.