How to Fix MySQL Error 1271: Illegal Mix of Collations for Operation

ERROR 1271 (HY000): Illegal mix of collations for operation '<operation>' is raised when MySQL cannot resolve a single collation from three or more operands in an expression, function call, or UNION query because they carry incompatible collations. The error symbol is ER_CANT_AGGREGATE_NCOLLATIONS.

Impact

The query is aborted immediately and no rows are returned or modified. The error surfaces whenever MySQL's collation coercibility rules cannot elect a single "winning" collation from all participating string operands — typically in multi-argument string functions (CONCAT, COALESCE, ELT, CASE … WHEN … THEN), UNION column lists, or comparisons that involve three or more string values each carrying a different explicit collation.

Developers most often encounter this in ORM-generated queries that mix user-supplied string literals (which inherit the connection collation) with columns from tables that were created with different character set / collation combinations. It also appears after database migrations where tables or individual columns were converted to utf8mb4 but not all at once, leaving a heterogeneous mix of utf8_general_ci, utf8mb4_unicode_ci, and latin1_swedish_ci columns in the same query.

Common Causes

Columns from different tables carry different collations — a JOIN or UNION combines columns declared as utf8_general_ci and utf8mb4_unicode_ci, and a string function or set operation cannot coerce them to one collation.
Explicit COLLATE clauses conflict — a query applies COLLATE utf8mb4_bin to one operand and COLLATE utf8mb4_unicode_ci to another; MySQL treats both as coercibility level 0 (highest precedence) and cannot pick a winner.
UNION result column collations differ — each SELECT branch contributes a column with a different effective collation, so MySQL cannot determine the result set collation.
Stored routine parameters vs. column collations — a procedure or function concatenates an input parameter (inheriting the connection collation, e.g., utf8mb4_general_ci) with columns declared latin1_swedish_ci.
String literals mixed with utf8mb4_bin columns — unadorned string literals coerce to the connection collation; if the connection collation differs from utf8mb4_bin used on several columns, a three-way conflict arises.
Partial utf8mb4 migration — some tables were altered to utf8mb4 but others were not, and a view or query references columns from both generations.

Troubleshooting and Resolution Steps

Identify the conflicting collations. Inspect the collation of each column involved in the failing expression:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME IN ('table_a', 'table_b')
  AND DATA_TYPE IN ('char', 'varchar', 'text', 'mediumtext', 'longtext');

Check the current connection collation (affects string literals and stored routine parameters):
```
SHOW VARIABLES LIKE 'collation_connection';
SHOW VARIABLES LIKE 'character_set_connection';
```

Add a CONVERT … USING or COLLATE clause to cast operands to a common collation inline — useful for a quick fix without schema changes:

-- Failing: mix of utf8_general_ci and utf8mb4_unicode_ci columns
SELECT CONCAT(t1.name, ' ', t2.label) FROM t1 JOIN t2 ON t1.id = t2.t1_id;

-- Fixed: coerce all operands to the same collation
SELECT CONCAT(
    CONVERT(t1.name USING utf8mb4) COLLATE utf8mb4_unicode_ci,
    ' ',
    CONVERT(t2.label USING utf8mb4) COLLATE utf8mb4_unicode_ci
) FROM t1 JOIN t2 ON t1.id = t2.t1_id;

Alter the column(s) to a uniform collation. When you control the schema, the permanent fix is to bring all affected columns in line:

ALTER TABLE t1
  MODIFY COLUMN name VARCHAR(255)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE t2
  MODIFY COLUMN label VARCHAR(255)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Set a consistent database-level default so new tables inherit a single collation:

ALTER DATABASE your_database
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Fix UNION queries by casting each branch explicitly:

SELECT CONVERT(name USING utf8mb4) COLLATE utf8mb4_unicode_ci AS name FROM t1
UNION ALL
SELECT CONVERT(label USING utf8mb4) COLLATE utf8mb4_unicode_ci FROM t2;

Audit stored procedures and functions that accept string parameters — add an explicit COLLATE clause in the routine body or declare parameters with a character set:
```
CREATE PROCEDURE search_users(IN p_term VARCHAR(100) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci)
BEGIN
  SELECT * FROM users WHERE name LIKE p_term;
END;
```

Additional Information

Related error 1267 (ER_CANT_AGGREGATE_2COLLATIONS) covers the two-operand variant of the same problem. Error 1271 is its N-operand generalisation; both share the same root cause and fixes.
Coercibility levels govern which collation wins when operands differ: explicit COLLATE clauses have level 0 (highest), column collations level 2, string literals level 4, and NULL level 5. When two or more level-0 operands carry different collations, neither can win and MySQL raises 1271.
utf8mb4_0900_ai_ci (the default in MySQL 8.0+) is not directly compatible with utf8mb4_unicode_ci or utf8mb4_general_ci at coercibility level 0. Mixing explicit COLLATE utf8mb4_0900_ai_ci with COLLATE utf8mb4_unicode_ci will trigger 1271 on MySQL 8.0.
ORM behavior — Hibernate, ActiveRecord, and SQLAlchemy do not automatically cast collations. When a framework-generated query mixes columns from differently-collated tables, the error propagates as a generic database exception. Enable SQL logging to capture the raw query and identify the conflicting columns.
SET NAMES at connection time sets both character_set_connection and collation_connection; mismatching this against your schema collation is a common source of literal-vs-column conflicts in application code.

Frequently Asked Questions

What is the difference between error 1267 and error 1271? Error 1267 (ER_CANT_AGGREGATE_2COLLATIONS) is raised when exactly two string operands have incompatible collations. Error 1271 (ER_CANT_AGGREGATE_NCOLLATIONS) is the more general form raised when three or more operands are involved. The cause and fixes are identical; MySQL simply uses a different error code based on how many conflicting operands it detected.

Why does this error appear after a partial migration to utf8mb4? When you convert only some tables or columns to utf8mb4, queries that join or union the migrated columns with unmigrated (utf8 / latin1) columns produce operands with different character sets and collations. Because MySQL cannot automatically coerce across character set boundaries in all contexts, it raises this error. The solution is to complete the migration so all involved columns share the same character set and collation.

Can I fix this without altering the table schema? Yes — wrapping each operand in CONVERT(col USING utf8mb4) COLLATE utf8mb4_unicode_ci is a purely query-level fix that requires no DDL changes. This is useful for one-off queries or when you cannot alter the schema immediately, but altering the columns is the more robust long-term solution.

Does the utf8mb4_bin collation cause more conflicts than others? Yes. utf8mb4_bin is case-sensitive and byte-by-byte, so it is never implicitly coercible to a case-insensitive collation. Any expression that mixes utf8mb4_bin columns with utf8mb4_unicode_ci literals or columns, alongside a third collation, will trigger 1271. Prefer a consistent utf8mb4_unicode_ci or utf8mb4_0900_ai_ci across your schema unless binary comparison is explicitly required.