MySQL: Search in FULLTEXT index hangs the base. Solution to the problem
A few months ago I caught one elusive bug with a search in the database. And today, after reading the topic HTTP, RFC 3986 and browsers , I decided to tell the public, suddenly someone will pop up too.
Problem
There is a site in UTF8. There is a MySQL base in UTF8 and FULLTEXT index. Search on it goes to BOOLEAN MODE. With a certain type of query, the database stops responding. And only for one DB within the MySQL server. Normal restart of the daemon does not work, it turns out to reboot only hard mysql -9.
Such a nuisance is noticed on MySQL 5.0.51a and higher.
Why?
But the thing is that this is some 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 N-th query of this type the database will hang in the FULLTEXT Initialization state, and fill all tables, and not just one with an index. Manifested not from the first request, but once or twice a day can happen.
')
Medicine
Before substituting a search phrase into a query, we convert it into UTF8, if necessary.
Who is guilty?
And here I will tell you what the topic is about, which reminded me of this story.
I support one site as a hobby. From the site you can put a search plugin to the browser in the format OpenSearch. As it turned out, the browsers of some users when searching through this plugin for some reason think that the site needs Windows-1251 encoding. Where do they have such confidence, I do not know. Perhaps Firefox remembered that once upon a time the site was Windows-encoded. 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 :)
Bugreport link
bugs.mysql.com/bug.php?id=37067
Problem
There is a site in UTF8. There is a MySQL base in UTF8 and FULLTEXT index. Search on it goes to BOOLEAN MODE. With a certain type of query, the database stops responding. And only for one DB within the MySQL server. Normal restart of the daemon does not work, it turns out to reboot only hard mysql -9.
Such a nuisance is noticed on MySQL 5.0.51a and higher.
Why?
But the thing is that this is some 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 N-th query of this type the database will hang in the FULLTEXT Initialization state, and fill all tables, and not just one with an index. Manifested not from the first request, but once or twice a day can happen.
')
Medicine
Before substituting a search phrase into a query, we convert it into UTF8, if necessary.
Who is guilty?
And here I will tell you what the topic is about, which reminded me of this story.
I support one site as a hobby. From the site you can put a search plugin to the browser in the format OpenSearch. As it turned out, the browsers of some users when searching through this plugin for some reason think that the site needs Windows-1251 encoding. Where do they have such confidence, I do not know. Perhaps Firefox remembered that once upon a time the site was Windows-encoded. 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 :)
Bugreport link
bugs.mysql.com/bug.php?id=37067
Source: https://habr.com/ru/post/68709/
All Articles