MySQL Bugs: #42907: Multi-term boolean fulltext query containing a single quote fails in 5.1.x

Bug #42907	Multi-term boolean fulltext query containing a single quote fails in 5.1.x
Submitted:	16 Feb 2009 23:21	Modified:	15 May 2009 23:31
Reporter:	Alexander Dewilde	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S1 (Critical)
Version:	5.1/6.0	OS:	MacOS (10.5.6 (x86))
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any
Tags:	regression

Description:
Following simple boolean fulltext query locks up mysqld...
The query refuses to be killed and the cpu goes nuts, basically turning the server pretty much useless!

How to repeat:
create table tmp (title varchar(64) not null,fulltext key (title)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into tmp values("dave"),("dave's"),("dave's shop"),("dave's shoppingcart"),("shop"),("shoppingcart");

select * from tmp where match (title) against("+dave's* +shop*" in boolean mode);

Indeed on machine dual core 64-bit Windows Vista that query arise 50-52% CPU usage, after a kill the query continues how can see below:

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.33-Win x86-64 bzr revno:2756-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >use test
Database changed
mysql 5.1 >create table tmp (title varchar(64) not null,fulltext key (title)) ENGINE=MyISAM DEFAULT
    -> CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql 5.1 >
mysql 5.1 >insert into tmp values("dave"),("dave's"),("dave's shop"),("dave's
    "> shoppingcart"),("shop"),("shoppingcart");
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 5.1 >
mysql 5.1 >select * from tmp where match (title) against("+dave's* +shop*" in boolean mode);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql 5.1 >show processlist\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: test

*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:49992
     db: test
Command: Killed
   Time: 2569
  State: Sending data
   Info: select * from tmp where match (title) against("+dave's* +shop*" in boolean mode)
*************************** 2. row ***************************
     Id: 3
   User: root
   Host: localhost:50095
     db: NULL
Command: Sleep
   Time: 52
  State:
   Info: NULL
*************************** 3. row ***************************
     Id: 4
   User: root
   Host: localhost:50235
     db: test
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
3 rows in set (0.02 sec)

mysql 5.1 >

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.33-Win x86-64 bzr revno:2756-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.1 >show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:49992
     db: test
Command: Query
   Time: 1327
  State: Sending data
   Info: select * from tmp where match (title) against("+dave's* +shop*" in boolean mode)
*************************** 2. row ***************************
     Id: 3
   User: root
   Host: localhost:50095
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
2 rows in set (0.00 sec)

mysql 5.1 >kill 1;
Query OK, 0 rows affected (0.00 sec)

Trying to shutdown the server it hangs forever:

c:\dbs>c:\dbs\5.1\bin\mysqladmin -uroot --port=3510 shutdown

c:\dbs>c:\dbs\5.1\bin\mysqld --defaults-file=c:\dbs\5.1\my.ini --standalone --console
090216 20:25:37  InnoDB: Started; log sequence number 0 46409
090216 20:25:37 [Note] Event Scheduler: Loaded 0 events
090216 20:25:37 [Note] c:\dbs\5.1\bin\mysqld: ready for connections.
Version: '5.1.33-Win x86-64 bzr revno:2756-log'  socket: ''  port: 3510  Source distributio
090216 21:12:38 [Note] c:\dbs\5.1\bin\mysqld: Normal shutdown

090216 21:12:38 [Note] Event Scheduler: Purging the queue. 0 events
090216 21:12:40 [Warning] c:\dbs\5.1\bin\mysqld: Forcing close of thread 3  user: 'root'

090216 21:12:40 [Warning] c:\dbs\5.1\bin\mysqld: Forcing close of thread 1  user: 'root'

The only way was to kill the process:

c:\dbs>pslist | findstr mysqld

pslist v1.28 - Sysinternals PsList
Copyright ® 2000-2004 Mark Russinovich
Sysinternals

mysqld              284   8  11 32693  30448     0:50:15.000     0:51:18.591

c:\dbs>pskill 284

PsKill v1.12 - Terminates processes on local or remote systems
Copyright (C) 1999-2005  Mark Russinovich
Sysinternals - www.sysinternals.com

Process 284 killed.

5.0 doesn't presents that behavior:

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.79-Win x86-64 bzr revno:2741-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql 5.0 > use test
Database changed
mysql 5.0 > create table tmp (title varchar(64) not null,fulltext key (title)) ENGINE=MyISAM DEFAULT
    -> CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql 5.0 >
mysql 5.0 > insert into tmp values("dave"),("dave's"),("dave's shop"),("dave's
    "> shoppingcart"),("shop"),("shoppingcart");
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql 5.0 >
mysql 5.0 > select * from tmp where match (title) against("+dave's* +shop*" in boolean mode);
+---------------------+
| title               |
+---------------------+
| dave's shop         |
| dave's
shoppingcart |
+---------------------+
2 rows in set (0.00 sec)

mysql 5.0 >

Repeatable on released versions: 5.1.(22/23a/24/25/26/28/29).
Not repeatable on 5.1.16:

c:\temp\mysql-5.1.16-beta-winx64>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.16-beta-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tmp (title varchar(64) not null,fulltext key (title)) ENGINE=MyISAM DEFAULT
    -> CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> insert into tmp values("dave"),("dave's"),("dave's shop"),("dave's
    "> shoppingcart"),("shop"),("shoppingcart");
Query OK, 6 rows affected (0.03 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> select * from tmp where match (title) against("+dave's* +shop*" in boolean mode);
+---------------------+
| title               |
+---------------------+
| dave's shop         |
| dave's
shoppingcart |
+---------------------+
2 rows in set (0.05 sec)

Thank you for the bug report. Verified as described on dual core machine 64-bit running on Windows Vista.

Bug #42741 was marked as duplicate of this one.

Call stack while query hangs

Attachment: call-stack-Bug42907.txt (text/plain), 13.31 KiB.

Bug: http://bugs.mysql.com/bug.php?id=43123 marked as duplicate of this one

The simple workaround is to create a dummy text column and add it to the index:
create table tmp (title varchar(64) not null, dummy varchar(1), fulltext key (title, dummy)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Your query:
select * from tmp where match (title) against("+dave's* +shop*" in boolean mode);
will work fine.

If you have a primary key column (or unique), you could use different solution I proposed in bug 43123 (link above).

Workaround verified as described.

However, the proposed workaround is not suitable for production servers.
It would take days to reorganise and to rewrite all queries in the application layer. 

We'll stick to the 5.0 branch untill the issue is resolved...

Proposed workaround requires only modifications in table structure - adding dummy column and modifying fulltext index. It's totally transparent for the application layer - you don't have to modify queries.

The workaround might work fine, but I prefer to wait untill the bug is fixed.
It's not a very elegant workaround. We use a lot of FT indexes and I see no point in rebuilding them with a combined dummy column, just to revert everything when the bug gets fixed?!

In 5.1.30, the presence of a hyphen in a keyword would cause the query to hang. In 5.1.32, I was able to reproduce a similar behavior with the phrase "test's edge" (mind the apostrophe). 5.1.33 seems to be able to handle it.

Whenever a search query fails because of a character combination, it creates an invincible runaway process that never releases its locks. The only thing that can put it to rest is the SIGKILL signal.

I'm using the full-text search engine for a major corporate site and switching away to Sphinx is not an option at the moment because it's not a "drop-in replacement". This bug has caused numerous disruptions, and I would really appreciate it if it was fixed or at least partially mitigated (e.g. by making the query terminate somehow whenever the offending condition occurs)

Here are a few other "triggers" in 5.1.32

        MATCH(uni_product_keywords.keywords)
        AGAINST( NAME_CONST('search_keywords',_latin1'( +LID,* +STRAW* +,SLOT* +,10* +,12*)' COLLATE 'latin1_swedish_ci') IN BOOLEAN MODE)

        MATCH(uni_product_keywords.keywords)
        AGAINST( NAME_CONST('search_keywords',_latin1'( +24lb* +classic* +crest* +A/B* +white* +env.*)' COLLATE 'latin1_swedish_ci') IN BOOLEAN MODE)

Sorry for the messy syntax, but I "caught" these in SHOW FULL PROCESSLIST.

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/72705

2864 Sergey Vojtovich	2009-04-23
      BUG#42907 - Multi-term boolean fulltext query containing a
                  single quote fails in 5.1.x
      
      Performing fulltext prefix search (a word with truncation
      operator) may cause a dead-loop.
      
      The problem was in smarter index merge algorithm - it was writing
      record reference to an incorrect memory area.
     @ mysql-test/r/fulltext.result
        A test case for BUG#42907.
     @ mysql-test/t/fulltext.test
        A test case for BUG#42907.
     @ storage/myisam/ft_boolean_search.c
        Fixed incorrect memory update by _mi_dpointer() when performing
        fulltext prefix search.

Any news on this bug ? 

I recently upgraded my 3 slave servers and 2 master servers to 5.1.34 . I notice that is not only a '(quote) but also a .(dot) and a -(sign) that crashes my servers using it in a MATCH AGAINST BOOLEAN MODE query

It hangs at "Copying to tmp table" and slowly the mysql Server will stop reponding. I dont want to downgrade to 5.0.xx. 

We changing the code to use normal LIKE statements now, untill this bug is fixed .

Yes, as you can see from the comment above yours "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."

The status is "Patch queued", so it was pushed, but not yet released in any particular release (otherwise there would've been a comment saying in what version the fix was done).

Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)

Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:joro@sun.com-20090430145739-7qp3by776q7ui81j) (merge vers: 6.0.12-alpha) (pib:6)

Noted in 5.1.35, 6.0.12 changelogs.

Full-text prefix searches could hang the connection and cause 100%
CPU consumption.

It looks like 5.1.35 isn't getting out the door anytime soon, since all the current effort seems to be concentrated on the 5.4 beta. Would it be possible to push out some sort of maintenance release that fixes just this issue? I'm reluctant to apply the patch manually to a production database, but this bug is sure to affect almost everyone who uses the full-text search in any type of production environment, even for low-to-medium-traffic business-to-business operations. In the interim, I've had to design an early warning system with an automatic hard restart if the number of runaway queries exceeds a certain number. At this point, the only way out is kill -9.

Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)

Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)

Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)