Problem with ajaxSearch and utf8 (cyrillic) searches

Hi,
I have a problem when searching with cyrillic (utf8) words. The problem results in MySql server crash.
And the crash results in infinte loop in the browser and practically 100% CPU usage and ... >:(

The query sent to the server looks like:

SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.menutitle, sc.content, sc.publishedon,
GROUP_CONCAT( DISTINCT CAST(ntv.id AS CHAR) SEPARATOR "," ) AS tv_id, GROUP_CONCAT( DISTINCT ntv.value SEPARATOR ", " )
AS tv_value FROM `modx_site_content` sc LEFT JOIN( SELECT DISTINCT tv.id, tv.value, tv.contentid
FROM `modx_site_tmplvar_contentvalues` tv WHERE (((tv.value LIKE ’%тест%’))) ) AS ntv
ON sc.id = ntv.contentid WHERE ((sc.published=1) AND (sc.searchable=1) AND (sc.deleted=0)
AND (sc.type=’document’) AND (sc.privateweb=0))
GROUP BY sc.id HAVING (((sc.pagetitle LIKE ’%тест%’)
OR (sc.longtitle LIKE ’%тест%’)
OR (sc.description LIKE ’%тест%’)
OR (sc.alias LIKE ’%тест%’)
OR (sc.introtext LIKE ’%тест%’)
OR (sc.menutitle LIKE ’%тест%’)
OR (sc.content LIKE ’%тест%’)
OR (tv_value LIKE ’%тест%’)))
ORDER BY sc.publishedon,sc.pagetitle;

The server crashes with:
ERROR 2013 (HY000): Lost connection to MySQL server during query
(The server is 5.0.58 on Centos 5)

The same query with latin search like:

SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.menutitle, sc.content, sc.publishedon,
GROUP_CONCAT( DISTINCT CAST(ntv.id AS CHAR) SEPARATOR "," ) AS tv_id, GROUP_CONCAT( DISTINCT ntv.value SEPARATOR ", " )
AS tv_value FROM `modx_site_content` sc LEFT JOIN( SELECT DISTINCT tv.id, tv.value, tv.contentid
FROM `modx_site_tmplvar_contentvalues` tv WHERE (((tv.value LIKE ’%test%’))) ) AS ntv
ON sc.id = ntv.contentid WHERE ((sc.published=1) AND (sc.searchable=1) AND (sc.deleted=0)
AND (sc.type=’document’) AND (sc.privateweb=0))
GROUP BY sc.id HAVING (((sc.pagetitle LIKE ’%test%’)
OR (sc.longtitle LIKE ’%test%’)
OR (sc.description LIKE ’%test%’)
OR (sc.alias LIKE ’%test%’)
OR (sc.introtext LIKE ’%test%’)
OR (sc.menutitle LIKE ’%test%’)
OR (sc.content LIKE ’%test%’)
OR (tv_value LIKE ’%test%’)))
ORDER BY sc.publishedon,sc.pagetitle;


goes just fine.

After a long search for what could cause the problem I found that following change fixes it:

SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.menutitle, sc.content, sc.publishedon,
GROUP_CONCAT( DISTINCT CAST(ntv.id AS CHAR) SEPARATOR "," ) AS tv_id, GROUP_CONCAT( DISTINCT CAST(ntv.value AS CHAR) SEPARATOR ", " )
AS tv_value FROM `modx_site_content` sc LEFT JOIN( SELECT DISTINCT tv.id, tv.value, tv.contentid
FROM `modx_site_tmplvar_contentvalues` tv WHERE (((tv.value LIKE ’%тест%’))) ) AS ntv
ON sc.id = ntv.contentid WHERE ((sc.published=1) AND (sc.searchable=1) AND (sc.deleted=0)
AND (sc.type=’document’) AND (sc.privateweb=0))
GROUP BY sc.id HAVING (((sc.pagetitle LIKE ’%тест%’)
OR (sc.longtitle LIKE ’%тест%’)
OR (sc.description LIKE ’%тест%’)
OR (sc.alias LIKE ’%тест%’)
OR (sc.introtext LIKE ’%тест%’)
OR (sc.menutitle LIKE ’%тест%’)
OR (sc.content LIKE ’%тест%’)
OR (tv_value LIKE ’%тест%’)))
ORDER BY sc.publishedon,sc.pagetitle;

And changing accordingly search.class.inc.php the search is working fine.

I don’t know what exactly is causing the problem, I think it is related to tv_value LIKE... and the text and char types and utf8. May be there is more precise solution, but I hope my findings will help to resolve this issue.

Regards

Thanks ddim for this feedback.

I have executed your request :

SELECT sc.id, sc.pagetitle, sc.longtitle, sc.description, sc.alias, sc.introtext, sc.menutitle, sc.content, sc.publishedon,
GROUP_CONCAT( DISTINCT CAST(ntv.id AS CHAR) SEPARATOR "," ) AS tv_id, GROUP_CONCAT( DISTINCT ntv.value SEPARATOR ", " )
AS tv_value FROM `modx_site_content` sc LEFT JOIN( SELECT DISTINCT tv.id, tv.value, tv.contentid
FROM `modx_site_tmplvar_contentvalues` tv WHERE (((tv.value LIKE ’%тест%’))) ) AS ntv
ON sc.id = ntv.contentid WHERE ((sc.published=1) AND (sc.searchable=1) AND (sc.deleted=0)
AND (sc.type=’document’) AND (sc.privateweb=0))
GROUP BY sc.id HAVING (((sc.pagetitle LIKE ’%тест%’)
OR (sc.longtitle LIKE ’%тест%’)
OR (sc.description LIKE ’%тест%’)
OR (sc.alias LIKE ’%тест%’)
OR (sc.introtext LIKE ’%тест%’)
OR (sc.menutitle LIKE ’%тест%’)
OR (sc.content LIKE ’%тест%’)
OR (tv_value LIKE ’%тест%’)))
ORDER BY sc.publishedon,sc.pagetitle;

without any error or crash with : MySQL 5.1.30 on XP windows and with MYSQL 5.1 on Linux

but the use of CAST(ntv.value AS CHAR), is, perhaps, requested, in some case.

It might be related to the version of mysql, the problem is that there are many things on this server and it is not easy to change it.
It is usually problematic to change versions when using non latin encoding and i’m careful with this.
Anyway, the thing is that there is working solution in case of such a crash.

Actually for me the bigger problem was the infinite loop after the crash and the browser "freeze". This happens with both IE ans FF.
The only difference is that with FF if you are very fast and you manage to stop the refreshes you will see the error messages.
This problem is very difficult to debug, but I think it is very important to investigate.
In my case the template is not changed (what comes with default installation of modx).
I would expect some loop, related to the landing page and going back to the search but cannot be sure.
May be it is not related to the search at all.
Probably it would be possible to simulate this with changing the search query to produce server error.
If I find something more will inform you.

Regards