1

I have a MySQL 8.0 table like this, which is UTF-8 apart from one field, which holds an ASCII UUID that doesn't need UTF-8 overhead:

CREATE TABLE `things` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `things_uuid_unique` (`uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and I'm getting query failures like this:

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations
(ascii_bin,IMPLICIT) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='
(SQL: select * from `things` where (`uuid` = 1abb9e11-4f00-4904-988e-233a3c0ce411) limit 1)

My connection is using utf8mb4 and utf8mb4_unicode_ci, the other fields, table, and database are using the same collation, and my scripts themselves are also UTF-8, though that makes no practical difference here since it's just ASCII. This particular query uses a string literal containing only 7-bit ASCII chars, not a field name or variable.

I don't understand why this fails, since (unlike ISO-8859 charsets), ASCII is UTF-8 compatible; there is no ASCII string that is not also a valid UTF-8 string, so why wouldn't this be coercible? Is it something to do with ci vs bin? I can't solve this by switching the connection to ASCII because the real queries use multiple fields, some of which are UTF-8.

I know that I can force the comparison using:

select * from `things` where (`uuid` = BINARY '1abb9e11-4f00-4904-988e-233a3c0ce411')

But that also seems unnecessary, and not something I've needed to do before, in the same circumstances. I guess worst case I could switch the UUID field to UTF-8, but that offends my developer sensibilities!

It's doubly frustrating as I know I've used this pattern successfully many times before!

15
  • 1
    Hi there. I don't have time right to test anything, but I suspect the issue is that MySQL is wanting to coerce the literal into the collation of the column. In that direction, even though in your case there wouldn't be any data loss, if the character set has to be coerced as well, then there could be data loss going from UTF-8 to ASCII. Swap the two character sets and collation and it might could work (just as a test, though). Still, since (as you also pointed out) all ASCII is also UTF-8, why not just use UTF-8 charset for that columnand UTF-8_BIN for the collation? – Solomon Rutzky Mar 23 at 18:01
  • Indeed, I could try to shove a UTF-8 value in that field and I would expect it to fail. The same could be said about UTF-8 ci vs bin collations. UTF-8 has a whole load of processing overhead that's really not needed for simple ASCII fields, and it eats index space too. As I said though, I've done this many times before without issue – this project has been doing this for a couple of years, but only just recently has this error started happening. I'm wondering if there's been some recent change in MySQL that's caused it. – Synchro Mar 23 at 18:27
  • 1
    You did need to quote the 36-character literal. – Rick James Mar 23 at 19:19
  • That's just how MySQL displays errors; it is quoted in the original query. – Synchro Mar 23 at 19:20
  • Synchro: I'm not sure what point you're trying to make regarding UTF-8 ci vs bin, but either way, those are collations, not character sets, so there is no issue of data loss. And sure, non-binary UTF-8 is certainly more complex than non-binary ASCII. But, binary is just that: binary, hence no linguistic rules. There shouldn't be much, if any, overhead for utf8_bin collation. There might be some overhead for the utf8_mb4 charset regarding determination of valid byte sequences, but that shouldn't have a noticeable impact here. (cont.) – Solomon Rutzky Mar 23 at 22:38
2

It turns out that I am doing everything correctly, and I'm not insane! This problem is a bug in MySQL 8.0.22 (also this) that is apparently fixed in 8.0.23. Unfortunately I'm using Percona server which has not yet released its 8.0.23 derivative.

1
  • 1
    Good job finding those bug reports. Hopefully the fix navigates its way to your server sooner than later :-) – Solomon Rutzky Mar 25 at 5:08

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.