Sandeep Rao Sandeep Rao - 4 years ago 209
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,, 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 join bundle_version bv on
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 Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download