I've recently just swapped all of a databases tables and columns to utf8_unicode_ci from the weird collection of about 3-5 different collations they were previously (don't ask... not my database)

So, every table, every column, and the database itself is set to utf8_unicode_ci.

Doing stuff inside of phpmyadmin, everything works fine.

However, switch to mysql workbench, and this is where things get fun. Running a simple select query

select * from table where field like value or field2 like value

results in the following:

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

Now, I'm a bit stumped, from everything I've read, most people get this error if they have columns or tables which are different collations... I've double and triple checked that they are all utf8 unicode.

Someone else mentioned an issue with workbench, but they didn't go into detail of how they fixed it...

Anyone have any ideas?

  • I doubt this has to do with the connection charset. From the error message I'd think that field and field2 have different collations - close but different (general vs. unicode). Double check this. Look at the creation code to see how that is defined. Also, if all your text fields use the table's charset + collation (and all your tables use the schema's charset + collation) you can set them all to use DEFAULT and only set your schema's values explicitly. This way you cannot get a mix by definition. – Mike Lischke Dec 15 '16 at 7:50
  • Table - utf8_unicode_ci, Fields in query - utf8_unicode_ci... Def the same, just double checked then. Also there is no issue when using clients other than workbench to query... the query which breaks on workbench works on phpmyadmin. No creation code for the table that I'm aware of (again, I took over management of this db and it was something of a shambles)... I'll try later the switching to default and see what that does. – Kieran Dec 16 '16 at 4:34
  • Is value a ‘literal‘? – eckes Aug 9 '18 at 21:35

The connection is using a different collation than the fields. That can be fixed with a collation setting in my.cnf and/or a collation on SET NAMES.

  • Would that be the my.cnf settings serverside? if so I'll poke around when I get access to my windows machine. – Kieran Dec 16 '16 at 4:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.