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
Triage: Needs Triage: D2 (Serious)

[29 May 2008 12:02] Roman Veretelnikov
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.
[29 May 2008 12:46] Miguel Solorzano
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.
[30 May 2008 16:53] Gleb Voronich
Miguel, I've just posted a hidden comment here with the requested info.
Kindly check it.
[7 Jun 2008 7:21] Sveta Smirnova
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.
[7 Jul 2008 23:00] Bugs System
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".
[4 Feb 2009 19:37] Serguei Ivantsov
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
[4 Feb 2009 20:53] Sveta Smirnova
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.
[4 Feb 2009 22:14] Simon Logic
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.
[4 Feb 2009 22:24] Konstantin Kopachev
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.
[5 Feb 2009 8:41] Serguei Ivantsov
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.
[8 Feb 2009 13:20] Serguei Ivantsov
Sveta Smirnova, are you able to reproduce the bug?
If not, I can make a PHP script, which hangs MySQL.
[5 Mar 2009 0:00] Bugs System
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".
[3 Apr 2009 0:53] Martyn D
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.
[3 Apr 2009 3:05] Martyn D
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
[23 Apr 2009 14:35] Alexandre Koriakine
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
[25 Apr 2009 10:26] Alexandre Koriakine
I think we solved it by filtering input query:
in PHP: $query=preg_replace('/[^\x80-\xFF\w\,\.\-]/',' ',$query);
[28 Apr 2009 8:54] Sveta Smirnova
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.
[28 Apr 2009 13:05] Serguei Ivantsov
PHP script to reproduce the bug. Tested on four servers.

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

[29 Apr 2009 5:32] Sveta Smirnova
Serguei,

thank you for the script.

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

Only version 5.0 affected.
[29 Apr 2009 5:33] Sveta Smirnova
Modified script to reproduce the problem

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

[13 Jan 2010 18:23] Site Admin
I am having the same issue

Is there a fix available?
[19 Jan 2010 22:57] Alex Pogodin
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
[7 May 2010 11:03] N BD
Also having the same issue. Any solution available?
[6 Sep 2010 15:20] Valera Leontyev
The same problem. Isn't is critical? The bug was known 2 years ago and still wasn't fixed!
[16 Sep 2010 12:55] Omer Barnir
This problem is specific to 5.0 version which is EOL.
[19 Apr 2011 14:37] poisonous dagger
what is the solution to this problem?
[19 Apr 2011 14:55] Shane Bester
The solution is to use 5.5.11 or 5.1.56....
[19 Apr 2011 15:04] poisonous dagger
thanks shane, i will try that.
[19 Apr 2011 15:04] poisonous dagger
thanks shane, i will try that.