Bug #38150 match ... against () never returns IN BOOLEAN MODE with characters like "-", "."
Submitted: 15 Jul 2008 19:51 Modified: 3 Sep 2008 17:41
Reporter: test test Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S1 (Critical)
Version:5.1.25-rc OS:Linux (Suse 10.3)
Assigned to: CPU Architecture:Any
Tags: full text search, match again, mysql hangs, special inword characters

[15 Jul 2008 19:51] test test
Description:
When we do this select with exact these searchwords "+explorer.exe* +fehler*" or other words with special charakters like "!", ".", "-" in one of more than two words the mysql server runs into 99.9 percent cpu usage and hangs.

Only kill -9 can kill the server process.

Our SQL statements that crashes the mysql server:

select id, id_parent, id_state, id_type, id_parent, id_user, id_group, sn_nickname, sn_childcount sn_numchilds, sn_msgsubject sn_threadlink, DATE_FORMAT(sn_datemodified,'%d.%m.%Y') 'sn_date' FROM tr_msgmain t1 WHERE MATCH ( sn_msgsubject ) AGAINST ('+explorer.exe* +fehler*' IN BOOLEAN MODE ) and id_group != 13 and id_type != 'comments' and t1.id_state & 1 != 0 and id_group = 40 group by id_parent order by sn_datecreated desc limit 0,

select id, id_parent, id_state, id_type, id_parent, id_user, id_group, sn_nickname, sn_childcount sn_numchilds, sn_msgsubject sn_threadlink, DATE_FORMAT(sn_datemodified,'%d.%m.%Y') 'sn_date' FROM tr_msgmain t1 WHERE MATCH ( sn_msgsubject, sn_msgbody ) AGAINST ('+fritz!box* +router*' IN BOOLEAN MODE ) and id_group != 13 and id_type != 'comments' and t1.id_state & 1 != 0 and id_group != 2 group by id_parent order by sn_datecreated desc

It seems that in this Serverversion has a problem with some special characters in FullTextSearch in a word.

If we search for "+fritz!box*" or for "+router*" there is no problem but the combination of one word with "!", ".", "-" characters in it with another word results in hang of server.

The same selects in the mysql server version 5.0 dont make this problem.

It seems that it has something to do with this problem, but it also seems that it is not fixed yet: http://lists.mysql.com/commits/30827

How to repeat:
Send "MATCH ( examplecolumn ) AGAINST ('+explorer.exe* +fehler*' IN BOOLEAN MODE ) to a database or:

MATCH ( examplecolumn ) AGAINST ('+fritz!box* +router*' IN BOOLEAN MODE )

Suggested fix:
no plan
[15 Jul 2008 21:00] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW CREATE TABLE tr_msgmain and SHOW TABLE STATUS LIKE 'tr_msgmain'
[15 Jul 2008 21:48] test test
SHOW CREATE TABLE tr_msgmain:

CREATE TABLE `tr_msgmain` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_parent` int(10) unsigned DEFAULT '0',
  `id_group` int(10) unsigned DEFAULT '1',
  `id_user` int(10) unsigned DEFAULT '1',
  `id_type` enum('posting','reply','news','faqs','sales','votes','guestbook','comments','articles','products','recipes','cheats','solutions') DEFAULT 'posting',
  `id_state` smallint(10) unsigned DEFAULT '1',
  `sn_accesscount` smallint(10) unsigned DEFAULT '0',
  `sn_childcount` smallint(10) unsigned DEFAULT '0',
  `sn_votecount` tinyint(4) DEFAULT '0',
  `sn_datecreated` datetime DEFAULT NULL,
  `sn_datemodified` datetime DEFAULT NULL,
  `sn_msgsubject` varchar(255) DEFAULT '',
  `sn_msgbody` mediumtext,
  `sn_nickname` varchar(255) DEFAULT '',
  `sn_nickmail` varchar(255) DEFAULT '',
  `sn_senderip` varchar(15) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_id` (`id`),
  KEY `index_id_type` (`id_type`),
  KEY `index_id_state` (`id_state`),
  KEY `index_id_parent` (`id_parent`),
  KEY `index_datemodified` (`sn_datemodified`),
  KEY `index_datecreated` (`sn_datecreated`),
  KEY `index_id_user` (`id_user`),
  KEY `index_id_group` (`id_group`),
  FULLTEXT KEY `index_msg_subject_body` (`sn_msgsubject`,`sn_msgbody`),
  FULLTEXT KEY `index_nickname` (`sn_nickname`),
  FULLTEXT KEY `index_nickmail` (`sn_nickmail`),
  FULLTEXT KEY `index_msg_subject` (`sn_msgsubject`)
) ENGINE=MyISAM AUTO_INCREMENT=2128532 DEFAULT CHARSET=latin1

SHOW TABLE STATUS LIKE 'tr_msgmain'

'tr_msgmain', 'MyISAM', 10, 'Dynamic', 1731235, 446, 772288584, 281474976710655, 741111808, 0, 2128532, '2008-07-15 09:51:17', '2008-07-15 23:45:57', '2008-07-15 17:25:45', 'latin1_swedish_ci', , '', ''
[15 Jul 2008 21:49] test test
and thank you for the fast response
[3 Aug 2008 17:41] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.26 and, in case of the same problem, send EXPLAIN results for the problematic query.
[3 Sep 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[15 Dec 2009 14:22] Mike Dett
I have notice that after changing type of fields in database to 'text' and making the select command only with that type... the query works well. 

But if I wanna include some different type it does not work...

Anyone can answer me why???