Searching with " " creates Exception in exec_SELECTquery

[machwatt]

If i want to search for something with apostrophes like "worda wordb" an exception is thrown: Tried to use phrase search https://www.typo3-macher.de/facettierte-suche-ke-search/dokumentation/suchverhalten/

We are running: TYPO3 Version: 8.7.13 Webserver: Apache PHP Version: 7.2.4 Database (Default) MySQL: 5.6.33

caller => 'TYPO3\CMS\Core\Database\DatabaseConnection::exec_SELECTquery' (60 chars) ERROR => 'syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or ''' (68 chars) lastBuiltQuery => 'SELECT SQL_CALC_FOUND_ROWS , MATCH (title, content) AGAINST ("worda wordb" ) + (2 MATCH (title) AGAINST ("worda wordb")) AS score FROM tx_kesearch_index WHERE 1=1 AND MATCH (title, content) AGAINST ('\"worda wordb\"' IN BOOLEAN MODE) AND pid in (37) AND language IN(0, -1) AND (`tx_kesearch_index`.`starttime` <= 1528189200) AND ((`tx_kesearch_index`.`endtime` = 0) OR (`tx_kesearch_index`.`endtime` > 1528189200)) AND (((`tx_kesearch_index`.`fe_group` = '') OR (`tx_kesearch_index`.`fe_group` IS NULL) OR (`tx_kesearch_index`.`fe_group` = '0') OR (FIND_IN_SET('0', `tx_kesearch_index`.`fe_group`)) OR (FIND_IN_SET('-1', `tx_kesearch_index`.`fe_group`)))) ORDER BY sortdate desc LIMIT 0,10'`

[localhorst]

This is a bug related to MySQL 5.6 / 5.7 and InnoDB.

When I change the table tx_kesearch_index to myISAM and manually add the FULLTEXT indexes, double quotes work again. https://bugs.mysql.com/bug.php?id=78485

I solved it in our projects by adding this to the ext_tables.sql of our sitepackage:

#
# Table structure for table 'tx_kesearch_index'
#
CREATE TABLE tx_kesearch_index (
  random_fieldname INT(11) DEFAULT '0' NOT NULL
) ENGINE = MyISAM;