Very odd error with foreign characters in mySQL db search

[galahad3]

Getting a strange error if users try inputting any characters which are Russian / Polish (haven't tried other character sets yet) into their search using AjaxSearch (an Ajax-based search facility that queries a mySQL db) on our site:


MODx encountered the following error while attempting to parse the requested resource:
« Execution of a query to the database failed - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like' »
SQL: 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 `expotel_site_content` sc LEFT JOIN( SELECT DISTINCT tv.id, tv.value, tv.contentid FROM `expotel_site_tmplvar_contentvalues` tv WHERE (((tv.value LIKE '%łdfdfłłgh%'))) ) 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 '%łdfdfłłgh%') OR (sc.longtitle LIKE '%łdfdfłłgh%') OR (sc.description LIKE '%łdfdfłłgh%') OR (sc.alias LIKE '%łdfdfłłgh%') OR (sc.introtext LIKE '%łdfdfłłgh%') OR (sc.menutitle LIKE '%łdfdfłłgh%') OR (sc.content LIKE '%łdfdfłłgh%') OR (tv_value LIKE '%łdfdfłłgh%'))) ORDER BY sc.publishedon,sc.pagetitle


Any ideas what might be causing this? And how to fix?

[abduraooft]

Any ideas what might be causing this? And how to fix?What's the collation applied to table/filed?

[galahad3]

Collation is set to latin1_swedish_ci - is this correct? This was the default setting when mySQL was set up on the server.

If not correct, how would I change this safely without affecting the db contents?

[abduraooft]

If not correct, how would I change this safely without affecting the db contents? I think you could solve the issue by setting a utf8 collation (say utf8_general_ci) to your fields (it may corrupt the current data in it, so take a backup first). Otherwise, you may need to convert the charset for comparison. See http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html

[galahad3]

Thanks, how would I set the collation to utf8_general_ci? Is there a mySQL query / command I can run that will do this?

[abduraooft]

You could use something like
ALTER TABLE `table_name` CHANGE `filed_name` `filed_name` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci
(Change the parameters according to your schema)

[galahad3]

Ok, so if my table name is table1 and I have fields called field1, field2 etc. I need to run this for each field like so?


ALTER TABLE `table1` CHANGE `field1` `field1` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci


- and then repeat that for each field, e.g:


ALTER TABLE `table1` CHANGE `field2` `field2` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci


And so on?