MySQL Bugs: #37067: SELECT query hang in state FULLTEXT initialization

Bug #37067	SELECT query hang in state FULLTEXT initialization
Submitted:	29 May 2008 12:02	Modified:	16 Sep 2010 12:54
Reporter:	Roman Veretelnikov	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S1 (Critical)
Version:	5.0.51a, 5.0.75, 5.0 bzr	OS:	Linux (Centos 5.1 x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	fulltext, hang, utf8

Description:
Mysql process hang during execution of query

SELECT thread.threadid, MATCH(thread.title) AGAINST ('не отображается проверка автоматических регистраций') AS score
FROM thread AS thread
WHERE MATCH(thread.title) AGAINST ('не отображается проверка автоматических регистраций')
AND thread.open <> 10
LIMIT 5

on table

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(100) 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 '',
  `hiddencount` int(10) unsigned NOT NULL default '0',
  `showfirstpost` int(10) unsigned NOT NULL default '0',
  `deletedcount` smallint(5) unsigned NOT NULL default '0',
  `lastpostid` int(10) unsigned NOT NULL default '0',
  `prefixid` varchar(25) NOT NULL default '',
  `taglist` mediumtext,
  PRIMARY KEY  (`threadid`),
  KEY `postuserid` (`postuserid`),
  KEY `pollid` (`pollid`),
  KEY `forumid` (`forumid`,`visible`,`sticky`,`lastpost`),
  KEY `lastpost` (`lastpost`,`forumid`),
  KEY `dateline` (`dateline`),
  KEY `prefixid` (`prefixid`,`forumid`),
  FULLTEXT KEY `title` (`title`)
) ENGINE=MyISAM AUTO_INCREMENT=22020 DEFAULT CHARSET=utf8

I think this only happens with table using utf8 charset. But I am not sure.

Thread can not be killed using "kill <thread id>" query. And mysqld process can be killed using "kill -9" only.

This happens not every time, but 1-2 per day. During this mysqld eats all cpu time of one processor.

I have dumped table data, recreated table and filled it with old data. This did not helped. 

How to repeat:
Create table and fill it with some data. Make sql queries many times.

P.S. This is vBulletin forum.

Thank you for the bug report. It is possible to you provide a dump file with data enough to repeat the behavior reported?. I am afraid the kind of text is relevant to this case. Thanks in advance.

Miguel, I've just posted a hidden comment here with the requested info.
Kindly check it.

Thank you for the feedback.

We weren't able to repeat described behavior. Please check table for errors, repair it if needed and if you still able to repeat problem with health table provide your configuration file.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have the same bug. It can be reproduced several times a day.
x86_64 QuadCore CPU, CentOS 5.2
Linux 2.6.26.3
MySQL 5.0.75-log

Serguei,

thank you for the feedback.

We could not repeat described behavior with data provided by Roman. Probably we can be more lucky with your case. Please check table for errors (with CHECK TABLE) and if it is correct provide its dump and configuration file. Or even better if you could provide *MYD, *MYI and *frm files.

I was told by the original developer the problem was in wrong incoming query codepage. The data which were put after AGAINST keyword were in win-1251 codepage (someone put data in win-1251 on the utf-8 page somehow or made some hack i guess), but the table (and so an index) codepage was utf-8. After preprocessing the search data to detect whether it is fault utf-8 data & explicit converting it from win-1251 (for simplicity the developer just assumed if the data is not utf-8 then it will be win-1251) to utf-8 the problem has gone.

This problem occured not for the first query. It may pass 20 or even 50 queries & then MySQL hangs.

I forgot to mention that database connection collation was utf-8.

Good luck in reproducing the bug.

Repair table and check table report "Ok".
All queries are in "Locked" state for the current DB only. Other DBs are served properly by the MySQL.

All that Simon Logic writes is valid for my case: it may hang not on the first query, but randomly. When one process waits in "FULLTEXT initialization" state, all other queries to that table are locked. The mysqld process can be only stopped with "kill -9".
Also it is definitely closely related to encodings. My database, tables, data and connection are in 'utf-8', but when the query (for some reason) comes in Win-1251 shit may happens.

Sveta Smirnova, are you able to reproduce the bug?
If not, I can make a PHP script, which hangs MySQL.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I too am having this issue on server version 5.1.30

It has happening very sporadically which is making investigations difficult.

At this time i have noticed no trend in the contents of the query (eg the single quote issue).

What is possibly worth noting is that after the initial "hung" query is killed (Which unlike the bug starter, in my case the queries can be killed) any further queries in the queue which use full text searches hang for a few seconds in the FULLTEXT initialization state.

After the backlog is cleared, server appears to perform normally again.

Once i can generate more useful information i shall post that, i just thought the presence of this in the 5.1.x series might be noteworthy.

It just happened again, nothing abnormal, was searching for used just standard english characters. Re-running the query had no issues.

One thing though which may just be a documentation mistake, but the docs say "FULLTEXT initialization" is "The server is preparing to perform a natural-language full-text search."

The query is in boolean mode
SELECT * FROM Posts WHERE post_id IN(SELECT post_id FROM Indexed_Posts WHERE MATCH(subject,message) AGAINST ('+supernia' IN BOOLEAN MODE) ) && board_id IN ('advertise') && time > '893124627' ORDER BY time DESC LIMIT 0, 25

We having the same issue with configuration:

Linux (Centos 5.2 x86_64  2.6.18-92.1.22.el5 #1 SMP)
Server version: 5.0.77-community-log

We didn't have problems on our old server with Server version: 5.0.45-community-log

I think we solved it by filtering input query:
in PHP: $query=preg_replace('/[^\x80-\xFF\w\,\.\-]/',' ',$query);

Serguei Ivantsov,

thank you for the feedback. No, I was not able to reproduce this bug. Please provide PHP script you wrote about. Thanks in advance.

PHP script to reproduce the bug. Tested on four servers.

Attachment: my_hang.php.gz (application/x-gzip, text), 1.16 KiB.

Serguei,

thank you for the script.

Verified as described with small modification: set names added, file will be attached.

Only version 5.0 affected.

Modified script to reproduce the problem

Attachment: my_hang.php (application/octet-stream, text), 3.00 KiB.

I am having the same issue

Is there a fix available?

I have the same issue when search performed from page in Windows-1251 encoding. Not all phrases cause the crash though.

# mysql --version
mysql  Ver 14.12 Distrib 5.0.84, for pc-linux-gnu (x86_64) using readline 6.0

Also having the same issue. Any solution available?

The same problem. Isn't is critical? The bug was known 2 years ago and still wasn't fixed!

This problem is specific to 5.0 version which is EOL.

what is the solution to this problem?

The solution is to use 5.5.11 or 5.1.56....

thanks shane, i will try that.

thanks shane, i will try that.