Resolving Illegal Mix of Collations Errors in MySQL 8.0 Views

Problem Background

A customer encountered an error in a MySQL 8.0.21 instance when querying a view, receiving ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' .

Problem Simulation

mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `name1` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

mysql> show create table t2\G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `name2` varchar(12) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> CREATE VIEW t3 as select * from t1,t2 where `t1`.`name1`= `t2`.`name2`;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t3;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

Problem Analysis

Viewing the definition of the view shows MySQL automatically applies CONVERT to align character sets because the two tables use different character sets.

mysql> show create view t3\G;
*************************** 1. row ***************************
                View: t3
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t3` AS select `t1`.`name1` AS `name1`,`t2`.`name2` AS `name2` from (`t1` join `t2`) where (`t1`.`name1` = convert(`t2`.`name2` using utf8mb4))
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci

In MySQL 8.0 the default collation for utf8mb4 is utf8mb4_0900_ai_ci , while table t1 uses utf8mb4_general_ci . Changing session variables does not affect the collation used by CONVERT , which follows the default collation defined in INFORMATION_SCHEMA.COLLATIONS .

mysql> show variables like '%collat%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_general_ci |
| collation_database            | utf8mb4_bin        |
| collation_server              | utf8mb4_bin        |
| default_collation_for_utf8mb4 | utf8mb4_general_ci |
+-------------------------------+--------------------+

Even after setting collation_connection to utf8mb4_general_ci , the query still fails because CONVERT uses the default collation utf8mb4_0900_ai_ci from the metadata.

mysql> select * from t1,t2 where `t1`.`name1`=convert(`t2`.`name2` using utf8mb4);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

The official documentation states that when CONVERT specifies only a character set, the resulting collation is the default collation of that character set, which in this case is utf8mb4_0900_ai_ci .

Problem Summary

To make the query succeed, explicitly specify the collation that matches the left‑hand side column:

mysql> select * from t1,t2 where `t1`.`name1` = convert(`t2`.`name2` using utf8mb4) collate utf8mb4_general_ci;
+-------+-------+
| name1 | name2 |
+-------+-------+
| jack  | jack  |
+-------+-------+

Additional tests confirm that the default_collation_for_utf8mb4 variable influences string literals but not the collation used by CONVERT .

Recommendations

When creating a database instance, set character_set_database and character_set_server to utf8mb4 (default).

For non‑default character sets, always specify both CHARACTER SET and COLLATION in DDL statements.

When using CONVERT , add an explicit COLLATE clause if the target collation differs from the column’s default.

During migration from MySQL 5.7 to 8.0, be aware that the default collation for utf8mb4 changes from utf8mb4_general_ci to utf8mb4_0900_ai_ci , which may cause hidden collation conflicts.