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:
Category:MySQL Server: FULLTEXT search Severity:S1 (Critical)
Version:4.1.10 OS:Any
Assigned to: CPU Architecture:Any

[7 Mar 2005 20:09] JJWR WR
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 ñ
[8 Mar 2005 4:43] MySQL Verification Team
Could you please provide a test case with your complete SQL script.
Please attach the script using the tab Files.

Thanks in advance.
[8 Mar 2005 8:47] Sergei Golubchik
Looks like the bug fixed a couple of days ago:
[25 Mar 2005 9:26] JJWR WR
Execute this sql script on any database to create the table to reproduce the bug

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

[25 Mar 2005 9:27] JJWR WR
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.

[25 Mar 2005 9:34] JJWR WR
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.
[7 Apr 2005 10:29] JJWR WR
It seems that the bug is fixed in version 4.1.11 as I am not able to reproduce it now.
[7 Mar 2008 9:29] MySQL Verification Team
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