mysql collation mess

mysql collation mess

by Edmundo Moris -
Number of replies: 0
Hi friends, I have an ugly issue with the moodle installation of our college, maybe you can suggest us how to solve it.
We are using external database connection to enrol our students. Both moodle database and external database lies on same mysql server.
The usernames of some of our students include special characters.
Theses users are not enrolled by the external database plugin, and if i execute the enrol_database_sync.php I got this error:
#########################
Notice: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' STATEMENT:
SELECT u.id AS userid, ra.id AS enrolmentid
FROM mdl_user u
LEFT OUTER JOIN mdl_role_assignments ra ON u.id = ra.userid
AND ra.roleid = 5
AND ra.contextid = 644
WHERE u.username = 'abu�o' AND (u.deleted IS NULL OR u.deleted=0) in /var/www/enrol/database/enrol.php on line 329
##########################

Cheking in my mysql variables I got in both databases the same result:

character_set_client latin1
character_set_connection latin1
character_set_database utf8
character_set_results latin1
character_set_server latin1
character_set_system utf8
collation_connection latin1_swedish_ci
collation_database utf8_unicode_ci
collation_server latin1_swedish_ci

I guess the collation_connection is involved in my problem. Should I set it to utf8? Is that safe?

thanks a lot

Average of ratings: -