11

I need your help in identifying why this error is coming

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

The procedure was working fine till yesterday and today it started giving error of illegal mix of collations. I checked each and every table of my database and all the tables and its columns are in one charset and collation that is:- utf8 utf8_unicode_ci.

I created another schema with same structrue and tried to execute same query it works fine but not on its original schema.

can anyone provide any input on this? Why is so happening ?

bluish
  • 26,356
  • 27
  • 122
  • 180
MySQL DBA
  • 5,692
  • 21
  • 54
  • 71
  • 1
    The actual query and error message would be helpful. – VolkerK Jun 15 '09 at 14:29
  • check my post for this problem. maybe it will work for you. https://stackoverflow.com/questions/11770074/illegal-mix-of-collations-utf8-unicode-ci-implicit-and-utf8-general-ci-implic/71659466#71659466 – pankaj Mar 29 '22 at 10:05

4 Answers4

8

I've had same problem, and i discovered that tables had same collation, but columns did not. You could download mysqlyog trial and in alter table uncheck hide language options.... Quickest way to see.

Radmilo
  • 174
  • 1
  • 10
4

in my case it was due to stupid mysql workbench - it somehow adds to procedure default collation from schema (to some metadata or what) and then if you collate anything in procedure to different collation, this exception occurs;

must set correct (the one which is used in procedure) collation to schema and then to re-edit the procedure and apply changes (must be resaved or recreated) and everything is ok now

meriu
  • 41
  • 1
4

Each parameter of the concat function needs to be converted.

CONCAT(CONVERT(field_A USING utf32), CONVERT(field_B USING utf32))

Or which ever function your having a problem with.

user1644564
  • 385
  • 3
  • 11
0

my problem was the same, but In my case sufficient was using conversion before using sql statement - in php

iconv(...)
Michal - wereda-net
  • 940
  • 9
  • 14