Bug #43123 Infinite loop with fulltext search, query never ends
Submitted: 23 Feb 2009 20:48 Modified: 23 Feb 2009 23:57
Reporter: Daniel Kukiela Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S2 (Serious)
Version:5.1.* OS:Any
Assigned to: CPU Architecture:Any

[23 Feb 2009 20:48] Daniel Kukiela 
Description:
In both Windows and Debian version of mySQL 5.1.31 some fulltext queries hang database server.
After it happens killing mySQL process doesn't help:
|  4 | root | 10.0.0.1:1286      | pclab | Killed  |   72 | Sending data | select
        id
from
        test
where
        MATCH(ft1,ft2,ft3) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN |
You have to either "kill -9 mysql" on Debian or kill system process on Windows.
The issue causes MySQL to use 100% of one of the CPUs and block following queries.

How to repeat:
Set ft_min_word_len to 2 in mysqld section of my.ini (my.cnf)
Run following queries:

CREATE TABLE test (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  ft1 varchar(255) DEFAULT NULL,
  ft2 text,
  ft3 longtext,
  PRIMARY KEY (id),
  FULLTEXT KEY ft (ft1,ft2,ft3)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;

INSERT INTO test VALUES ('1', null, 'Viacom - Paramount', '');
INSERT INTO test VALUES ('2', null, null, 'ac-2560.png');

Case 1:

Queries causing problems:

SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN MODE)
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+AC`97* +VIA*' IN BOOLEAN MODE)

In case of both queries part of the search string matches one record, but whole search string shouldn't match anything (see below).

Queries not causing any problems:
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+6.14.0001* +AC`97*' IN BOOLEAN MODE)
Returns id=2
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+6.14.0001* +VIA*' IN BOOLEAN MODE)
Returns id=1
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+6.14.0001*' IN BOOLEAN MODE)
Returns empty set
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+AC`97*' IN BOOLEAN MODE)
Returns id=2
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+VIA*' IN BOOLEAN MODE)
Returns id=1

Case 2:

There is no problem when less columns are used in a query than there are in fulltext index.

Causes problems:
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN MODE)

Doesn't cause problems:
SELECT id FROM test WHERE MATCH(ft2,ft3) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN MODE)
SELECT id FROM test WHERE MATCH(ft1,ft3) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN MODE)
SELECT id FROM test WHERE MATCH(ft1,ft2) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN MODE)

Case 3:

There is no problem when we rewrite query from:
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN MODE)
into:
SELECT test.id FROM test AS test2 LEFT JOIN test on test.id = test2.id WHERE MATCH(test.ft1,test.ft2,test.ft3) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN MODE)

Case 4:

Query causing problems:
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN MODE)
works fine when ft_min_word_len is set to 3 or more.

Case 5:

The easiest workaround is to add additional text column and add it to the index:
CREATE TABLE test (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  ft1 varchar(255) DEFAULT NULL,
  ft2 text,
  ft3 longtext,
  dummy text,
  PRIMARY KEY (id),
  FULLTEXT KEY ft (ft1,ft2,ft3,dummy)
) ENGINE=MyISAM DEFAULT CHARSET=latin2;

INSERT INTO test VALUES ('1', null, 'Viacom - Paramount', '');
INSERT INTO test VALUES ('2', null, null, 'ac-2560.png');

The query causing the problem:
SELECT id FROM test WHERE MATCH(ft1,ft2,ft3) AGAINST ('+6.14.0001* +AC`97* +VIA*' IN BOOLEAN MODE)
this time works fine (matching is done on less columns then there are in fulltext index)
[23 Feb 2009 23:57] MySQL Verification Team 
Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=42907
[27 May 2010 13:05] Claudio Nanni 
Linux CentOS 5.4
MySQL 5.1.44
One query in particular with paricular (but normal) arguments would stuck forever : against('+xxxxx+xxx* x*')
so the arguments were: [plus sign][5letters word][plus sign][3letters word][asterisk][space][1letter word][asterisk]

Problem seemed to be only with 'hot' buffers / caches because on identical slave servers that query would not hang, but only on the hi load master.

Workaround number #5 worked!

Thanks

Claudio