MySQL Bugs: #38150: match ... against () never returns IN BOOLEAN MODE with characters like "-", "."

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

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

Thank you for the report.

Please provide output of SHOW CREATE TABLE tr_msgmain and SHOW TABLE STATUS LIKE 'tr_msgmain'

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', , '', ''

and thank you for the fast response

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.

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".

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???