collation mismatch in stored proc
Posted by: Erik Ullman
Date: January 19, 2010 10:08PM

Hi,

I have a simple (hopefully) but puzzling problem.

When I call a stored procedure from the query browser I get the following error:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_swedish_ci,IMPLICIT) for operation '='

I have called that procedure dozens of times before without a problem and to my knowledge I haven't changed anything. When i do:

> show variables like '%colla%';

I get:

collation_connection | utf8_general_ci
collation_database | utf8_swedish_ci
collation_server | utf8_swedish_ci

which tells me I have a mismatch in collation_connection, so I tried to set it:

> set collation_connection=utf8_swedish_ci;

To no avail, collation_connection stays utf8_general_ci

I can set the collation_connection to utf8_swedish_ci in the commandline tool however by adding skip-character-set-client-handshake under [mysqld] in my.ini, but the problem remains. The command line tool gives me the same error even when all three collations and the compared column are set to utf8_swedish_ci.

The proc looks like this:

create procedure sp_attribute_refcnt(
IN _varde varchar(270)
)
begin
select
a.attribut_id
,count(aa.attribut_id)
,typnamn
,group_concat(artikelnummer separator ' | ')
from T_attribut a
left join t_artikelattribut aa using(attribut_id)
left join T_attributtyper using(typ)
where
a.varde = _varde
group by attribut_id, typ;

end

The problem line I suppose is "a.varde = _varde"

neither changing it to:
> a.varde = CONVERT(a.varde using utf8) collate utf8_swedish_ci
nor changing the call
> call sp_attribute_refcnt(convert('nessr' using utf8) collate utf8_swedish_ci);
works.

Why is this happening and what should I do?

Options: ReplyQuote


Subject
Views
Written By
Posted
collation mismatch in stored proc
15311
January 19, 2010 10:08PM
6391
January 19, 2010 10:14PM
4561
January 19, 2010 10:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.