collation mismatch in stored proc
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?
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.