Sandeep Rao Sandeep Rao - 4 months ago 17
MySQL Question

Illegal mix of collations error in mysql query

Is there any way to compare the generated range column in the mysql query ?

SELECT ue.bundle,ue.timestamp,b.id,bv.id as bundleVersionId,bv.start_date,bv.end_date, bv.type,ue.type from (
SELECT bundle,timestamp,tenant, case when Document_Id ='' then 'potrait'
WHEN Document_Id<>'' then 'persisted' end as type from uds_expanded ) ue
JOIN bundle b on b.name=ue.bundle join bundle_version bv on b.id=bv.bundle_id
WHERE ue.tenant='02306' and ue.timestamp >= bv.start_date and ue.timestamp <=bv.end_date and **ue.type=bv.type ;**


I am getting the following error when I try to compare the types

Error Code: 1267. Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '=' 0.000 sec

kba kba
Answer

Stick to one encoding/collation for your entire system. Right now you seem to be using UTF8 one place and latin1 in another place. Convert the latter to use UTF8 as well and you'll be good.

You can change the collation to UTF8 using

alter table <some_table> convert to character set utf8 collate utf8_general_ci;
Comments