Everything is utf8mb4_unicode_ci but getting Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Question

I'm doing a query through PHP and getting the following error from MySQL:

1267: Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) 
for operation '=')

This is the query, and the value is being read from a json file, json_decode()d, and then passed in for the placeholder. It is Egor. The original text appears to be in utf8, but I also tried converting the input string to utf8 just to be sure and it didn't change the error.

select fcid 
from herd_fieldchange
where 
    fieldvalue = ?

The table definition for herd_fieldchange is:

CREATE TABLE `herd_fieldchange` (
  `fcid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  ...
  `fieldvalue` varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  ...
  PRIMARY KEY (`fcid`),
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

My overall database collation is also utf8mb4_unicode_ci. My table collation is also set to utf8mb4_unicode_ci. My column collation is also set to utf8mb4_unicode_ci.

mysql> show variables like "collation_database";
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_unicode_ci |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> show table status;
+----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+----------------------+
| Name                       | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment              |
+----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+----------------------+
...
| herd_fieldchange           | InnoDB |      10 | Dynamic    | 7402938 |             89 |   662700032 |               0 |   1259978752 |   6291456 |        8143311 | 2023-10-20 14:24:24 | 2023-10-20 14:18:20 | NULL       | utf8mb4_unicode_ci |     NULL |                |                      |
...
+----------------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+----------------------+
47 rows in set (0.00 sec)

mysql> show full columns from herd_fieldchange;
+----------------+------------------+--------------------+------+-----+-------------------+----------------+---------------------------------+---------+
| Field          | Type             | Collation          | Null | Key | Default           | Extra          | Privileges                      | Comment |
+----------------+------------------+--------------------+------+-----+-------------------+----------------+---------------------------------+---------+
| fcid           | int(10) unsigned | NULL               | NO   | PRI | NULL              | auto_increment | select,insert,update,references |         |
...
| fieldvalue     | varchar(2000)    | utf8mb4_unicode_ci | YES  |     | NULL              |                | select,insert,update,references |         |
...
+----------------+------------------+--------------------+------+-----+-------------------+----------------+---------------------------------+---------+
9 rows in set (0.00 sec)

mysql>

All of the other posts on stack overflow say that the solution is to make sure the column and the table and the database have the same utf8mb4_unicode_ci collation... but all of mine do have the correct collation and I'm still getting the error.

What am I missing?

Answer 1

I ran into the exact same problem a couple of days ago, even using the same emoji (while ❤️ has been working just fine). Turned out the charset of the connection had been set to utf8 and should have been set to utf8mb4.