MySQL illegal mix of collations, ASCII to UTF-8

character-setcollationMySQL

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!

Best Answer

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.