MySQL: Searching by FULLTEXT index hangs the base. Solution
Caught a few months ago, one subtle bug with a database search. And today, after reading the HTTP topic , RFC 3986 and browsers , I decided to tell the public, suddenly someone will come up too.
Problem
There is a site in UTF8. There is a MySQL database in UTF8 and a FULLTEXT index. Search on it goes to BOOLEAN MODE. With a certain type of query, the database stops responding. And only for one database within the MySQL server. Normal restart of the daemon does not work, it turns out to reboot only with hard mysql -9.
Such a nuisance is noticed on MySQL 5.0.51a and higher.
Why?
And the thing is that this is some kind of strange bug of MySQL itself and for some reason they are not in a hurry to solve it. It turns out that if the search phrase is in cp1251 encoding, and the base is in UTF8, then on the Nth such query the database will hang in the FULLTEXT Initialization state, and it will lock all the tables, and not just one with the index. It does not appear from the first request, but once or twice a day it can happen.
Medicine
Before substituting the search phrase into the query, we convert it to UTF8, if necessary.
Who's guilty?
And here I’ll tell you what the topic has to do with that reminded me of this story.
I support one site as a hobby. From the site you can put a search plug-in to the browser in the OpenSearch format. As it turned out, the browsers of some users when searching through this plugin for some reason think that the site needs a Windows-1251 encoding. Where did they get such confidence from, I don’t know. Perhaps Firefox remembered that once upon a time the site was in Windows encoding. Perhaps this is affected by the lack of encoding in the plugin code. Or maybe just magnetic storms, the problem appeared once or twice a day :)
Link to the bug
report bugs.mysql.com/bug.php?id=37067
Problem
There is a site in UTF8. There is a MySQL database in UTF8 and a FULLTEXT index. Search on it goes to BOOLEAN MODE. With a certain type of query, the database stops responding. And only for one database within the MySQL server. Normal restart of the daemon does not work, it turns out to reboot only with hard mysql -9.
Such a nuisance is noticed on MySQL 5.0.51a and higher.
Why?
And the thing is that this is some kind of strange bug of MySQL itself and for some reason they are not in a hurry to solve it. It turns out that if the search phrase is in cp1251 encoding, and the base is in UTF8, then on the Nth such query the database will hang in the FULLTEXT Initialization state, and it will lock all the tables, and not just one with the index. It does not appear from the first request, but once or twice a day it can happen.
Medicine
Before substituting the search phrase into the query, we convert it to UTF8, if necessary.
Who's guilty?
And here I’ll tell you what the topic has to do with that reminded me of this story.
I support one site as a hobby. From the site you can put a search plug-in to the browser in the OpenSearch format. As it turned out, the browsers of some users when searching through this plugin for some reason think that the site needs a Windows-1251 encoding. Where did they get such confidence from, I don’t know. Perhaps Firefox remembered that once upon a time the site was in Windows encoding. Perhaps this is affected by the lack of encoding in the plugin code. Or maybe just magnetic storms, the problem appeared once or twice a day :)
Link to the bug
report bugs.mysql.com/bug.php?id=37067
