Resolving Illegal Mix of Collations Error After Upgrading MySQL 5.7 to 8.0

Problem Description

After upgrading MySQL from 5.7.34 to 8.0.32, certain queries that use FIND_IN_SET fail with the error:

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'find_in_set'

Root Cause

MySQL 8.0 defaults to the collation utf8mb4_0900_ai_ci , while the existing tables were created with utf8mb4_general_ci . The mismatch between the session collation ( collation_connection ) and the table/column collations triggers the error.

Reproduction Steps

Create a test table with the old collation:

CREATE TABLE `t01` (
  `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

Execute the problematic query:

SELECT AAA.* FROM (
  SELECT @xxx AS _xxx,
         (SELECT @xxx := GROUP_CONCAT(A_CODE) FROM t01 WHERE FIND_IN_SET(B_CODE, @xxx)) AS cxxx
  FROM t01,
       (SELECT @xxx := 'xxx') b
  WHERE @xxx IS NOT NULL
) ID, t01 AAA
WHERE FIND_IN_SET(AAA.A_CODE, ID._xxx)
ORDER BY A_CODE;

The query returns the same collation error.

Analysis

Inspecting collations and variables shows the discrepancy:

mysql> SHOW VARIABLES LIKE '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database           | utf8mb4_general_ci |
| collation_server             | utf8mb4_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+

The client session uses utf8mb4_0900_ai_ci while the server/database defaults remain utf8mb4_general_ci . The my.cnf file also confirms the server‑side setting:

[mysqld]
collation_server = utf8mb4_general_ci

Solutions

Adjust Server Parameters Synchronise collation_connection with the server default or disable the client‑handshake (deprecated): [mysqld] skip-character-set-client-handshake Restart MySQL and verify that collation_connection now shows utf8mb4_general_ci .

Alter Table Collations Change the database/table/column collations to the MySQL 8.0 default: ALTER TABLE cjc.t01 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE cjc.t01 MODIFY COLUMN `ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL; ALTER TABLE cjc.t01 MODIFY COLUMN `A_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL; ALTER TABLE cjc.t01 MODIFY COLUMN `B_CODE` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL; After alteration, the query runs without errors.

Rewrite the SQL Statement Explicitly cast the columns to the desired collation within the query: SELECT AAA.* FROM ( SELECT @xxx AS _xxx, (SELECT @xxx := GROUP_CONCAT(A_CODE COLLATE utf8mb4_0900_ai_ci) FROM t01 WHERE FIND_IN_SET(B_CODE COLLATE utf8mb4_0900_ai_ci, @xxx)) AS cxxx FROM t01, (SELECT @xxx := 'xxx') b WHERE @xxx IS NOT NULL ) ID, t01 AAA WHERE FIND_IN_SET(AAA.A_CODE COLLATE utf8mb4_0900_ai_ci, ID._xxx) ORDER BY A_CODE; This approach is suitable for quick fixes without altering schema or server settings.

Conclusion

Three remediation paths are available: (1) modify server parameters for large‑scale upgrades, (2) alter table collations when data volume is manageable, and (3) adjust the SQL for temporary queries. Choose the method that best fits the environment and migration constraints.