I have a similar situation as described here - http://forums.mysql.com/read.php?103,63845,63845; basically
If I run the query in this SP directly, it executes fine:
However, if I execute the stored procedure, I get an "Illegal mix of collations" error:
It is, however, not clear how to get rid of it.
If both sides have the same coercibility, then:
If both sides are Unicode, or both sides are not Unicode, it is an error.
which seems to be my particular case. My error message:
ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='
Oh, and it used to work fine, but toyed with the db, cant remember what I have done though, and ... this is the outcome now :S
Version: 5.1.67-0ubuntu0.11.10.1-log. Issue observed from both mysql-client and another custom java client.
EDIT: the stored procedure in question
create procedure TigUserLogin(_user_id varchar(2049) CHARSET utf8, _user_pw varchar(255) CHARSET utf8) begin if exists(select 1 from tig_users where (account_status > 0) AND (sha1_user_id = sha1(lower(_user_id))) AND (user_pw = _user_pw) AND (user_id = _user_id)) then update tig_users set online_status = online_status + 1, last_login = CURRENT_TIMESTAMP where sha1_user_id = sha1(lower(_user_id)); select _user_id as user_id; else update tig_users set failed_logins = failed_logins + 1 where sha1_user_id = sha1(lower(_user_id)); select NULL as user_id; end if; end //