Fixed MySQL search with multiple consecutive asterisks causes error

R

REP13

Member
Affected version
v2.2.11
Hi there can someone explain what does mean this :
  • XF\Db\InvalidQueryException: MySQL query error [1064]: syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*'
  • src/XF/Db/AbstractStatement.php:230
  • ed '+', expecting FTS_TERM or FTS_NUMB or '*'
  • src/XF/Db/AbstractStatement.php:230
  • Generated by:  xxxx
  • Feb 24, 2024 at 08:54

Stack trace​

SELECT search_index.content_type, search_index.content_id
FROM xf_search_index AS search_index

WHERE MATCH(search_index.title, search_index.message, search_index.metadata) AGAINST ('+word1 +word2 +word3 +word4 +word5 +*** +word6 -_md_node_17 -_md_hidden_1' IN BOOLEAN MODE)

ORDER BY search_index.item_date DESC
LIMIT 200

------------

#0 src/XF/Db/Mysqli/Statement.php(198): XF\Db\AbstractStatement->getException('MySQL query err...', 1064, '42000')
#1 src/XF/Db/Mysqli/Statement.php(79): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1064, '42000')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(162): XF\Db\AbstractAdapter->query('
SELECT sear...', Array)
#4 src/XF/Search/Source/MySqlFt.php(293): XF\Db\AbstractAdapter->fetchAllNum('
SELECT sear...')
#5 src/addons/SV/SearchImprovements/XF/Search/Source/MySqlFt.php(58): XF\Search\Source\MySqlFt->search(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), 200)
#6 src/XF/Search/Search.php(185): SV\SearchImprovements\XF\Search\Source\MySqlFt->search(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), '200')
#7 src/XF/Search/Search.php(214): XF\Search\Search->XF\Search\{closure}(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), '200')
#8 src/XF/Search/Search.php(181): XF\Search\Search->executeSearch(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), '200', Object(Closure), true)
#9 src/XF/Repository/Search.php(33): XF\Search\Search->search(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery))
#10 src/addons/SV/SearchImprovements/XF/Repository/Search.php(103): XF\Repository\Search->runSearch(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), Array, true)
#11 src/XF/Pub/Controller/Search.php(437): SV\SearchImprovements\XF\Repository\Search->runSearch(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), Array, true)
#12 src/XF/Pub/Controller/Search.php(119): XF\Pub\Controller\Search->runSearch(Object(SV\SearchImprovements\XF\Search\Query\KeywordQuery), Array)
#13 src/addons/SV/SearchImprovements/XF/Pub/Controller/Search.php(87): XF\Pub\Controller\Search->actionSearch()
#14 src/XF/Mvc/Dispatcher.php(352): SV\SearchImprovements\XF\Pub\Controller\Search->actionSearch(Object(XF\Mvc\ParameterBag))
#15 src/XF/Mvc/Dispatcher.php(259): XF\Mvc\Dispatcher->dispatchClass('XF:Search', 'Search', Object(XF\Mvc\RouteMatch), Object(SV\SearchImprovements\XF\Pub\Controller\SearchPatchFirst), NULL)
#16 src/XF/Mvc/Dispatcher.php(115): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(SV\SearchImprovements\XF\Pub\Controller\SearchPatchFirst), NULL)
#17 src/XF/Mvc/Dispatcher.php(57): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#18 src/XF/App.php(2353): XF\Mvc\Dispatcher->run()
#19 src/XF.php(524): XF\App->run()
#20 index.php(20): XF::runApp('XF\\Pub\\App')
#21 {main}
 
Same v2.3.2

XF\Db\InvalidQueryException: MySQL query error [1064]: syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*' in src/XF/Db/AbstractStatement.php at line 230

Code: 
XF\Db\InvalidQueryException: MySQL query error [1064]: syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*' src/XF/Db/AbstractStatement.php:230

SELECT search_index.content_type, search_index.content_id, MATCH(search_index.title, search_index.message, search_index.metadata) AGAINST ('+Развитие +коммуникативных +навыков** +Эффективное +взаимодействие +командой +клиентами +партнерами -_md_node_8 -_md_node_17 -_md_node_56 -_md_node_57 -_md_node_58 -_md_node_59 -_md_node_60 -_md_node_61 -_md_node_97 -_md_node_100 -_md_node_123 -_md_node_129 -_md_node_130 -_md_node_131 -_md_node_132 -_md_node_133 -_md_node_134 -_md_node_150 -_md_node_151 -_md_node_184 -_md_node_185 -_md_node_211 -_md_node_212 -_md_node_239 -_md_node_240 -_md_node_266 -_md_node_267 -_md_node_293 -_md_node_294 -_md_node_331 -_md_node_332 -_md_node_359 -_md_node_360 -_md_node_388 -_md_node_389 -_md_node_416 -_md_node_417 -_md_node_444 -_md_node_445 -_md_node_721 -_md_node_722 -_md_node_723 -_md_node_724 -_md_node_933 -_md_node_934 -_md_node_935 -_md_node_936 -_md_node_938 -_md_node_939 -_md_node_940 -_md_node_941 -_md_node_942 -_md_node_943 -_md_node_944 -_md_node_945 -_md_node_946 -_md_node_947 -_md_node_948 -_md_node_949 -_md_node_950 -_md_node_1099 -_md_node_1100 -_md_node_1101 -_md_node_1216 -_md_node_1217 -_md_node_1218 -_md_node_1340 -_md_node_1402 -_md_node_1403 -_md_node_1404 -_md_node_1488 -_md_node_1489 -_md_node_1490 -_md_node_1533 -_md_node_1534 -_md_node_1535 -_md_node_1569 -_md_node_1570 -_md_node_1571 -_md_node_1756 -_md_node_1757 -_md_node_1758 -_md_node_1967 -_md_node_1968 -_md_node_1969 -_md_node_2083 -_md_node_2084 -_md_node_2085 -_md_node_2153 -_md_node_2154 -_md_node_2155 -_md_node_2156 -_md_node_2157 -_md_node_2158 -_md_node_2159 -_md_node_2160 -_md_node_2161 -_md_node_2162 -_md_node_2163 -_md_node_2164 -_md_node_2165 -_md_node_2166 -_md_node_2167 -_md_node_2168 -_md_node_2169 -_md_node_2170 -_md_node_2171 -_md_node_2172 -_md_node_2173 -_md_node_2174 -_md_node_2175 -_md_node_2176 -_md_node_2177 -_md_node_2178 -_md_node_2270 -_md_node_2271 -_md_node_2272 -_md_node_2273 -_md_node_2517 -_md_node_2641 -_md_node_2642 -_md_node_2643 -_md_node_2644 -_md_node_2746 -_md_node_2747 -_md_node_2748 -_md_node_2749 -_md_node_2866 -_md_node_2872 -_md_node_2873 -_md_node_2874 -_md_node_3177 -_md_node_3178 -_md_node_3180 -_md_node_3181 -_md_node_3182 -_md_node_3183 -_md_node_3184 -(+(_md_content_conversation _md_content_conversation_message) -_md_active_recipients_222696) -_md_hidden_1' IN BOOLEAN MODE) AS score
            FROM xf_search_index AS search_index
            
            WHERE MATCH(search_index.title, search_index.message, search_index.metadata) AGAINST ('+Развитие +коммуникативных +навыков** +Эффективное +взаимодействие +командой +клиентами +партнерами -_md_node_8 -_md_node_17 -_md_node_56 -_md_node_57 -_md_node_58 -_md_node_59 -_md_node_60 -_md_node_61 -_md_node_97 -_md_node_100 -_md_node_123 -_md_node_129 -_md_node_130 -_md_node_131 -_md_node_132 -_md_node_133 -_md_node_134 -_md_node_150 -_md_node_151 -_md_node_184 -_md_node_185 -_md_node_211 -_md_node_212 -_md_node_239 -_md_node_240 -_md_node_266 -_md_node_267 -_md_node_293 -_md_node_294 -_md_node_331 -_md_node_332 -_md_node_359 -_md_node_360 -_md_node_388 -_md_node_389 -_md_node_416 -_md_node_417 -_md_node_444 -_md_node_445 -_md_node_721 -_md_node_722 -_md_node_723 -_md_node_724 -_md_node_933 -_md_node_934 -_md_node_935 -_md_node_936 -_md_node_938 -_md_node_939 -_md_node_940 -_md_node_941 -_md_node_942 -_md_node_943 -_md_node_944 -_md_node_945 -_md_node_946 -_md_node_947 -_md_node_948 -_md_node_949 -_md_node_950 -_md_node_1099 -_md_node_1100 -_md_node_1101 -_md_node_1216 -_md_node_1217 -_md_node_1218 -_md_node_1340 -_md_node_1402 -_md_node_1403 -_md_node_1404 -_md_node_1488 -_md_node_1489 -_md_node_1490 -_md_node_1533 -_md_node_1534 -_md_node_1535 -_md_node_1569 -_md_node_1570 -_md_node_1571 -_md_node_1756 -_md_node_1757 -_md_node_1758 -_md_node_1967 -_md_node_1968 -_md_node_1969 -_md_node_2083 -_md_node_2084 -_md_node_2085 -_md_node_2153 -_md_node_2154 -_md_node_2155 -_md_node_2156 -_md_node_2157 -_md_node_2158 -_md_node_2159 -_md_node_2160 -_md_node_2161 -_md_node_2162 -_md_node_2163 -_md_node_2164 -_md_node_2165 -_md_node_2166 -_md_node_2167 -_md_node_2168 -_md_node_2169 -_md_node_2170 -_md_node_2171 -_md_node_2172 -_md_node_2173 -_md_node_2174 -_md_node_2175 -_md_node_2176 -_md_node_2177 -_md_node_2178 -_md_node_2270 -_md_node_2271 -_md_node_2272 -_md_node_2273 -_md_node_2517 -_md_node_2641 -_md_node_2642 -_md_node_2643 -_md_node_2644 -_md_node_2746 -_md_node_2747 -_md_node_2748 -_md_node_2749 -_md_node_2866 -_md_node_2872 -_md_node_2873 -_md_node_2874 -_md_node_3177 -_md_node_3178 -_md_node_3180 -_md_node_3181 -_md_node_3182 -_md_node_3183 -_md_node_3184 -(+(_md_content_conversation _md_content_conversation_message) -_md_active_recipients_222696) -_md_hidden_1' IN BOOLEAN MODE)
            
            ORDER BY score DESC, search_index.item_date DESC
            LIMIT 200
------------

#0 src/XF/Db/Mysqli/Statement.php(207): XF\Db\AbstractStatement->getException('MySQL query err...', 1064, '42000')
#1 src/XF/Db/Mysqli/Statement.php(83): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1064, '42000')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(162): XF\Db\AbstractAdapter->query('SELECT search_i...', Array)
#4 src/XF/Search/Source/MySqlFt.php(359): XF\Db\AbstractAdapter->fetchAllNum('SELECT search_i...')
#5 src/XF/Search/Search.php(337): XF\Search\Source\MySqlFt->search('SELECT search_i...', 200)
#6 src/XF/Search/Search.php(366): XF\Search\Search->XF\Search\{closure}(Object(XF\Search\Query\KeywordQuery), '200')
#7 src/XF/Search/Search.php(332): XF\Search\Search->executeSearch(Object(XF\Search\Query\KeywordQuery), '200', Object(Closure), true)
#8 src/XF/Repository/SearchRepository.php(37): XF\Search\Search->search(Object(XF\Search\Query\KeywordQuery))
#9 src/XF/Pub/Controller/SearchController.php(562): XF\Repository\SearchRepository->runSearch(Object(XF\Search\Query\KeywordQuery), Array, true)
#10 src/XF/Pub/Controller/SearchController.php(182): XF\Pub\Controller\SearchController->runSearch(Object(XF\Search\Query\KeywordQuery), Array)
#11 src/XF/Mvc/Dispatcher.php(362): XF\Pub\Controller\SearchController->actionSearch(Object(XF\Mvc\ParameterBag))
#12 src/XF/Mvc/Dispatcher.php(264): XF\Mvc\Dispatcher->dispatchClass('XF:Search', 'Search', Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\SearchController), NULL)
#13 src/XF/Mvc/Dispatcher.php(121): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(XF\Pub\Controller\SearchController), NULL)
#14 src/XF/Mvc/Dispatcher.php(63): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#15 src/XF/App.php(2813): XF\Mvc\Dispatcher->run()
#16 src/XF.php(802): XF\App->run()
#17 index.php(23): XF::runApp('XF\\Pub\\App')
#18 {main}
 
Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.3.4).

Change log:
Mark multiple consecutive asterisks as an invalid term word on MySQL full-text searches
Click to expand...
There may be a delay before changes are rolled out to the XenForo Community.
 
Back
Top Bottom