[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

Status: Closed
Project: phpBB3
Component/s: Search
Affects Version/s: 3.1.10
Fix Version/s: 3.2.2-RC1

Type: Bug Priority: Major
Reporter: BioLogIn Assignee: Marc
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Cause
was caused by solution of PHPBB3-11621 Improve MySQL fulltext search indexes Unverified Fix

 Description   

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 [1191]

SQL

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

BACKTRACE

FILE: (not given by php)
LINE: (not given by php)
CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 855
CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php
LINE: 193
CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/mysql_base.php
LINE: 45
CALL: phpbb\db\driver\mysqli->sql_query()

FILE: [ROOT]/phpbb/db/driver/driver.php
LINE: 261
CALL: phpbb\db\driver\mysql_base->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php
LINE: 321
CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/phpbb/search/fulltext_mysql.php
LINE: 573
CALL: phpbb\db\driver\factory->sql_query_limit()

FILE: [ROOT]/search.php
LINE: 590
CALL: phpbb\search\fulltext_mysql->keyword_search()
[/code]

https://www.phpbb.com/community/viewtopic.php?f=466&t=2421931



 Comments   
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 [1], 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.

[1] https://github.com/phpbb/phpbb/blob/0f19c507e2620be8db00041e167144152f6d3ea7/phpBB/phpbb/search/fulltext_mysql.php#L974

 

Comment by AmigoJack [ 04/Jul/17 ]

I investigated this briefly
I would WONTFIX

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.

Generated at Sat Nov 17 23:39:54 UTC 2018 using JIRA 7.9.2#79002-sha1:3bb15b68ecd99a30eb364c4c1a393359bcad6278.