Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

From http://dev.mysql.com/doc/refman/5.6/en/charset-collation-expressions.html:

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.

Thanks

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 //
share|improve this question
1  
Pasting a minimal version of the stored procedure into your question might be a good idea. Making it minimal might solve the problem, too. –  Mike Sherrill 'Cat Recall' Sep 5 '13 at 14:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.