7

While testing some code I stumbled on the following MySQL error:

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

I was using a WHERE statement on a standard MySQL UTF-8 collation column which contained a character using 4 bytes. Unless I misunderstood, while reading, I found the following information:

  • MySQL's original UTF-8 implementation was incomplete (supporting maximum 3 bytes)
  • The way to solve this is a new collation called utf8mb4 which by no means a new encoding but only used by MySQL to patch their original mistake.

On my end I see no reasons to use the original MySQL UTF-8 implementation since it's incomplete. So I did a few server side configuration to make sure all defaults were pointing to utf8mb4. Everything seemed fine but now on my application: I can use characters in my form without having to worry about MySQL.

My problem now remains that when I connect with MySQL Workbench, it seems that the encoding is being forced to UTF-8. So even if my application works correctly, if I want to run tests directly in MySQL Workbench, I get the "Illegal mix of collation" error unless I run this fix (in Workbench) after starting the application:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

I found this old question (MySQL Workbench charset) where it seemed impossible to overwrite the setting but even after I spent too much time searching for the config, I cannot believe this is still the case??

Community
  • 1
  • 1
Nicolas Bouvrette
  • 4,295
  • 1
  • 39
  • 53

1 Answers1

2

For now, I'm afraid, you will have to live with that. There's a WL for MySQL to rename that encoding to utf8 (throwing out the existing 3 byte variant). So it makes sense to keep utf8 in MySQL Workbench or we have to use different settings for different servers, which makes things more complicated.

Mike Lischke
  • 48,925
  • 16
  • 119
  • 181
  • Do you know when this UTF8 change is planned? Also I see multiple ways to solve the transitory position. Such having encoding configurable at the connection level. Or, enabling users to run specific commands after connecting to specific servers. Etc... right now it looks like if you want to user proper UTF8, you must manually overwrite the hardcoded connection settings? Why not have a more flexible way to handle this? – Nicolas Bouvrette Sep 21 '15 at 11:48
  • Can't say anything about schedules. For the flexibility: with that also more possibilities arise to make something fail. It's always a trade-off and the simpler something can be the better, at least for the majority of the user base. – Mike Lischke Sep 21 '15 at 12:05
  • Well the "run after connection" seems straight forward enough for common users and flexible enough for power users. Is there a way to make feature requests for Workbench? :) Thanks again for taking the time to answer – Nicolas Bouvrette Sep 21 '15 at 12:11
  • Absolutely, post your feature request in our bug tracker (http://bugs.mysql.com). – Mike Lischke Sep 21 '15 at 14:51