After having many issues with ISO-8859-1 and reading a lot about collations I finally understand that I need to use charset UTF8 collate utf8_unicode_ci. So I configure my whole application with these changes and seems to be working smoothly until I needed to execute a manual query

SET @id := '10000016';
SET @invoice := (SELECT invoice FROM WAREHOUSE WHERE invoice_id = @id);

This query is giving me errors in MySQL Workbench with collation message:

Error Code: 1267. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

I can fix it using SET @id := '10000016' collate utf8_unicode_ci;, but this seems weird to me (to say least). I'm sure there's some missing step.

Maybe do I need to tweak something in my.cnf?

Other information

SELECT @@character_set_client; # utf8
SELECT @@character_set_results; # utf8
SELECT @@collation_connection; # utf8_general_ci

Thank you for your time

  • What is the datatype of invoice_id? Where did @tk come from? What version of MySQL are you using?
    – Rick James
    Commented Jul 22, 2016 at 4:35
  • Sorry, I mistype @id with @tk (edit my answer). invoice_id is utf8_unicode_ci. I assume workbench is interpreting the string '10000016' as utf8_general_ci? Commented Jul 22, 2016 at 14:41
  • Apparently that is the case.
    – Rick James
    Commented Jul 22, 2016 at 15:55
  • 5
    See if running SET NAMES utf8 COLLATE utf8_unicode_ci; from workbench will solve the problem (until you logout).
    – Rick James
    Commented Jul 22, 2016 at 18:20
  • 1
    Yes, your sollution SET NAMES utf8 COLLATE utf8_unicode_ci; works. How can I implement this in my.cnf? Thank you @RickJames Commented Jul 22, 2016 at 19:38


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.