Recently I restored some older MySQL 5.1 database. In the meantime some minor patches were applied and suddenly I received an error (while calling from a java app):

MySQL Database Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation …

Usually my tables are all created (something) like this:

create table apl_users
(usr_id                 int(10)        unsigned not null auto_increment primary key
,usr_login              varchar(40)             not null
,usr_password           varchar(40)             not null
,usr_name               varchar(80)
,index (usr_login)
) engine=InnoDB default charset=utf8;

And a stored procedure who uses this is not really fancy:

create procedure sp_get_usr( in p_in_usr_login varchar(40) )
begin
  select usr_id
  ,      usr_login
  ,      usr_password
  ,      usr_name
  from   apl_users
  where  usr_login = p_in_usr_login;
end;

But somehow (after an upgrade) I got the error mentioned above. After some experimenting on how to collate, I found out maybe it has something to do with the database settings itself (for some odd reason, a bugmantis installation in the past was defaulted to a latin_swedish character set; I’d never paid much attention to that). So I recreated the database from:

create database my_database;

to:

create database my_database default character set 'utf8' collate 'utf8_general_ci';

An now, so far (the fat lady is not singing yet), no errors calling stored procedures from my Java application…