MySQL Bugs: #9028: Full text search hangs mysql with 100% CPU

Bug #9028	Full text search hangs mysql with 100% CPU
Submitted:	7 Mar 2005 20:09	Modified:	2 May 2005 10:55
Reporter:	JJWR WR	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S1 (Critical)
Version:	4.1.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I run MySQL version 4.1.10 on Windows 2003 Server configured with UTF8 and with a UTF8 table having a fulltext search index on a field (besides other normal indexes). The table has about 700.000 records, and the fulltext indexed field has about 5-10 words for each record. I am using my own stop words file. 

MySQL receives about 10-20 fulltext queries per minute and the fulltext queries run very fast, but suddenly MySQL start to use 100% CPU forever. Looking at the running threads when MySQL is hanged I can see a thread in state “FULLTEXT INITILIZATION” and many other threads with fulltext querys in “LOCKED” state.

A query similar to this one is executed:
select * from tablename where match (fulltextfield) AGAINST ('+searchword' IN BOOLEAN MODE)

I noticed that MySQL hangs when the 'searchword' is not a valid UTF8 string, for example a ISO-8859-1 string with characters like á ò ñ ....

I think this is also a scurity issue because in most web sites is pretty easy to build and send a invalid UTF8 string in a free text search form and crash the server. 

How to repeat:
- Configure the server with UTF8 (Windows)
- Create an UTF8 database and a table with one text field in UTF8
- Create a full text index for that field
- Add some data
- Perform a query like this:
select * from tablename where match (fulltextfield) AGAINST ('+searchword' IN BOOLEAN MODE)

where searchword is a ISO-8859-1 string having the character ñ

Could you please provide a test case with your complete SQL script.
Please attach the script using the tab Files.

Thanks in advance.

Looks like the bug fixed a couple of days ago:
http://mysql.bkbits.net:8080/mysql-4.1/patch@1.2084

Execute this sql script on any database to create the table to reproduce the bug

Attachment: createtable.txt (text/plain), 279 bytes.

After creating the table, execute this php script to crash the server. You must edit it and set your mysql connection parameters

Attachment: crash.php (application/octet-stream, text), 1.17 KiB.

I uploaded two files.
- Execute createtable.txt included sql commands to create the table and to add some data on any database you want.
- Place search.php on your website root and edit it to set up your mysql connection details
- Calling http://your-machine/crash.php?q=espa%F1ol will hang MySQL with 100% CPU
- Tested on Windows XP and 2003 Server, MySQL 4.1.10 and 4.1.9, Internet Information Server and PHP5 as CGI.

It would be great if you can reproduce it and give a workaround.

It seems that the bug is fixed in version 4.1.11 as I am not able to reproduce it now.

for the record. A testcase for this bug.

start server with --character_set_server=utf8   --collation_server=utf8_general_ci 

run the following:

set names utf8;
drop table if exists `t1`;
create table `t1`(`a` char(2))engine=myisam;
insert into `t1` set `a`='a';
select * from `t1` where match(`a`) against ('+í' in boolean mode);

processlist will show this (cut to fit):

+------+-------------------------+-------------------------------
| Time | State                   | Info
+------+-------------------------+-------------------------------
|   51 | FULLTEXT initialization | select * from `t1` where match

repeatable in 4.1.10 but not repeatable in 4.1.11