Bug #50351 ft_min_word_len=2 Causes query to hang
Submitted: 15 Jan 2010 3:21 Modified: 18 Jun 2010 1:41
Reporter: Donna Harmon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: FULLTEXT search Severity:S3 (Non-critical)
Version:5.1.35 + OS:Any
Assigned to: Sergey Vojtovich CPU Architecture:Any
Tags: regression

[15 Jan 2010 3:21] Donna Harmon
Description:
The problem did not exist in 5.0.89,5.1.30, 5.1.32 or 5.1.34  but started in 5.1.35 and persists through 5.1.42.

Problem:

When executing the following query:

SELECT COUNT(*) as NB, a.testid FROM dhtest as a WHERE MATCH(a.testdesc) AGAINST ("+195/60* +r14*" in boolean mode);

CREATE TABLE `dhtest` (
  `testid` int(12) unsigned NOT NULL DEFAULT '0',
  `testdesc` varchar(100) NOT NULL,
  `testint1` smallint(5) NOT NULL DEFAULT '0',
  `testint2` smallint(5) unsigned NOT NULL DEFAULT '0',
  FULLTEXT KEY `testdesc:` (`testdesc`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

It hangs in "Sending data" state:

+----+------+-----------+----------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------+
| Id | User | Host      | db       | Command | Time | State        | Info                                                                                                                |
+----+------+-----------+----------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------+
|  1 | root | localhost | dhtestdb | Query   |   31 | Sending data | SELECT COUNT(*) as NB, a.testid FROM dhtest as a WHERE MATCH(a.testdesc) AGAINST ("+195/60* +r14*" in boolean mode) |
|  2 | root | localhost | NULL     | Query   |    0 | NULL         | show full processlist                                                                                               |
+----+------+-----------+----------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------+

When killed just remains in "Sending data" with "Killed" for command and holds lock on table so other queries are prevented from executing on that table until server is restarted.

+----+------+-----------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
| Id | User | Host      | db        | Command | Time | State        | Info                                                                                                                 |
+----+------+-----------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
|  3 | root | localhost | dhtestdb | Killed  |  375 | Sending data | SELECT COUNT(*) as NB, a.testid FROM dhtest as a WHERE MATCH(a.testdesc) AGAINST ("+195/60* +r14*" in boolean mode)  |
|  9 | root | localhost | sbtestdb  | Query   |    0 | NULL         | show full processlist                                                                                                |
+----+------+-----------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+

How to repeat:

Set ft_min_word_len=2, run above query, to repeat requires test data which was uploaded separately.
[15 Jan 2010 3:41] Donna Harmon
Workaround:
Set ft_min_word_len in your my.cnf back to the default of 4 and restart the server

[mysqld]
...
ft_min_word_len=4
[22 Jan 2010 19:12] Matthew Lord
How is this S3?  adding another affected customer.
[2 Feb 2010 11:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/98930

3338 Sergey Vojtovich	2010-02-02
      BUG#50351 - ft_min_word_len=2 Causes query to hang
      
      Performing fulltext prefix search (a word with truncation
      operator) may cause a dead-loop. ft_min_word_len value
      doesn't matter actually.
      
      The problem was introduced along with "smarter index merge"
      optimization.
     @ mysql-test/r/fulltext.result
        A test case for BUG#50351.
     @ mysql-test/t/fulltext.test
        A test case for BUG#50351.
     @ storage/myisam/ft_boolean_search.c
        When going up to first-level tree, we need to restore docid[0],
        so it informs fulltext index merge not to enter this second-level
        tree again (avoiding dead-loop).
[1 Mar 2010 8:44] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joerg@mysql.com-20100212173307-ph563zr4wmoklgwd) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:08] Sergey Vojtovich
BUG#50556 was marked as duplicate.
[2 Mar 2010 14:33] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:38] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)
[2 Mar 2010 14:43] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)
[7 Mar 2010 20:14] Paul DuBois
Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

Full-text queries that used the truncation operator (*) could enter
an infinite loop.
[17 Jun 2010 11:56] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:35] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:22] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)