MySQL Full Text Query Locking Table

Question

Every once in a while we get a particularly long running full text query in MySQL. The query will run for a very long time, currently I'm seeing one that's running for 50,000 seconds (and still going). Using Kill, or Kill Query on the query seems to do nothing. Also, the command Timeout on the client side is 30 seconds, so the client has already failed and went away, but the server just keeps on running the query, using up quite a lot of resources. In the past we have had to restart the MySQL service to get such queries to stop running. Is there any other way to stop these queries. We are taking measures to ensure the queries don't run in the first place, but they still get run every once in a while, and it's a major pain. Mostly because while the queries are running, we are unable to perform updates on the table.

An example of a long running query would be something along the lines of:

SELECT id,Title From Articles WHERE MATCH(ArticleText) AGAINST('+Nintendo*HD*Wii*' IN BOOLEAN MODE)

This runs particularly long because of the * in the middle of the search string, as well as at the end of the string.

Answer 1

What version of MySQL are you using? You might be able to fix this by upgrading.

Also, is that sample query real? I would expect the search string to be something like '+Nintendo* +HD* +Wii*' instead of '+Nintendo*HD*Wii*.

I've never had this type of issue in 5.0, but in earlier releases of MySQL 5.1 I have seen several very similar issues regarding certain content in the search string causing the fulltext query to hang and be unkillable.

Here's one that I saw in 5.1.42, that was fixed in 5.1.45: http://bugs.mysql.com/bug.php?id=50556 which was marked as a dupe of: http://bugs.mysql.com/bug.php?id=50351

I reported another issue with fulltext queries hanging when the search string contained a colon. That issue existed in 5.1.31 and was fixed in 5.1.37.