0

I have written this query

select * 
from order mbo 
where  storeId in(select storeid 
                  from store bs 
                  INNER JOIN  distributioncenter bd
                    on bd.distributionCenterId = bs.distributionCenterId            
                  where (bd.orderCloseTime > curtime() and date(bo.orderDate)>=date(now())
                     and bs.storeId=1112211)  
                   or (date(bo.orderDate)>date(now())and bs.storeId=1112211));

And this query is working fine in my local but when in other colleague's system i am getting below issue.

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '>';

What could be the issue?

Akash
  • 816
  • 3
  • 13
  • 38

1 Answers1

0

In your mysql database,"SHOW VARIABLES LIKE 'character_set_%'",make sure all of them are "UTF8"

Leaves.J
  • 1
  • you want me run that command? – Akash Jul 20 '17 at 07:36
  • I mean all the connected table format are in urf8, i have verified it. – Akash Jul 20 '17 at 07:37
  • Also Character_set_client? – Leaves.J Jul 20 '17 at 07:50
  • 'character_set_client', 'utf8' its is in my system but the exception is in prod – Akash Jul 20 '17 at 07:53
  • Not only the table,but the mysql client of your colleague's system and database need to be 'UTF8' – Leaves.J Jul 20 '17 at 08:03
  • CAST worked fine for me. `select * from mb_opsorder mbo where storeId in(select storeid from mb_store mbs INNER JOIN mb_distributioncenter mbd on mbd.distributionCenterId = mbs.distributionCenterId where (CAST(mbd.orderCloseTime As Time) > curtime() and date(mbo.orderDate)>=date(now()) and mbs.storeId=1709) or (date(mbo.orderDate)>date(now())and mbs.storeId=1709));` – Akash Jul 20 '17 at 08:37