MySQL Bugs: #5089: Server crashing on fulltext search for short keys in boolean mode

Bug #5089	Server crashing on fulltext search for short keys in boolean mode
Submitted:	18 Aug 2004 12:32	Modified:	19 Aug 2004 0:00
Reporter:	Konstantin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.20	OS:	Linux (RedHat 8.0)
Assigned to:		CPU Architecture:	Any

Description:
MySQL constantly crashes while executing the fulltext search query in boolean mode, when the search key contains two words and the first one is shorter then minimal word length for fulltext index and possibly contains an escaped quote like:
WHERE MATCH(post.title,pagetext) AGAINST ('+3\" +exhaust' IN BOOLEAN MODE)

Also possible that it happens due to two-fields fulltext index.

How to repeat:
Not sure that's easy, but to repeat you have to build create the following tables:

CREATE TABLE `deletionlog` (
  `primaryid` int(10) unsigned NOT NULL default '0',
  `type` enum('post','thread') NOT NULL default 'post',
  `userid` int(10) unsigned NOT NULL default '0',
  `username` varchar(50) NOT NULL default '',
  `reason` varchar(125) NOT NULL default '',
  PRIMARY KEY  (`primaryid`,`type`)
) TYPE=MyISAM;

# --------------------------------------------------------

#
# Table structure for table `post`
#

CREATE TABLE `post` (
  `postid` int(10) unsigned NOT NULL auto_increment,
  `threadid` int(10) unsigned NOT NULL default '0',
  `parentid` int(10) unsigned NOT NULL default '0',
  `username` varchar(50) NOT NULL default '',
  `userid` int(10) unsigned NOT NULL default '0',
  `title` varchar(250) NOT NULL default '',
  `dateline` int(10) unsigned NOT NULL default '0',
  `pagetext` mediumtext NOT NULL,
  `allowsmilie` smallint(6) NOT NULL default '0',
  `showsignature` smallint(6) NOT NULL default '0',
  `ipaddress` varchar(15) NOT NULL default '',
  `iconid` smallint(5) unsigned NOT NULL default '0',
  `visible` smallint(6) NOT NULL default '0',
  `attach` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`postid`),
  KEY `iconid` (`iconid`),
  KEY `userid` (`userid`),
  KEY `threadid` (`threadid`,`userid`),
  FULLTEXT KEY `title` (`title`,`pagetext`)
) TYPE=MyISAM AUTO_INCREMENT=1548073696 ;

# --------------------------------------------------------

#
# Table structure for table `thread`
#

CREATE TABLE `thread` (
  `threadid` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(250) NOT NULL default '',
  `firstpostid` int(10) unsigned NOT NULL default '0',
  `lastpost` int(10) unsigned NOT NULL default '0',
  `forumid` smallint(5) unsigned NOT NULL default '0',
  `pollid` int(10) unsigned NOT NULL default '0',
  `open` smallint(6) NOT NULL default '0',
  `replycount` int(10) unsigned NOT NULL default '0',
  `postusername` varchar(50) NOT NULL default '',
  `postuserid` int(10) unsigned NOT NULL default '0',
  `lastposter` varchar(50) NOT NULL default '',
  `dateline` int(10) unsigned NOT NULL default '0',
  `views` int(10) unsigned NOT NULL default '0',
  `iconid` smallint(5) unsigned NOT NULL default '0',
  `notes` varchar(250) NOT NULL default '',
  `visible` smallint(6) NOT NULL default '0',
  `sticky` smallint(6) NOT NULL default '0',
  `votenum` smallint(5) unsigned NOT NULL default '0',
  `votetotal` smallint(5) unsigned NOT NULL default '0',
  `attach` smallint(5) unsigned NOT NULL default '0',
  `similar` varchar(55) NOT NULL default '',
  PRIMARY KEY  (`threadid`),
  KEY `iconid` (`iconid`),
  KEY `postuserid` (`postuserid`),
  KEY `forumid` (`forumid`,`visible`,`sticky`,`lastpost`),
  KEY `pollid` (`pollid`),
  KEY `lastpost` (`lastpost`),
  FULLTEXT KEY `title` (`title`)
) TYPE=MyISAM AUTO_INCREMENT=886315 ;

And have about 9 000 000 records in post, 800 000 in thread and 1000 in deletionlog

and then run the following query:

SELECT post.postid, post.threadid
FROM post AS post 
INNER JOIN thread AS thread ON(thread.threadid = post.threadid)
LEFT JOIN deletionlog AS delpost ON(delpost.primaryid = post.postid AND delpost.type = 'post')
WHERE MATCH(post.title,pagetext) AGAINST ('+3\" +exhaust' IN BOOLEAN MODE)
AND delpost.primaryid IS NULL
AND post.visible = 1
LIMIT 0, 400

Suggested fix:
I've experimented with the query and found out that:

A. If you run a search on the second keyword only like "exhaust" in the example above, and then run the full query above, it will work. My guess is that it doesn't crash while it uses cache.

B. If you swap the words places like ('+exhaust +3\" ' IN BOOLEAN MODE), the query will work

C. If you change number in the keyword to letter like ('+a\" +exhaust' IN BOOLEAN MODE) the server will also crash.

Can you please provide us the tables with data or the script for to populate them?

You can upload the files to:

ftp://ftp.mysql.com/pub/mysql/upload/

Thanks.

Well, the data files are about 4Gb, I could try to gzip them, but I doubt it would help. Is it ok to upload ~4Gb your FTP? Also - could we upload data/index files rather then dump?

Also we don't have 'script' to populate the data, because it's online forum archive for more than 5 years ;)

Thanks.

Sorry I thought the tables are some MBs size. What we need is a repeatable 
test case, that it is the only way we can process and fix a bug!. So my question
now is: are you able for to create the test case with tables around of 50 - 100
MB of size ? 

Thanks :)

I've managed to create a test case for you. I've put a small extract of my big tables to another database and run the query from my first report - it has crashed the server again.
The gzipped dump of the extract is uploaded to your FTP named bug5089_test_case_.sql.gz

Thanks.

I have just run the query on a different newly installed server, with the data from the test case I've uploaded. The result is the same - the server crashes.

Thank you for the test case, just now I am downloading it for
to run on my Slackware box.

I tested against 4.0 BK source tree (3 days older) and wasn't to repeat your
test case:

mysql> SELECT post.postid, post.threadid
    -> FROM post AS post
    -> INNER JOIN thread AS thread ON(thread.threadid = post.threadid)
    -> LEFT JOIN deletionlog AS delpost ON(delpost.primaryid = post.postid AND
    -> delpost.type = 'post')
    -> WHERE MATCH(post.title,pagetext) AGAINST ('+3\" +exhaust' IN BOOLEAN MODE)
    -> AND delpost.primaryid IS NULL
    -> AND post.visible = 1
    -> LIMIT 0, 400
    -> ;
Empty set (3.61 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.21-debug-log |
+------------------+
1 row in set (0.07 sec)

The problem is not in the tables - MySQL crashes on a query with boolean fulltext search, where you have a single double quote in the search string.

It's a known bug, fixed in 4.0 and 4.1 some time ago, see elsewhere in bugdb

Thanks for your help. 

I've build the server from the development tree and the problem has gone.