Cms: Assets search not working

[jangidgirish]

Description

When i search full assets name in popup, it's returning SQL query error

SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'

Steps to reproduce

1. Update entry in CP
2. Select asset search by file name

Additional info

• Craft version: Craft CMS 3.1.12
• PHP version: PHP 7.2.12-1+ubuntu16.04.1+deb.sury.org+1 (cli) (built: Nov 12 2018 09:55:12) ( NTS )
• Database driver & version: MySql 5.7.25-0ubuntu0.16.04.2 - (Ubuntu)
• Plugins & versions: --

[brandonkelly]

Looks like this is a MySQL bug that only affects full text searches on InnoDB tables. So I’m guessing you converted your searchindex table over to InnoDB? You should be able to fix by converting it back to MyISAM.

It was most likely introduced by 1b68fe5d8018a2d5df472d0746e035fe2e23d061, which fixed a different bug where this sort of query would have yielded zero results.

[jkorff]

You should be able to fix by converting it back to MyISAM.

Does this mean we need to trade better search results for not getting this error?

[brandonkelly]

@jkorff Until MySQL fixes #78485, yes.

[angrybrad]

Not hopeful that MySQL is going to fix https://bugs.mysql.com/bug.php?id=78485 anytime soon, so worked around it in our code in the meantime: https://github.com/craftcms/cms/commit/b39240a4b409b535cae7ea1acdf5880c0ef8cb2f